asked on

Inserting Record into multiple table

i want to insert a record from local_table to two remote tables
following code is working fine for One Remote Table,
but i want to insert same record into Second Remote Table..

Please assist me.

string _sql = "Insert Into Remot_Table_One Values('" + _RTO1 + "','" + _RTO2 + "','" + _RTO3 + "','" + _RTO4 + "','" + _RTO5 + "')";
                    
string _update = "update Remot_Table_One set Field2='" + _RTO2 + "',Field3='" + _RTO3 + "',FIELD4='" + _RTO4 + "',FIELD5='" + _RTO5 + "' where Field1='" + _RTO1 + "'";

if (server.State == ConnectionState.Open)
server.Close();

server.Open();
SqlCommand servercm = new SqlCommand("Select * From One_Table where Field1='" + _RTO1 + "' ", server);

SqlDataAdapter daserver = new SqlDataAdapter(servercm);
DataSet dsserver = new DataSet();
daserver.Fill(dsserver);
server.Close();

if (dsserver.Tables[0].Rows.Count != 0)
{
   server.Open();
          SqlCommand cmdinsert = new SqlCommand(_update, server);
          _rec = cmdinsert.ExecuteNonQuery();
   server.Close();
////Deleting record from local
if (_rec > 0)
{
local.Open();
OleDbCommand Bcmd = new OleDbCommand("Delete from Local_Table where Field1=" + _RTO1 + " ", local);
int res = Bcmd.ExecuteNonQuery();
local.Close();
}
}
else
{
if (server.State == ConnectionState.Open)
server.Close();
server.Open();
SqlCommand cmdinsert = new SqlCommand(_sql, server);
_rec = cmdinsert.ExecuteNonQuery();
server.Close();
if (_rec > 0)
{
local.Open();
OleDbCommand Bcmd = new OleDbCommand("Delete  from Local_Table where Field1=" + _RTO1 + "", local);
int res = Bcmd.ExecuteNonQuery();
local.Close();
}
}
    dsserver.Clear();
 }

Open in new window

ransommule

If the record exists in Remote Table One and not exists in remote Table Two, I think you are trying to update a non existant record.
I think you are never inserting the second record.

Try to reorganice your code in a manner like

Main Function

objRecord = GetLocalRecord()

InsertRecord(objRecord, Server1);
InsertRecord(objRecord, Server2);

DeleteLocalRecord(objRecord)

----
InsertRecord function has this structure

if (existsRecord)
update
else
insert

----
Finally your code is sensible to SQL Injection
http://en.wikipedia.org/wiki/SQL_injection

Altaf Patni

ASKER

<< Finally your code is sensible to SQL Injection >>

so you mean my code is dangerous for my remote server..?
or may be i am not clear..?
you mean some one can hack my server database..?

Altaf Patni

ASKER

my above mentioned code is just working fine to insert record into one table
but i want to insert same record into another table.

if records is allready in remote_table then it is updating my record. and if not then inserting it.
and after this opertion .. deleting this record from local_Table.

So my question is how can i insert this same record into another remote table using above code.. before delete this record from local database..
Or..
Do i need to write same code using another function..?

ransommule

Yes.. .i mean your code is suceptible to be hacked if you allow an user to input _RTOn vars.

You could use stored procedures to avoid this or do some string handling to _RTOn vars like

_RTOn = _RTOn.Replace(" ' ",string.empty) -->

One of the keys is not allowing a user to input ' or --

ransommule

You mean something like this ?

string _sql = "Insert Into Remot_Table_One Values('" + _RTO1 + "','" + _RTO2 + "','" + _RTO3 + "','" + _RTO4 + "','" + _RTO5 + "')";

string _sql2 = "Insert Into Remot_Table_One Values('" + _RTO1 + "','" + _RTO2 + "','" + _RTO3 + "','" + _RTO4 + "','" + _RTO5 + "')";

string _update = "update Remot_Table_One set Field2='" + _RTO2 + "',Field3='" + _RTO3 + "',FIELD4='" + _RTO4 + "',FIELD5='" + _RTO5 + "' where Field1='" + _RTO1 + "'";

string _update2 = "update Remot_Table_One set Field2='" + _RTO2 + "',Field3='" + _RTO3 + "',FIELD4='" + _RTO4 + "',FIELD5='" + _RTO5 + "' where Field1='" + _RTO1 + "'";

if (server.State == ConnectionState.Open)
server.Close();

server.Open();
SqlCommand servercm = new SqlCommand("Select * From One_Table where Field1='" + _RTO1 + "' ", server);

SqlDataAdapter daserver = new SqlDataAdapter(servercm);
DataSet dsserver = new DataSet();
daserver.Fill(dsserver);
server.Close();

if (dsserver.Tables[0].Rows.Count != 0)
{
server.Open();
SqlCommand cmdinsert = new SqlCommand(_update, server);
_rec = cmdinsert.ExecuteNonQuery();

SqlCommand cmdinsert = new SqlCommand(_update2, server);
_rec = cmdinsert.ExecuteNonQuery();
server.Close();
////Deleting record from local
if (_rec > 0)
{
local.Open();
OleDbCommand Bcmd = new OleDbCommand("Delete from Local_Table where Field1=" + _RTO1 + " ", local);
int res = Bcmd.ExecuteNonQuery();
local.Close();
}
}
else
{
if (server.State == ConnectionState.Open)
server.Close();
server.Open();
SqlCommand cmdinsert = new SqlCommand(_sql, server);
_rec = cmdinsert.ExecuteNonQuery();

SqlCommand cmdinsert = new SqlCommand(_sql2, server);
_rec = cmdinsert.ExecuteNonQuery();
server.Close();
if (_rec > 0)
{
local.Open();
OleDbCommand Bcmd = new OleDbCommand("Delete from Local_Table where Field1=" + _RTO1 + "", local);
int res = Bcmd.ExecuteNonQuery();
local.Close();
}
}
dsserver.Clear();
}

ransommule

        public struct RTO {
            #region Properties
            private int _intRTO1;
            private string _strRTO2;
            private string _strRTO3;
            private string _strRTO4;
            private string _strRTO5;

            public int RTO1 {
                get { return _intRTO1; } 
            }

            public string RTO2 {
                get { return RemoveDangerousChars(_strRTO2); }
            }

            public string RTO3 {
                get { return RemoveDangerousChars(_strRTO3); }
            }

            public string RTO4 {
                get { return RemoveDangerousChars(_strRTO4); }
            }

            public string RTO5 {
                get { return RemoveDangerousChars(_strRTO5); }
            }

            #endregion

            #region Constructor
            public RTO(int intRTO1, string strRTO2, string strRTO3, string strRTO4, string strRTO5) {
                _intRTO1 = intRTO1;
                _strRTO2 = strRTO2;
                _strRTO3 = strRTO3;
                _strRTO4 = strRTO4;
                _strRTO5 = strRTO5;
            }
            #endregion

            #region Private methods
            private string RemoveDangerousChars(string strChar) {
                strChar = strChar.Replace("'", string.Empty);
                strChar = strChar.Replace("-", string.Empty);

                return strChar;
            }
            #endregion
        }

        public void UpdateRecords() { 
            RTO objData = new RTO(_RTO1,_RTO2 ,_RTO3 ,_RTO4 ,_RTO5);

            if (server.State != ConnectionState.Open) {
                server.Open();
            }
            if (local.State != ConnectionState.Open) {
                server.Open();
            }

            InsertOrUpdate(objData, "Remot_Table_One", server);
            InsertOrUpdate(objData, "Remot_Table_TWO", server);

            Delete(objData, "LocalTable", local);


            if (server.State == ConnectionState.Open) {
                server.Close();
            }

            if (local.State == ConnectionState.Open) {
                server.Close();
            }
        }

        public void InsertOrUpdate(RTO objData, string strTable, SqlConnection objServer) {
            if (Exists(objData, strTable, server)) {
                Update(objData, strTable, server);
            } else {
                Insert(objData, strTable, server);
            }
        }
        
        private bool Exists(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;
            SqlCommand objCommand = null;
            object objResult = null;
            bool boolExists = false;
            strSQL = string.Format("SELECT COUNT(*) FROM {0} WHERE Field1 = {1}", strTable, objData.RTO1);

            objCommand = new SqlCommand(strSQL, objServer);
            objResult = objCommand.ExecuteScalar();
            objCommand.Dispose();

            if (objResult != null && Convert.ToInt32(objResult) > 0) {
                boolExists = true;
            }

            return boolExists;
        }

        private void Insert(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;
            SqlCommand objCommand = null;

            strSQL = string.Format("INSERT INTO {0} (Field1, Field2, Field3, Field4, Field5) VALUES ({1},'{2}','{3}','{4}','{5}')", strTable, objData.RTO1, objData.RTO2, objData.RTO3, objData.RTO4, objData.RTO5);

            objCommand = new SqlCommand(strSQL, objServer);
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
        }  
 
        private void Update(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;
            SqlCommand objCommand = null;

            strSQL = string.Format("UPDATE {0} SET Field2 = '{2}', Field3 = '{3}', Field4 = '{4}', Field5 = '{5}' WHERE Field1 = {1}", strTable, objData.RTO1, objData.RTO2, objData.RTO3, objData.RTO4, objData.RTO5);

            objCommand = new SqlCommand(strSQL, objServer);
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
        } 

        private void Delete(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;
            SqlCommand objCommand = null;

            strSQL = string.Format("DELETE FROM {0} WHERE Field1 = {1}", strTable, objData.RTO1);

            objCommand = new SqlCommand(strSQL, objServer);
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
        }

Open in new window

ransommule

A bit better solution...

        public struct RTO {
            #region Properties
            private int _intRTO1;
            private string _strRTO2;
            private string _strRTO3;
            private string _strRTO4;
            private string _strRTO5;

            public int RTO1 {
                get { return _intRTO1; } 
            }

            public string RTO2 {
                get { return RemoveDangerousChars(_strRTO2); }
            }

            public string RTO3 {
                get { return RemoveDangerousChars(_strRTO3); }
            }

            public string RTO4 {
                get { return RemoveDangerousChars(_strRTO4); }
            }

            public string RTO5 {
                get { return RemoveDangerousChars(_strRTO5); }
            }

            #endregion

            #region Constructor
            public RTO(int intRTO1, string strRTO2, string strRTO3, string strRTO4, string strRTO5) {
                _intRTO1 = intRTO1;
                _strRTO2 = strRTO2;
                _strRTO3 = strRTO3;
                _strRTO4 = strRTO4;
                _strRTO5 = strRTO5;
            }
            #endregion

            #region Private methods
            private string RemoveDangerousChars(string strChar) {
                strChar = strChar.Replace("'", string.Empty);
                strChar = strChar.Replace("-", string.Empty);

                return strChar;
            }
            #endregion
        }

        public void UpdateRecords() { 
            RTO objData = new RTO(_RTO1,_RTO2 ,_RTO3 ,_RTO4 ,_RTO5);

            if (server.State != ConnectionState.Open) {
                server.Open();
            }
            if (local.State != ConnectionState.Open) {
                server.Open();
            }

            InsertOrUpdate(objData, "Remot_Table_One", server);
            InsertOrUpdate(objData, "Remot_Table_TWO", server);

            Delete(objData, "Remot_Table_TWO", local);


            if (server.State == ConnectionState.Open) {
                server.Close();
            }

            if (local.State == ConnectionState.Open) {
                server.Close();
            }
        }

        public void InsertOrUpdate(RTO objData, string strTable, SqlConnection objServer) {
            if (Exists(objData, strTable, server)) {
                Update(objData, strTable, server);
            } else {
                Insert(objData, strTable, server);
            }
        }
        
        private bool Exists(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;
            SqlCommand objCommand = null;
            object objResult = null;
            bool boolExists = false;
            strSQL = string.Format("SELECT COUNT(*) FROM {0} WHERE Field1 = {1}", strTable, objData.RTO1);

            objCommand = new SqlCommand(strSQL, objServer);
            objResult = objCommand.ExecuteScalar();
            objCommand.Dispose();

            if (objResult != null && Convert.ToInt32(objResult) > 0) {
                boolExists = true;
            }

            return boolExists;
        }

        private void Insert(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;

            strSQL = string.Format("INSERT INTO {0} (Field1, Field2, Field3, Field4, Field5) VALUES ({1},'{2}','{3}','{4}','{5}')", strTable, objData.RTO1, objData.RTO2, objData.RTO3, objData.RTO4, objData.RTO5);

            ExecuteNonQuery(strSQL, objServer);
        }  
 
        private void Update(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;

            strSQL = string.Format("UPDATE {0} SET Field2 = '{2}', Field3 = '{3}', Field4 = '{4}', Field5 = '{5}' WHERE Field1 = {1}", strTable, objData.RTO1, objData.RTO2, objData.RTO3, objData.RTO4, objData.RTO5);

            ExecuteNonQuery(strSQL, objServer);
        } 

        private void Delete(RTO objData, string strTable, SqlConnection objServer) {
            string strSQL = string.Empty;
            
            strSQL = string.Format("DELETE FROM {0} WHERE Field1 = {1}", strTable, objData.RTO1);

            ExecuteNonQuery(strSQL, objServer);
        }

        private void ExecuteNonQuery(string strSQL, SqlConnection objServer) {
            SqlCommand objCommand = null;

            objCommand = new SqlCommand(strSQL, objServer);
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
        }

Open in new window

Altaf Patni

ASKER

one more thing i want to clear is i am newbie for C#,
and another thing is above code is just a part for a large function.

and _RTO, _RTO1, _RTO2 etc... is

string _RTO = dr["MyFieldName"].ToString();
string _RTO1 = dr["MyFieldName1"].ToString();
string _RTO2 = dr["MyFieldName2"].ToString();
etc...

and following lines of code is not the part of exists code , i just tried to insert record into another remote table... so i just tried it

string [b]_sql2[/b] = "Insert Into Remot_Table_One Values('" + _RTO1 + "','" + _RTO2 + "','" + _RTO3 + "','" + _RTO4 + "','" + _RTO5 + "')";

Open in new window

string [b]_update2[/b] = "update Remot_Table_One set Field2='" + _RTO2 + "',Field3='" + _RTO3 + "',FIELD4='" + _RTO4 + "',FIELD5='" + _RTO5 + "' where Field1='" + _RTO1 + "'";

Open in new window

SqlCommand cmdinsert = new SqlCommand[b](_update2, server);[/b]
          _rec = cmdinsert.ExecuteNonQuery();

Open in new window

SqlCommand cmdinsert = new SqlCommand[b](_sql2, server);[/b]
_rec = cmdinsert.ExecuteNonQuery();

Open in new window

ransommule

One thing I think you have to take care is that the pourpose of ONE function is do ONE (only one) thing.

The idea is divide a big problem into smaller ones, and these into more smallers and so on.

Look/try the above code.

Altaf Patni

ASKER

ok i have made my mind to use your code into my application..
But
There is 65 fields.
some of fields contain Date value..
some of fields contain Integer value..

i am handling Date fields like this
DateTime _RTO1 = Convert.ToDateTime("1/1/1900 12:00:00 AM", cultEnUs);

ASKER CERTIFIED SOLUTION

ransommule

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Altaf Patni

ASKER

idea was ok