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.
