Getting just generated Pkeys from SQL Server

[bernardmanning]

Hi,

Could somebody give me some clues as to what is the best way to get a newly generated pkey value back from sql server.

I'm using a dataset and using the sqlcommandbuilder to automatically generate all my insert, delete and update commands. it looks a little like this :

Dim CommandBuilder As New SqlCommandBuilder(DataAdapter)
DataAdapter.DeleteCommand = CommandBuilder.GetDeleteCommand()
DataAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand()
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand()

Dim ObjNewRow As DataRow
ObjNewRow = dataset.Tables(0).NewRow
dataset.Tables(0).Rows.Add(ObjNewRow)

I have my tables set up with integer based primary key fields which are set up to be auto-incrementing identity fields.

What's the most efficent and flexible way of getting back the pkey from sql server so that it appears in the table in my dataset.

I've read the MS example of creating stored procedures within sql server which perform the insert and pass the pkey back, but this mean I'd have to create a seperate stored procedure for each table, maybe a seperate sp if the same table has a different set of fields being updated

Is they a better way?

Many thanks, Bernard

 

[ThatRickGuy]

The Stored Procedure route is a fairly well traveled one. In more complexe systems with large databases, it often easier/faster to create a script that makes the generic update/insert/delete stored procedures for each table. As for the different fields, you can get arround most of that with parameters. The more advanced to procedure though, the more likely you will have to make another stored procedure.

The other option is to just build your SQL on the fly/hard coded in the app. This is good for if you have an inconsistant back end, or a read only data source, where either you can't create stored procedures or you can't be garunteed of their consistency.

-Rick

----------------------

http://www.ringdev.com