Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
CommandBuilder vs Stored Proc for updating
Message
 
 
À
25/01/2010 07:27:30
Mike Cole
Yellow Lab Technologies
Stanley, Iowa, États-Unis
Information générale
Forum:
ASP.NET
Catégorie:
ADO.NET
Versions des environnements
Environment:
C# 2.0
Divers
Thread ID:
01445437
Message ID:
01445782
Vues:
26
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>
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform