开发者

How to get identities of inserted data records using SQL bulk copy

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

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.

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];
        }
    }
}

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.

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜