Enterprise Library and Dataset Update

[Oscar Thornell]

Hi,

I have a relativley large/complex typed dataset that contains 7-8 tables and
some supporting relational tables (lookups) for many-to-many relations. A
good exampel would be a dataset that contained the whole Northwind database
with all tables and relations.

In my business logic I will retrive a populated instance of the dataset and
perform some operations (adding/deleting/updating of rows in various
tables...).

What is the best way to persist these changes back to the database (SQL
Server)?.

I would rely appreciate a link to a relativly complete exampel or if someone
could detail how/and what best practices is for a scenario like this one.

Regards
/Oscar

[Dries De Rudder]

Just use the SqlDataAdapter!

you can look for it on the msdn website msdn.microsoft.com/library en
search for the SqlDataAdapter class, there will be an example of how to
load and save data by using the DataAdapter class.

greetz

Oscar Thornell schreef:

Hi,

I have a relativley large/complex typed dataset that contains 7-8 tables and
some supporting relational tables (lookups) for many-to-many relations. A
good exampel would be a dataset that contained the whole Northwind database
with all tables and relations.

In my business logic I will retrive a populated instance of the dataset and
perform some operations (adding/deleting/updating of rows in various
tables...).

What is the best way to persist these changes back to the database (SQL
Server)?.

I would rely appreciate a link to a relativly complete exampel or if someone
could detail how/and what best practices is for a scenario like this one.

Regards
/Oscar

[Oscar Thornell]

Ok! Thanks Dries...
The SqlDataAdapter is of course the class that ADO.NET will use to transfer
data between a Dataset and persistent storage.
The core in my question is really what is the best practices to obtain the
SqlCommand instance for the current state of my dataset...
In other words: I don´t want to write the extreme amount of sql manually
that such an update would require...

///
I don´t want 1000 rows of this with coresponding sprocs:
// Establish our Insert, Delete, and Update commands
DBCommandWrapper insertCommandWrapper =
db.GetStoredProcCommandWrapper("AddProduct");
insertCommandWrapper.AddInParameter("@CategoryID", DbType.Int32,
"CategoryID", DataRowVersion.Current);
insertCommandWrapper.AddInParameter("@UnitPrice", DbType.Currency,
"UnitPrice", DataRowVersion.Current);

DBCommandWrapper deleteCommandWrapper =
db.GetStoredProcCommandWrapper("DeleteProduct");
deleteCommandWrapper.AddInParameter("@ProductID", DbType.Int32, "ProductID",
DataRowVersion.Current);

DBCommandWrapper updateCommandWrapper =
db.GetStoredProcCommandWrapper("UpdateProduct");
updateCommandWrapper.AddInParameter("@ProductID", DbType.Int32, "ProductID",
DataRowVersion.Current);
updateCommandWrapper.AddInParameter("@LastUpdate", DbType.DateTime,
"LastUpdate", DataRowVersion.Current);
///

So basically should I use the SqlCommandBuilder class here? Or what? What
support(or rather recommendations..) is there for Entlib here?
Maybee I am just stupid here..but I would rely like some advice.. ->
pointers to best practices code here...

/Oscar

"Dries De Rudder" <dr***@bestopia.be> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ...

Just use the SqlDataAdapter!

you can look for it on the msdn website msdn.microsoft.com/library en
search for the SqlDataAdapter class, there will be an example of how to
load and save data by using the DataAdapter class.

greetz

Oscar Thornell schreef:

Hi,

I have a relativley large/complex typed dataset that contains 7-8 tables
and some supporting relational tables (lookups) for many-to-many
relations. A good exampel would be a dataset that contained the whole
Northwind database with all tables and relations.

In my business logic I will retrive a populated instance of the dataset
and perform some operations (adding/deleting/updating of rows in various
tables...).

What is the best way to persist these changes back to the database (SQL
Server)?.

I would rely appreciate a link to a relativly complete exampel or if
someone could detail how/and what best practices is for a scenario like
this one.

Regards
/Oscar

[Dries De Rudder]

Oscar,

there is a way to simplify your problem the SqlCommandBuilder. This
class creates the InsertCommand, UpdateCommand and DeleteCommand for
you, based on the SelectCommand.

an example the way this works:

SqlConnection conn = new SqlConnection(...);
SqlCommand selectcmd = new SqlCommand("SELECT * FROM tablename",conn);

SqlDataAdapter da = new SqlDataAdapter(selectcmd);
SqlCommandBuilder cb = new SqlCommandBuilder(da);

and here everything is ready to be used.

the SqlCommandBuilder needs the SqlDataAdapter as a
constructor-parameter, it will use the SelectCommand property of the
SqlDataAdapter en create the three other Commands for you, and add them
to the SqlDataAdapter.

Be careful: Microsoft does advice to use the CommandBuilder only once,
just to create the sql-strings etc. For performance reasons you have to
retype the Insert, Update and Delete commands afterwards yourself.

so Oscar, I hope this is a more complete answer.

succes, Dries

Oscar Thornell schreef:

Ok! Thanks Dries...
The SqlDataAdapter is of course the class that ADO.NET will use to transfer
data between a Dataset and persistent storage.
The core in my question is really what is the best practices to obtain the
SqlCommand instance for the current state of my dataset...
In other words: I don´t want to write the extreme amount of sql manually
that such an update would require...

///
I don´t want 1000 rows of this with coresponding sprocs:
// Establish our Insert, Delete, and Update commands
DBCommandWrapper insertCommandWrapper =
db.GetStoredProcCommandWrapper("AddProduct");
insertCommandWrapper.AddInParameter("@CategoryID", DbType.Int32,
"CategoryID", DataRowVersion.Current);
insertCommandWrapper.AddInParameter("@UnitPrice", DbType.Currency,
"UnitPrice", DataRowVersion.Current);

DBCommandWrapper deleteCommandWrapper =
db.GetStoredProcCommandWrapper("DeleteProduct");
deleteCommandWrapper.AddInParameter("@ProductID", DbType.Int32, "ProductID",
DataRowVersion.Current);

DBCommandWrapper updateCommandWrapper =
db.GetStoredProcCommandWrapper("UpdateProduct");
updateCommandWrapper.AddInParameter("@ProductID", DbType.Int32, "ProductID",
DataRowVersion.Current);
updateCommandWrapper.AddInParameter("@LastUpdate", DbType.DateTime,
"LastUpdate", DataRowVersion.Current);
///

So basically should I use the SqlCommandBuilder class here? Or what? What
support(or rather recommendations..) is there for Entlib here?
Maybee I am just stupid here..but I would rely like some advice.. ->
pointers to best practices code here...

/Oscar

"Dries De Rudder" <dr***@bestopia.be> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ...

Just use the SqlDataAdapter!

you can look for it on the msdn website msdn.microsoft.com/library en
search for the SqlDataAdapter class, there will be an example of how to
load and save data by using the DataAdapter class.

greetz

Oscar Thornell schreef:

Hi,

I have a relativley large/complex typed dataset that contains 7-8 tables
and some supporting relational tables (lookups) for many-to-many
relations. A good exampel would be a dataset that contained the whole
Northwind database with all tables and relations.

In my business logic I will retrive a populated instance of the dataset
and perform some operations (adding/deleting/updating of rows in various
tables...).

What is the best way to persist these changes back to the database (SQL
Server)?.

I would rely appreciate a link to a relativly complete exampel or if
someone could detail how/and what best practices is for a scenario like
this one.

Regards
/Oscar

[Oscar Thornell]

Thanks Dries!

Some of the issues with SqlCommandBuilder like the extra roundtrip for
gathering metadata I can avoid by using a typed dataset. And for the sql
with all the WHERE clauses in this case I can live with it...

The thing that rely bothers me though is the approach taken for the actual
update...

adapter.Update(Table1);
adapter.Update(Table2);
adapter.Update(Table3);
....

I realise that this is the approach choosen by MS but I don´t like it (or
maybee understand the beauty of it..).
I would like to be able to just do one larger:

adapter.Update(myDataSet);

Somehow I hoped that I could come up with schema for this... :-(

/Oscar

"Dries De Rudder" <dr***@bestopia.be> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ...

Oscar,

there is a way to simplify your problem the SqlCommandBuilder. This class
creates the InsertCommand, UpdateCommand and DeleteCommand for you, based
on the SelectCommand.

an example the way this works:

SqlConnection conn = new SqlConnection(...);
SqlCommand selectcmd = new SqlCommand("SELECT * FROM tablename",conn);

SqlDataAdapter da = new SqlDataAdapter(selectcmd);
SqlCommandBuilder cb = new SqlCommandBuilder(da);

and here everything is ready to be used.

the SqlCommandBuilder needs the SqlDataAdapter as a constructor-parameter,
it will use the SelectCommand property of the SqlDataAdapter en create the
three other Commands for you, and add them to the SqlDataAdapter.

Be careful: Microsoft does advice to use the CommandBuilder only once,
just to create the sql-strings etc. For performance reasons you have to
retype the Insert, Update and Delete commands afterwards yourself.

so Oscar, I hope this is a more complete answer.

succes, Dries

Oscar Thornell schreef:

Ok! Thanks Dries...
The SqlDataAdapter is of course the class that ADO.NET will use to
transfer data between a Dataset and persistent storage.
The core in my question is really what is the best practices to obtain
the SqlCommand instance for the current state of my dataset...
In other words: I don´t want to write the extreme amount of sql manually
that such an update would require...

///
I don´t want 1000 rows of this with coresponding sprocs:
// Establish our Insert, Delete, and Update commands
DBCommandWrapper insertCommandWrapper =
db.GetStoredProcCommandWrapper("AddProduct");
insertCommandWrapper.AddInParameter("@CategoryID", DbType.Int32,
"CategoryID", DataRowVersion.Current);
insertCommandWrapper.AddInParameter("@UnitPrice", DbType.Currency,
"UnitPrice", DataRowVersion.Current);

DBCommandWrapper deleteCommandWrapper =
db.GetStoredProcCommandWrapper("DeleteProduct");
deleteCommandWrapper.AddInParameter("@ProductID", DbType.Int32,
"ProductID", DataRowVersion.Current);

DBCommandWrapper updateCommandWrapper =
db.GetStoredProcCommandWrapper("UpdateProduct");
updateCommandWrapper.AddInParameter("@ProductID", DbType.Int32,
"ProductID", DataRowVersion.Current);
updateCommandWrapper.AddInParameter("@LastUpdate", DbType.DateTime,
"LastUpdate", DataRowVersion.Current);
///

So basically should I use the SqlCommandBuilder class here? Or what? What
support(or rather recommendations..) is there for Entlib here?
Maybee I am just stupid here..but I would rely like some advice.. ->
pointers to best practices code here...

/Oscar

"Dries De Rudder" <dr***@bestopia.be> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ...

Just use the SqlDataAdapter!

you can look for it on the msdn website msdn.microsoft.com/library en
search for the SqlDataAdapter class, there will be an example of how to
load and save data by using the DataAdapter class.

greetz

Oscar Thornell schreef:

Hi,

I have a relativley large/complex typed dataset that contains 7-8 tables
and some supporting relational tables (lookups) for many-to-many
relations. A good exampel would be a dataset that contained the whole
Northwind database with all tables and relations.

In my business logic I will retrive a populated instance of the dataset
and perform some operations (adding/deleting/updating of rows in various
tables...).

What is the best way to persist these changes back to the database (SQL
Server)?.

I would rely appreciate a link to a relativly complete exampel or if
someone could detail how/and what best practices is for a scenario like
this one.

Regards
/Oscar