TableAdapter and Transactions

[Jonathan Seidner]

I saw a lot of people were having a problem with this and I found an
elegent solution to
this problem so I decided to post it.

Use the following Example:
---------------------------------------

private void BindingNavigator_OnSaveItemClick(object sender,
EventArgs e)
{

OleDbConnection Connection = new
OleDbConnection(Properties.Settings.Default.ConnectionString);
OleDbTransaction Transaction = null;

try
{
Connection.Open();

Transaction = Connection.BeginTransaction();

this.SaveClients(Connection, Transaction);

Transaction.Commit();
}
catch (System.Exception ex)
{
MessageBox.Show("An error has occured. Changes will be
rolled back" + "\n" + ex.Message);

Transaction.Rollback();
}
finally
{
Connection.Close();
}

}


private void SaveClients(OleDbConnection Connection,
OleDbTransaction Transaction)
{
try
{
if (clientsTableAdapter.Connection != Connection)
{
clientsTableAdapter.Connection = Connection;

OleDbDataAdapter Adapter =
(OleDbDataAdapter)clientsTableAdapter.GetType().GetProperty("Adapter",
BindingFlags.Instance |
BindingFlags.NonPublic).GetValue(clientsTableAdapter, null);

Adapter.UpdateCommand.Transaction = Transaction;
Adapter.DeleteCommand.Transaction = Transaction;
Adapter.InsertCommand.Transaction = Transaction;
}


this.clientsTableAdapter.Update(Properties.Settings.FastForwardDataSet);
}
catch (System.Data.DBConcurrencyException ex)
{

ClientsConcurrencyManager.ResolveConcurrencyIssue(ex.Row);

this.SaveClients(Connection, Transaction);
}

}


Actually, the only part you need is this:
OleDbDataAdapter Adapter =
(OleDbDataAdapter)clientsTableAdapter.GetType().GetProperty("Adapter",
BindingFlags.Instance |
BindingFlags.NonPublic).GetValue(clientsTableAdapter, null);

NOTE: you should also add "using reflection;" in order for this to
work.

This will give you the Adapter property of the TableAdapter and then
you can do whatever you'd like with it...
no need to write partial classes for all your tableadapters, no need to
worry about code changes done by
Visual studio typed datasets wizard, thank god for reflection!

Works perfect for me!

Jonathan

 

[Sahil Malik [MVP C#]]

What is wrong with this one? -
http://codebetter.com/blogs/sahil.malik/archive/2005/10/19/133309.aspx

Not arguing better or worse - just wondering plusses and minuses of either


- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Jonathan Seidner]

well, there's nothing wrong with what you wrote, but it's a lot of
hassle extending every tableadapter and wrapping it with a partial
class,
I have 40 tables, I'm not going to write a partial class extending each
and
every one of those 40 tableadapters.

This way you don't have to worry about writing further code,
it's as if the Adapter property of the tableadapter wasn't private at
all.

Jonathan.

 

[Sahil Malik [MVP C#]]

Well you don't quite have to do it for every single one of them. Once you
write one, the others are practically copy paste - even better a tool could
be written to do that.

Though I gotta ask - why doesn't that happen by default?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Jonathan Seidner]

Yes, of course, but copy-paste 40 of those? why write a tool, when you
have a generic alternative like this?

I don't know why they did it, I can't possibly even begin to
understand, this
problem is soooo annoying!