How to use SQL bulk copy to retrieve the IDs of added data records

ado.net database identity sqlbulkcopy sql-server

Question

I use ADO.NET.DataTable around 100,000 records. This table has a column that readsxyID which is an automatically produced column that has no valuesIDENTITY in the SQL Server database I use.

For other operations, I must get the created IDs back. I'm trying to find a method to replicate this in mass.DataTable within the same "step" to "fill" my data into the SQL Server database.DataTable with the IDs produced.

How can I get the identity values of entries that were added into a database using theSqlBulkCopy class?

1
9
3/24/2010 2:29:41 PM

Popular Answer

I'm actively engaged in the following:

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

Every row we're adding will have the same version id, and we've already deleted any database rows that had this version id, so take note that our data design allows filtering for all our new objects by using the version ID.

Even with the 10-minute interval, my select query is presently stalling out in ExecuteReader. So, this isn't the end of the line for us.

2
6/11/2010 6:41:47 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow