ADO.NET: How to have N parameters?

Question

i need to mark a batch of rows in the database as "processed".

In the olden days, when things were easier, i would create some SQL that said:

UPDATE Readings SET IsProcessed = 1 WHERE ReadingDateTime IN (
    "2010-10-07 22:02:13.327",
    "2010-10-07 22:02:14.213",
    "2010-10-07 22:02:15.595",
    ...
    "2010-10-07 23:03:36.981")

by looping through a list of dates:

sql = "UPDATE Readings SET IsProcessed = 1 WHERE ReadingDateTime IN (";
foreach (DateTime datetime in dates)
{
   sql = sql + CRLF+ DateTimeToSql(datetime)+",";
}
sql = sql+")";

And issue the SQL:

ExecuteNonQuery(connection, sql);

And all was good.

Now i want to try to do things the hard way; i want to try to use parameters:

sql = ???;
command.CommandText = sql;

DbCommand command = connection.CreateCommand();
foreach (DateTime datetime in readings)
{
    command.Parameters.Add(new SqlParameter(???, SqlDbType.DateTime) {Value = datetime});
}
using (DbTransaction transaction = connection.BeginTransaction())
{
    command.Transaction = transaction;
    command.ExecuteNonQuery();
    transaction.Commit();
}

The only trick is what to put in the sql string, and what to put in each iteration of the parameters loop. They need some name.

What's the recommended way to name arbitrary number of parameters?

Answer 1

You can go as minimal as you like: the provider will accept parameters named @0, @1, etc. But if you want to be more descriptive, just use a base name of @ReadingDateTime, with an integer suffix from 0..n. Easy peasy.

Answer 2

I think what you could do is something like this:-

sql = "UPDATE Readings SET IsProcessed = 1 WHERE ReadingDateTime IN (";
for (int count=0; count<dates.Length; count++)
{
   sql = sql + CRLF + ":param" + count;
}
sql = sql+")";

for (int count=0; count<dates.Length; count++)
{
    command.Parameters.Add(new SqlParameter(":param" + count, SqlDbType.DateTime) {Value = datetime});
}

However, i think that having parameters specifically in this scenario is kind of unnecessary.

Considering that your dynamic values are dates and not strings, you can instead validate the dates directly using a TryParse to ensure that they are the correct datatype before you append them in your original solution!!

I dont know if you are achieving anything extra over that by using parameters in this case.