transaction handling in dataset based insert/update in c#
- by user3703611
I am trying to insert bulk records in a sql server database table using dataset. But i am unable to do transaction handling. Please help me to apply transaction handling in below code.
I am using adapter.UpdateCommand.Transaction = trans; but this line give me an error of Object reference not set to an instance of an object.
Code:
string ConnectionString = "server=localhost\\sqlexpress;database=WindowsApp;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable);
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Test ORDER BY Id", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.UpdateCommand.Transaction = trans;
// Create a dataset object
DataSet ds = new DataSet("TestSet");
adapter.Fill(ds, "Test");
// Create a data table object and add a new row
DataTable TestTable = ds.Tables["Test"];
for (int i=1;i<=50;i++)
{
DataRow row = TestTable.NewRow();
row["Id"] = i;
TestTable .Rows.Add(row);
}
// Update data adapter
adapter.Update(ds, "Test");
trans.Commit();
conn.Close();
