在C#中关闭MySql连接 [英] Closing MySql connections in C#

问题描述

我正在尝试与c#应用程序一起使用mySQL.我遇到了一个问题，即关闭的连接无法正确关闭.我进行了一些谷歌搜索，发现连接字符串中有一个选项，可以将pooling设置为false，这会正确关闭连接.但是，如果我尝试使用多个连接来执行此操作，则应用程序将崩溃.我碰到了这篇文章；

http://www.primaryobjects.com/CMS/Article69.aspx

可以更好地描述我的问题，并且有一种方法可以终止每个连接的进程，但是不适用于我刚刚打开的活动连接.有谁知道解决此问题的方法，或者我确实确实缺少的东西.

这是我正在使用的代码:

I am experimenting with mySQL for use with a c# application. I have come across a problem where connections that are closed aren''t closing properly. I did some googling and found there is an option in the connecting string where I can set pooling to false, which does close the connection properly. However if I try this with multiple connections, the application crashes. I came across this article;

http://www.primaryobjects.com/CMS/Article69.aspx

which describes my problem better and has a method of killing the process for each connection, however it will not work for an active connection I just opened. Does anyone know a workaround for this problem or something really obvious that I''m missing.

This is the code I am using:

using (MySqlConnection connection = new MySqlConnection())
                {
                    MySqlDataAdapter data = new MySqlDataAdapter();
                    connection.ConnectionString = "server=127.0.0.1;" +
                        "database=database;" +
                        "uid=root;" +
                        "password=password;";

                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }

                    MySqlCommand command = connection.CreateCommand();
                    command.CommandText = "select * from T_SCHEDULE";
                    MySqlCommandBuilder thisBuilder = new MySqlCommandBuilder(data);
                    data.SelectCommand = command;
                    DataSet dataset = new DataSet();
                    data.Fill(dataset, "T_SCHEDULE");

                    DataRow thisRow = dataset.Tables[0].NewRow();
                    thisRow[1] = "4000";
                    thisRow[2] = DateTime.Now;
                    thisRow[3] = 12;
                    dataset.Tables["T_SCHEDULE"].Rows.Add(thisRow);

                    DataRow thisRow2 = dataset.Tables[0].NewRow();
                    thisRow2[1] = "4001";
                    thisRow2[2] = DateTime.Now;
                    thisRow2[3] = 12;
                    dataset.Tables["T_SCHEDULE"].Rows.Add(thisRow2);

                    data.Update(dataset, "T_SCHEDULE");

                    connection.Close();
                    connection.Dispose();
                }

推荐答案

尝试处理可能很好解决您问题的连接(以及您的命令等)！
最简单的方法是使用using block:

Try Disposing your connections (and your commands, etc.) that may well dispose of your problem!
The easiest way is to use a using block:

using (MySqlConnection con = new MySqlConnection(strCon))
    {
    con.Open();
    using (MySqlCommand ver = new MySqlCommand("SELECT MAX(version) FROM dlContent WHERE fileName=@FN", con))
        {
        ver.Parameters.AddWithValue("@FN", filename);
        object o = ver.ExecuteScalar();
        if (o != null && o != System.DBNull.Value)
            {
            // Exists already.
            version = (int) o + 1;
            }
        }
    using (MySqlCommand ins = new MySqlCommand("INSERT INTO dlContent (iD, fileName, description, dataContent, version) " +
                                           "VALUES (@ID, @FN, @DS, @DT, @VS)", con))
        {
        ins.Parameters.AddWithValue("@ID", Guid.NewGuid());
        ins.Parameters.AddWithValue("@FN", filename);
        ins.Parameters.AddWithValue("@DS", "");
        ins.Parameters.AddWithValue("@DT", filedata);
        ins.Parameters.AddWithValue("@VS", version);
        ins.ExecuteNonQuery();
        }
    }

将无济于事-它直接来自我的Web.Config(修订版):

Won''t help - it comes direct from my Web.Config (redacted version):

<connectionstrings>
  <add name="LoginDatabase" connectionstring="Database=mydatabase;Data Source=localhost;User Id=XXXX;Password=XXXX"></add>
  <add name="LocalMySqlServer" connectionstring="server=localhost;database=XXXX;uid=XXXX;pwd=XXXX" providername="MySql.Data.MySqlClient"></add>
</connectionstrings>

这篇关于在C#中关闭MySql连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！