c# - 连续打开和关闭连接后无法连接到本地主机上的 MySql 服务器

Question

我有一些 mysql 问题，我有一个名为“transactionNumber”和“counter”的列。

transactionNumber 值可以是这样的:(不包括字母)

a. 12345, 1234566
b. 9876, 98332, 23423
c. 12347
d. - 

'counter' 列是一个字段，用于计算一行中有多少个 transactionNumber。例如上面的字母 'a' 有 2 个 transactionNumber，字母 'b' 有 3 个等等。字母 'd' 算作 1。

但当应用程序启动时，计数器列为空。所以我创建了一个方法来计算每行的 transactionNUmber 并更新计数器列。

这是我的方法:

private void TransactionNumberCounter(DataSet dsData)
{
    for (int i = 0; i < dsData.Tables["data"].Rows.Count; i++)
    {
        DataRow dRow = dsData.Tables["data"].Rows[i];

        string tNumber = dRow.ItemArray.GetValue(1).ToString();
        string id = dRow.ItemArray.GetValue(0).ToString();

        string[] numbers = tNumbers.Split(',');

        char[] arr = new char[] { '\t', '\n' };
        int tNumberCounter= 0;

        List<string> listAccNumbers = new List<string>();

        foreach (string number in numbers)
            listAccNumbers.Add(number.TrimStart(arr));

        tNumberCounter = listAccNumbers.Count;

        string query = @"UPDATE bartran SET aNumberCounter = ? WHERE id = ?;";

        OdbcParameter[] parameters = new OdbcParameter[]{
        new OdbcParameter("?", aNumberCounter),
        new OdbcParameter("?", id)
        };

        DBServer.ExecuteQuery(query, "database_name", parameters);
    }
}

这个方法有效。但是如果有几千行要计算，我会得到一个错误:

Can't connect to MySQL server on "localhost" (10061)"

所以我试着在这里为你们寻求帮助。如何计算 t.Number 并根据页面加载计数更新计数器字段。提前致谢!

还有其他可行的方法吗？

EDIT: 

这是 DBServer 类:

using System;
using System.Data;
using System.Data.Odbc;

public static class DBServer
{
//Test
private const string Server = "localhost";
private const string Uid = "root";
private const string Pwd = "defaultpass";

//LIVE
//private const string Server = "ipaddress";
//private const string Uid = "root";
//private const string Pwd = "password";

public static DataTable GetTable(string query, string database)
{
    using (OdbcConnection cn = new OdbcConnection(
        string.Format("Driver={{MySQL ODBC 5.1 Driver}};Server={0};dataBase={1};User={2};Password={3};Option=3;",
        Server, database, Uid, Pwd)))
    {

        using (OdbcCommand cmd = new OdbcCommand(query, cn))
        {
            DataTable dt = new DataTable();

            cn.Open();

            using (OdbcDataReader rdr = cmd.ExecuteReader())
            {
                dt.Load(rdr);

                return dt;
            }
        }
    }
}

public static DataTable GetTable(string query, string database, OdbcParameter[] parameters)
{
    using (OdbcConnection cn = new OdbcConnection(
        string.Format("Driver={{MySQL ODBC 5.1 Driver}};Server={0};database={1};User={2};Password={3};Option=3;",
        Server, database, Uid, Pwd)))
    {
        using (OdbcCommand cmd = new OdbcCommand(query, cn))
        {
            if (parameters != null)
                cmd.Parameters.AddRange(parameters);

            DataTable dt = new DataTable();

            cn.Open();

            using (OdbcDataReader rdr = cmd.ExecuteReader())
            {
                dt.Load(rdr);

                return dt;
            }
        }
    }
}

/// <summary>
/// Executes query to the database (Parameterized)
/// </summary>
/// <param name="query">Query String</param>
/// <param name="database">Target Database</param>
/// <param name="parameters">Collection of parameters</param>
/// <returns>Number of affected records</returns>
public static int ExecuteQuery(string query, string database, OdbcParameter[] parameters)
{
    using (OdbcConnection cn = new OdbcConnection( string.Format("Driver={{MySQL ODBC 5.1 Driver}};Server={0};database={1};User={2};Password={3};Option=3;",
        Server, database, Uid, Pwd)))
    {
        using (OdbcCommand cmd = new OdbcCommand(query,cn))
        {
            if (parameters != null)
                cmd.Parameters.AddRange(parameters);

            cn.Open();

            return cmd.ExecuteNonQuery();
        }
    }
}

/// <summary>
/// Check if the query has record (Parameterized)
/// </summary>
/// <param name="query">Query string</param>
/// <param name="database">Target database</param>
/// <param name="parameters">Collection of parameters</param>
/// <returns>true if has record else false</returns>
public static bool HasRecord(string query, string database, OdbcParameter[] parameters)
{
    using (OdbcConnection cn = new OdbcConnection( string.Format("Driver={{MySQL ODBC 5.1 Driver}};Server={0};database={1};User={2};Password={3};Option=3;",
        Server, database, Uid, Pwd)))
    {
        using (OdbcCommand cmd = new OdbcCommand(query,cn))
        {
            if (parameters != null)
                cmd.Parameters.AddRange(parameters);               

            cn.Open();

            using (OdbcDataReader reader = cmd.ExecuteReader())
            {
                return reader.HasRows;
            }
        }
    }
}

/// <summary>
/// Check if the query has record
/// </summary>
/// <param name="query">Query string</param>
/// <param name="database">Target database</param>
/// <returns>true if has record else false</returns>
public static bool HasRecord(string query, string database)
{
    using (OdbcConnection cn = new OdbcConnection(string.Format("Driver={{MySQL ODBC 5.1 Driver}};Server={0};database={1};User={2};Password={3};Option=3;",
        Server, database, Uid, Pwd)))
    {
        using (OdbcCommand cmd = new OdbcCommand(query, cn))
        {
            cn.Open();

            using (OdbcDataReader reader = cmd.ExecuteReader())
            {
                return reader.HasRows;
            }
        }
    }
}

public static DataTable CallStoredProcedure(string query, string database, OdbcParameter[] parameters)
{
    using (OdbcConnection cn = new OdbcConnection(
        string.Format("Driver={{MySQL ODBC 5.1 Driver}};Server={0};database={1};User={2};Password={3};Option=3;",
        Server, database, Uid, Pwd)))
    {
        cn.Open();
        using (OdbcCommand cmd = new OdbcCommand(query, cn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
                cmd.Parameters.AddRange(parameters);

            OdbcDataReader rdr = cmd.ExecuteReader();

            DataTable dt = new DataTable();

            dt.Load(rdr);

            return dt;
        }
    }
}

}

Answer 1

不要实例化 Command 对象，也不要在每次迭代时定义参数。您应该尝试编写如下代码:

   string query = @"UPDATE bartran SET aNumberCounter = ? WHERE id = ?;";
   string cnstr=@"Driver={{MySQL ODBC 5.1 Driver}};Server=localhost;dataBase=database_name;User=root;Password=;Option=3;";
   using(OdbcConnection connection=new OdbcConnection(cnstr))
   {
    using(OdbcCommand cmd=new OdbcCommand(query,connection))
    {
    cmd.Parameters.Add("?",OdbcType.Int);
    cmd.Parameters.Add("?",OdbcType.Int);
    connection.Open();
    for (int i = 0; i < dsData.Tables["data"].Rows.Count; i++)
     {
      ...
      cmd.Parameters[0].Value=aNumberCounter;
      cmd.Parameters[1].Value=id;
      cmd.ExecuteNonQuery();
      }
    connection.Close();
    }
   }

PS:您应该使用 MySql Provider API 而不是 Odbc API。