c# - 如何在 SQL Server 中使用 SqlTransaction 在 FOR 循环中运行一次命令

Question

我正在DataGridview使用 FOR 循环从一到三个数据库表中插入行，SqlTransaction并且我只想在一个表中插入事务的引用一次。

我怎样才能做到这一点？

我试图在bool flag = false;我使用过的 FOR 循环中声明变量

if(flag == false)
{
   .......
   flag == true;
}

但是它没有帮助，因为cmd3.ExecuteNon()如果我SqlCommand cmd3;在 FOR 之前声明并在 IF 语句中实例化，或者如果我在 IF 语句中声明并实例化，这两种情况都不起作用。

string cs = ConfigurationManager.ConnectionStrings["PRMSConnectionString"].ToString();

SqlConnection con = new SqlConnection(cs);

SqlTransaction objTransaction;
SqlCommand cmd3;

bool flag = false;

for (int i = 0; i < dgv_Purchase.Rows.Count - 1; i++)
{
    con.Open();
    objTransaction = con.BeginTransaction();
    //**************************Command 1 Code*******************
    string query1 = "INSERT ......";
    SqlCommand cmd1 = new SqlCommand(query1, con, objTransaction);

    //***************************Command 2 Code*******************
    string query2 = "INSERT .....";
    SqlCommand cmd2 = new SqlCommand(query2, con, objTransaction);

    //****************************Command 3 Code*******************
    if (flag == false)
    {
        string query3 = "INSERT. ......";
        cmd3 = new SqlCommand(query3, con, objTransaction);
        flag = true;
    }

    try
    {
        cmd1.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();
        cmd3.ExecuteNonQuery();
        objTransaction.Commit();
        lblSF.Text = "Success!";
    }
    catch (Exception ex)
    {
        MessageBox.Show("Exception " + ex);
        objTransaction.Rollback();
        lblSF.Text = "Failed!";
        return false;
    }
    finally
    {
        con.Close();
    }
}

标签： c#.netsql-server

Answer 1

如果我正确理解您的问题，您query3只需要在循环的第一个循环中for，并且仅在两者query1都query2执行之后。

如果是这种情况，只需执行两者cmd1和cmd2首先，然后将执行移动到cmd3您if声明它的位置。

string cs = ConfigurationManager.ConnectionStrings["PRMSConnectionString"].ToString();
SqlConnection con = new SqlConnection(cs);
SqlTransaction objTransaction;
bool flag = false;

for (int i = 0; i < dgv_Purchase.Rows.Count - 1; i++)
{
    con.Open();
    objTransaction = con.BeginTransaction();
    //**************************Command 1 Code*******************
    string query1 = "INSERT ......";
    SqlCommand cmd1 = new SqlCommand(query1, con, objTransaction);

    //***************************Command 2 Code*******************
    string query2 = "INSERT .....";
    SqlCommand cmd2 = new SqlCommand(query2, con, objTransaction);
    //****************************Command 3 Code*******************
    try
    {
        cmd1.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();
        if (flag==false)
        {
            string query3 = "INSERT. ......";
            SqlCommand cmd3 = new SqlCommand(query3, con, objTransaction);
            cmd3.ExecuteNonQuery();
            flag = true;
        }
        objTransaction.Commit();
        lblSF.Text = "Success!";
    }
    catch (Exception ex)
    {

        MessageBox.Show("Exception " + ex);
        objTransaction.Rollback();
        lblSF.Text = "Failed!";
        return false;
    }
    finally
    {
        con.Close();
    }
}