SQL UPDATE 循环非顺序键

发布于 2024-08-10 07:53:07 字数 753 浏览 2 评论 0原文

我正在编写一个 C# 应用程序，它将更新 SQL Server 数据库中的字段。我正在测试的当前算法只是从“State”字段中提取数据，将每个值存储在 ArrayList 中，将其大写，然后将其写回数据库。我的逻辑有问题。

我将所有值拉入 ArrayList 并将它们大写。这工作正常。我现在有一个包含 100 个值的数组（即 myArray[0] - myArray[99] ）。然后，我使用 FOR 循环将值写回数据库：

for (int i = 0; i <= (myArray.Count - 1); i++)
{
   SqlCommand myCommand = 
      new SqlCommand("UPDATE myList SET State = '" + recordArray[i].ToString() + 
                     "' WHERE uniqueID = '" + (i + 1) + "'", dbConnection);
   myCommand.ExecuteNonQuery();
}

我在上面的示例中使用“uniqueID”根据主键放置这些值。然而，问题在于主键只是近乎顺序的；序列中缺少一些数字。因此，即使我正好有我需要的值的数量，并且它们在数组中的顺序正确，将被推回数据库，但一旦我达到序列中的一个错误，其余的数据就会被放置在错误的领域。我知道这是我的逻辑失误，但我不知道如何确保每个单独的值都被正确放置。

预先感谢您的帮助。

原文

I am writing a C# application that will update the fields in a SQL Server database. The current algorithm I am testing simply pulls the data from a "State" field, stores each value in an ArrayList, capitalizes it, and then writes it back to the database. I am having a problem with logic.

I pull all of the values into the ArrayList and capitalize them. This is working fine. I now have an array with, for instance, 100 values (i.e., myArray[0] - myArray[99] ). I then use a FOR loop to write the values back to the database:

for (int i = 0; i <= (myArray.Count - 1); i++)
{
   SqlCommand myCommand = 
      new SqlCommand("UPDATE myList SET State = '" + recordArray[i].ToString() + 
                     "' WHERE uniqueID = '" + (i + 1) + "'", dbConnection);
   myCommand.ExecuteNonQuery();
}

I am using "uniqueID" in the above example to place these values according to primary key. However, the problem is that the primary key is only nearly sequential; there are a few missing numbers in the sequence. Thus, even though I have exactly the number of values that I need, and they are in the correct order in the array to be pushed back out to the database, once I reach a lapse in the sequence, the rest of the data is placed in the wrong field. I know this is a lapse in my logic, but I am at a loss as to how I can ensure that every individual value is being placed correctly.

Thanks in advance for the help.

分享到QQ

分享到微博

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

发布评论

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

静水深流 2024-08-17 07:53:07

这里有很多问题......

永远不要永远使用这样的动态SQL。如果您的“状态”之一中有撇号怎么办？
除非这是 .Net 1.0 或 1.1，否则您不应该使用 ArrayList。请改用 System.Collections.Generic.List。
不要创建 99 个 SqlCommand 对象。创建1 SqlCommand 对象，并在循环的每次迭代中更新参数值。
使用 using 语句创建 SqlCommand（更重要的是 SqlConnection）对象，以确保在引发异常时立即释放非托管资源。
最重要的是，当您意识到可以在一个 sql 语句中更新多条记录，并且 sql 有一个非常简单的“UPPER”函数时，所有这些都变得毫无意义。

由于看起来您可以使用正确方法的示例来构建此类查询，因此我暂时假设 #5 不是一个选项，并且您确实需要将所有这些数据拉到应用程序中然后逐条记录地更新它（提示：你不需要）。以下是构建该代码的方法：

using (SqlConnection cn1 = new SqlConnection("connection string here")) //inbound data
using (SqlCommand cmd1 = new SqlCommand("SELECT uniqueid, State FROM myList", cn1)) 
using (SqlConnection cn2 = new SqlConnection("connection string here"))
using (SqlCommand cmd2 = new SqlCommand("UPDATE myList SET State= @State WHERE uniqueID= @ID", cn2))
{
    SqlParameter StateParam = cmd2.Parameters.Add("@State", SqlDbType.VarChar, 50);
    SqlParameter IDParam = cmd2.Parameters.Add("@ID", SqlDbType.Int);

    cn1.Open();
    cn2.Open();

    using (SqlDataReader rdr = cmd1.ExecuteReader())
    {
        while (rdr.Read())
        {
            StateParam.Value = rdr["State"].ToString().ToUpper();
            IDParam.Value    = rdr["uniqueID"];
            cmd2.ExecuteNonReader();
        }
    }
}

请注意，这只是为了演示使用块和参数化查询。您不应该不使用此代码。相反，请仔细看看我的第五点。这可以而且应该全部在单个 sql UPDATE 语句中完成。

So many things wrong here...

Never NEVER NEVER use dynamic SQL like that. What if one of your "state" has an apostrophe in it?
Unless this is .Net 1.0 or 1.1, you should NOT be using an ArrayList. Use System.Collections.Generic.List<string> instead.
Don't create 99 SqlCommand objects. Create 1 SqlCommand object, and update the parameter value on each iteration through the loop.
Create your SqlCommand (and even more importantly, SqlConnection) object with a using statement, to make sure the unmanaged resources are released promptly if an exception is thrown.
Most of all, all this becomes moot when you realize you can update multiple records in one sql statement, and that sql has a nice easy "UPPER" function.

Since it looks like you could use an example of the correct way to build this kind of query, I'll assume for a moment that #5 is somehow not an option and that you really do need to pull all this data down to the application and then update it back record by record (hint: you don't). Here is how you should build that code:

using (SqlConnection cn1 = new SqlConnection("connection string here")) //inbound data
using (SqlCommand cmd1 = new SqlCommand("SELECT uniqueid, State FROM myList", cn1)) 
using (SqlConnection cn2 = new SqlConnection("connection string here"))
using (SqlCommand cmd2 = new SqlCommand("UPDATE myList SET State= @State WHERE uniqueID= @ID", cn2))
{
    SqlParameter StateParam = cmd2.Parameters.Add("@State", SqlDbType.VarChar, 50);
    SqlParameter IDParam = cmd2.Parameters.Add("@ID", SqlDbType.Int);

    cn1.Open();
    cn2.Open();

    using (SqlDataReader rdr = cmd1.ExecuteReader())
    {
        while (rdr.Read())
        {
            StateParam.Value = rdr["State"].ToString().ToUpper();
            IDParam.Value    = rdr["uniqueID"];
            cmd2.ExecuteNonReader();
        }
    }
}

Note that this is just to demonstrate using blocks and parameterized queries. You should NOT use this code. Instead, take a good look at my point #5. This can and should all be done in a single sql UPDATE statement.

回复收藏 0 原文