我正在嘗試使用第一個表中的主鍵一次插入兩個表以插入第二個表，但我不知道如何去做。
我的表結構如下：
人
PersonId
FirstName
Surname
員工
EmployeeId
HoursWorked
PersonId表中Person是一個自動遞增的列。EmployeeId第二個表中的主鍵和外鍵應與PersonId.
uj5u.com熱心網友回復：
在 C# 中，您可以嘗試以下操作：
// define the INSERT query - insert a firstname,surname into "Person",
// and insert a row into the Emplyoee table with the new ID
// created by the first insert
string insertStmt = @"BEGIN TRANSACTION
INSERT INTO dbo.Person(FirstName, Surname) VALUES(@FirstName, @Surname);
DECLARE @NewPersonId INT = SCOPE_IDENTITY();
INSERT INTO dbo.Employee(EmployeeId, HoursWorked) VALUES(@NewPersonId, @HoursWorked);
COMMIT TRANSACTION;"
// define connection and command for inserting data
using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmdInsert = new SqlCommand(insertStmt, conn))
{
// Define parameters - adapt datatype and max length as required
cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar, 100);
cmdInsert.Parameters.Add("@Surname", SqlDbType.VarChar, 100);
cmdInsert.Parameters.Add("@HoursWorked", SqlDbType.Int);
// set parameter values
cmdInsert.Parameters["@FirstName"].Value = "John";
cmdInsert.Parameters["@Surname"].Value = "Doe";
cmdInsert.Parameters["@HoursWorked"].Value = 35;
// Open connection, execute query, close connection
conn.Open();
int rowsInserted = cmdInsert.ExecuteNonQuery();
conn.Close();
}
更新：正如@charlieface 所提到的，如果您只插入一行，則可以更簡潔地撰寫此代碼 - 如下所示：
// define connection and command for inserting data
using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmdInsert = new SqlCommand(insertStmt, conn))
{
// Define and set parameters
cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar, 100).Value = "John";
cmdInsert.Parameters.Add("@Surname", SqlDbType.VarChar, 100).Value = "Doe";
cmdInsert.Parameters.Add("@HoursWorked", SqlDbType.Int).Value = 35;
// Open connection, execute query, close connection
conn.Open();
int rowsInserted = cmdInsert.ExecuteNonQuery();
conn.Close();
}
uj5u.com熱心網友回復：
您需要指定要插入的列。
SET XACT_ABORT ON如果您有明確的交易，您也應該使用。還要注意多行字串的使用。
string queryString = @"
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO Person (FirstName, Surname)
VALUES(@firstName, @surname);
DECLARE @DataID int = scope_identity();
INSERT INTO Employee (EmployeeId, HoursWorked)
VALUES(@DataId, @hoursWorked);
COMMIT;
";
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482491.html