question about sql update

[suzy]

hello,

i have a table with 10 fields. i want to write an update stored procedures
so i can update any number of fields in the table, but i don't want to write
a seperate sp for every combination of parameters.

is there a way around this problem? and do i need to do anything special
when calling my sp from my c# code?

thanks.

 

[William Ryan eMVP]

Hi Suzy:

hello,

i have a table with 10 fields. i want to write an update stored procedures
so i can update any number of fields in the table, but i don't want to write
a seperate sp for every combination of parameters.

is there a way around this problem? and do i need to do anything special
when calling my sp from my c# code?

thanks.

You'll have two problems to address that I can think of. 1) If the
parameter is expected, you'll have to pass it in or give it a default value.
2) You'll only want to Update fields based on parameters that have changed.
so if @FirstValue is what you specified as the default, you don't want to
overwrite the 'good' value with the default.

Dealing with the first problem is pretty easy, just give the params default
values on the server side.
http://www.knowdotnet.com/articles/defaultparameters.html
You can also specify the sourceColumn in the Parameter constructor
http://msdn.microsoft.com/library/d...systemdatasqlclientsqlparameterclasstopic.asp
Which will give you the defaults that you need and make sure everythign is
mapped correctly. For simplicity, this is probably all you really need to
do , not write code for every scenario of the proc.



However, I came across a very interesting approach in Rockford Lhotka's
Business Objects bookl. If you pull over your data and model your objects
according to the data in the db, store the objects in a collection, then
just map each of Param values to its respective field in the object. This
way, each object you have will have the exact values taht were in the DB
originally. You can change any of the fields but the ones you didn't change
will still match the database values. Then you can pass in the object to
your function which builds the parameters and calls the proc. in doing so,
you'll do away with the problem of parameters and you won't have to check
your default values before calling the Set in your proc b/c the 'default'
value will in fact the the value that's already in the DB.

For instance, I have a Table with ID, FirstName, LastName and DOB

I create a Person Object with Four Properties adn then call DataAdapter.Fill
and grab each of the four fields in a DataTable. now, each time I do
anything, update, insert delete etc, I can create an instance of the object
and set it's values according the the datatable. So, the only things in my
collection will be records that have changed. So, now I pass the
collection to a function that calls the update and I can walk through it
specifying the parameters which will map directly to the given object in the
collection and call executenonquery after I set them.

This is the 'short summary' of the concept and doesn't do Lhotka's idea
justice, but you may want to check out his book if you have some time b/c
it's a really cool approach.

HTH,

Bill

 

[suzy]

Hi William,

I understand the method you mentioned (Rockford Lhotka), but unfortunately I
can't model that as I'm writing a generic tool so I have no idea how many
fields or what type the table will have beforehand.

With regards to giving paramters default values, I didn't expect this to be
a problem for updates, maybe I am wrong? Let me explain...

If I am updating a table row with new value(s), then the only paramters I
want to pass in are the fields I want to update (and also the primary key
for reference). At the moment, the only way I can think of making this work
is to write a single SP that takes all fields as paramters. Then before I
make the update, I do a retrieve and populate the paramters collection with
existing values. Finally, I overwrite the parameters with any new values
that need updating before executing the SP.

This seems a little long winded, plus I'd be making 2 hits to the DB. But I
can't think of any other way (apart from writing separate SPs for every
combination of paramters which is a definate no no!).

 

[William Ryan eMVP]

Suzy:

You definitely don't have to hit the DB twice. Are you doing this so that
you can detect if any of the values have changed? You'll already have the
original values in your datatable as well as the changed ones. However,
depending on the type of concurrency you want to use, you may not need
these. If you use the DataAdatper Configuration Wizard for instance and
turn off the Optimistic Concurrency option, all that it does is leave fields
other than the primary key field out of the where clause. However, if you
don't specify this, it will specify every field that's in the query in the
Where clause. With that said, if your reason for hitting the DB twice is to
get the most current changes so your Where clause will match, I'd recommend
just firing the query and then trap DBConcurrency exception. b/c they won't
necessarily happen and if they do, it's easy enough to respond
http://www.dotnet247.com/247referen...ial/552C60EC-AE3C-4038-B3AB-28A147E4E06A.dcik

If you aren't using this approach to deal with concurrency, then you can
simply write your proc so that you have Update SomeTable Set FirstField=
@FirstParam , SecondField = @SecondParam (etc) Where PrimaryKeyField =
@PrimaryKeyFieldValue... you won't need to know the rest and you'll know the
PK value from the datatable. If you definitely want to test every value and
not jsut the PK, you can just use the constructor which includes a
sourceColumn in it for the parameters in the Set part of the Param. Then in
the Where part, assuming you had "Where FirstField =@OriginalValue And
SecondField = @SecondValue " you could use SqlParameter param ;
param = ParameterCollection.Add("@OriginalValue" SqlDbType.Integer, 0,
"FirstField");
param.SourceVersion = DataRowVersion.Original;

This will give you the original values of each field you reference.

If you haven't read David Sceppa's ADO.NET core Reference, it's one of the
best books I've read on the subject...he discusses this approach in depth.

Anyway, Can you show me a sample of the Proc that you want to use? If I had
that I could walk you through it with an actual example. Either way though,
you don't have to make multiple trips to the db.

HTH,

Bill

 

[suzy]

Hi, thanks for offering help. Here is my sp (this assumes you are passing
in all parameters):

CREATE PROC UpdateUserById

@UserId bigint,
@UserTypeId bigint,
@Username varchar (50),
@Password varchar (50),
@LastLogin datetime
AS

UPDATE User SET

UserTypeId = @UserTypeId,
Username @Username ,
Password @Password ,
LastLogin @LastLogin

WHERE User.UserId = @UserId

 

[suzy]

Sorry, here is the corrected syntax version...

CREATE PROC UpdateUserById

@UserId bigint,
@UserTypeId bigint,
@Username varchar (50),
@Password varchar (50),
@LastLogin datetime
AS

UPDATE [User] SET

UserTypeId = @UserTypeId,
Username = @Username ,
Password = @Password ,
LastLogin = @LastLogin

WHERE [User].UserId = @UserId

 

[William Ryan eMVP]

Suzy:

Do you have any code that adds the parameters? That's the necessary step
and in the constructor where you are adding the params, specify the
SourceColum

 

[suzy]

Hi William,

No I'm afraid I don't have any update code at the moment. I was just
thinking it through in my head before I started coding. One thing I should
mention though is that I am not using a data adapter as the majority of the
time I will be updating 1 row at a time (not batch updating).

Suzy:

Do you have any code that adds the parameters? That's the necessary step
and in the constructor where you are adding the params, specify the
SourceColum

Sorry, here is the corrected syntax version...

CREATE PROC UpdateUserById

@UserId bigint,
@UserTypeId bigint,
@Username varchar (50),
@Password varchar (50),
@LastLogin datetime
AS

UPDATE [User] SET

UserTypeId = @UserTypeId,
Username = @Username ,
Password = @Password ,
LastLogin = @LastLogin

WHERE [User].UserId = @UserId

 

[William Ryan eMVP]

Suzy:

dataAdapter's don't support batch updates yet unfortunately...can't wait
until ADO.NET 2.0 in which they do. Anyway, if you set the source column
property of your params, you should be good to go. I used your procs
building dummy tables, and all should work fine once you specify the
sourceColumn. Looks like you are on the right track, and for what it's
worth, kudos for thinking it out first..I'm often too guilty of coding away
when I should be writing and thinking ;-)

Hi William,

No I'm afraid I don't have any update code at the moment. I was just
thinking it through in my head before I started coding. One thing I should
mention though is that I am not using a data adapter as the majority of the
time I will be updating 1 row at a time (not batch updating).

Suzy:

Do you have any code that adds the parameters? That's the necessary step
and in the constructor where you are adding the params, specify the
SourceColum

Sorry, here is the corrected syntax version...

CREATE PROC UpdateUserById

@UserId bigint,
@UserTypeId bigint,
@Username varchar (50),
@Password varchar (50),
@LastLogin datetime
AS

UPDATE [User] SET

UserTypeId = @UserTypeId,
Username = @Username ,
Password = @Password ,
LastLogin = @LastLogin

WHERE [User].UserId = @UserId

 

[suzy]

Hi William

Sorry if I am being stupid but could you give an example please? The reason
I ask is because I have looked in msdn about the sourceColumn property but I
can only see examples which use dataadapters/datatables. I have decided
(wisely I hope) that I am going to bypass using data adapters and just call
my stored procedure directly.

I am still new to asp.net and ado.net and maybe I am still thinking along
the old fashioned way of classic asp/vb6 but my ultimate aim is to use my db
access tool for asp.net pages, and I don't want to waste too much resources
on storing datasets in cache/session, etc. so I've decided to call the db on
every hit of a web page.

I hope I am making myself clear.

Suzy:

dataAdapter's don't support batch updates yet unfortunately...can't wait
until ADO.NET 2.0 in which they do. Anyway, if you set the source column
property of your params, you should be good to go. I used your procs
building dummy tables, and all should work fine once you specify the
sourceColumn. Looks like you are on the right track, and for what it's
worth, kudos for thinking it out first..I'm often too guilty of coding away
when I should be writing and thinking ;-)

Hi William,

No I'm afraid I don't have any update code at the moment. I was just
thinking it through in my head before I started coding. One thing I should
mention though is that I am not using a data adapter as the majority of the
time I will be updating 1 row at a time (not batch updating).

Suzy:

Do you have any code that adds the parameters? That's the necessary step
and in the constructor where you are adding the params, specify the
SourceColum
Sorry, here is the corrected syntax version...

CREATE PROC UpdateUserById

@UserId bigint,
@UserTypeId bigint,
@Username varchar (50),
@Password varchar (50),
@LastLogin datetime
AS

UPDATE [User] SET

UserTypeId = @UserTypeId,
Username = @Username ,
Password = @Password ,
LastLogin = @LastLogin

WHERE [User].UserId = @UserId

 

[William Ryan eMVP]

Ohhh, I got you. The whole time I thought you wanted to use Stored
Procedures with DataAdapters (you can use them in multiple scenarios).

You can do this, but you'll have to loop through your rows each time, set
the param values to each of the fields in the DB and then executenonquery.

I'd really advise against this...If you do this, you'll either have to
ignore concurrency, or you'll have to wire in a Timestampt column to check
it. THis is fine if you ahve a stateless middle tier, but in most
scenarios, this is a bit much.You'll have to use aDataadapter to fill your
datatable (or go through a lot of excess stuff with a datareader then copy
it into a datatable,) so why not use a DataAdapter to update and delete
things too?

Hi William

Sorry if I am being stupid but could you give an example please? The reason
I ask is because I have looked in msdn about the sourceColumn property but I
can only see examples which use dataadapters/datatables. I have decided
(wisely I hope) that I am going to bypass using data adapters and just call
my stored procedure directly.

I am still new to asp.net and ado.net and maybe I am still thinking along
the old fashioned way of classic asp/vb6 but my ultimate aim is to use my db
access tool for asp.net pages, and I don't want to waste too much resources
on storing datasets in cache/session, etc. so I've decided to call the db on
every hit of a web page.

I hope I am making myself clear.

Suzy:

dataAdapter's don't support batch updates yet unfortunately...can't wait
until ADO.NET 2.0 in which they do. Anyway, if you set the source column
property of your params, you should be good to go. I used your procs
building dummy tables, and all should work fine once you specify the
sourceColumn. Looks like you are on the right track, and for what it's
worth, kudos for thinking it out first..I'm often too guilty of coding away
when I should be writing and thinking ;-)

Hi William,

No I'm afraid I don't have any update code at the moment. I was just
thinking it through in my head before I started coding. One thing I should
mention though is that I am not using a data adapter as the majority

of
the

time I will be updating 1 row at a time (not batch updating).



Suzy:

Do you have any code that adds the parameters? That's the necessary step
and in the constructor where you are adding the params, specify the
SourceColum
Sorry, here is the corrected syntax version...

CREATE PROC UpdateUserById

@UserId bigint,
@UserTypeId bigint,
@Username varchar (50),
@Password varchar (50),
@LastLogin datetime
AS

UPDATE [User] SET

UserTypeId = @UserTypeId,
Username = @Username ,
Password = @Password ,
LastLogin = @LastLogin

WHERE [User].UserId = @UserId