Hi, I have some code on .NET 2.0 that creates a TransactionScope and
then tries to open a SQL Connection.
When I try to open the connection, I get this error:
Test method
CDNDWUnitTestsNet.CDNDWFacadesNet_DealerAdminTest. DeleteDealerTest
threw exception: System.Transactions.TransactionException: The
operation is not valid for the state of the transaction..
Stack trace:
at CDNDWDataAccessNet.DealerAdminDataAccess.Save(Stri ng dealerXML)
in C:\DevProjects\CanalDigitalDealerWeb\Features\Ibs6 1\Source\DealerWeb
\Source\CDNDWDataAccessNet\DealerAdminDataAccess.c s:line 277
at CDNDWMiddlewareNet.DealerTransactions.Save(String dealerXml) in
C:\DevProjects\CanalDigitalDealerWeb\Features\Ibs6 1\Source\DealerWeb
\Source\CDNDWMiddlewareNet\DealerTransactions.cs:l ine 21
at CDNDWFacadesNet.DealerAdmin.SaveDealer(String dealerXML) in C:
\DevProjects\CanalDigitalDealerWeb\Features\Ibs61\ Source\DealerWeb
\Source\CDNDWFacadesNet\DealerAdmin.cs:line 29
at
CDNDWUnitTestsNet.CDNDWFacadesNet_DealerAdminTest. DeleteDealerTest()
in C:\DevProjects\CanalDigitalDealerWeb\Features\Ibs6 1\Source\DealerWeb
\CDNDWUnitTestsNet\CDNDWFacadesNet_DealerAdminTest .cs:line 184
Everything is running on the same machine, using Windows XP SP2, SQL
Server 2005.
When the code is deployed, the database will be on a separate single
server, so I think I will need to use distributed transactions.
My code is like this:
public int Save(string dealerXML)
{
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.Required))
{
TransactionInterop.GetTransmitterPropagationToken( Transaction.Current);
this.SaveDealerCategory(dealer.Category); //this uses
a connection
this.SaveDealerChain(dealer.Chain); //this uses a
connection
cnn = new SqlConnection(connectionString);
string command =
"INSERT INTO Dealer...;SELECT @@IDENTITY AS ID";
cmd = new SqlCommand(command, cnn);
try
{
cnn.Open(); //Point of Failure <===
dr = cmd.ExecuteReader();
if (dr.Read())
{
dealerId = Convert.ToInt32(dr[0]);
dr.Close();
if (dealer.BaseSystemInfo != null)
{
command = "INSERT
INTO...";
foreach (DealerBaseSystem seg in
dealer.BaseSystemInfo)
{
cmd = new SqlCommand(command, cnn);
cmd.ExecuteNonQuery();
}
}
}
return dealerId;
}
catch (Exception e)
{
throw e;
}
finally
{
cnn.Close();
//scope.Dispose();
}
//Commit transaction
scope.Complete();
}
}
Hope someone can tell me why I get the error, and what I can do to
make it work.
Sincerely
Svein Terje Gaup
