?
Solved

Inserting Record into multiple table

Posted on 2012-08-30
12
Medium Priority
?
485 Views
Last Modified: 2012-09-20
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

0
Comment
Question by:crystal_Tech
  • 7
  • 5
12 Comments
 
LVL 2

Expert Comment

by:ransommule
ID: 38350992
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38351566
<< 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..?
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38351628
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..?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Expert Comment

by:ransommule
ID: 38351702
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 --
0
 
LVL 2

Expert Comment

by:ransommule
ID: 38351718
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();
 }
0
 
LVL 2

Expert Comment

by:ransommule
ID: 38351837
        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

0
 
LVL 2

Expert Comment

by:ransommule
ID: 38351861
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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38351914
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

0
 
LVL 2

Expert Comment

by:ransommule
ID: 38352076
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.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38352275
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);
0
 
LVL 2

Accepted Solution

by:
ransommule earned 1500 total points
ID: 38352363
Every table has the same structure ?

It it so, you could bind three datatables to the databases and import rows.

It is easier.

something like

DataAdapter objDALocal;
DataAdapter objDA1;
DataAdapter objDA2;

<fill datadapters>

foreach datarow  objDR in objDALocal.tables("Table").rows {
objDA1.tables("Table1").importRow(objDR)
objDA1.tables("Table2").importRow(objDR)
}

objDA1.Update(dsPubs, "Table1")
objDA1.Update(dsPubs, "Table1")

http://support.microsoft.com/kb/301248/en-us

It wont compile above code, but i think the idea is that.

Sorry, I have to go.

Cya and good luck.
0
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 38419338
idea was ok
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Loops Section Overview

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question