Error while using TransactionScope

[RP]

I have a two classes, first named "ModCon" has procedures written for
connections and the second named "ModRes" contains functions and
procedures that can be reused. For my question it is important to add
sample codes of the two classes and later the code from a Form's
button click event which is giving problem.

========[ ModCon Code ]================================
public string ConString;
public SqlConnection myCN = new SqlConnection();

public void OpenConnection()
{
if (myCN.State == ConnectionState.Closed)
{
myCN.ConnectionString =
ReadConnectionStringFromFile();
}
try
{
if (myCN.State != ConnectionState.Open)
{
myCN.Open();
}
}
catch (Exception ex)
{

}
}

public void CloseConnection()
{
if (myCN.State == ConnectionState.Open)
try
{
myCN.Close();
}
catch (Exception ex)
{

}
}
===================================================
=======[ ModRes Code ]=============================
public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (Exception ex)
{
MessageBox.Show("Routine: ModReUsable-
InsertNewRecord(" + myQuery + ") " + ex.ToString(), "Error:",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}
}
=================================================

=========[ Windows Form Code Sample ]=================
private void cmdProceed_Click(object sender, EventArgs e)
{
using (TransactionScope scope = new
TransactionScope())
{
//Start Generation
GenerateALL();
scope.Complete();
}
}
}

private void GenerateAll()
{
Loop Starts
Dim qInsert = "...............";
ModRes.InsertNewRecord(qInsert);

//Call Function A
FunctionA();

Dim qInsert = "...............";
ModRes.InsertNewRecord(qInsert);

FunctionB();
}

private void FunctionA()
{
Dim qUpdate = "..............."
ModRes.InsertNewRecord(qUpdate);
}

private void FunctionB()
{
................
}
===================================================================

The above code from Windows Form Button Click event works well if I
don't use TransactionScope, whereas when I use TransactionScope it
displays error in Function A:

======[ ERROR ]======
ModRes.InsertNewRecord(........)
System.InvalidOperationException: ExecuteNonQuery requires an open and
available connection. The connection's current state is closed.
=====================

The exception is returned by Class ModRes, InsertNewRecord function.
Why? Does TransactionScope not allowing calls and execution of SQL in
other classes.

 

[Guest]

I think if you read the documentation on the TransactionScope class

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

-- it will become much clearer how it must be used. It requires a valid and
open connection to be able
to handle transactions across multiple method calls. By definition, this
means the same connection object must be visible to and in the scope of each
call.
In your case the exception message is quite clear - you don't even have an
open connectioni.

-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

I have a two classes, first named "ModCon" has procedures written for
connections and the second named "ModRes" contains functions and
procedures that can be reused. For my question it is important to add
sample codes of the two classes and later the code from a Form's
button click event which is giving problem.

========[ ModCon Code ]================================
public string ConString;
public SqlConnection myCN = new SqlConnection();

public void OpenConnection()
{
if (myCN.State == ConnectionState.Closed)
{
myCN.ConnectionString =
ReadConnectionStringFromFile();
}
try
{
if (myCN.State != ConnectionState.Open)
{
myCN.Open();
}
}
catch (Exception ex)
{

}
}

public void CloseConnection()
{
if (myCN.State == ConnectionState.Open)
try
{
myCN.Close();
}
catch (Exception ex)
{

}
}
===================================================
=======[ ModRes Code ]=============================
public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (Exception ex)
{
MessageBox.Show("Routine: ModReUsable-
InsertNewRecord(" + myQuery + ") " + ex.ToString(), "Error:",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}
}
=================================================

=========[ Windows Form Code Sample ]=================
private void cmdProceed_Click(object sender, EventArgs e)
{
using (TransactionScope scope = new
TransactionScope())
{
//Start Generation
GenerateALL();
scope.Complete();
}
}
}

private void GenerateAll()
{
Loop Starts
Dim qInsert = "...............";
ModRes.InsertNewRecord(qInsert);

//Call Function A
FunctionA();

Dim qInsert = "...............";
ModRes.InsertNewRecord(qInsert);

FunctionB();
}

private void FunctionA()
{
Dim qUpdate = "..............."
ModRes.InsertNewRecord(qUpdate);
}

private void FunctionB()
{
................
}
===================================================================

The above code from Windows Form Button Click event works well if I
don't use TransactionScope, whereas when I use TransactionScope it
displays error in Function A:

======[ ERROR ]======
ModRes.InsertNewRecord(........)
System.InvalidOperationException: ExecuteNonQuery requires an open and
available connection. The connection's current state is closed.
=====================

The exception is returned by Class ModRes, InsertNewRecord function.
Why? Does TransactionScope not allowing calls and execution of SQL in
other classes.