Transactions + multiple table adapters

[Mark Baldwin]

I am using SQL2000 and .NET 2.0

I have a dataset, with two DataTables each having a TableAdapter. I need to
use the insert command on the first TableAdapter and then execute a stored
procedure on the second table adapter - all within a transaction.

Now I want to use datasets, because they auto generate the code which is
exactly the same as I intend to manually create anyway and they have the
advantage that I can quickly refresh them if any columns change etc

All of my database operations are in stored procedures in the server, but
accessed through the TableAdapter.

Problem is that each TableAdapter uses it's own connection. I have tried
creating my own connection and transaction and assigning the connection to
the TableAdapter but I just get an error when executing a stored
procedure...

[System.InvalidOperationException] = {"ExecuteScalar requires the command to
have a transaction when the connection assigned to the command is in a
pending local transaction. The Transaction property of the command has not
been initialized."}

Anyway, I have tried to wrap these operations in a TransactionScope but this
promotes to a DTC transaction with SQL2000 which isn't acceptable.

Surely there is straightforward way in which you can utilize datasets to
load data from the database and then tableadapters to run any operations
through predetermind stored procedures.

Any help much appreciated...
Mark

 

[WenYuan Wang [MSFT]]

Hi Mark and Bruno,

By the way, due to some performance issue, I'm afraid reflection is not a
good choice for us. However, have you consider adding a partial class to
expose the DataAdapter? So that we can access DataAdapter out of
TableAdapter namesapace and set the transaction to its commands.

For example: (This is a sample, you should have to modify it according to
your scenario.)
namespace WebApplication3.TestDataBase20060908DataSetTableAdapters{
partial class Table_1TableAdapter
{
public System.Data.SqlClient.SqlDataAdapter getDataAdapter()
{
return this.Adapter;
}
}
}

Additionally, expose a method to set transcation,
public void setTransactions(System.Data.SqlClient.SqlTransaction st)
{
this.Adapter.SelectCommand.Transaction = st;
this.Adapter.UpdateCommand.Transaction = st;
this.Adapter.InsertCommand.Transaction = st;
this.Adapter.DeleteCommand.Transaction = st;
return;
}

Hope this helps,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Bruno Piovan]

Mark,
I solved this problem by doing the following:

1 - Within the same project as the dataset, you can get a reference to each
TA's connection by using the Connection property. Get a reference for a
connection of any TA and set it to all the others TA related
2 - Each TA have an Adapter property that returns a DataAdapter, but this
property is private, so I use reflection to get access to this property to
set the transaction to its commands, I also do the same with the
CommandCollection that is protected. The clode is below

Public Shared Sub SetTransaction(ByVal tableAdapter As Object, ByVal
sqlTransaction As SqlTransaction)
Dim adapterProperty As PropertyInfo =
tableAdapter.GetType.GetProperty("Adapter", Reflection.BindingFlags.Instance
Or Reflection.BindingFlags.NonPublic)
Dim adapter As SqlDataAdapter =
CType(adapterProperty.GetValue(tableAdapter, Nothing),
SqlClient.SqlDataAdapter)

If adapter.InsertCommand IsNot Nothing Then
adapter.InsertCommand.Transaction = sqlTransaction
If adapter.UpdateCommand IsNot Nothing Then
adapter.UpdateCommand.Transaction = sqlTransaction
If adapter.DeleteCommand IsNot Nothing Then
adapter.DeleteCommand.Transaction = sqlTransaction

Dim CommandCollectionProperty As PropertyInfo =
tableAdapter.GetType.GetProperty("CommandCollection",
Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic)
Dim commandCollection() As SqlCommand =
CType(CommandCollectionProperty.GetValue(tableAdapter, Nothing),
SqlCommand())
For Each command As SqlCommand In commandCollection
command.Transaction = sqlTransaction
Next
End Sub

It works very well to me, I used to create methods for all my TAs for this
before, but it was boring as each TA required this, so I came up with this
solution that works great. Let me know if it works for you.

Bruno

I am using SQL2000 and .NET 2.0

I have a dataset, with two DataTables each having a TableAdapter. I need
to use the insert command on the first TableAdapter and then execute a
stored procedure on the second table adapter - all within a transaction.

Now I want to use datasets, because they auto generate the code which is
exactly the same as I intend to manually create anyway and they have the
advantage that I can quickly refresh them if any columns change etc

All of my database operations are in stored procedures in the server, but
accessed through the TableAdapter.

Problem is that each TableAdapter uses it's own connection. I have tried
creating my own connection and transaction and assigning the connection to
the TableAdapter but I just get an error when executing a stored
procedure...

[System.InvalidOperationException] = {"ExecuteScalar requires the command
to have a transaction when the connection assigned to the command is in a
pending local transaction. The Transaction property of the command has
not been initialized."}

Anyway, I have tried to wrap these operations in a TransactionScope but
this promotes to a DTC transaction with SQL2000 which isn't acceptable.

Surely there is straightforward way in which you can utilize datasets to
load data from the database and then tableadapters to run any operations
through predetermind stored procedures.

Any help much appreciated...
Mark

 

[Bruno Piovan]

Hi Wen,
I used to to this way.... but as I have lots of dt/ta it's pretty boring to
create partial classes to each ta... so I use the reflection way.... the
system I'm working on won't have lots of data access at once by lots of
user, so it's not a problem to me, otherwise I would consider partial clsses
anyway.

thanks!
Bruno