Help in converting to "using" statement?

EDN Admin · Jul 24, 2008

Dear All,
 Currently I have an application with 2 grids that is one gridSales and gridPayment. So based on the list of item in both of this grid I generate the query and insert them accordingly. So I have put all the sql statements under one 1 transaction. Currently I am using my own method based on a variable. Based on the rollbackBoolean variable if any of the queries value is equal =1 then whole transaction will be roll back.But some article says the "using" statement is much efficient but my problem I dont know how to convert to it from my code ? Any idea please ? Below is the snippet of my code.


int rollbackBoolean = 0; 

MySqlTransaction transactionLocal = null;
MySqlConnection connectionLocal = null;
transactionConnectionLocal1 callTransactionConnectionLocal1 = null;
try 
{
callTransactionConnectionLocal1 = new transactionConnectionLocal1();
connectionLocal = callTransactionConnectionLocal1.localConnection1;
connectionLocal.Open();
transactionLocal = connectionLocal.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Database Connection (Local Server Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Database Connection (Local Server Is Down) " + ex.Message);
}


for (int j = 0; j < gridSales.RowCount; j++)
{
 String myInsertQuery1 = "Insert into tblReceiptDetails " +
<blockquote dir=ltr style="margin-right:0px
"Set receiptID=" + receiptID + ", " +
"receiptDetailsID = " + receiptDetailsID + ", " + 
"productID= " + Convert.ToInt32(this.gridSales[0, j].Value.ToString()) + ", " + 
"productQuantity = " + Convert.ToInt16(this.gridSales[16, j].Value.ToString()) + ", " + 
"sellingPrice = " + Convert.ToDouble(this.gridSales[18, j].Value.ToString()) + ", " + 
"profit = " + this.gridSales[5, j].Value.ToString() + ", " +
"stockStatus =" + stockStatus + "";
// MessageBox.Show("myInsertQuery2 : "+myInsertQuery2);

MySqlCommand myCommandLocal1 = new MySqlCommand(myInsertQuery1);
myCommandLocal1.Connection = connectionLocal;
myCommandLocal1.Transaction = transactionLocal;
try

{
myCommandLocal1.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandLocal1 myInsertQuery1 " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal1 myInsertQuery1 " + ex.Message);
}
finally

{
myCommandLocal1.Dispose(); </blockquote>


}

for (int p = 0; p < gridPayment.RowCount; p++)
{ 
<blockquote dir=ltr style="margin-right:0px
 myInsertQuery2 = "Insert into tblPaymentDetails " +
"Set paymentDetailsID=" + paymentDetailsID + ", " +
receiptID = " + receiptID + ", " +
"paymentTypeID= 1, " +
"amount = " + Convert.ToDouble(this.gridPayment[11, p].Value.ToString()); </blockquote>
<blockquote dir=ltr style="margin-right:0px
MySqlCommand myCommandLocal2 = new MySqlCommand(myInsertQuery2);
myCommandLocal2.Connection = connectionLocal;
myCommandLocal2.Transaction = transactionLocal;
try 
{
myCommandLocal2.ExecuteNonQuery();
} </blockquote>
<blockquote dir=ltr style="margin-right:0px
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Payment Details myCommandLocal2 myInsertQuery2 " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Payment Details " + ex.Message);
}
finally 
{
myCommandLocal2.Dispose();
} </blockquote>}


if (rollbackBoolean == 1)
{
transactionLocal.Rollback(); 
}
else 
{
<blockquote dir=ltr style="margin-right:0px
try 
{
transactionLocal.Commit(); 
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
try 
{
transactionLocal.Rollback();
} </blockquote>
}
catch (MySqlException ex1)
{
if (transactionLocal.Connection != null)
{
MessageBox.Show("An exception of type " + ex1.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
MessageBox.Show("An exception of type " + ex.GetType() +
" was encountered while inserting the data.");
MessageBox.Show("Neither record was written to database.");
}
finally 
{
connectionLocal.Close(); 
}
} 

View the full article

Help in converting to "using" statement?

EDN Admin

Well-known member