G
Greg
As I understand it, DataAdapter.Update works off of a single DataTable. Is
it possible to do a multi table insert with multiple DataTables, all in one
transaction, using one call to DataAdapter.Update?
I need to do an insert into 5 Sql Server tables in one batch, running in one
sql transaction. In the current, non .net app, we do it all without sprocs.
Now, we have an insert sproc per Sql Server table. If I call them one by one
I cannot run in one transaction. I would like to have one sproc that does
the insert into all 5 tables and be able to set the input and output params
for this sproc on the InsertCommand for the Update call.
I know I can do this myself by building the command object, adding all the
sproc input\output params, retrieve all the param values from the Added
DataRows and then call ExecuteNonQuery. However, I would really like to take
advantage of the features provided when using DataAdapter.Update and a
Datatable.
I have followed all the guidelines in keeping our DataSet schema as close to
our sql tables as possible, that is each DataTable represents a table in Sql
server. The 5 DataTables above have DataRelations between them on the same
foreign keys in sql server. All works great for Updates and Deletes, however
Inserts are not so easy when using the DataAdapter Update model.
One thought I have is to keep the 5 DataTables. Then prior to the call to
Update create one DataTable with all the required fields; basically combine
all 5 DataTables into one datatable, then pass this datatable to the Update
method. This gets all my data in one datatable and allows me to take
advantage of the features of using DataAdapter.Update. But it feels like a
hack to me.
One thing we need on a successful insert is 5 keys that are created as a
result of the inserts. So, I would like to use Output Params and
SourceColumn in the Parameter type to handle this. My 2nd thought on this is
to do a select after all the inserts and return a resultset to the client.
Then, pull out the keys, modify the client DataTables and call
AcceptChanges.
Just checking to make sure I am not missing something here prior to coding
this up.
it possible to do a multi table insert with multiple DataTables, all in one
transaction, using one call to DataAdapter.Update?
I need to do an insert into 5 Sql Server tables in one batch, running in one
sql transaction. In the current, non .net app, we do it all without sprocs.
Now, we have an insert sproc per Sql Server table. If I call them one by one
I cannot run in one transaction. I would like to have one sproc that does
the insert into all 5 tables and be able to set the input and output params
for this sproc on the InsertCommand for the Update call.
I know I can do this myself by building the command object, adding all the
sproc input\output params, retrieve all the param values from the Added
DataRows and then call ExecuteNonQuery. However, I would really like to take
advantage of the features provided when using DataAdapter.Update and a
Datatable.
I have followed all the guidelines in keeping our DataSet schema as close to
our sql tables as possible, that is each DataTable represents a table in Sql
server. The 5 DataTables above have DataRelations between them on the same
foreign keys in sql server. All works great for Updates and Deletes, however
Inserts are not so easy when using the DataAdapter Update model.
One thought I have is to keep the 5 DataTables. Then prior to the call to
Update create one DataTable with all the required fields; basically combine
all 5 DataTables into one datatable, then pass this datatable to the Update
method. This gets all my data in one datatable and allows me to take
advantage of the features of using DataAdapter.Update. But it feels like a
hack to me.
One thing we need on a successful insert is 5 keys that are created as a
result of the inserts. So, I would like to use Output Params and
SourceColumn in the Parameter type to handle this. My 2nd thought on this is
to do a select after all the inserts and return a resultset to the client.
Then, pull out the keys, modify the client DataTables and call
AcceptChanges.
Just checking to make sure I am not missing something here prior to coding
this up.