c# - 如何连续3次重复C#中的Insert命令到SQL Server数据库

Question

我是新来的，我在这部分代码上遇到了麻烦。我需要根据给定的数量连续多次重复插入。

int ctr = int.Parse(txtquantity3.ToString());
int x=0;

SqlCommand cmd = new SqlCommand("INSERT INTO tbl_books (Book_num,Call_Num, Book_Title, Book_Author, Book_Publisher, Book_Quantity, Book_Pages, genre , Book_available,book_status) VALUES (@Bn, @Cn, @Title, @Author, @Publisher, @Quantity, @Pages, @Genre, @Available, @Status)");
cmd.CommandType = CommandType.Text;
cmd.Connection = con;

MessageBox.Show("Save successful!");

while (x <= ctr)
{
    x += 1;

    cmd.Parameters.AddWithValue("@Bn", txtbooknum3.Text);
    cmd.Parameters.AddWithValue("@Cn", txtcallnum3.Text);
    cmd.Parameters.AddWithValue("@Title", txttitle3.Text);
    cmd.Parameters.AddWithValue("@Author", txtauthor3.Text);
    cmd.Parameters.AddWithValue("@Publisher", txtpublisher3.Text);
    cmd.Parameters.AddWithValue("@Quantity", txtquantity3.Text);
    cmd.Parameters.AddWithValue("@Pages", txtpages3.Text);
    cmd.Parameters.AddWithValue("@Genre", txtgenre3.Text);
    cmd.Parameters.AddWithValue("@Available", txtquantity3.Text);
    cmd.Parameters.AddWithValue("@Status", "Available");

    con.Open();
    cmd.ExecuteNonQuery();
}

Answer 1

您不能以这种方式使用 AddWithValue。 SqlCommand 实例始终相同，因此使用相同的参数名称第二次调用 AddWithValue 会产生异常。您需要在每个循环中添加对

的调用

 while (x <= ctr)
 {
     x+=1;
     cmd.Parameters.Clear();
     ....

 }

但是，您的代码可以更改为在进入循环之前创建参数集合，并且在循环内仅更改参数值。
关于如何重写代码以引入更好的连接处理的完整示例如下....

// Using statement to close and dispose on exit
using(SqlConnection con = new SqlConnection(.....))
using(SqlCommand cmd = new SqlCommand(@"INSERT INTO tbl_books 
            (Book_num,Call_Num, Book_Title, Book_Author, 
            Book_Publisher, Book_Quantity, Book_Pages, genre , 
            Book_available,book_status) 
            VALUES (@Bn, @Cn, @Title, @Author, @Publisher, @Quantity, 
            @Pages, @Genre, @Available, @Status)", con))
{
    // Open the connection just one time here
    con.Open();

    // Create all the parameters required SPECIFYING the correct datatype
    cmd.Parameters.Add("@Bn", SqlDbType.NVarChar);
    ...... declare other parameters....

    // Start your loop  
    while (x <= ctr)
    {
        x+=1;

        cmd.Parameters["@bn"].Value = txtbooknum3.Text;
        .... set the value for other parameters at each loop ....
        cmd.ExecuteNonQuery();
    }
}

最后，如果您只需要插入特定数量的具有相同值的记录，那么只需在进入循环之前调用 Adds 设置值并执行 ExecuteNonQuery 所需的次数而不更改参数集合中的任何内容

using(SqlConnection con = new SqlConnection(.....))
using(SqlCommand cmd = new SqlCommand(@"INSERT INTO tbl_books 
            (Book_num,Call_Num, Book_Title, Book_Author, 
            Book_Publisher, Book_Quantity, Book_Pages, genre , 
            Book_available,book_status) 
            VALUES (@Bn, @Cn, @Title, @Author, @Publisher, @Quantity, 
            @Pages, @Genre, @Available, @Status)", con))
{
    con.Open();
    // Create all the parameters required SPECIFYING the correct datatype
    cmd.Parameters.Add("@Bn", SqlDbType.NVarChar).Value = txtbooknum3.Text;
    ...... declare other parameters and set the value before the loop....

    // Start your loop  
    while (x <= ctr)
    {
        x+=1;
        // Just execute the query three times
        cmd.ExecuteNonQuery();
    }
}