DataTableAdapters and transactions

[Frank Hauptlorenz]

Hi together,

I have a DataTableAdapter with different configured insert and update
commands.
Now I have to fire some of theses inside an transaction. For this I must
provide the transaction to the underlying SqlCommands.

I can't see how this can be done, because the commands are defined as
internal.
As a workaround I fire direct sql statements "START TRANSACTION" and so
on against the database.

Is there any better solution?

Thanks,
Frank

 

[Jialiang Ge [MSFT]]

Hello Frank

Here are some ideas that you can consider.

1. To access the internal SqlCommands of DataTableAdapter, you can use .NET
Reflection.

2. Consider using TransactionScope in .NET 2.0.
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope
aspx
This automatically makes a code block transactional.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[HEX [MCPD]]

Hi

are you using SP in Adapter?

why not to implement transaction in your SP and not to touch .NET

may be distributed transaction can help?

 

[Frank Hauptlorenz]

Hi Jialing,

I build myself a workaround by defining an extension partial class which
does make the property accessible with methods ;-)

Thanks,
Frank

 

[Jialiang Ge [MSFT]]

Nice, Frank!

- Jialiang Ge

 

[Roberto H.]

how can I use Reflection to access the internal SQLCommands of
DataTableAdapter?
give me an example please!

 

[SyntaxError]

DataTableAdapter or TableAdapter?

A TableAdapter has the .DeleteCommand, .InsertCommand, .SelectCommand, and
..UpdateCommand properties.

how can I use Reflection to access the internal SQLCommands of
DataTableAdapter?
give me an example please!

Hello Frank

Here are some ideas that you can consider.

1. To access the internal SqlCommands of DataTableAdapter, you can use
.NET
Reflection.

2. Consider using TransactionScope in .NET 2.0.
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope
.aspx
This automatically makes a code block transactional.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you.
Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no
rights.
=================================================

 

[Roberto H.]

I want to make a transaction with the adapters generated in the data set
designer. And the .DeleteCommand, .InsertCommand, .SelectCommand and
..UdateCommand are not available to assign the transaction.
How can I do?

DataTableAdapter or TableAdapter?

A TableAdapter has the .DeleteCommand, .InsertCommand, .SelectCommand, and
.UpdateCommand properties.

how can I use Reflection to access the internal SQLCommands of
DataTableAdapter?
give me an example please!

Hello Frank

Here are some ideas that you can consider.

1. To access the internal SqlCommands of DataTableAdapter, you can use
.NET
Reflection.

2. Consider using TransactionScope in .NET 2.0.
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope
.aspx
This automatically makes a code block transactional.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you.
Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no
rights.
=================================================

 

[Jim Rand]

I've long since chucked the TableAdapter as well as Microsoft's designer as
both have major limitations. With that said, if you want to stick with the
TableAdapter, you might consider exposing the underlying DataAdapter. Here
is a code sample:

namespace WindowsApplication1 {

public partial class DataSet1
{
}
}

namespace WindowsApplication1.DataSet1TableAdapters
{
public partial class ProductTableAdapter
{
public System.Data.SqlClient.SqlDataAdapter daProductTable
{
get
{
this.Adapter.SelectCommand = this.CommandCollection[0];
return this.Adapter;
}
}
}
}

 

[Roberto H.]

Do you have a Visual Basic Sample Please !

 

[Roberto H.]

I have already made my project with Table Adapters, for my next project which
is the best way to do it? What is your suggestion?


In this example: can I assign all the four operations the transaction:
adpCxP_Facturas.adpFacturas.InsertCommand.Transaction = Tran
adpCxP_Facturas.adpFacturas.DeleteCommand.Transaction = Tran
adpCxP_Facturas.adpFacturas.SelectCommand.Transaction = Tran
adpCxP_Facturas.adpFacturas.UpdateCommand.Transaction = Tran




Partial Class DataSet

End Class

Namespace DataSetTableAdapters

Partial Public Class ProductTableAdapter
Public ReadOnly Property adpProduct() As
System.Data.SqlClient.SqlDataAdapter
Get
Me.Adapter.SelectCommand = Me.CommandCollection(0)
Return Me.Adapter
End Get

End Property

End Class
End Namespace

Do you have a Visual Basic Sample Please !

I've long since chucked the TableAdapter as well as Microsoft's designer as
both have major limitations. With that said, if you want to stick with the
TableAdapter, you might consider exposing the underlying DataAdapter. Here
is a code sample:

namespace WindowsApplication1 {

public partial class DataSet1
{
}
}

namespace WindowsApplication1.DataSet1TableAdapters
{
public partial class ProductTableAdapter
{
public System.Data.SqlClient.SqlDataAdapter daProductTable
{
get
{
this.Adapter.SelectCommand = this.CommandCollection[0];
return this.Adapter;
}
}
}
}

 

[Jim Rand]

Personally, I would dump the table adapters and work directly with data
adapters.

If you have a generic list of data adapters that just happen to be in the
order you need them (ie. dataset.datatable parent, child, grandchild, then
you could build a static helper class that handles all of you data filling /
updating given the data adapter list and related dataset.

/* Fill all tables in the dataset */
public static void Fill(System.Data.DataSet ds,
JER.Data.Common.AdapterCollection adapterCollection)
{
ds.Clear();
foreach (System.Data.SqlClient.SqlDataAdapter da in
adapterCollection.DataAdapterList)
{
string tableName = da.TableMappings[0].DataSetTable;
da.Fill(ds, tableName);
}
} /* internal static void Fill */

/* Update processing within a transaction */
public static bool Update(System.Data.DataSet ds,
JER.Data.Common.AdapterCollection adapterCollection,
System.Data.IsolationLevel isolationLevel)
{

bool tranOK = false;

SqlConnection cn = (SqlConnection)adapterCollection.Connection;

try
{
cn.Open();

using (SqlTransaction tran = cn.BeginTransaction(isolationLevel))
{
try
{

/* Assign transaction */
foreach (SqlDataAdapter da in adapterCollection.DataAdapterList)
{
if (da.UpdateCommand != null) da.UpdateCommand.Transaction =
tran;
if (da.InsertCommand != null) da.InsertCommand.Transaction =
tran;
if (da.DeleteCommand != null) da.DeleteCommand.Transaction =
tran;
}

/* Get the changes for deletes, adds and modifies */
System.Data.DataSet dsDeleted =
ds.GetChanges(System.Data.DataRowState.Deleted);
System.Data.DataSet dsAdded =
ds.GetChanges(System.Data.DataRowState.Added);
System.Data.DataSet dsModified =
ds.GetChanges(System.Data.DataRowState.Modified);

/* Windows forms replaces nulls with "". Undo this */
if (dsAdded != null)
JER.Data.Common.AdapterHelper.ReplaceWithNulls(dsAdded, adapterCollection,
DataRowState.Added);
if (dsModified != null)
JER.Data.Common.AdapterHelper.ReplaceWithNulls(dsModified,
adapterCollection, DataRowState.Modified);

/* Process the updates */
UpdateOperation(dsDeleted, adapterCollection,
DataRowState.Deleted);
UpdateOperation(dsAdded, adapterCollection, DataRowState.Added);
UpdateOperation(dsModified, adapterCollection,
DataRowState.Modified);

/* Merge in the changes */
if (dsDeleted != null) ds.Merge(dsDeleted, false);
if (dsAdded != null) ds.Merge(dsAdded, false);
if (dsModified != null) ds.Merge(dsModified, false);

ds.AcceptChanges();

tran.Commit();

tranOK = true;
}
catch (System.Data.DBConcurrencyException ex)
{
tran.Rollback();
throw new System.Data.DBConcurrencyException("", ex);
}
catch (System.Data.ConstraintException ex)
{
tran.Rollback();
throw new System.Data.ConstraintException("", ex);
}
catch (System.Data.SqlClient.SqlException ex)
{
tran.Rollback();
_log.Warn("SQL Update", ex);
throw new Exception("", ex);
}
catch (Exception ex)
{
tran.Rollback();
_log.Error("SQL Update", ex);
throw new Exception("", ex);
}
}
}
finally
{
if ((cn != null) && (cn.State == ConnectionState.Open)) cn.Close();
}

return tranOK;
}

I have already made my project with Table Adapters, for my next project
which
is the best way to do it? What is your suggestion?


In this example: can I assign all the four operations the transaction:
adpCxP_Facturas.adpFacturas.InsertCommand.Transaction = Tran
adpCxP_Facturas.adpFacturas.DeleteCommand.Transaction = Tran
adpCxP_Facturas.adpFacturas.SelectCommand.Transaction = Tran
adpCxP_Facturas.adpFacturas.UpdateCommand.Transaction = Tran




Partial Class DataSet

End Class

Namespace DataSetTableAdapters

Partial Public Class ProductTableAdapter
Public ReadOnly Property adpProduct() As
System.Data.SqlClient.SqlDataAdapter
Get
Me.Adapter.SelectCommand = Me.CommandCollection(0)
Return Me.Adapter
End Get

End Property

End Class
End Namespace

Do you have a Visual Basic Sample Please !

I've long since chucked the TableAdapter as well as Microsoft's
designer as
both have major limitations. With that said, if you want to stick with
the
TableAdapter, you might consider exposing the underlying DataAdapter.
Here
is a code sample:

namespace WindowsApplication1 {

public partial class DataSet1
{
}
}

namespace WindowsApplication1.DataSet1TableAdapters
{
public partial class ProductTableAdapter
{
public System.Data.SqlClient.SqlDataAdapter daProductTable
{
get
{
this.Adapter.SelectCommand = this.CommandCollection[0];
return this.Adapter;
}
}
}
}