Simpler UpdateDataSet?

Topics: Data Access Application Block
Mar 22, 2007 at 2:52 PM
In some of my tables I have large amount of fields, so using the UpdateDataSet of DAAB means I will have to create insert, delete and update command for all the fields, this is a lot of typing, as a result I have to do something like this:
DataSet ds = new DataSet();
Database db = DatabaseFactory.CreateDatabase();
string sql = "select * from tableA";
cmd = db.GetSqlStringCommand(sql);
db.LoadDataSet(cmd, ds, "tableA");
DbDataAdapter adapter = db.DbProviderFactory.CreateDataAdapter();
cmd.Connection = db.CreateConnection();
adapter.SelectCommand = cmd;
DbCommandBuilder cmd_builder = db.DbProviderFactory.CreateCommandBuilder();
cmd_builder.DataAdapter = adapter;
adapter.DeleteCommand = cmd_builder.GetDeleteCommand();
adapter.InsertCommand = cmd_builder.GetInsertCommand();
adapter.UpdateCommand = cmd_builder.GetUpdateCommand();

I guess my question is, wouldn't it make sense to give it a dataset, and it will do its things without us defining all those commands? Is that approach bad?
Mar 22, 2007 at 3:55 PM
Edited Mar 22, 2007 at 3:55 PM
There is a lot more that can be added to the SqlDatabase Class to give it simpler and additional functionality for sure.

I don't see the Enterprise Library Team writing it though, because a lot of it might be 1) particular to a certain database, 2) have a niche audience, and 3) could be abused if not quite understood.

For example, although not bad, what you do isn't the best performing approach as CommandBuilder needs to go out to the database and grab schema information in order to create the commands for you. Here is an article I wrote on it:

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

So, everytime this code runs you have this extra roundtrip call to the database to help you build commands. Not a big deal if performance isn't critical. However, some developers may unknowingly use this approach in performance sensitive applications and be angry that it was even offered as an option in the DAAB.

More to your problem, however. The UpdateDataSet Command is a bit verbose, but you don't have to included all the commands if you don't need them. You may only be inserting records and hence the Update and Delete Commands can be set to Null.

If you use this code a lot though and it works well for the applications you build, you can certainly add it to a custom SqlDatabase Class of your own. I added several methods to my own custom class for working with DataTables, SqlBulk Copy, Schema, etc.




David Hayden
Microsoft MVP C#