TableAdapters and Transactions again!

[J055]

Hi

I've read various articles/blogs/posts about adding transactions to
TableAdapters, mainly from Sahil Malik. I'm certainly not an expert in
ADO.NET but I understand what transactions, commands and connections are.
Sadly I've been unable to get the examples I found to work.

I want to be able to work with 2 or more TableAdapters within one
transaction. I understand that I need to extend the appropriate TableAdapter
partial classes. This isn't too much of a problem because I only have 10 or
so database tables and TableAdapters to worry about.

I don't think I want to use System.Transaction.TransactionScope. I'm only
concerned with one database. I would like to continue to use TableAdapters
(because I've already invested time implementing for non-transactional
uses).

Another thing: I need to do quite a bit of manipulation of the DataTable
between Filling and Updating using the TableAdapter(s) so it makes sense to
do this in my business logic layer. However, if I need to work with
transactions then the business layer is no longer 'Data Provider neutral',
i.e. in my case, I need to work with the System.Data.SqlClient namespace. I
don't suppose this is a real world problem for me but if a customer did want
to use Oracle with our application then we have to create a new DAL and BLL.

Is there a tried and tested solution out there yet that someone would be
good enough to share?

Thanks
Andrew

 

[Sahil Malik [MVP C#]]

I want to be able to work with 2 or more TableAdapters within one

transaction. I understand that I need to extend the appropriate
TableAdapter partial classes. This isn't too much of a problem because I
only have 10 or so database tables and TableAdapters to worry about.

<---- That would be a good approach.

You could also write something like a factory that ties all TableAdapters in
a transaction.

- Sahil Malik
http://blah.winsmarts.com

 

[J055]

Hi Sahil

I've poked around a bit more to see what the TableAdapter is doing and
managed to get the commands enlisted in a transaction for one TableAdapter.
Now I just need to work out how to add another one.

I took delivery of your ADO.NET book yesterday so I hope to advance my
understanding of this wondrous technology soon.

You could also write something like a factory that ties all TableAdapters
in a transaction.

But this I fear may well be beyond me for a while yet, although I do like
the idea.

Thanks
Andrew

 

[Sahil Malik [MVP C#]]

Andrew,

I hope you like the book - even though I've advocated against TableAdapters
in Chapter 3 for the very reasons you are running into.

Anyway, the idea of factory would be something as simple as creating an
EnlistInTransaction method on each TableAdapter. You could then have each
TableAdapter implement a specific custom interface, and then you could
create a class on top that calls EnlistInTransaction for every instance of
that interface passed in.

This way, you can easily enlist multiple table adapters in one tx.

- Sahil Malik [MVP]
http://blah.winsmarts.com

 

[J055]

Hi

I hope you like the book - even though I've advocated against
TableAdapters in Chapter 3 for the very reasons you are running into.

Yes, the lack of support for transactions has caught me out a bit.

Anyway, the idea of factory would be something as simple as creating an
EnlistInTransaction method on each TableAdapter. You could then have each
TableAdapter implement a specific custom interface, and then you could
create a class on top that calls EnlistInTransaction for every instance of
that interface passed in.

OK. I thought I could loop through a dictionary collection of say
'ITransTableAdapter' interfaces. The business layer can add all the TAs in
and then call them by the key once they have been enlisted in the
transaction.

Can I start a transaction on the first adapter in the loop and then pass it
to each subsequent adapter in the collection? Can you give me an idea of how
the EnlistInTransaction method might look?

Does this sound reasonable? It's a bit messy using keys but I can't think
how else to do it.

I think it's starting to look like a workable plan. Your comments will be
very much appreciated.

Thanks again
Andrew

 

[Sahil Malik [MVP C#]]

OK. I thought I could loop through a dictionary collection of say

'ITransTableAdapter' interfaces. The business layer can add all the TAs in
and then call them by the key once they have been enlisted in the
transaction.

Yep that sounds awrite. (Also see a comment about DbConnectionScope below).

Can I start a transaction on the first adapter in the loop and then pass
it to each subsequent adapter in the collection?

Yeah that should work out just fine.

Can you give me an idea of how the EnlistInTransaction method might look?

It should be very similar to a single table adapter scenario; take in a
transaction object, and a connection object. The connection needs to be
open.

BTW - Alazel Acheson from the MSFT ADO.NET team once blogged about something
called DbConnectionScope; (just search on it), he used an interesting design
pattern that you may find useful as well. His stuff was specific to Sys.Tx
though; but no reason why you couldn't use the same paradigm.

- Sahil Malik [MVP]
http://blah.winsmarts.com

 

[J055]

Hi

I've knocked up some code which I've included here for anyone who might
benefit and also to get comments on how it might be improved.

The main class is called the AdapterTransactor which sits in the DAL. It
adds one or many adapters into a transaction. They must implement the
ITransTableAdapter interface and include the Transaction and OpenConnection
methods. It all seems to work ok although I haven't done much testing. I
like the solution because my BLL remains Data Provider neutral and fairly
uncluttered. The obvious downside is that you have to add the methods to any
adapters which may need to work in a transaction. It is only copying and
pasting though.

Thanks to Sahil for the advice.

Andrew.

public interface ITransTableAdapter
{
SqlConnection OpenConnection();
void Transaction(SqlTransaction tran);
}

public partial class MyTableAdapter : System.ComponentModel.Component,
ITransTableAdapter
{
public SqlConnection OpenConnection()
{
if (_connection == null)
{
InitConnection();
}
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
return _connection;
}

public void Transaction(SqlTransaction trans)
{
// must use the connection from the transaction object
_connection = trans.Connection;

if (_adapter == null)
{
InitAdapter();
}
if (_adapter.InsertCommand != null)
{
_adapter.InsertCommand.Transaction = trans;
}
if (_adapter.DeleteCommand != null)
{
_adapter.DeleteCommand.Transaction = trans;
}
if (_adapter.UpdateCommand != null)
{
_adapter.UpdateCommand.Transaction = trans;
}
if (_adapter.SelectCommand != null)
{
_adapter.SelectCommand.Transaction = trans;
}

if (_commandCollection == null)
{
InitCommandCollection();
}

foreach (SqlCommand cmd in _commandCollection)
{
cmd.Transaction = trans;
}
}
}


public class AdapterTransactor
{
private SqlConnection conn = null;
private SqlTransaction trans = null;
private List<ITransTableAdapter> adapterCollection = new
List<ITransTableAdapter>();
private bool hasStarted = false;

public void AddAdapter(ITransTableAdapter adapter)
{
adapterCollection.Add(adapter);
}

public void BeginTransaction()
{
if (adapterCollection.Count < 1)
{
throw new ArgumentOutOfRangeException("No adapters have been
added.");
}

int counter = 0;
foreach (ITransTableAdapter adapter in adapterCollection)
{
counter++;
if (counter == 1)
{
// open the connection on the first adapter in the list
conn = adapter.OpenConnection();
// begin a transaction on the connection
trans = conn.BeginTransaction();
// call the adapter transaction method to enlist the
command objects
adapter.Transaction(trans);
}
else
{
adapter.Transaction(trans);
}
}
hasStarted = true;
}

public void Commit()
{
if (!hasStarted)
{
throw new ApplicationException("BeginTransaction must be
called before Commit.");
}

try
{
// Commit the transaction
trans.Commit();
}
catch (Exception ex)
{
// Rollback if it all goes wrong
trans.Rollback();
throw (ex);
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
conn.Close();
}
}

// In your BLL, or wherever, you can then create an AdapterTransactor object
and use like this:

AdapterTransactor atx = new AdapterTransactor();

atx.AddAdapter(adapter1);
atx.AddAdapter(adapter2);
atx.AddAdapter(adapter3);

atx.BeginTransaction();

// transactional work on adapters

atx.Commit();

 

[J055]

Oops! I said I hadn't done much testing. You need to add a rollback method
to the AdapterTransactor class.

public void Rollback()
{
// Rollback if it all goes wrong
trans.Rollback();
if (conn.State == System.Data.ConnectionState.Open)
conn.Close();
}

Then put your adapter transactions is a try catch block

AdapterTransactor atx = new AdapterTransactor();

atx.AddAdapter(adapter1);
atx.AddAdapter(adapter2);
atx.AddAdapter(adapter3);

atx.BeginTransaction();

try
{
// transactional work on adapters
atx.Commit();
}
catch (Exception)
{
atx.Rollback();
throw;
}

Otherwise Rollback will never be reached if there's an exception.

Thanks
Andrew

 

[Chris Lim]

I've been looking into ways of making multiple TableAdapters share the
same transaction as well. To avoid having to use a partial class for
every adapter, the following code is a generic method that works on any
table adapter. I have a feeling that dynamically reading properties
like this is a big no-no, but I was wondering what people's thoughts
are? Is the benefit of reducing the amount of code a valid reason for
doing something like this?


public static void SetTransaction(object tableAdapter,
SqlTransaction transaction)
{
// Get the table adapter's type
Type type = tableAdapter.GetType();

// Set the transaction on each command in the adapter's
command collection
PropertyInfo commandsProperty =
type.GetProperty("CommandCollection",

BindingFlags.NonPublic | BindingFlags.Instance);
SqlCommand[] commands =
(SqlCommand[])commandsProperty.GetValue(tableAdapter, null);
foreach (SqlCommand command in commands)
command.Transaction = transaction;

// Set the transaction on the insert, update, and delete
commands in the adapter
PropertyInfo commandsProperty2 =
type.GetProperty("Adapter",

BindingFlags.NonPublic | BindingFlags.Instance);
System.Data.SqlClient.SqlDataAdapter Adapter =
(System.Data.SqlClient.SqlDataAdapter)commandsProperty2.GetValue(tableAdapter,
null);

if (Adapter.InsertCommand != null)
Adapter.InsertCommand.Transaction = transaction;

if (Adapter.UpdateCommand != null)
Adapter.UpdateCommand.Transaction = transaction;

if (Adapter.DeleteCommand != null)
Adapter.DeleteCommand.Transaction = transaction;
}

 

[rekha anand]

Hi Andrew,
I tried your solution as you suggested. My second adapter is failing and my first adapter is still getting updated in the database and not getting rolled back. For example, I have one header row and 3 details row. When the 3rd row of the detail is failing it is not rolling back the entire transaction. Instead a row is added in the header table and 2 rows are added in the detail table.

Please advise as how to overcome this issue.

I am using XSD generated strong type dataset and multi table adapter.I modified the code with partial classes as you suggested.

Please let me know if you need any additional information.

Thanks,
Rekha

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Miha Markic]

You should use transaction..
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

in message news:[email protected]...

 

[moniq]

Hi,

I have tried your solution on my project. There are four tables in sql server 2005 express, that form a tree (table1 has foreign key to table2, table2 to table3, and table3 to table4)

Firstly, I insert into table4, then table3, table2 and last table1. But when inserting into table1, trouble arise as it stated time out

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

When I change the sql table relation (enforce foreign key constraint to "no") for the last table(table1). it works alright, but it's not what I want to ( it didn't check the integrity)

Please advise
Thanks, appriciate all reply

Sincerely,

Moniq

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[moniq]

Hi,

I have tried your solution on my project. There are four tables in sql server 2005 express, that form a tree (table1 has foreign key to table2, table2 to table3, and table3 to table4)

Firstly, I insert into table4, then table3, table2 and last table1. But when inserting into table1, trouble arise as it stated time out

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

When I change the sql table relation (enforce foreign key constraint to "no") for the last table(table1). it works alright, but it's not what I want to ( it didn't check the integrity)

Please advise
Thanks, appriciate all reply

Sincerely,

Moniq

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[moniq angel]

Hi,

I have tried your solution on my project. There are four tables in sql server 2005 express, that form a tree (table1 has foreign key to table2, table2 to table3, and table3 to table4)

Firstly, I insert into table4, then table3, table2 and last table1. But when inserting into table1, trouble arise as it stated time out

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

When I change the sql table relation (enforce foreign key constraint to "no") for the last table(table1). it works alright, but it's not what I want to ( it didn't check the integrity)

Please advise
Thanks, appreciate all reply

Sincerely,

Moniq

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com