ADO.NET：如何有N个参数？

发布于 2024-09-26 18:21:59 字数 1155 浏览 2 评论 0原文

我需要将数据库中的一批行标记为“已处理”。

在过去，当事情变得更容易时，我会创建一些 SQL 来表示：

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")

通过循环遍历日期列表：

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

并发出 SQL：

ExecuteNonQuery(connection, sql);

一切都很好。

现在我想尝试用困难的方式做事；我想尝试使用参数：

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();
}

唯一的技巧是在 sql 字符串中放入什么，以及在参数循环的每次迭代中放入什么。他们需要一些名字。

命名任意数量的参数的推荐方法是什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

戴着白色围巾的女孩 2024-10-03 18:21:59

我认为你可以做的是这样的：-

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});
}

但是， 我认为在这种情况下专门设置参数是不必要的。

考虑到您的动态值是日期而不是字符串，您可以直接使用TryParse验证日期，以确保它们是正确的数据类型在将它们添加到原始解决方案之前！

我不知道在这种情况下使用参数是否可以实现任何额外的目标。

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.

回复收藏 0 原文