如何在使用SqlBulkCopy时从身份主键列中获取新插入的id

Question

我正在使用c#和。net Core 6。我想一次批量插入大约100行到数据库中，并从BigInt标识列中获取它们的id。

我已经尝试了很多不同的变体，但仍然没有工作的解决方案。当我预览table变量时，Id列有DbNull值，而不是新插入的Id。

如何获得新插入行的id ?

What I have:

SQL Server表:

CREATE TABLE dbo.ExamResult 
(
Id bigint IDENTITY(1, 1) NOT NULL,
Caption nvarchar(1024) NULL,
SortOrder int NOT NULL,
CONSTRAINT PK_ExmRslt PRIMARY KEY CLUSTERED (Id)
) ON [PRIMARY]
GO

c#代码:

private static void BulkCopy(IEnumerable<ExamResultDb> examResults, SqlConnection connection)
{
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, null))
{
var table = CreateDataTable(examResults);
bulkCopy.DestinationTableName = "ExamResult";
bulkCopy.EnableStreaming = true;
foreach (var item in table.Columns.Cast<DataColumn>())
{
if (item.ColumnName != "Id")
{
bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
}
using (var reader = new DataTableReader(table))
{
bulkCopy.WriteToServer(reader);
}
}
}
private static DataTable CreateDataTable(IEnumerable<ExamResultDb> examResults)
{
var table = new DataTable("ExamResult");
table.Columns.AddRange(new[]
{
new DataColumn("Id", typeof(long)),
new DataColumn("Caption", typeof(string)),
new DataColumn("SortOrder", typeof(int))
});
////table.Columns[0].AutoIncrement = true;
////table.PrimaryKey = new[] { table.Columns[0] };
foreach (var examResult in examResults)
{
var row = table.NewRow();
row["Caption"] = examResult.Caption;
row["SortOrder"] = examResult.SortOrder;
table.Rows.Add(row);
}
return table;
}

Answer 1

您需要在插入上使用OUTPUT子句，但是Bulk Copy不允许这种自定义

虽然没有那么简洁，但您可以通过使用表值参数和自定义INSERT语句来实现这一点。TVP使用大容量复制机制，所以这仍然应该是相当快的，尽管将有两个插入:一个插入到TVP，一个插入到实际表。

首先创建一个表类型

CREATE TYPE dbo.Type_ExamResult AS TABLE
(
Caption nvarchar(1024) NULL,
SortOrder int NOT NULL
);

这个函数将迭代SqlDatRecord

private static IEnumerable<SqlDataRecord> AsExamResultTVP(this IEnumerable<ExamResultDb> examResults)
{
// fine to reuse object, see https://stackoverflow.com/a/47640131/14868997
var record = new SqlDataRecord(
new SqlMetaData("Caption", SqlDbType.NVarChar, 1024),
new SqlMetaData("SortOrder", SqlDbType.Int)
);
foreach (var examResult in examResults)
{
record.SetString(0, examResult.Caption);
record.SetInt32(0, examResult.SortOrder);
yield return record;  // looks weird, see above link
}
}

最后插入使用OUTPUT

private static void BulkCopy(IEnumerable<ExamResultDb> examResults, SqlConnection connection)
{
const string query = @"
INSERT dbo.ExamResult (Caption, SortOrder)
OUTPUT Id, SortOrder
SELECT t.Caption, t.SortOrder
FROM @tvp t;
";
var dict = examResults.ToDictionary(er => er.SortOrder);
using (var comm = new SqlCommand(query, connection))
{
comm.Parameters.Add(new SqlParameter("@tmp", SqlDbType.Structured)
{
TypeName = "dbo.Type_ExamResult",
Value = examResults.AsExamResultTVP(),
});

using (var reader = comm.ExecuteReader())
{
while(reader.Read())
dict[(int)reader["SortOrder"]].Id = (int)reader["Id"];
}
}
}

注意，上面的代码假设SortOrder是要插入的数据集中的一个自然键。如果没有，那么你需要添加一个，如果你没有插入该列，那么你需要一个更复杂的MERGE语句来访问OUTPUT中的该列，就像这样:

MERGE dbo.ExamResult er
USING @tvp t
ON 1 = 0 -- never match 
WHEN NOT MATCHED THEN
INSERT (Caption, SortOrder)
VALUES (t.Caption, t.SortOrder)
OUTPUT er.Id, t.NewIdColumn;