Using a datagrid to update values in sql server (best practice)

[Mok]

Hello,
I want to pull information from a table in sql server, bind it to a
grid and update those values. I would like to know the best way to do
it. I used the configure data adapter wizard and it came up with a
crazy stored proc. As in this example I would only update [start date]
and [description]:

CREATE PROCEDURE [mok].[NewUpdateCommand]
(
@Description varchar(1000),
@Param2 datetime,
@Original_ID int,
@Original_Description varchar(1000),
@Original_Start_Date datetime,
@ID int
)
AS
SET NOCOUNT OFF;
UPDATE dbo.tblTasks SET Description = @Description, [Start Date] =
@Param2 WHERE (ID = @Original_ID) AND (Description =
@Original_Description OR @Original_Description IS NULL AND Description
IS NULL) AND ([Start Date] = @Original_Start_Date OR
@Original_Start_Date IS NULL AND [Start Date] IS NULL);
SELECT ID, Description, [Start Date] FROM dbo.tblTasks WHERE (ID =
@ID);
GO

Basically all this query does is receive start date and description. If
one of the values wasn't modified in the datagrid, then this query
won't modify that field. I'm thinking that since this query came out of
a generator, it may not be the most efficient. For example, always
sending the old values as parameters..

My question is, what is the best way to code the stored proc and the
application level code keeping in mind that I only want to call 1
update stored proc? From examining the datagrid tutorials where updates
are performed, they usually assume all the fields in the grid will be
updated (which is not always the case).

 

[Gerhard Pretorius]

We have the same problem..(Assuming ASP.NET 2.0)
The trick is to write on a specific sproc for the grid you are updating
from.
Make sure the fields you don't want to update is set as readonly.
Don't include the "key field in the grid columns, but make sure it is in the
datakeys of the grid.
Change the OldValuesParatmerFormat to "{0}" the default is "Original_{0}"

CREATE PROCEDURE [mok].[NewUpdateCommand]
(
@Description varchar(1000),
@Param2 datetime,
@ID int
)

Grid should ONLY have Description and Param2 as columns and paramaters. (Do
not specify ID as paramater, it is in the datakeys)
ASP.NET 2.0 add the Datakey value automatically as a extra paramater.

Use SQL Profiler and you will find
exec NewUpdateCommand @Description='sdsdf', @Param2 = '2005/09/04', @ID='3'
when you click update on the row you are editing...

BTW. SQL profiler is mans best friend (after dog) to see what really happens
when you do grid editing....

HTH

Gerhard

PS. Stay away from Generated SQL code from the designer... it is not
optimized.....use it only to see how it works once... after that... write
your own sprocs...