DataAdapter.Update with transactions

[Guest]

Hello

I need some help with my code. So far, I've a typed Dataset, fill it with some data from the SQL server and edit it. Now, I want to save my changes back to the server. I'm using DataAdapter.Update for that. That works great. Next I want to wrap the update in a transaction, here's my code so far

private SqlDataAdapter GetAdapter(string tableName, SqlConnection conn

SqlDataAdapter adapter = new SqlDataAdapter(String.Format("SELECT {0} FROM {1} WHERE CustomerID={2};", FieldNames(tableName), tableName, customer), conn)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter)
adapter.SelectCommand = new SqlCommand(String.Format("SELECT {0} FROM {1};", FieldNames(tableName), tableName), conn)
adapter.DeleteCommand = commandBuilder.GetDeleteCommand()
adapter.InsertCommand = commandBuilder.GetInsertCommand()
adapter.UpdateCommand = commandBuilder.GetUpdateCommand()

return adapter


public void SaveChanges(CustomerData dataset)

SqlConnection conn = OpenConnection()
SqlTransaction transaction = conn.BeginTransaction()
try

IDictionary tables= GetTables(dataset)
foreach (string tableName in tables.Keys)

SqlDataAdapter adapter = GetAdapter(tableName, conn)
adapter.Update((DataTable) tables[tableName])

transaction.Commit()

catch (Exception ex)

transaction.Rollback()
throw ex

finally

conn.Close()



Now, the Update method throws an exception saying that the command's transaction property is not initialized
I already tried adapter.xxxCommand.Transaction = transaction, but that didn't help

Thank

Daniel

 

[Marina]

You needed to set the SelectCommand's transaction property. Then the
commandbuilder will know to use the transaction for the other ones.

Also, why are you explicitly assigning the DeleteCommand, InsertCommand and
UpdateCommand of the adapter by calling the command builder's methods? You
can just set the select command , create the command builder, and be done
with it.

Also, I see that you are resetting the SelectCommand property in the adapter
after creating the command builder. I believe only the first select
statement is actually used, as you are not having the commandbuilder
refresh.

Hello,

I need some help with my code. So far, I've a typed Dataset, fill it with

some data from the SQL server and edit it. Now, I want to save my changes
back to the server. I'm using DataAdapter.Update for that. That works great.
Next I want to wrap the update in a transaction, here's my code so far:

private SqlDataAdapter GetAdapter(string tableName, SqlConnection conn)
{
SqlDataAdapter adapter = new SqlDataAdapter(String.Format("SELECT {0} FROM

{1} WHERE CustomerID={2};", FieldNames(tableName), tableName, customer),
conn);

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
adapter.SelectCommand = new SqlCommand(String.Format("SELECT {0} FROM

{1};", FieldNames(tableName), tableName), conn);

adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
adapter.InsertCommand = commandBuilder.GetInsertCommand();
adapter.UpdateCommand = commandBuilder.GetUpdateCommand();

return adapter;
}

public void SaveChanges(CustomerData dataset)
{
SqlConnection conn = OpenConnection();
SqlTransaction transaction = conn.BeginTransaction();
try
{
IDictionary tables= GetTables(dataset);
foreach (string tableName in tables.Keys)
{
SqlDataAdapter adapter = GetAdapter(tableName, conn);
adapter.Update((DataTable) tables[tableName]);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}

Now, the Update method throws an exception saying that the command's

transaction property is not initialized.