ADO .NET & transactions

  • Thread starter Thread starter Mario L. Eppinger
  • Start date Start date
M

Mario L. Eppinger

[might be ot, if so pls. point me to the correct ng]

Hi @ll,

I have a problem using transactions in c#:

I have a class that implements all my database actions. In this class I have
-) The SqlConnection conn (which is opened in the ctor)
-) Some SqlCommand's (one of the we call cmd ) which are created like cmd =
conn.CreateCommand()
-) The statements are assigned cmd.CommandText = "SELECT x FROM y WHERE x =
h";
-) The statements are Prepared() cmd.Prepare

Then some functions are called and some commands are ExecuteReader()'ed.
Everything wors fine.
But then I have a methodthat does something like this (wihtin this class)

public updateInventory( string strProductCode )
{
SqlTransaction transact = conn.BeginTransaction(
System.Data.IsolationLevel.RepeatableRead );

cmd.Transaction = transact;
cmd.Parameters["@product_code"] = strProductCode;

cmd.ExecuteNonQuery();
}

The Problem is the line containing the BeginTransaction() - Statement. It
throws an System.InvalidOperationException
With the Details: "Begin transaction requires an open and available
connection. Current State: Open"

This confuses me, since the state is really open...


What is the real Problem?

TIA

Regards


Mario
 
Hi Mario,

Mario L. Eppinger said:
[might be ot, if so pls. point me to the correct ng]

Hi @ll,

I have a problem using transactions in c#:

I have a class that implements all my database actions. In this class I have
-) The SqlConnection conn (which is opened in the ctor)
-) Some SqlCommand's (one of the we call cmd ) which are created like cmd =
conn.CreateCommand()
-) The statements are assigned cmd.CommandText = "SELECT x FROM y WHERE x =
h";
-) The statements are Prepared() cmd.Prepare

Then some functions are called and some commands are ExecuteReader()'ed.
Everything wors fine.
But then I have a methodthat does something like this (wihtin this class)

public updateInventory( string strProductCode )
{
SqlTransaction transact = conn.BeginTransaction(
System.Data.IsolationLevel.RepeatableRead );

cmd.Transaction = transact;
cmd.Parameters["@product_code"] = strProductCode;

cmd.ExecuteNonQuery();
}

The Problem is the line containing the BeginTransaction() - Statement. It
throws an System.InvalidOperationException
With the Details: "Begin transaction requires an open and available
connection. Current State: Open"

This confuses me, since the state is really open...


What is the real Problem?
Click to expand...

A guess: Is there perhaps a SqlDataReader object that is open and
attached to the connection. I think DataReaders tie up a connection as long
as they are open.

Regards,
Dan
 
Thanks Daniell! That's it!


Best Regards

Mario

Daniel Pratt said:
Hi Mario,

Mario L. Eppinger said:
[might be ot, if so pls. point me to the correct ng]

Hi @ll,

I have a problem using transactions in c#:

I have a class that implements all my database actions. In this class I have
-) The SqlConnection conn (which is opened in the ctor)
-) Some SqlCommand's (one of the we call cmd ) which are created like
Click to expand...
cmd
=
conn.CreateCommand()
-) The statements are assigned cmd.CommandText = "SELECT x FROM y WHERE
Click to expand...
x
=
h";
-) The statements are Prepared() cmd.Prepare

Then some functions are called and some commands are ExecuteReader()'ed.
Everything wors fine.
But then I have a methodthat does something like this (wihtin this class)

public updateInventory( string strProductCode )
{
SqlTransaction transact = conn.BeginTransaction(
System.Data.IsolationLevel.RepeatableRead );

cmd.Transaction = transact;
cmd.Parameters["@product_code"] = strProductCode;

cmd.ExecuteNonQuery();
}

The Problem is the line containing the BeginTransaction() - Statement. It
throws an System.InvalidOperationException
With the Details: "Begin transaction requires an open and available
connection. Current State: Open"

This confuses me, since the state is really open...


What is the real Problem?
Click to expand...

A guess: Is there perhaps a SqlDataReader object that is open and
attached to the connection. I think DataReaders tie up a connection as long
as they are open.

Regards,
Dan
Click to expand...
 
Back
Top