asp.net 连接 MySQL数据库

1.添加MySql.Data.dll引用

 

2.SQLHelper类：

private string strConn;
        private MySqlConnection sqlConn;
        private MySqlCommand sqlComm;

        private string errMsg = null;
        private int errNumber = 0;

        #region Member
        public string ErrorMessage
        {
            get { return errMsg; }
        }

        public int ErrorNumber
        {
            get { return errNumber; }
        }
        #endregion

        public SqlHelper()
        {
            strConn = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        }

        //public SqlHelper(string dbName)
        //{
        //    strConn = ConfigurationManager.ConnectionStrings[dbName].ConnectionString;
        //}

        /// <summary>
        /// query db. get recordSet. Must close.
        /// </summary>
        /// <param name="commandText">T-SQL command string</param>
        ///
        /// <returns>SqlDataReader Or Null</returns>
        public MySqlDataReader ExecuteReader(string commandText)
        {
            MySqlDataReader sqlDR = null;
            try
            {
                sqlConn = new MySqlConnection(strConn);
                sqlConn.Open();

                sqlComm = new MySqlCommand(commandText, sqlConn);
                sqlDR = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (MySqlException e)
            {
                errMsg = e.Message;
                errNumber = e.Number;

                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }

            return sqlDR;
        }

        /// <summary>
        /// Opreation db. include: insert and update and delete.
        /// </summary>
        /// <param name="commandText">T-SQL command string</param>
        /// <returns>True or False</returns>
        public bool ExecuteNonQuery(string commandText)
        {
            bool result = true;

            try
            {
                sqlConn = new MySqlConnection(strConn);
                sqlConn.Open();

                sqlComm = new MySqlCommand(commandText, sqlConn);
                sqlComm.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                result = false;

                errMsg = e.Message;
                errNumber = e.Number;
            }

            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();

            return result;
        }

        /// <summary>
        /// Get a DataSet
        /// </summary>
        /// <param name="commandText">T-SQL command string</param>
        /// <returns>DataSet type</returns>
        public DataSet ExecuteDataSet(string commandText)
        {
            DataSet ds = new DataSet();
            MySqlDataAdapter da = new MySqlDataAdapter(commandText, new MySqlConnection(strConn));
            da.Fill(ds);

            return ds;
        }

        /// <summary>
        /// Get a DataTable.
        /// </summary>
        /// <param name="commandText">T-SQL command string</param>
        /// <returns>DataTable type</returns>
        public DataTable ExecuteDataTable(string commandText)
        {
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(commandText, new MySqlConnection(strConn));
            da.Fill(dt);

            return dt;
        }

        /// <summary>
        /// Execute a transacton.NonQuery.
        /// </summary>
        /// <param name="commandTextList">T-SQL command string set</param>
        /// <returns>True or False</returns>
        public bool ExecuteTransaction(List<string> commandTextList)
        {
            bool result = true;

            sqlConn = new MySqlConnection(strConn);
            MySqlTransaction sqlTrans = null;
            try
            {
                sqlConn.Open();
                sqlTrans = sqlConn.BeginTransaction();

                sqlComm = new MySqlCommand();
                sqlComm.Connection = sqlConn;
                sqlComm.Transaction = sqlTrans;
                sqlComm.CommandType = CommandType.Text;

                foreach (string commandText in commandTextList)
                {
                    sqlComm.CommandText = commandText;
                    sqlComm.ExecuteNonQuery();
                }
                sqlTrans.Commit();
            }
            catch (MySqlException e)
            {
                sqlTrans.Rollback();

                errMsg = e.Message;
                errNumber = e.Number;
                result = false;
            }

            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();

            return result;
        }

        /// <summary>
        /// Execute a StroreProcedure.
        /// </summary>
        /// <param name="storeProcedureName">StroeProcedure Name</param>
        /// <param name="paraList">Dictionary type</param>
        /// <returns>True or False. if execute failed, ErrorNumber=@@error and ErrorMessag is NULL.</returns>
        public bool ExecuteProcedure(string storeProcedureName, Dictionary<string, string> paraList)
        {
            bool result = true;

            try
            {
                sqlConn = new MySqlConnection(strConn);
                sqlConn.Open();

                sqlComm = new MySqlCommand(storeProcedureName, sqlConn);
                sqlComm.CommandType = CommandType.StoredProcedure;

                foreach (KeyValuePair<string, string> kvp in paraList)
                {
                    sqlComm.Parameters.Add(new MySqlParameter(kvp.Key, kvp.Value));
                }

                int spReturnValue = (int)sqlComm.ExecuteScalar();
                if (spReturnValue != 0)
                {
                    errNumber = spReturnValue;
                    errMsg = null;
                    result = false;
                }
            }
            catch (MySqlException e)
            {
                errMsg = e.Message;
                errNumber = e.Number;
                result = false;
            }

            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();

            return result;
        }