ADO.NET Transaction Support

[localhost]

I have two stored procedures in an MS SQL Server 2000 database.
Internally, each stored procedure performs an insert operation and
then returns a table with some return message info (identity column,
etc).

I use the OleDb provider to normally execute the stored procs. I use
OleDb because it makes my data code portable to other databases.

Can anyone show sample code using OleDb Transactions at the ADO.NET
level to make sure that both SPs execute.

Thanks.


I prototyped the below code but I am not sure if it is OK to use.



using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;

namespace testit
{
class testTransDemo
{
[STAThread]
static void Main(string[] args)
{
OleDbConnection testConnection = null;
OleDbCommand testCommand1 = null;
OleDbCommand testCommand2 = null;
OleDbTransaction testTransaction = null;
OleDbDataAdapter testAdapter1 = null;
OleDbDataAdapter testAdapter2 = null;
DataSet testSet1 = new DataSet();
DataSet testSet2 = new DataSet();
string storedProcName = "heh";

try
{
testConnection = new OleDbConnection();

testCommand1 = new OleDbCommand();
testCommand1.CommandType = CommandType.StoredProcedure;
testCommand1.CommandText = storedProcName;
/*
...add command parameters...
*/
testConnection.Open();

testCommand2 = new OleDbCommand();
testCommand2.CommandType = CommandType.StoredProcedure;
testCommand2.CommandText = storedProcName;
/*
...add command parameters...
*/
testConnection.Open();
}
catch{}

try
{
testTransaction = testConnection.BeginTransaction();

testCommand1.Transaction = testTransaction;
testCommand2.Transaction = testTransaction;

testAdapter1 = new OleDbDataAdapter( testCommand1 );
testAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;
testAdapter1.Fill( testSet1 );

testAdapter2 = new OleDbDataAdapter( testCommand2 );
testAdapter2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
testAdapter2.Fill( testSet2 );

testTransaction.Commit();
}
catch
{
Console.WriteLine( "Error occurred, performing rollback." );
try
{
testTransaction.Rollback();
}
catch( OleDbException xxxOle )
{
Console.WriteLine(
"Serious DB error, transaction could not be rolled
back. \n " +
xxxOle.GetType().ToString() );
}
finally
{
Console.WriteLine( "Rollback complete." );
}
}
finally
{
if ( testCommand1 != null ) testCommand1 = null;
if ( testCommand2 != null ) testCommand2 = null;
if ( testConnection != null ) testConnection.Close();
}


}
}
}

 

[Marina]

That looks off hand. To be sure, try forcing an error in the second stored
procedure part way through, and then check the database afterwards to make
sure it all got rolled back.

 

[Kevin Yu [MSFT]]

Hi localhost,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you would like to see some sample code
for OleDb Transactions. If there is any misunderstanding, please feel free
to let me know.

I think your prototype is quite right for beginning a transaction. Here I
found some more sample for you. Please check the following link for more
information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconperformingtransactionusingadonet.asp

HTH. If anything is unclear, please feel free to reply to the post.

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