How to pass a MySQL database function as a query parameter from c#

Question

Let me explain a little.

Image you have the flowing MySQL table:

CREATE TABLE test (
  value DATETIME
);

Currently I am doing something like this:

command.CommandText = "UPDATE test SET value = ?value";
command.Parameters.AddWithValue("?value", DateTime.Now);

But the clients date/time settings are unreliable so I would like the time to be determined at the server. Something like:

command.CommandText = "UPDATE test SET value = NOW()";

The trick is that I want to pass the "NOW()" function as a parameter to the original query. Please understand that this small example is OVERLY simplified. In reality the table has 48 columns, 7 of which are datetime, marking the date and/or time the user made a particular operation to the object the database row denotes. When the user performs a new operation to the object I read the entire row and insert it again, with a different ID of course, (Something like a poor man's revision history) so I like to keep the date/time of the old operation and insert it in the new row, but at the same time set the current date/time (as seen by the database) for the new operation.

For simplicity lets go back to the previous example.

As far as I can find I have two choices.

bool switch;
....
command.CommandText = "UPDATE test SET value = " + switch ? "NOW()" : "?value";
command.Parameters.AddWithValue("?value", value);

OR

bool switch;
....
if (switch) {
    command.CommandText = "SELECT NOW()";
    value = Convert.ToDateTime(command.ExecuteScalar());
}
command.CommandText = "UPDATE test SET value = ?value";
command.Parameters.AddWithValue("?value", value);

I don't like neater of them. Wouldn't it be neat if I could do something similar to:

command.CommandText = "UPDATE test SET value = ?value";
if (switch) {
    command.Parameters.AddWithValue("?value", "NOW()");
} else {
    command.Parameters.AddWithValue("?value", value);
}

That way I would still execute the same one query.

Waiting for your opinions on this. I will be very grateful for any help you can provide.

Costo · Accepted Answer · 2009-12-15 23:10:59Z

1

You could use a stored procedure with an optional parameter to achieve this. If the parameter is NULL, you use NOW() instead. Although I'm not a big fan of stored procs (they are evil ! :-).

You could also use IFNULL(). Here is how it is done in T-SQL (function name is ISNULL) :

UPDATE test SET value = ISNULL(@value, GetDate() )

edited Dec 15, 2009 at 23:10

answered Dec 15, 2009 at 22:43

Costo

5,9708 gold badges35 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VaSk0_A Over a year ago

I already got a procedure just for this functionality. It's not actually stored, it's called a function and it's written in C# :-). I totally agree with you, they are evil! Plus a stored proc would mean yet another thing to change when I "redecorate" the table.

Collectives™ on Stack Overflow

How to pass a MySQL database function as a query parameter from c#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related