MySql Helper Class

最新项目需要用到MySql数据库，由于之前很少涉及到数据库相关的操作，看到同事写的MySql数据库操作类，感觉挺不错的，记下来希望有时间能用到。具体数据库相关操作如下：

1.　　建立数据库连接

/// <summary>建立数据库连接.    
        /// </summary>    
        /// <returns>返回MySqlConnection对象</returns>    
        public static MySqlConnection GetMySqlConnection()
        {
            string connStr = "server=wudh2-1;database=AutomationManager;Uid=amuser;Pwd=amuser";
            //string connStr = "server=localhost;database=AutomationManager;Uid=root;Pwd=wudonghai";
            MySqlConnection myCon = new MySqlConnection(connStr);

            return myCon;
        }

2.　　执行MySqlCommand命令

/// <summary>执行MySqlCommand    
        /// </summary>    
        /// <param name="sqlCommand">SQL语句</param>
        public static void ExecuteMySqlCommand(string sqlCommand)
        {
            //if (sqlCommand.Contains('\'))
            //{
            //    sqlCommand = sqlCommand.Replace(@"", @"\");
            //}

            MySqlConnection mysqlcon = GetMySqlConnection();
            mysqlcon.Open();
            MySqlCommand mysqlcom = new MySqlCommand(sqlCommand, mysqlcon);
            mysqlcom.ExecuteNonQuery();
            mysqlcom.Dispose();
            mysqlcon.Close();
            mysqlcon.Dispose();
        }

3.　　插入记录

        /// <summary>
        /// 插入记录
        /// </summary>
        public static int Insert(string tableName, string filter)
        {
            int newRecordId = 0;
            string commandText = string.Format("INSERT INTO {0} {1}", tableName, filter);
            if (commandText.Contains('\'))
            {
                commandText = commandText.Replace(@"", @"\");
            }
            string getIdCommandText = "SELECT LAST_INSERT_ID()";


            MySqlConnection mysqlcon = GetMySqlConnection();
            mysqlcon.Open();
            MySqlCommand mysqlcom = new MySqlCommand(commandText, mysqlcon);
            mysqlcom.ExecuteNonQuery();

            MySqlCommand getIdCommand = new MySqlCommand(getIdCommandText, mysqlcon);

            MySqlDataReader mysqlreader = getIdCommand.ExecuteReader(CommandBehavior.CloseConnection);

            if (mysqlreader.Read())
            {
                newRecordId = Convert.ToInt32(mysqlreader["LAST_INSERT_ID()"]);
            }

            mysqlcom.Dispose();
            mysqlcon.Close();
            mysqlcon.Dispose();

            return newRecordId;
        }

4.　　更新记录

/// <summary>
        /// 更新记录
        /// </summary>
        public static void Update(string tableName, string filter)
        {
            string commandText = string.Format("UPDATE {0} SET {1}", tableName, filter);

            if (commandText.Contains('\'))
            {
                commandText = commandText.Replace(@"", @"\");
            }

            ExecuteMySqlCommand(commandText);
        }

5.　　删除记录

/// <summary>
        /// 删除记录
        /// </summary>
        public static void Delete(string tableName, string filter)
        {
            string commandText = string.Format("DELETE FROM {0} {1}", tableName, filter);
            ExecuteMySqlCommand(commandText);
        }

6.　　查询数据

/// <summary>
        /// 查询数据
        /// </summary>
        public static DataTable Query(string tableName, string filter)
        {
            string sqlCommand;
            if (string.IsNullOrEmpty(filter))
            {
                sqlCommand = "SELECT * FROM " + tableName;
            }
            else
            {
                sqlCommand = "SELECT * FROM " + tableName + " WHERE " + filter;
            }
            //MySqlDataReader reader = GetMySqlReader(sqlCommand);
            DataTable table = new DataTable();
            MySqlDataAdapter adapter = GetMySqlAdapter(sqlCommand);
            adapter.Fill(table);

            adapter.Dispose();

            return table;
        }

/// <summary>
        /// 查询数据
        /// </summary>
        public static DataTable Query(string commandText)
        {
            DataTable table = new DataTable();
            MySqlDataAdapter adapter = GetMySqlAdapter(commandText);
            adapter.Fill(table);
            
            return table;
        }

7.　　获取最大值

/// <summary>
        /// 从指定表和列中获取最大值
        /// </summary>
        public static Int32 GetMaxValue(string columnName, string tableName, string filter)
        {
            Int32 result = 0;
            string commandText;
            if (filter == null || filter == string.Empty)
            {
                commandText =
                    string.Format("SELECT MAX({0}) FROM {1}",
                    columnName, tableName);
            }
            else
            {
                commandText =
                    string.Format("SELECT MAX({0}) FROM {1} WHERE {2}",
                    columnName, tableName, filter);
            }

            MySqlConnection mysqlcon = GetMySqlConnection();
            MySqlCommand mysqlcom = new MySqlCommand(commandText, mysqlcon);
            mysqlcon.Open();

            string resultString = mysqlcom.ExecuteScalar().ToString();
            if (!string.IsNullOrEmpty(resultString))
            {
                result = Convert.ToInt32(resultString);
            }

            mysqlcom.Dispose();
            mysqlcon.Close();
            mysqlcon.Dispose();

            return result;
        }

8.　　创建MySqlDataReader对象

/// <summary>创建一个MySqlDataReader对象    
        /// </summary>    
        /// <param name="M_str_sqlstr">SQL语句</param>    
        /// <returns>返回MySqlDataReader对象</returns>    
        public static MySqlDataReader GetMySqlReader(string sqlCommand)
        {
            MySqlConnection mysqlcon = GetMySqlConnection();
            MySqlCommand mysqlcom = new MySqlCommand(sqlCommand, mysqlcon);
            mysqlcon.Open();
            MySqlDataReader mysqlreader = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);

            return mysqlreader;
        }

9.　　创建MySqlDataAdapter对象

/// <summary>创建一个MySqlDataAdapter对象    
        /// </summary>    
        /// <param name="M_str_sqlstr">SQL语句</param>    
        /// <returns>返回MySqlDataAdapter对象</returns>    
        public static MySqlDataAdapter GetMySqlAdapter(string sqlCommand)
        {
            MySqlConnection mysqlcon = GetMySqlConnection();
            MySqlCommand mySQLCommand = new MySqlCommand(sqlCommand, mysqlcon);
            MySqlDataAdapter mySQLAdapter = new MySqlDataAdapter(mySQLCommand);
            
            return mySQLAdapter;
        }