Data Philosophy Question: Of Sorts: Writing back data.

[Interwizard]

I'm relatively new to ADO.net. In my VB 6 days I would use ADO to write back to my data source directly and my VB code usually contained my select/update statements. Given that I am partial (though, not entirely biased) to generating my data code from scratch as opposed to having the wizards do it for me.

As I've moved to more DotNet development, I've found myself utilizing stored procedures increasingly. My question is one more of design philosophy and your experiences. How are you writing your data back to the Datasource? CommandBuilder? Some sort of complex update Stored Procedure? Update statements in the VB?

In my case as I've been using Stored Procedures for my select statements, I find myself questioning the "best" way to do it. I'd like to defer to your experiences on this matter..

Cheers,
Bill Dodd

 

[RiverGuy]

I use mostly stored procedures to write back data.

 

[chiph]

I probably should be using stored procedures, but I use SQL update/insert statements. I figure as long as I use ADO.NET parameters, I'm not losing that much performance, as my SQL will be placed in the procedure cache.

Chip H.

 

[Interwizard]

When you use Stored Procedures, does your SP build dynamically to only update fields that have been updated or does it update the entire record?

 

[RiverGuy]

The last few record by record data apps I've made were in VB 6. Most of my .Net stuff that is data aware is for adding new reocrds and displaying them. A lot of ASP.Net too. SO it doesn't really apply to me.

But more than likely if I was to make a data entry/editing app in .Net, I would use select statements in the wizard and let it generate my update command for me.

 

[chiph]

When you use Stored Procedures, does your SP build dynamically to only update fields that have been updated or does it update the entire record?

Stored Procs are not built dynamically - they're built by a programmer. So they'll update whatever columns you tell them to, because you'll have a SQL UPDATE statement in there.

The difference between running an UPDATE in your code, and running UPDATE in a stored procedure, is the database only has to parse & compile the stored procedure once when it's in a stored proc. When you submit an UPDATE from your code the database needs to parse it to verify that it's a valid SQL statement, and this takes time and CPU resources.

There's a caveat to this: If a submitted SQL statement is identical to one that the DB has seen recently, it will pull the compiled statement out of it's procedure cache, and not have to parse/compile again. The trick to doing this is using identical connection strings, and using ADO parameter objects in your SQL.

So by using the same SQL over and over again (differing only in the values stored in ADO.NET parameter objects), your code is almost (but not quite) as fast as a stored procedure.

Chip H.

 

[RiverGuy]

Actually you could theorettically have a stored proc built dynamically to update only certain columns. If you had optional parameters and used dynamic SQL to update specific columns. Although it wouldn't be worth doing.

 

[Interwizard]

....I've had several cases where I built an SQL Statement dynamically in the Stored procedure based on the parameters.
( The SQL Statement is treated as text and then passed into EXECSQL)

However, this was a pain in the butt.

My problem is and continues to be - in a disconnected model, the users in one of my apps may be disconnected for days. When they apply their changes back to the database, I do not want to do a blanket update of the whole record, only the fields that changed....

In the white-glove- all happy microsoft best practice universe this doesn't seem feasible. If I have a sp, similar to :

Update Table
Set Field1=@parm1, Field2=@parm2, Field3=@parm3
Where Key =@parmKey

then I have to pass in all values and always have to update the whole record...

I have to essentially pass in original and new values, compare them and build the sql statement to only update the fields I want.

My thinking and my post, is - my problem can't be that rare - that are others doing>

 

[RiverGuy]

Well it depends. On a table with less than 20 fields, it wouldn't be a big deal to update them all. If there was 10000 fields, then maybe so.

To tell you the truth, if you use the ADO.Net update, I'm not sure if it looks for changes at the record level or the record/field level.

 

[Interwizard]

Good point. Even ADO probably wrote the entire recordset back as it appeared in the cursor. I suppose the field level isn't that big of a deal. I don't hear other people being concerned with it. Probably just me being paranoid of spastic data issues. =)