SQLHelper帮助类_下（支持多数据库的封装）

    在上篇关于SQLHelper类中，主要针对SQLServer数据库进行的。在使用别的数据库，就要修改部分代码！所以今天就写一个支持多数据库的封装！主要用到枚举，读取config文件！接口的简单用法。获取连接等...

 

一.config配置文件：

  <connectionStrings>
    <add name="constr" connectionString="server=127.0.0.1;user id=用户名;password=密码;database=数据库名"/>
  </connectionStrings>
  <appSettings>
    <add key="database" value ="MYSQL"/>
  </appSettings>

关于连接字符串在SQLHelper帮助类_ 上 进行介绍  http://www.cnblogs.com/fengxuehuanlin/p/5271944.html

二.SqlHelper:

       private static string connstr;
        /// <summary>
        /// 从配置文件中获取连接字符串名,使用什么数据库
        /// <appSettings>中key名：database，value值:数据库
        /// <connectionStrings>name：constr
        /// </summary>
        static SqlHelper()
        {
            connstr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; //获取config文件中连接字符串
            string databaseType = ConfigurationManager.AppSettings["database"].ToUpper();
            DBdatabase.database = AdoHelper.EnumParse<DatabaseList>(databaseType);   //将字符串转换成枚举类型
        }
       
        /// <summary>
        /// 获取相应数据库的连接
        /// </summary>
        /// <returns></returns>
        public static IDbConnection getConnection()
        {
            string value = DBdatabase.database.ToString();
            DatabaseList database= AdoHelper.EnumParse<DatabaseList>(value);
            switch(database)
            {
                case DatabaseList.MYSQL:
                    return new MySqlConnection(connstr);
                case  DatabaseList.SQLSERVER:
                    return new SqlConnection(connstr);
                default:
                    throw new Exception("数据库类型目前不支持！");
            }
        }

        /// <summary>
        /// 将数据加载到本地，在本地对数据进行操作
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameter">参数化查询</param>
        /// <returns>返回从数据库中读取到的DataTable表</returns>
        public static DataTable ExecuteQuery(string Sql, params IDbDataParameter[] parameter)
        {
             using (IDbConnection conn=getConnection())
             using (IDbCommand cmd = conn.CreateCommand())            
             {
                conn.Open();
                cmd.CommandText = Sql;
                for (int i = 0; i < parameter.Length; i++)
                {
                    cmd.Parameters.Add(parameter[i]);
                }
                DataTable tab = new DataTable();
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    tab.Load(reader);
                    return tab;
                }
            }        
        }
        /// <summary>
        /// 用于执行增加和删除语句
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameter">参数化查询</param>
        /// <returns>有多少语句执行成功</returns>
        public static int ExecuteNonQuery(string Sql, params IDbDataParameter[] parameter)
        {
            using (IDbConnection conn = getConnection())
            using (IDbCommand cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = Sql;
                for (int i = 0; i < parameter.Length; i++)
                {
                    cmd.Parameters.Add(parameter[i]);
                }
                return cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 执行语句后，返回第一行第一列的数据
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="parameter"></param>
        /// <returns>object类型的值</returns>
        public static object ExecuteScalar(string Sql, params IDbDataParameter[] parameter)
        {
            using (IDbConnection conn=getConnection())
             using (IDbCommand cmd = conn.CreateCommand())            
             {
                conn.Open();
                cmd.CommandText = Sql;
                for (int i = 0; i < parameter.Length; i++)
                {
                    cmd.Parameters.Add(parameter[i]);
                }
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 在数据库中，进行数据库的查询操作
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(string Sql, params IDbDataParameter[] parameter)
        {
            using (IDbConnection conn = getConnection())
            using (IDbCommand cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = Sql;
                for (int i = 0; i < parameter.Length; i++)
                {
                    cmd.Parameters.Add(parameter[i]);
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
    }
    /// <summary>
    /// 判断是什么数据库
    /// </summary>
    public class DBdatabase
    {
        public static DatabaseList database { get; set; }
    }
    public class AdoHelper
    {
        /// <summary>
        /// 将字符串转换成枚举类型
        /// </summary>
        /// <typeparam name="T">枚举类型</typeparam>
        /// <param name="value">字符串值</param>
        /// <returns></returns>
        public static T EnumParse<T>(string value)
        {
            return (T)Enum.Parse(typeof(T), value);
        }

        /// <summary>
        /// 对数据库进行操作
        /// </summary>
        /// <param name="conn">获取一个连接</param>
        /// <returns></returns>
        public static IDbCommand CreateCommand(IDbConnection conn)
        {
            IDbCommand cmd = conn.CreateCommand();
            conn.Open();
            return cmd;
        }
    }
    /// <summary>
    /// 数据库类型
    /// </summary>
    public enum DatabaseList
    {
        MYSQL = 0,
        SQLSERVER = 1
    }