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.Connection String =
ReadConnectionS tringFromFile() ;
}
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.OpenC onnection();
SqlCommand cmdInsert = new SqlCommand(myQu ery,
objModCon.myCN) ;
try
{
Int32 RecordsAffected = cmdInsert.Execu teNonQuery();
return RecordsAffected ;
}
catch (Exception ex)
{
MessageBox.Show ("Routine: ModReUsable-
InsertNewRecord (" + myQuery + ") " + ex.ToString(), "Error:",
MessageBoxButto ns.OK, MessageBoxIcon. Error);
return 0;
}
finally
{
cmdInsert.Dispo se();
objModCon.Close Connection();
}
}
=============== =============== =============== ====
=========[ Windows Form Code Sample ]=============== ==
private void cmdProceed_Clic k(object sender, EventArgs e)
{
using (TransactionSco pe scope = new
TransactionScop e())
{
//Start Generation
GenerateALL();
scope.Complete( );
}
}
}
private void GenerateAll()
{
Loop Starts
Dim qInsert = ".............. .";
ModRes.InsertNe wRecord(qInsert );
//Call Function A
FunctionA();
Dim qInsert = ".............. .";
ModRes.InsertNe wRecord(qInsert );
FunctionB();
}
private void FunctionA()
{
Dim qUpdate = ".............. ."
ModRes.InsertNe wRecord(qUpdate );
}
private void FunctionB()
{
............... .
}
=============== =============== =============== =============== =======
The above code from Windows Form Button Click event works well if I
don't use TransactionScop e, whereas when I use TransactionScop e it
displays error in Function A:
======[ ERROR ]======
ModRes.InsertNe wRecord(....... .)
System.InvalidO perationExcepti on: 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 TransactionScop e not allowing calls and execution of SQL in
other classes.
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.Connection String =
ReadConnectionS tringFromFile() ;
}
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.OpenC onnection();
SqlCommand cmdInsert = new SqlCommand(myQu ery,
objModCon.myCN) ;
try
{
Int32 RecordsAffected = cmdInsert.Execu teNonQuery();
return RecordsAffected ;
}
catch (Exception ex)
{
MessageBox.Show ("Routine: ModReUsable-
InsertNewRecord (" + myQuery + ") " + ex.ToString(), "Error:",
MessageBoxButto ns.OK, MessageBoxIcon. Error);
return 0;
}
finally
{
cmdInsert.Dispo se();
objModCon.Close Connection();
}
}
=============== =============== =============== ====
=========[ Windows Form Code Sample ]=============== ==
private void cmdProceed_Clic k(object sender, EventArgs e)
{
using (TransactionSco pe scope = new
TransactionScop e())
{
//Start Generation
GenerateALL();
scope.Complete( );
}
}
}
private void GenerateAll()
{
Loop Starts
Dim qInsert = ".............. .";
ModRes.InsertNe wRecord(qInsert );
//Call Function A
FunctionA();
Dim qInsert = ".............. .";
ModRes.InsertNe wRecord(qInsert );
FunctionB();
}
private void FunctionA()
{
Dim qUpdate = ".............. ."
ModRes.InsertNe wRecord(qUpdate );
}
private void FunctionB()
{
............... .
}
=============== =============== =============== =============== =======
The above code from Windows Form Button Click event works well if I
don't use TransactionScop e, whereas when I use TransactionScop e it
displays error in Function A:
======[ ERROR ]======
ModRes.InsertNe wRecord(....... .)
System.InvalidO perationExcepti on: 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 TransactionScop e not allowing calls and execution of SQL in
other classes.