使用string []在循环中多次查询 [英] using string[] to multi-query in a loop

问题描述

你好，

我想使用string []循环查询.

我的想法是:将我的信息保留在string []中，并放置在查询字符串中，并使用index
指定
这是我的代码

Hello,

I would like to use string[] to query in a loop.

my idea is: keep my information in string[] and place in query string,specify with index

here is my code

string[] edit_list = txtReqList.Text.Split('\n');
string query ;
MySqlCommand cmd = new MySqlCommand();
for (int i = 0; i < edit_list.Length; i++)
{
    try
    {
        query = "UPDATE submit
                 SET submit_status ='S'
                 WHERE submit_id = '" + edit_list[i] + "'";
        //Open connection
        if (this.OpenConnection() == true)
        {
            //Assign the query using CommandText
            cmd.CommandText = query;
            //Assign the connection using Connection
            cmd.Connection = connection;
            //Execute query
            cmd.ExecuteNonQuery();

        }
    }
    catch (MySqlException ex)
    {
        MessageBox.Show(ex.ToString());
    }
    this.CloseConnection();
}

不幸的是，仅查询最后一个索引，例如

我的string []是:0,1,2,3.

根据我的想法，它应该更新为Submit_id = 0,1,2和3
但是，它只会更新Submit_id =3.

怎么会这样?我该如何解决这个问题?

谢谢您的回答.

PS.我使用MVSC#作为工具，在Windows窗体应用程序模式中

Unfortunately, the only last index is queried, e.g.

my string[] are : 0,1,2,3.

following from my idea, it should update at submit_id = 0,1,2 and 3
but, it update only submit_id = 3.

how can this be? and how can I slove this problem?

Thank you for your answer.

PS. I use MVSC# as tool, in Windows Forms Application Mode

推荐答案

而不是循环运行，为什么不只发出一个命令? SQL有一个IN子句:

Instead of running that in a loop, why not issue just the one command? SQL has an IN clause:

UPDATE submit SET submit_status ='S' WHERE submit_id IN (list)

所有您需要做的就是建立列表:

All you have to do is build the list:

StringBuilder sb = new StringBuilder();
sb.Append("UPDATE submit SET submit_status ='S' WHERE submit_id IN (");
sb.Append(string.Join(",", edit_list));
sb.Append(")");
cmd.CommandText = sb.ToString();

您好，您可以尝试在大多数代码的顶部替换此行.

Hello can you try with replacing this line on top most of your code.

string[] edit_list = txtReqList.Text.Split('\n');
with 
string[] edit_list = txtReqList.Text.Split(',');

现在调试您的代码，并检查edit_list项中的内容.
如果尚未解决该问题，请提供该结果.

now debug your code and check what will be there in edit_list items.
please provide that result if this is not re-solved yet.

1.)拆分应该用逗号而不是换行符吗?
2.)foreach循环后是否应该关闭连接?

1.) Split should be on comma and not on line-break?
2.) Should the connection be closed after the foreach loop?

string[] edit_list = txtReqList.Text.Split('\,');
string query ;
MySqlCommand cmd = new MySqlCommand();
for (int i = 0; i < edit_list.Length; i++)
{
    try
    {
        query = String.Format("UPDATE submit SET submit_status='S' WHERE submit_id = '{0}'", edit_list[i]);

        //Open connection
        if (this.OpenConnection() == true)
        {
            //Assign the query using CommandText
            cmd.CommandText = query;
            //Assign the connection using Connection
            cmd.Connection = connection;
            //Execute query
            cmd.ExecuteNonQuery();
        }
    }
    catch (MySqlException ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

this.CloseConnection();

这篇关于使用string []在循环中多次查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！