ADO.NET: How to have N parameters?

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?

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.

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.