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(); }

Answer 1

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.