client-side transaction for 3 stored procedures.....

[CLRBOY]

Greetings,

I have 3 SP and I want to build client-side(C#) transaction.

this is how it looks like right now :
I have one sqlconnection and three sqlcommands(each one per SP)

bool InsertOne = false;
bool InsertTwo = false;
bool InsertThree = false;
SqlTransaction myTransOne = sqlConnection1.BeginTransaction();
SqlTransaction myTransTwo = sqlConnection1.BeginTransaction();
SqlTransaction myTransThree = sqlConnection1.BeginTransaction();

sqlCommand1.Transaction = myTransOne;
try
{
....First SP.....
myTransOne.Commit();
InsertOne = true;
}

sqlCommand2.Transaction = myTransTwo;
try
{
....Second SP.....
myTransTwo.Commit();
InsertTwo = true;
}

sqlCommand3.Transaction = myTransThree;
try
{
....Third SP.....
myTransThree.Commit();
InsertThree = true;
}

if ((InsertOne == false) || (InsertTwo == false) || (InsertThree == false))
{
myTransOne.Rollback();
myTransTwo.Rollback();
myTransThree.Rollback();
MessageBox("........");
}


the problem is at run-time. I get exception when Im trying to assign the (An unhandled exception of type System.InvalidOperationException occurred in system.data.dll
Additional information: SqlConnection does not support parallel transactions.)

so what do I have to do ? to give every command its own connection ??
or maybe their is another way to do this ?

Any Answer will be appreciated.

 

[HJB417]

using(SqlTransaction myTransOne = sqlConnection1.BeginTransaction())
{
	try 
	{
		....First SP.....
		....Second SP.....
		....Third SP.....
		myTransOne.Commit();
	}
	catch
	{
		try
		{
			myTransOne.Rollback();
		}
		catch{}
		throw;
	}
	MessageBox("........");
}

I would keep the connection:transaction ratio at 1:1.

 

[CLRBOY]

using(SqlTransaction myTransOne = sqlConnection1.BeginTransaction())
{
	try 
	{
		....First SP.....
		....Second SP.....
		....Third SP.....
		myTransOne.Commit();
	}
	catch
	{
		try
		{
			myTransOne.Rollback();
		}
		catch{}
		throw;
	}
	MessageBox("........");
}

I would keep the connection:transaction ratio at 1:1.

Thank you... it works excellent and economize me 2 try-catch blocks.