It probably only matters if you're doing something a bit different. I'll show you what I mean with an example:

I used web service methods to communicate with the backend. In doing so, the data that needed to update the database was passed as an XML string over the wire. In order to do this with the smallest XML footprint (IOW, not by using a diffgram), I proceeded in this manner:

DataSet dsChanged = ds.GetChanges();
DataSet dsDeleted = ds.GetChanges(DataRowState.Deleted);

string ChangedXML = dsChanged.GetXml();
dsDeleted.RejectChanges(); // necessary, otherwise you will get no XML
string DeletedXML = dsDeleted.GetXml();

MyWebService.Save(dsChanged, dsDeleted);

Now, because I'm passing XML in this manner, I've lost the row state. IOW, when these XML strings are re-serialized server-side into a ChangedDataSet and a DeletedDataSet, since this XML (done in this manner) contains no row state, neither will the new server-side DataSets. I rely on PKs < =0 to indicate an added row, everything else is an update. And obviously, everything contained in the DeletedDataSet will be deleted rows.

The above methodology would not work with the DataAdapter.Update() method for obvious reasons.

The only way to use the DataAdapter.Update() is to have passed data that contains the row state, which means passing the DataSet itself to your server-side Save() method. Going over a web service, I think that .NET will automatically serialize the DataSet into XML for you, but it's serialized into a DiffGram which has a much, *much* larger footprint than what I outlined above. At any rate, I don't recommend sending a DataSet in this manner over a web service. If you're not using a web service, then that's a different matter.

Anyway, I hope that cleared it up a bit.

~~Bonnie


>Thank you, Mike. But do you think it is better to perform the update this way (using DataAdapter) or - if I know that I only need to update one row and it is an update (not insert) to call the UpdateStoredProcedure directly (without use of DataAdapter.update() method)?
>
>>The DataAdapter is smart enough to perform multiple methods on each needed row. For example, if two rows need updating, the DataAdapter will call the update functionality (however you set it up) for each row. If you would turn on your SQL Profiler you would see the update Stored Proc being called twice.
>>
>>>Hi Bonnie,
>>>
>>>I hope you don't mind if I ask you another question on this topic of calling stored procedure for updating database from a dataset (or someone who can, please, chime in).
>>>
>>>I thought that in order to update a database from the changes to a dataset (using stored procedure approach) the stored procedure should be executed (for example a stored procedure that you wrote below) passing parameters to the stored procedure. By executed I thought simply called with parameters.
>>>
>>>But then I read in a book I have on WinForm binding that the author describing a code of calling a stored procedure for updating database as following (in pseudo code):
>>>
>>>1. Create data adapter
>>>2. Create SqlCommand for insert as for example:
>>>

>>>SqlCommand insertCmd = SqlCommand('InsertStoredProcedure', conn);
>>>insertCmd.CommandType = CommandType.StoredProcedure;
>>>insertCmd.Parameters.Add(.....);
>>>

>>>3. Create SqlCommand for udpating as for exampe:
>>>

>>>SqlCommand updateCmd = SqlCommand('UpdateStoredProcedure', conn);
>>>updateCmd.CommandType = CommandType.StoredProcedure;
>>>updateCmd.Parameters.Add(.....);
>>>

>>>4. Create SqlCommand for delete.. similar approach
>>>5. Associate each command with the adapter
>>>

>>>m_Adapter.InsertCommand = insertCmd;
>>>m_Adapter.UpdateCommand = updateCmd;
>>>m_Adapter.DeleteCommand = deleteCmd;
>>>

>>>6. Call UPDATE method of the adapter passing it the dataset and the name of the table.
>>>
>>>What confused me a little is that the author calls UPDATE method of the adapter instead of calling stored procedure directly. Is this the right approach?
>>>
>>>TIA.
>>>
>>>>Hi Dmitry,
>>>>
>>>>Using StoredProcs is preferable and does not really entail that much extra work. What we've done in the past is have two basic stored procs for each table ... a PUT (which does both Inserts and Updates, depending on the value of the PK) and a DELETE. The PUT has parameters for every column in the table, the DELETE has only the PK as a parameter. Also, in the PUT proc, if you set up the TSQL parameters and the update of them this way:
>>>>
>>>>

>>>>CREATE PROCEDURE MyTablePut
>>>>	@mytablekey		bigint = NULL OUTPUT,
>>>>	@startdatetime		datetime = NULL,
>>>>	@enddatetime		datetime = NULL,
>>>>	@description		varchar(100) = NULL
>>>>	-- etc.etc.etc,
>>>>AS
>>>>	SET NOCOUNT ON
>>>>	SET XACT_ABORT ON
>>>>
>>>>	BEGIN TRANSACTION
>>>>	IF (@mytablekey <= 0 OR @mytablekey IS NULL)
>>>>	BEGIN
>>>>		-- Insert Values into MyTable
>>>>		INSERT MyTable
>>>>			(startdatetime,
>>>>			enddatetime,
>>>>			description)
>>>>		SELECT 
>>>>			@startdatetime,
>>>>			@enddatetime,
>>>>			@description
>>>>
>>>>		SELECT @mytablekey = SCOPE_IDENTITY()
>>>>	END
>>>>	ELSE
>>>>	BEGIN
>>>>		-- Update MyTable
>>>>		UPDATE MyTable
>>>>		SET startdatetime = ISNULL(@startdatetime, startdatetime),
>>>>		    enddatetime = ISNULL(@enddatetime, enddatetime),
>>>>		    description = ISNULL(@description, description)
>>>>		WHERE mytablekey = @mytablekey
>>>>	END
>>>>
>>>>	COMMIT TRANSACTION
>>>>

>>>>
>>>>you don't have to worry whether or not you pass every parameter in your DAL.
>>>>
>>>>As far as how much code is necessary, you can easily have a method in your DAL that simply spins through every column in your table and programmatically adds the SqlParameters ... you simply pass the DataRow and it adds the paramters for each column in the DataRow. See the SetAllParameters() method in my third blog about the subject (http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html).
>>>>
>>>>Anyway, something to think about.
>>>>
>>>>~~Bonnie
>>>>
>>>>
>>>>
>>>>
>>>>>Hi,
>>>>>
>>>>>I am sorry if this question has been asked before (and maybe even many times). But I can't seem to understand what is a better approach for updating database (SQL Server) from a dataset (e.g. of 1 table with 1 row).
>>>>>
>>>>>1. Calling stored procedure.
>>>>> With this approach, I have to "manually" create SqlParameter object and add a parameter for each column that has been changed. Seems like a lot of coding.
>>>>>2. Using SqlCommandBuilder. This approach seems to be less code as the work of creating an Update command is dynamically done by the adapter.
>>>>>
>>>>>I am probably simplifying too much. But to me the 2nd approach seems to be win-win. But do I not see something that make some developers use the 1st approach?
>>>>>
>>>>>TIA>