Insert datatable to SQL database

Feb 16 2009 8:54 PM
I am trying to insert a datatable which holds the changes to a datagridview into a SQL table. I have tried a "foreach" loop and that displays the test column data (one by one), but the insert statement only plugs in the last row of data to the SQL table. This is the wrong path to take apparently. Any ideas would be appreciated. "ds1" is the original dataset that displays in the gridview.

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            DataTable ds1changes = ds1.Tables[0].GetChanges();
            if (ds1changes != null)
            {
                SqlConnection dbConn = new SqlConnection(appString.ToString());
                SqlCommand dbCommand = new SqlCommand();               
                dbCommand.Connection = dbConn;
                foreach (DataRow row in ds1changes.Rows)
                {               
                    dbCommand.CommandText =
                    "BEGIN TRANSACTION " +               
                    "DELETE ACE_SALE_CONTRACT_UPDATE " +               
                    "COMMIT " +

                    "BEGIN " +
                    "INSERT INTO ACE_SALE_CONTRACT_UPDATE " +
                    "(ITEM_NO) " +
                    "SELECT '" + Convert.ToString(row["ITEM_NO"]) + "' AS ITEM_NO END";
                   
                    MessageBox.Show(row["ITEM_NO"].ToString());
                    dbCommand.Connection.Open();
                    dbCommand.ExecuteNonQuery();
                                
                if (dbCommand.Connection.State != ConnectionState.Closed)
                {
                    dbCommand.Connection.Close();
                }

Answers (3)