I have facing some problem to insert multiple table some time that insert only single table not insert into another table and rollback is not working what happen i can't understand please help me
I use this code(Database is online )
using (SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["Connection"].ToString()))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Connection = connection;
command.Transaction = transaction;
try
{
// First Store Procedure
command.CommandText = "PrintInvoiceNew";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@BillNo", SqlDbType.VarChar, 50).Value = addstr;
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerID;
command.ExecuteNonQuery();
command.Parameters.Clear();
//Second Store Procedure
command.CommandText = "PrintInvoiceDetailsInsert";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@BillNo", SqlDbType.VarChar, 50).Value = addstr;
command.Parameters.Add("@ItemVariantID", SqlDbType.Int).Value = dgvInvoice[0, i].Value;
command.Parameters.Add("@ItemCode", SqlDbType.NVarChar, 50).Value = dgvInvoice[1, i].Value;
command.Parameters.Add("@ItemName", SqlDbType.NVarChar, 250).Value = dgvInvoice[2, i].Value;
command.ExecuteNonQuery();
command.Parameters.Clear();
// Third store procedure
command.CommandText = "PrintStockUpdate";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Stock", SqlDbType.Int).Value = dgvInvoice[3, i].Value;
command.Parameters.Add("@LocationID", SqlDbType.Int).Value = Convert.ToInt32(Variables.LocationID);
command.Parameters.Add("@ItemVariantID", SqlDbType.Int).Value = dgvInvoice[0, i].Value;
command.ExecuteNonQuery();
command.Parameters.Clear();
...........
transaction.Commit();
}
catch (Exception exc)
{
try
{
transaction.Rollback();
}
catch (SqlException ex)
{
if (transaction.Connection != null)
{
MessageBox.Show("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
MessageBox.Show("An exception of type " + exc.GetType() +
" was encountered while inserting the data. Message :" + exc.Message);
MessageBox.Show("Neither record was written to database.");
}
}