OleDb: DataSets versus ExecuteNonQuery(), ExecuteReader()

[Francis]

Hello,

My question is: is it better to use DataSets than the simple
ExecuteNonQuery() methods. I thought the DataSets would be a better
structured way of programming, but it seems to result in very lengthy
code with a lot of frustration during debugging.


This is my case:

I am creating a commerical website for a school ASP.NET project. The
code is written in C#.

I created a ShoppingCart using DataSets and DataGrids. This took me
multiple weeks to finish because of the many problems I encountered.

For example the CommandBuilder class does not work if your DataTable
is based on a SELECT-query with a 'join' in it. This means I had to
write my own Insert, Update and Delete commands with a LOT of
debugging...

This is what the code now looks like, ONLY the commands for the
shoppingcart:

DataTable dtShoppingCartChanges =
dsShopping.Tables["dtShoppingCart"].GetChanges();
if (dtShoppingCartChanges != null)
{
for (int i = 0; i < dtShoppingCartChanges.Rows.Count; i++)
{
OleDbCommand tempCommand = new OleDbCommand();

switch (dtShoppingCartChanges.Rows.RowState.ToString())
{
case "Modified":
{
// Update-command
string strUpdate = String.Format(
"Update tblShoppingCart " +
"Set Aantal = {0} " +
"Where ProdID = {1}",

dtShoppingCartChanges.Rows["Aantal"].ToString(),

dtShoppingCartChanges.Rows["ProdID"].ToString());
odbDataAdapterShoppingCart.UpdateCommand = new
OleDbCommand (strUpdate);
odbDataAdapterShoppingCart.UpdateCommand.Connection
= odbConnection;
}
break;
case "Added":
{
// Insert-command
string strInsert = String.Format(
"Insert into tblShoppingCart (UsrID, ProdID,
Aantal) " +
"Values ({0}, {1},{2})",

dtShoppingCartChanges.Rows["UsrID"].ToString(),

dtShoppingCartChanges.Rows["ProdID"].ToString(),

dtShoppingCartChanges.Rows["Aantal"].ToString());
odbDataAdapterShoppingCart.InsertCommand = new
OleDbCommand (strInsert);
odbDataAdapterShoppingCart.InsertCommand.Connection
= odbConnection;
}
break;
case "Deleted":
{
// Delete-command
string strDelete = String.Format(
"Delete from tblShoppingCart " +
"Where ProdID = {0}",
dtShoppingCartChanges.Rows["ProdID",
DataRowVersion.Original].ToString());
odbDataAdapterShoppingCart.DeleteCommand = new
OleDbCommand (strDelete);
odbDataAdapterShoppingCart.DeleteCommand.Connection
= odbConnection;
}
break;
}

try
{
odbDataAdapterShoppingCart.Update(dsShopping.Tables["dtShoppingCart"]);
}
catch (FormatException e)
//catch (DBConcurrencyException e)
{
}
}
}

The entire ShoppingCart page takes about 400 lines of code. I think
this is too much for a simple application. What do you think ?

Greetings,
Francis

 

[Miha Markic [MVP C#]]

Hi,

Hello,

My question is: is it better to use DataSets than the simple
ExecuteNonQuery() methods. I thought the DataSets would be a better
structured way of programming, but it seems to result in very lengthy
code with a lot of frustration during debugging.

Though a code might be lengthy (hura for flexibility) many things can be
automated (code generation using wizards perhaps) or done by using higher
level methods, such as MS Data Access Application Block for .NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp.
Or you might even create such methods by yourself.
Also, I don't see any showstoper bug there - there are some but really not
vital.

The entire ShoppingCart page takes about 400 lines of code. I think
this is too much for a simple application. What do you think ?

That you are not grasping the concept of flexibility and you want one method
that does everything. ;-)