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

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


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.