Commit And Rollback in C#

Question

This is my C# WinForm Code I have 2 stored procedure, the 1st doing Insert in Table1 and the 2nd doing Update in Table2 both are in a for loop. Please guide me on how to use Commit and Rollback in this code because I have GridView and GridView have many rows if any row have incorrect data then no rows get inserted and updated in Tables

try
{
    con.Open();

    da = DateTime.Now;
    if (txt_challanno.Text == "")
    {
        //MessageBox.Show("Insert Order No.", "Message Box Sample", MessageBoxButtons.OK, MessageBoxIcon.Error);
        toolTip1.ToolTipIcon = ToolTipIcon.Warning;
        toolTip1.ToolTipTitle = "Warning!";
        toolTip1.Show("Missing 'Lot No.'", txt_challanno);
    }
    else if (txt_challanno.Text != "" && DataGridView1.CurrentRow.Cells["program_no"].Value == null)
    {
        toolTip1.Hide(txt_challanno);
        MessageBox.Show("Insert Program No.");
    }
    else if (dataGridView1.CurrentRow.Cells["program_no"].Value != null && dataGridView1.CurrentRow.Cells["bundle_weight"].Value == null)
    {
        toolTip1.Hide(txt_challanno);
        MessageBox.Show("Insert Bundle Weight");
    }
    else if (dataGridView1.CurrentRow.Cells["bundle_weight"].Value == null && dataGridView1.CurrentRow.Cells["pcs"].Value == null)
    {
        toolTip1.Hide(txt_challanno);
        MessageBox.Show("Insert Pcs");
    }
    else
    {
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            Double r_weight1 = Convert.ToDouble(dataGridView1.Rows[i].Cells["r_weight"].Value);
            Double use_weight1 = Convert.ToDouble(dataGridView1.Rows[i].Cells["use_weight"].Value);
            Double r_rolls = Convert.ToDouble(dataGridView1.Rows[i].Cells["r_rolls"].Value);
            Double use_rolls = Convert.ToDouble(dataGridView1.Rows[i].Cells["use_rolls"].Value);


            if (use_weight <= r_weight1 && use_rolls <= r_rolls)
            {

                string a = dataGridView1.Rows[i].Cells["pcs_wt"].Value.ToString();
                var data = Regex.Match(a, @"\d+").Value;
                var6 = Convert.ToDouble(data);

                SqlCommand cmd = new SqlCommand("dailycuttinginsert", con);
                cmd.Parameters.Add("@id1", SqlDbType.Int).Value = 1;
                cmd.Parameters.Add("@challan_no", SqlDbType.NVarChar).Value = txt_challanno.Text;
                cmd.Parameters.Add("@size_name", SqlDbType.NVarChar).Value = dataGridView1.Rows[i].Cells["size"].Value.ToString();
                cmd.Parameters.Add("@quality_name", SqlDbType.NVarChar).Value = dataGridView1.Rows[i].Cells["quality"].Value.ToString();
                cmd.Parameters.Add("@use_weight", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["use_weight"].Value);
                cmd.Parameters.Add("@use_rolls", SqlDbType.Int).Value = Convert.ToInt32(dataGridView1.Rows[i].Cells["use_rolls"].Value);
                cmd.Parameters.Add("@bundle_weight", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["bundle_weight"].Value);
                cmd.Parameters.Add("@ls", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["ls"].Value);
                cmd.Parameters.Add("@shape", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["shape"].Value);
                cmd.Parameters.Add("@b", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["b"].Value);
                cmd.Parameters.Add("@total_pcs", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["total_pcs"].Value);
                cmd.Parameters.Add("@avg", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["avg"].Value);
                cmd.Parameters.Add("@pcs_wt", SqlDbType.Float).Value = var6;/*Convert.ToDecimal(dataGridView1.Rows[i].Cells["pcs_wt"].Value)*/
                cmd.Parameters.Add("@cutting_size", SqlDbType.NVarChar).Value = dataGridView1.Rows[i].Cells["cutting"].Value.ToString();
                cmd.Parameters.Add("@date2", SqlDbType.Date).Value = dataGridView1.Rows[i].Cells["dt"].Value.ToString();
                cmd.Parameters.Add("@date1", SqlDbType.Date).Value = da.ToString("MM/dd/yyyy");
                cmd.Parameters.Add("@r_id", SqlDbType.NVarChar).Value = dataGridView1.Rows[i].Cells["id1"].Value.ToString();
                cmd.Parameters.Add("@balance_pcs", SqlDbType.Float).Value = 0;
                db.insertprocedure(cmd);
                cmd.Parameters.Clear();


                SqlCommand cmd1 = new SqlCommand("dailycuttinginsert", con);
                cmd1.Parameters.Add("@id1", SqlDbType.Int).Value = 3;
                cmd1.Parameters.Add("@challan_no", SqlDbType.NVarChar).Value = txt_challanno.Text;
                cmd1.Parameters.Add("@r_id", SqlDbType.NVarChar).Value = dataGridView1.Rows[i].Cells["id1"].Value.ToString();
                cmd1.Parameters.Add("@balance_weight", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["bal_weight"].Value);
                cmd1.Parameters.Add("@balance_rolls", SqlDbType.Float).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells["bal_rolls"].Value);
                db.insertprocedure(cmd1);
                cmd1.Parameters.Clear();
            }
            else { }
        }

        MessageBox.Show("Data Inserted");
        frmload();
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally { con.Close(); }

This should probably be on [programmers](http://programmers.stackexchange.com/) or [codereview](http://codereview.stackexchange.com/). — Patrick Quirk, Jan 30 '14 at 18:57
what about to use TransactionScope http://msdn.microsoft.com/ru-ru/library/system.transactions.transactionscope(v=vs.110).aspx — potehin143, Jan 30 '14 at 18:57

score 12 · Accepted Answer · edited Jun 21 '21 at 18:54

12

First thing you want is to start the transaction immediately after you open the connection. Also, please move the connection opening out of the try block and wrap its creation in the using - this is a good practice for IDisposable implementations:

using (var conn = new SqlConnection(connectionString))
{
    // your code before conn.Open()
    conn.Open();
    SqlTransaction tran = conn.BeginTransaction();
    
    try
    {
    // your code goes here

Then leave everything as it is, and then in the end of the operation commit the transaction:

tran.Commit();
MessageBox.Show("Data Inserted");
frmload();

Also note that there is no finally because using is taking care of closing the connection in case of exceptions.

edited Jun 21 '21 at 18:54

Jim G.

15,141
22
103
166

answered Jan 30 '14 at 18:59

Andrei

55,890
9
87
108

How are you calling it? Note that the code in the post does not use `SqlCommand` object at all - only `con` – Andrei Jan 30 '14 at 19:07
thx bro its working confusing in defining tran in sqlCommand because i search commit roll there have many posts have define tran in sqlcommand without defining its work thx – user2491383 Jan 30 '14 at 19:25
@Andrei In store procedure i have setup to do commit and Rollback. Should i still write Commit and Rollback logic in C# when i call that SPROC ? – dev Jun 17 '15 at 17:32
@Mvcdev, depends on how you organize your rollbacks. If you do error catching inside the stored proc and then rollback (as in [here](http://stackoverflow.com/questions/11531352/how-to-rollback-a-transaction-in-a-stored-procedure)), then you should be fine – Andrei Jun 18 '15 at 09:53
2

why we need tran.Rollback? when connection closed, it will automatically rolls back any open transaction – FLICKER Aug 15 '19 at 16:50
FLICKER is correct. https://stackoverflow.com/a/18389131/109941 – Jim G. Jun 21 '21 at 18:54

Commit And Rollback in C#

1 Answers1

Linked