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

Question

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.

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?

Answer 1

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.