Hi Bonnie,

Coming back to your suggestion on how to write a stored procedure for updating database, I was wondering if you could, please, clarify something. In your blog III you have a method SetAllParameters() that iterates through columns of the table. How do you make sure that the order of parameters in the stored procedure match the order of parameters sent when executing stored procedure from DAL?


>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>