在表中插入然后将其复制到另一个表的正确方法 [英] Right Approach to Insert in Table then copy it to another table

问题描述

在我的应用程序中，用户将插入一本书.例如 someBook 将插入 3 个副本.Table1.BookID = 1, Table1.Copy = 3，然后在另一个表中，这 3 本书将有它们的主键，因此它将是 Table2.AccessionID = 1,2,3 Table2.BookID = 1, 1, 1.

In my application a user will insert a book. For example someBook will be insert with 3 copies. Table1.BookID = 1, Table1.Copy = 3, Then in another table those 3 books will have their primary key so it will be Table2.AccessionID = 1,2,3 Table2.BookID = 1, 1, 1.

这就是我目前正在做的事情，但正如 Aaron Bertrand 所说的那样，这是一种糟糕的做法.

This is what I current doing but a bad practice as what Aaron Bertrand said.

int BookTitlesID;
public void addBookTitle()
{
  int copy = int.Parse(textBox2.Text);
  try
  {
    using (SqlConnection myDatabaseConnection = new SqlConnection(myConnectionString.ConnectionString))
    {
      myDatabaseConnection.Open();

      using (SqlCommand mySqlCommand1 = new SqlCommand("INSERT INTO BookTitles(BookTitle, Copies) Values(@BookTitle,  @Copies)", myDatabaseConnection))
      {
        mySqlCommand1.Parameters.AddWithValue("@BookTitle", BookTitletextBox.Text);
        mySqlCommand1.Parameters.AddWithValue("@Copies", copy);
        mySqlCommand1.ExecuteNonQuery();
      }
    }
  }
  catch (Exception Ex)
  {
    MessageBox.Show(Ex.Message, "Exception");
  }
}

public void addBook()
{
  int copy = int.Parse(textBox2.Text);
  try
  {
    for (int x = 0; x < copy; x++)
    {
      using (SqlConnection myDatabaseConnection = new SqlConnection(myConnectionString.ConnectionString))
      {
        myDatabaseConnection.Open();
        using (SqlCommand mySqlCommand1 = new SqlCommand("INSERT INTO book(BookTitleID) Values(@BookTitleID)", myDatabaseConnection))
        {
          mySqlCommand1.Parameters.AddWithValue("@BookTitleID", BookTitlesID);
          mySqlCommand1.ExecuteNonQuery();
        }
      }
    }
  }
  catch (Exception Ex)
  {
    MessageBox.Show(Ex.Message, "Exception");
  }
}

private void Form_Load(object sender, EventArgs e)
{
  using (SqlConnection myDatabaseConnection = new SqlConnection(myConnectionString.ConnectionString))
  {
    myDatabaseConnection.Open();
    using (SqlCommand mySqlCommand1 = new SqlCommand("SELECT TOP 1 BookTitleID + 1 FROM BookTitles ORDER BY BookTitleID DESC", myDatabaseConnection))
    {
      string x = mySqlCommand1.ExecuteScalar().ToString();
      BookTitlesID = Convert.ToInt32(x);
    }
  }
}

private void button1_Click(object sender, EventArgs e)
{
  addBookTitle();
  addBook();
}

推荐答案

简化.最值得注意的是，正如您在其他相关问题中所讨论的，绝对没有出去看看当前 MAX 是什么是安全的，加 1，并假设这将是下一个生成的标识值.如果您在插入后检索它，您只能依赖该数字，而最可靠的方法是使用 SCOPE_IDENTITY()(或者，对于多个-row INSERT，一个 OUTPUT 子句).

Simplify. Most notably, as discussed in your other, related question, there is absolutely no safety in going out and seeing what the current MAX is, adding 1, and assuming that will be the next identity value generated. You can only rely on that number if you retrieve it after you have inserted, and the most reliable way to do that is to use SCOPE_IDENTITY() (or, for a multiple-row INSERT, an OUTPUT clause).

CREATE PROCEDURE dbo.InsertBook
  @BookTitle NVARCHAR(256),
  @Copies    INT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @BookTitleID INT;

  INSERT dbo.BookTitles(BookTitle, Copies) SELECT @BookTitle, @Copies;

  SELECT @BookTitleID = SCOPE_IDENTITY();

  INSERT dbo.Books(BookTitleID) SELECT @BookTitleID
  FROM (SELECT TOP (@Copies) rn = ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects ORDER BY [object_id]) AS y;
END
GO

现在您可以真正简化您的 C# 代码(我不是 C# 专家，所以这是否是最好的方法或者它是否可以编译超出了我的范围，但希望您可以自己解决这个问题).

Now you can really simplify your C# code (I'm not a C# guy, so whether this is the best approach or if it will even compile is beyond me, but hopefully you can sort that out on your own).

public void addBook()
{
  try
  {
    int Copies    = int.Parse(textBox2.Text);
    string BTitle = BookTitletextBox.Text
    using (SqlConnection conn = new SqlConnection ...blah blah...)
    {
      conn.Open();

      using (SqlCommand cmd = new SqlCommand("EXEC dbo.InsertBook", conn))
      {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BookTitle", BTitle);
        cmd.Parameters.AddWithValue("@Copies",    Copies);
        cmd.ExecuteNonQuery();
      }
    }
  }
  catch (Exception Ex)
  {
    MessageBox.Show(Ex.Message, "Exception");
  }
}

private void button1_Click(object sender, EventArgs e)
{
  addBook();
}

这篇关于在表中插入然后将其复制到另一个表的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！