C#连接mysql，运行一段时间后报告too many connections异常

Question

C#连接mysql，程序运行约20分钟后，报告异常：

Answer 1

但是我的数据库每次用后，都关闭的。
代码如下：using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Windows.Forms;
using MySQLDriverCS;
namespace comback
{    class Database
    {
        private static MySQLConnection conn = null;        public static void getConnection()
        {
            conn = new MySQLConnection(new MySQLConnectionString("127.0.0.1", "comm", "admin", "123456").AsString);
        }        /// <summary>
        /// 获得数据集DataSet
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet getDS(DataSet ds, string sql)
        {
            //打开连接
            getConnection();
            conn.Open();            //设置字符编码
            MySQLCommand command = new MySQLCommand("set charset gb2312", conn);
            command.ExecuteNonQuery();
            command.Dispose();            //查询结果
            MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);
            mda.Fill(ds, "data");
            
            conn.Close();
            return ds;
        }        public static void execute(string sql)
        {
            //打开连接
            getConnection();
            conn.Open();            //设置字符编码
            MySQLCommand command = new MySQLCommand("set charset gb2312", conn);
            command.ExecuteNonQuery();
            command.Dispose();            MySQLCommand cc = new MySQLCommand(sql, conn);
            cc.ExecuteNonQuery();
            conn.Close();
        }        /// <summary>
        /// 设备维修的更新：insert、update、delete
        /// </summary>
        /// <param name="rows"></param>
        public static void executeRepair(DataTable table, string tableName)
        {
            //打开连接
            getConnection();
            conn.Open();            //设置字符编码
            MySQLCommand command = new MySQLCommand("set charset gb2312", conn);
            command.ExecuteNonQuery();
            command.Dispose();            DataRow row;
            int count = table.Rows.Count;
            int newid;
            bool isAdd = false, isUpdate = false, isDelete = false, first = true;
            for (int i = 0; i < count; i++)
            {
                row = table.Rows[i];
                if (row.RowState != DataRowState.Unchanged)
                {
                    //添加
                    if (row.RowState == DataRowState.Added)
                    {
                        //SELECT @@IDENTITY        Select SCOPE_IDENTITY()
                        MySQLCommand cmd = new MySQLCommand("INSERT INTO " + tableName + "(en,place,bugCause,replacePart,repairTime) VALUES(@en,@place,@bugCause,@replacePart,@repairTime)", conn);                        cmd.Parameters.Add("@en", DbType.String);
                        cmd.Parameters.Add("@place", DbType.String);
                        cmd.Parameters.Add("@bugCause", DbType.String);
                        cmd.Parameters.Add("@replacePart", DbType.String);
                        cmd.Parameters.Add("@repairTime", DbType.Date);                        string en = row["en"].ToString().Trim();
                        string place = row["place"].ToString().Trim();
                        string bugCause = row["bugCause"].ToString().Trim();                        if(string.Empty.Equals(en))
                        {
                            MessageBox.Show("设备编号不能为空"); return;
                        }
                        if (string.Empty.Equals(place))
                        {
                            MessageBox.Show("地址不能为空"); return;
                        }
                        if (string.Empty.Equals(bugCause))
                        {
                            MessageBox.Show("请输入产生原因"); return;
                        }                        cmd.Parameters["@en"].Value = en;
                        cmd.Parameters["@place"].Value = place;
                        cmd.Parameters["@bugCause"].Value = bugCause;
                        cmd.Parameters["@replacePart"].Value = row["replacePart"];
                        cmd.Parameters["@repairTime"].Value = row["repairTime"];                        cmd.ExecuteNonQuery();                        //cmd = new MySQLCommand("SELECT MAX(id) FROM " + tableName, conn);
                        cmd = new MySQLCommand("select @@identity", conn);
                        newid = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                        row["id"] = newid;                        isAdd = true;
                    } //修改
                    else if (row.RowState == DataRowState.Modified)
                    {
                        //MessageBox.Show("修改");
                        MySQLCommand cmd = new MySQLCommand("UPDATE " + tableName + " SET en=@en,place=@place,bugCause=@bugCause,replacePart=@replacePart,repairTime=@repairTime Where id=@id", conn);
                        
                        //添加占位符参数
                        cmd.Parameters.Add("@en", DbType.String);
                        cmd.Parameters.Add("@place", DbType.String);
                        cmd.Parameters.Add("@bugCause", DbType.String);
                        cmd.Parameters.Add("@replacePart", DbType.String);
                        cmd.Parameters.Add("@repairTime", DbType.Date);
                        cmd.Parameters.Add("@id", DbType.Int32);                        string en = row["en"].ToString().Trim();
                        string place = row["place"].ToString().Trim();
                        string bugCause = row["bugCause"].ToString().Trim();                        if (string.Empty.Equals(en))
                        {
                            MessageBox.Show("设备编号不能为空"); return;
                        }
                        if (string.Empty.Equals(place))
                        {
                            MessageBox.Show("地址不能为空"); return;
                        }
                        if (string.Empty.Equals(bugCause))
                        {
                            MessageBox.Show("请输入产生原因"); return;
                        }
                        //设置占位符参数值
                        cmd.Parameters["@en"].Value = en;
                        cmd.Parameters["@place"].Value = place;
                        cmd.Parameters["@bugCause"].Value = bugCause;
                        cmd.Parameters["@replacePart"].Value = row["replacePart"];
                        cmd.Parameters["@repairTime"].Value = row["repairTime"];
                        cmd.Parameters["@id"].Value = row["id", DataRowVersion.Original];//取修改前的值                        //处理
                        cmd.ExecuteNonQuery();
                        isUpdate = true;
                    }
                    //删除
                    else if (row.RowState == DataRowState.Deleted)
                    {
                        if (first)
                        {
                            if (DialogResult.Yes == MessageBox.Show("确认删除吗？", "友情提示", MessageBoxButtons.YesNo))
                            {
                                delete(row, tableName);                                first = false;
                                isDelete = true;
                            }
                            else
                            {
                                return;
                            }
                        }
                        else
                        {
                            delete(row, tableName);
                        }
                    }
                }
            }
            conn.Close();
            table.AcceptChanges();            //显示处理结果：添加、删除、修改是否成功
            //showDialog(isAdd, isUpdate, isDelete);
            //if (isAdd)
            //{
            //    MessageBox.Show("添加成功!");
            //}
            //if (isUpdate)
            //{
            //    MessageBox.Show("修改成功!");
            //}
            //if (isDelete)
            //{
            //    MessageBox.Show("删除成功!");
            //}
        }

Answer 2

接上面的代码：/// <summary>
        /// 设备被盗统计的更新：insert、update、delete
        /// </summary>
        /// <param name="rows"></param>
        public static void executeStolen(DataTable table)
        {
            //打开连接
            getConnection();
            conn.Open();            //设置字符编码
            MySQLCommand command = new MySQLCommand("set charset gb2312", conn);
            command.ExecuteNonQuery();
            command.Dispose();            DataRow row;
            int count = table.Rows.Count;
            int newid;
            bool isAdd = false, isUpdate = false, isDelete = false, first = true;
            for (int i = 0; i < count; i++)
            {
                row = table.Rows[i];
                if (row.RowState != DataRowState.Unchanged)
                {
                    //添加
                    if (row.RowState == DataRowState.Added)
                    {
                        //SELECT @@IDENTITY        Select SCOPE_IDENTITY()
                        MySQLCommand cmd = new MySQLCommand("INSERT INTO equipmentStolen(place,partStolen,brokenLevel,stolenTime,comment) VALUES(@place,@partStolen,@brokenLevel,@stolenTime,@comment)", conn);                        cmd.Parameters.Add("@place", DbType.String);
                        cmd.Parameters.Add("@partStolen", DbType.String);
                        cmd.Parameters.Add("@brokenLevel", DbType.String);
                        cmd.Parameters.Add("@stolenTime", DbType.String);
                        cmd.Parameters.Add("@comment", DbType.Date);                        string place = row["place"].ToString().Trim();
                        string partStolen = row["partStolen"].ToString().Trim();
                        string brokenLevel = row["brokenLevel"].ToString().Trim();
                        if(string.Empty.Equals(place))
                        {
                            MessageBox.Show("地址不能为空"); return;
                        }
                        if(string.Empty.Equals(partStolen))
                        {
                            MessageBox.Show("请输入被盗配件"); return;
                        }
                        if(string.Empty.Equals(brokenLevel))
                        {
                            MessageBox.Show("请输入损坏程度"); return;
                        }                        cmd.Parameters["@place"].Value = place;
                        cmd.Parameters["@partStolen"].Value = partStolen;
                        cmd.Parameters["@brokenLevel"].Value = brokenLevel;
                        cmd.Parameters["@stolenTime"].Value = row["stolenTime"];
                        cmd.Parameters["@comment"].Value = row["comment"];                        cmd.ExecuteNonQuery();                        cmd = new MySQLCommand("select @@identity", conn);
                        newid = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                        row["id"] = newid;                        isAdd = true;
                    } //修改
                    else if (row.RowState == DataRowState.Modified)
                    {
                        MySQLCommand cmd = new MySQLCommand("UPDATE equipmentStolen SET place=@place,partStolen=@partStolen,brokenLevel=@brokenLevel,stolenTime=@stolenTime,comment=@comment WHERE id=@id", conn);                        cmd.Parameters.Add("@place", DbType.String);
                        cmd.Parameters.Add("@partStolen", DbType.String);
                        cmd.Parameters.Add("@brokenLevel", DbType.String);
                        cmd.Parameters.Add("@stolenTime", DbType.String);
                        cmd.Parameters.Add("@comment", DbType.Date);
                        cmd.Parameters.Add("@id", DbType.Int32);                        string place = row["place"].ToString().Trim();
                        string partStolen = row["partStolen"].ToString().Trim();
                        string brokenLevel = row["brokenLevel"].ToString().Trim();
                        if (string.Empty.Equals(place))
                        {
                            MessageBox.Show("地址不能为空"); return;
                        }
                        if (string.Empty.Equals(partStolen))
                        {
                            MessageBox.Show("请输入被盗配件"); return;
                        }
                        if (string.Empty.Equals(brokenLevel))
                        {
                            MessageBox.Show("请输入损坏程度"); return;
                        }                        cmd.Parameters["@place"].Value = place;
                        cmd.Parameters["@partStolen"].Value = partStolen;
                        cmd.Parameters["@brokenLevel"].Value = brokenLevel;
                        cmd.Parameters["@stolenTime"].Value = row["stolenTime"];
                        cmd.Parameters["@comment"].Value = row["comment"];
                        cmd.Parameters["@id"].Value = row["id", DataRowVersion.Original];//取修改前的值                        cmd.ExecuteNonQuery();
                        isUpdate = true;
                    }
                    //删除
                    else if (row.RowState == DataRowState.Deleted)
                    {
                        if (first)
                        {
                            if (DialogResult.Yes == MessageBox.Show("确认删除吗？", "友情提示", MessageBoxButtons.YesNo))
                            {
                                delete(row, "equipmentStolen");
                                
                                first = false;
                                isDelete = true;
                            }
                            else
                            {
                                return;
                            }
                        }
                        else
                        {
                            delete(row, "equipmentStolen");
                        }
                    }
                }
            }
            conn.Close();
            table.AcceptChanges();            //显示处理结果：添加、删除、修改是否成功
            //showDialog(isAdd, isUpdate, isDelete);
        }        

Answer 3

/// <summary>
        /// 巡检记录的更新：insert、update、delete
        /// </summary>
        /// <param name="rows"></param>
        public static void executeCheck(DataTable table)
        {
            //打开连接
            getConnection();
            conn.Open();            //设置字符编码
            MySQLCommand command = new MySQLCommand("SET charset GB2312", conn);
            command.ExecuteNonQuery();
            command.Dispose();            DataRow row;
            int count = table.Rows.Count;
            //int newid;
            bool isDelete = false, first = true;
            for (int i = 0; i < count; i++)
            {
                row = table.Rows[i];
                if (row.RowState != DataRowState.Unchanged)
                {
                    if (row.RowState == DataRowState.Deleted)
                    {
                        if(first)
                        {
                            if (DialogResult.Yes == MessageBox.Show("确认删除吗？", "友情提示", MessageBoxButtons.YesNo))
                            {
                                delete(row, "elecheck");                                first = false;
                                isDelete = true;
                            }
                            else
                            {
                                return;
                            }
                        }
                        else
                        {
                            delete(row, "elecheck");
                        }
                    }
                }
            }
            conn.Close();
            table.AcceptChanges();            //显示处理结果：添加、删除、修改是否成功
            //showDialog(false, false, isDelete);
        }        private static void delete(DataRow row, string tableName)
        {
            if(conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            MySQLCommand cmd = new MySQLCommand("DELETE FROM " + tableName + " WHERE id=@id", conn);            cmd.Parameters.Add("@id", DbType.Int32);
            cmd.Parameters["@id"].Value = row["id", DataRowVersion.Original];
            cmd.ExecuteNonQuery();            conn.Close();
        }        private static void showDialog(bool isAdd, bool isUpdate, bool isDelete)
        {
            if (isAdd)
            {
                MessageBox.Show("添加成功!");
            }
            if (isUpdate)
            {
                MessageBox.Show("修改成功!");
            }
            if (isDelete)
            {
                MessageBox.Show("删除成功!");
            }
        }
        
        /// <summary>
        /// 获得数组
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string[] getRS(String sql, bool flag)
        {
            StringBuilder sb = new StringBuilder();            //打开连接
            getConnection();
            conn.Open();            //设置字符编码
            MySQLCommand command = new MySQLCommand("set charset gb2312", conn);
            command.ExecuteNonQuery();
            command.Dispose();            //查询获得数据读取对象
            MySQLCommand command2 = new MySQLCommand(sql, conn);
            MySQLDataReader reader = command2.ExecuteReaderEx();            //循环读取，封装
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    sb.Append(reader.GetString(0) + ",");
                }
            }
            if (sb.ToString().Length == 0)
            {
                reader.Close();
                conn.Close();
                if(flag)
                {
                    MessageBox.Show("无设备！");
                }                //return new string[]{""};
                return null;
            }
            else
            {
                reader.Close();
                conn.Close();
                //获得封装对象的数组
                string temp = sb.ToString().Substring(0, sb.ToString().Length - 1);
                return temp.Split(',');
            }
        }
    }
}

Answer 4

如果是c/s程序，没有必要这样封装。
如果是web程序，你需要查下 线程池 是否释放了。

Answer 5

找个网络工具看看到服务器的连接有多少个(针对 mysql 服务的)?
tcpview 可以看
见过一个连接 oracle 的应用 , 连接真是惨不忍睹.

Answer 6

第一，不要把连接弄成 static 的，每次连接都创建一个新的连接。
第二，把连接放在 using 语句里，或者在 finally 里手动关闭

Answer 7

lz为什么每个都要做成static 的啊？ 

Answer 8

汗。。做成static，可以直接用类名调用

Answer 9

conn.Dispose();
做成static调用起来确实简单，不过也有很多问题，该方法肃然包含在类里面，但是不属于类的托管范围，应用程序开启即开启资源，只到应用程序关闭才被释放。类释放了它都没有释放，在某种意义上面是增加了系统的开销，因为某些方法是根本不需要让其一直存活。
你每次通过Database.getConnection()方法都会实例化一个全局的连接对象，但是，虽然你关闭了，但是没有调用Dispose()方法，也会存在。

Answer 10

"private static MySQLConnection conn = null"
把static去掉就可以了~static的对象名只能指到一个对象,如果a,b两个线程同时进入通一个方法,conn的对象会生成两次,先生成的conn的引用在conn没有关闭就被后生成的那个conn对象替换了,所以没关~
另外建议你close在finnally块中做,因为你无法确定close之前的代码不会异常~

Answer 11

加上try/catch/finally就不报错了，呵呵。