How to pass field name itself as a parameter? -- dotnet ado.net

Andrew
4/19/2007 7:20:02 AM

Hello, friends,

In our c#.net app (2.0), I have an UPDATE statement like the follows for
Oracle DB:

UPDATE contract SET phone = :phone, fax = :fax, status = :status, ....(more
fields here) WHERE contractNum = :contractNum

I know I need to pass values for each parameters before execution, something
like:

command.Parameters.Add("phone", OracleType.VarChar, 10).Value = "1234567890";

However, sometimes, some field values do not have to be changed, (e.g., the
status field value in above Update statement). At this case, can I just pass
the field name itself so that in Update statement for this field will look
like fieldname = fieldname?

For example, in this example, can I do:

command.Parameters.Add("phone", OracleType.VarChar, 10).Value = "phone";

so that the phone field value won't be changed after this Update statement
was executed. (As you see, I wished command object would interprete this as

UPDATE contract SET phone = phone, fax='0987654321',....., WHERE contractNum
= '123'

since the value is the same as field name, not

UPDATE contract SET phone = 'phone', fax='0987654321',....., WHERE
contractNum = '123')

This wish does not seem right. But that is what I try to do.

Any way to do this? Any reference papers? Thanks a lot.

Jay Allard
4/20/2007 4:57:33 PM

Greetings

You could do something like

update
contract
set
phone = coalesce(@phone, phone),
fax = coalesce(@fax, fax)
where
whatever

That's TSQL syntax, though, not oracle. The coalesce will use the first
non-null value. So, if you pass null for @phone, then phone=phone, else
phone=@phone. Does oracle have an IF or CASE statement, maybe?

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : How to pass field name itself as a parameter?