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.
精彩评论