Doing an IDENTITY_INSERT from ADO.NET

  • Thread starter Thread starter Nevyn Twyll
  • Start date Start date
N

Nevyn Twyll

Situation:
I have a bunch of data in a type-specific System.Data.DataTable that matches
a table in my server.

I want to Insert all the data from my DataTable into the SQL Server table
using ADO and a SqlCommand (probably using the Command builder, because I
don't want to hand-code anything). I'm okay with the table's data all being
deleted.
HOWEVER, the table has an Identity Column, and I need to make sure that the
values in my in-memory DataTable are preserved exactly into the SQL Server
Table.

What's the easiest way to do this?

Thanks in advance!

- Nevyn
 
Hi,

Check out
Retrieving Identity or Autonumber Values
..net help topic.
 
Nevyn,

The first problem with this scenario is that your table has an Identity
column set on it. So you'll first have to set IDENTITY_INSERT to off on your
Sql Server, or SQL Server won't let you insert into that table with
explicitly defined values - as you are trying to do (you need to copy the
keys from the datatable too .. right?). Alternatively you can choose not to
use identity columns.

The second issue would be that you will have to specify the column names in
your insert statement, which I am pretty sure CommandBuilder does.

Once you have taken care of the above two, here is what I'd do.

Use command builder (if you must use that ugly generated query), and get the
insert command text.
Create another command that looks like this "Delete from yourtable"

Then on the relevant connection, do a begintransaction, associate the
transaction to both of the above commands - one your delete command, and
second your command builder generated insert command.

Then execute the delete command first
command builder command next
commit the transaction.

Thats it.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Hey, Sahil.

This works like a charm, except the metadata that CommandBuilder gets from
the database makes it exclude the IDENTITY column from the insert.

Is there any way to alter the metadata the SqlCB is using, or...?

- Nevyn
 
Sahil is correct on both counts.

The SqlCommandBuilder recognizes the identity column and omits it from
the INSERT query. It is unaware of the IDENTITY_INSERT switch and there's
no way to force it to push the current value from the DataTable into the
database.

Your best bet would be to use the SqlCommandBuilder's (or VS.NET
DataAdapter Configuration Wizard's) updating logic as a starting point and
modify the logic to include the client-generated values.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
Back
Top