如何使用SQL批量复制来获取插入数据记录的身份 [英] How to get identities of inserted data records using SQL bulk copy

问题描述

我有一个ADO.NET 数据表约10万条记录。在该表中有一栏 xyID 有没有在它的价值，因为该列是自动生成的身份在我的SQL Server数据库。

I have an ADO.NET DataTable with about 100,000 records. In this table there is a column xyID which has no values in it, because the column is an auto-generated IDENTITY in my SQL Server database.

我需要检索生成的ID为其他进程。我要寻找一种方式来批量复制这个数据表到SQL Server数据库，并在同一个台阶，以填我的数据表与生成的ID。

I need to retrieve the generated IDs for other processes. I am looking for a way to bulk copy this DataTable into the SQL Server database, and within the same "step" to "fill" my DataTable with the generated IDs.

我怎样才能检索使用 SqlBulkCopy的类插入到表中的记录标识值？

How can I retrieve the identity values of records inserted into a table using the SqlBulkCopy class?

推荐答案

目前，我正在做这样的事情：

I'm currently doing something like this:

DataTable objects = new DataTable();
DataColumn keyColumn = new DataColumn("name", typeof(string));
DataColumn versionColumn = new DataColumn("version", typeof(int));
versionColumn.DefaultValue = iVersionID;

objects.Columns.Add(keyColumn);
objects.Columns.Add(versionColumn);

foreach (KeyValuePair<string, NamedObject> kvp in Directory)
{
    NamedObject o = kvp.Value;
    DataRow row = objects.NewRow();
    row[0] = o.Name;
    objects.Rows.Add(row);
}

using (SqlBulkCopy updater = new SqlBulkCopy(conn,
        SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null))
{
    updater.DestinationTableName = "object_table";
    updater.WriteToServer(objects);
}

string sQuery = @"SELECT id, name FROM object_table WHERE version = @ver";
using (SqlCommand command = new SqlCommand(sQuery, conn))
{
    SqlParameter version = new SqlParameter("@ver", SqlDbType.Int, 4);
    version.Value = versionID;
    command.Parameters.Add(version);

    command.CommandTimeout = 600;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            string key = (string)reader[1];

            NamedObject item = Directory[key];
            item.ID = (int)reader[0];
        }
    }
}

请注意，我们的数据设计使使用版本ID过滤为我们所有的新对象;每一行我们增加都会有相同的版本ID，我们已经previously除去已经有这个版本ID数据库的所有行。

Note that our data design enables filtering for all our new objects by using the version ID; every row we're adding will have the same version id, and we've previously removed any rows in the database that already had this version id.

不过，我选择查询当前超时的ExecuteReader中，即使是10分钟的窗口。所以，这不是我们最终的解决方案。

However, my select query is currently timing out in ExecuteReader, even with that 10-minute window. So this is not our final solution.

这篇关于如何使用SQL批量复制来获取插入数据记录的身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！