MySQL常用执行SQL方法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Configuration;
//程序集 MySql.Data.dll, v6.9.8.0
using MySql.Data.MySqlClient;
using System.Data;

namespace SqlIsMySQL
{
    public class DbHelpMySql
    {
        //定义连接字符串;当第一次进入这个类的时候，就会给静态全局变量赋值
        public static string mysqlConnectString = ConfigurationManager.AppSettings["mysqlConnectString"];
        /// <summary>
        /// 获得最大的Id
        /// </summary>
        /// <param name="filedName">字段名称</param>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public static int GetMaxId(string filedName, string tableName)
        {
            string strSql = string.Format("select max({0}) +1 from {1} ", filedName, tableName);
            //简化释放资源，结束了括号自动释放资源
            using (MySqlConnection connection = new MySqlConnection(mysqlConnectString))
            {
                using (MySqlCommand cmd = new MySqlCommand(strSql, connection))
                {
                    try
                    {
                        //打开连接
                        connection.Open();
                        //执行语句超时时间设置为120s；默认是30s
                        cmd.CommandTimeout = 120;
                        //返回结果的第一行第一列的值
                        object obj = cmd.ExecuteScalar();
                        if (object.Equals(obj, null) || object.Equals(obj, System.DBNull.Value))
                            return 1;
                        else
                            return int.Parse(obj.ToString());
                    }
                    catch (MySqlException e)
                    {
                        //出现异常，关闭连接
                        connection.Close();
                        throw (e);
                    }

                }
            }
        }
        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="strSql">需要执行的SQL语句</param>
        /// <returns></returns>
        public static bool isExist(string strSql)
        {
            using (MySqlConnection connection = new MySqlConnection(mysqlConnectString))
            {
                using (MySqlCommand cmd = new MySqlCommand(strSql, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if (object.Equals(obj, null) || object.Equals(obj, System.DBNull.Value))
                            return false;
                        else
                            return true;
                    }
                    catch (MySqlException e)
                    {
                        connection.Close();
                        throw (e);
                    }
                }
            }
        }
        /// <summary>
        /// mysql执行查询SQL语句，不含有参数
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public static DataTable ExcuteQuerySql(string strSql)
        {
            using (MySqlConnection connection = new MySqlConnection(mysqlConnectString))
            {
                using (MySqlDataAdapter cmd = new MySqlDataAdapter(strSql, connection))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        connection.Open();
                        cmd.Fill(dataSet, "ds");
                    }
                    catch (MySqlException e)
                    {
                        connection.Close();
                        throw (e);
                    }
                    return dataSet.Tables[0];
                }
            }
        }
        /// <summary>
        /// 执行update delete insert语句返回的参数。
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static int ExcuteNonQuery(string strSql)
        {
            using (MySqlConnection connection = new MySqlConnection(mysqlConnectString))
            {
                using (MySqlCommand cmd = new MySqlCommand(strSql, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = 120;
                        //ExecuteNonQuery返回值的问题；如果是执行update delete insert语句，则返回影响的行数；
                        //执行其他类型的语句，则返回-1；例如select语句；发生回滚也返回-1
                        int rows = cmd.ExecuteNonQuery();
                        if (rows == -1)
                            return 0;
                        else
                            return rows;
                    }
                    catch (MySqlException e)
                    {
                        throw (e);
                    }                    
                }
            }
        }
        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <param name="cmdParams">参数</param>
        /// <returns></returns>
        public static MySqlDataReader ExcuteNoQueryHasParam(string strSql,params MySqlParameter[] cmdParams)
        {
            using (MySqlConnection connection = new MySqlConnection(mysqlConnectString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        PrePareCommand(cmd, connection, null, strSql, cmdParams);
                        MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        cmd.Parameters.Clear();
                        return myReader;
                    }
                    catch (MySqlException e)
                    {
                        throw (e);
                    }                   
                }
            }
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="con"></param>
        /// <param name="trans"></param>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        public static void PrePareCommand(MySqlCommand cmd,MySqlConnection con,MySqlTransaction trans,string strSql,MySqlParameter[] cmdParams)
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            cmd.Connection = con;
            cmd.CommandText = strSql;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParams != null)
            {
                foreach (MySqlParameter param in cmdParams)
                {
                    if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Input) && param.Value != null)
                        param.Value = DBNull.Value;
                    cmd.Parameters.Add(param);
                }
            }
        }
    }
}