updating database using a dataset

[nids]

dear all
i am new to .net platform. i am working on a project in vb.net now.
i plan to use gotdotnet daab version 3 for my DAL.I must have
database independence for this project. i am able to retrieve data
from the datbase and fill it to a dataset. i have attached this
dataset to a data grid and can update the values in the dataset. but
i am not sure abtout how to update the database with the modification
in the datset.
i had went through the documentation of UpdateDatset method of
adohelper class but coudnt understand how to specify the commands in
it. Also i am not sure whether i will be using stored procedures .its
more likely that i will be using sql commands directly as text.
because i dont think stored procedures will be supported by all
database systems.
can some one please help me in this by posting sample codes of how
to update a database using dataset .

regards
nids

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Sahil Malik]

Nids,

You are on the right path by using the Data Access Application Block - for a
good start - however I like to see that as a start that you should try and
move on from. Even though the DAB is a good attempt in abstracting out the
intricacies of ADO.NET, I really don't see producing a good application
without understanding the internals of ADO.NET.

Updating the database - per the dataset is fairly simple. Essentially the
DAB has to do the same, and since DAB does come with the code; you should
peep under the hood and see this happening.

Assuming it is a simple dataset (not multiple tables) - get the changed
rows - and create an UpdateCommand or InsertCommand or DeleteCommand, and
attach it to the dataadapter. Then issue a DataAdapter.Update. Alternatively
the commands can be executed on their own using Command.ExecuteNonQuery.

To make this database independent, you can stick the interfaces and instead
of using SqlDataAdapter, use IDbDataAdapter. Ado.net 2.0 has a whole new
really cool factory mechanism to support DB independent code; but I'm
assuming you're doing ADO.NET 1.1 for now.

To sum it up all - there is no substitute (in my eyes atleast) to learning
ADO.NET. My advice would be to jettison DAB, and read about ADO.NET and
create your own DAL. (There is a good example of that in my book chapter
#10). DAB is a fantastic starter - agreed, but in an enterprise app, you
need to know whats going on inside.

,,, ,,, ,,, ,,, and now that you've gone through the pain of my harsh
preaching .. I will now attempt to solve your exact problem.

First of all, the latest I could find was DAB version #2 - but I am hoping
the advice still holds good (Where did you get version #3 from?) (PS : I
downloaded my DAB from
http://www.microsoft.com/downloads/...0A-9877-4A7B-88EC-0426B48DF275&displaylang=en,
where did you get version #3 from?)

"You wish to send an update to the d/b using the MS DAB". Okay thats easy -
you have the changes in your dataset right?

The function as you have rightly identified is "UpdateDataSet" - and just as
with a regular DBAdapter, you'd have to specify an
updatecommand/deletecommand/insertcommand. As I invited you to look under
the hood of DAB - here is the relevant code -

using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{

// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update (dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}



WHOAA !!! First of all it isn't DB Agnostic, and secondly this is exactly
what you would have done if you wrote your own DAL.

How you'd call it is -

SqlHelper.UpdateDataset(insertCommand, deleteCommand, updateCommand,
dataset, tablename);

Again, I'd recommend learning about ADO.NET and writing your own DAL.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik