Updating back thru DAAB when DataSet/DataTable comes from elsewhere

Topics: Data Access Application Block
Feb 22, 2007 at 2:16 AM
OK, so I'm the new guy w/ all the questions, if I need to be 'smacked down', somebody please say so.

Trying to build some common methods of updating tables in Oracle when the actual DataTable that I'm passing back thru the DAAB came from somewhere else. For example, my DataTable was populated thru the DAAB as a select from a view (or even a stored proc returning a refcursor). The view is a subset of fields from my table. When I send the DataTable back thru the DAAB, I use GetSqlCommand("select * from mytable") and then pass the DataTable. This works fine when my view is select * from mytable, but not when it's a subset of fields.

It would seem to make sense that as long as my DataTable (generated from a view) contains fields that comprise a unique key, or the actual primary key, that the update would still work and the internal insert/update/delete command would only use the fields present in the DataTable.

Any suggestions on how this would work, or should work, going back thru the DAAB ?

Thanks,
Chris
Feb 22, 2007 at 3:55 PM
The DataTable doesn't really care where its data has come from - view, stored procedure, etc.

If you have modified, inserted, or deleted any rows in the DataTable, you just need to make sure that when you call Database.UpdateDataSet(...) that you have defined Insert, Update, and/or Delete Commands that will properly execute against your database.

The Database.UpdateDataSet command looks like this:

Database.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, updateBehavior);

One option is to manually create those commands. Another option is to use the proper DbCommandBuilder Class to automatically build them for you based on the SelectCommand used to get the data. The only requirement for DbCommandBuilder is that the SelectCommand contain a primary key or unique column to properly build the other commands.

I talk a little about DbCommandBuilder from a SQL Server point of view here:

SqlCommandBuilder and DbCommandBuilder - Generating Update Insert Delete Queries From Select Query

I recommend looking at the DbCommandBuilder Documentation if you want to use it:

DbCommandBuilder

Btw, EntLib 3.0 supports BatchUpdates which will be nice in these scenarios for a bit of speed boost :)

Regards,

Dave

________________________

David Hayden
Microsoft MVP C#
Feb 22, 2007 at 5:07 PM
Thanks...

My codes is currently using the DbCommandBuilder, my function takes my DataTable (which was populated from a view w/ fewer fields than the underlying table, let's call it mytable), constructs the base DbCommand using 'select * from mytable'. There is where my problem seems to lie. When the DbCommandBuilder takes the base DbCommand and generates the ins/upd/del commands, and I call db.UpdateDataSet, I get a concurrency error. But, if my DataTable (i.e. view) contains all the fields my_table, it works fine.

What I haven't had time to try today is to build my basd DbCommand by generating the fields right out of my DataTable. That may get me around the problem. I guess I was hoping that it was being handled down in the data block.

Chris
Feb 22, 2007 at 6:22 PM
Solved...that was my problem, using 'select *' as the select statement of the base command sent into the command builder. After I changed it to build off of the fields in the datatable, the update worked as expected.

Is that something that the DAAB should do internally, or is this 'by design' for some reason that I'm missing?

Chris