C＃中的提交和回滚 [英] Commit And Rollback in C#

问题描述

这是我的C＃WinForm代码，我有2个存储过程，第一个在 Table1 中进行插入，第二个在 Table2 都在for循环中。请指导我如何在此代码中使用提交和回滚，因为如果有任何行包含错误的数据，我的GridView和GridView会有很多行，那么表中就不会插入和更新任何行

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

推荐答案

首先要立即开始交易打开连接后。另外，请将连接开口移出 try 块，并使用将其创建内容包装在中-这是一个很好的选择 IDisposable 实现的实践：

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

在 catch 块中，需要回滚事务失败了。确保将其包装在另一个try / catch中，因为它也可能引发异常：

In the catch block you need to rollback the transaction since something has failed. Make sure to wrap it in another try/catch because it may throw exception as well:

catch (Exception e)
{
    try
    {
        tran.Rollback();
    }
    catch (Exception exRollback)
    {
        Console.WriteLine(exRollback.Message);
    }

    throw;
}

还请注意，最终没有 ，因为使用会在出现异常的情况下关闭连接。

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

这篇关于C＃中的提交和回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！