Geek asks and answers

Updating a database with changes made to a DataTable ... confusion

If I populate the DataTable with DataAdapter.Fill(DataTable);, and then make changes to the row in the DataTable with something simple: DataTable.Rows[0]["Name"] = "New Name";how can I easily save these changes back to the database? I suggested that I can name it DataAdapter.Update(DataTable);, but I read that it only works with the TableAdapter (?).

+5
source share
2 answers

Here is a real helpful answer if someone else needs to know how to do this:

string selectStatement = "SELECT * FROM Contact";
System.Data.DataTable dt = new System.Data.DataTable();
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
conn.Open();
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = new SqlCommand(selectStatement, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(sqlDa);
sqlDa.Fill(dt);
dt.Rows[0]["Name"] = "Some new data here";
sqlDa.UpdateCommand = cb.GetUpdateCommand();
sqlDa.Update(dt);
+22
source

Pay attention to SqlBulkCopyOptions.KeepIdentity, you may or may not want to use this for your situation.

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }
      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}
0
source

More articles:


All Articles