右键插入到表中,然后将其复制到另一个表中
本文关键字:复制 另一个 插入 右键 然后 | 更新日期: 2023-09-27 18:27:27
在我的应用程序中,用户将插入一本书。例如,某本书将插入3本。Table1.BookID=1,Table1.Copy=3,然后在另一个表中,这3本书将有它们的主键,因此它将是Table2.AccessionID=1,2,3Table2.BookID=1、1、1。
这是我目前正在做的事情,但正如Aaron Bertrand所说,这是一种糟糕的做法。
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()
(或者,对于多行INSERT
,使用OUTPUT
子句)。
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#爱好者,所以这是否是最好的方法,或者它是否会编译,我都不知道,但希望你能自己解决这个问题)。
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();
}