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>