SqlHelper

以前只用一种数据库，倒也无所谓，但是再数据库切换的时候，发现代码差不多呀。

最初，两种数据库，大不了写两个SqlHelper，但是多了也就发现代码重用率太低了吧。

因此，下面的SqlHelper诞生了。

using System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;namespace WangSql.DBUtility{    public static class SqlHelperExt    {        public static int AddRange(this IDataParameterCollection coll, IDataParameter[] par)        {            int i = 0;            foreach (var item in par)            {                coll.Add(item);                i++;            }            return i;        }    }    #region SqlHelper    public class SqlHelper    {        private IDbConnection conn = null;        private IDbCommand cmd = null;        private IDataReader dr = null;        private DbType type = DbType.NONE;        #region 创建数据库连接        /// <summary>        /// 创建数据库连接        /// </summary>        public SqlHelper(string connectionString)        {            conn = DBFactory.CreateDbConnection(type, connectionString);        }        #endregion        #region 判断并打开conn        /// <summary>        /// 判断并打开conn        /// </summary>        /// <returns></returns>        public IDbConnection CreatConn()        {            if (conn.State == ConnectionState.Closed)            {                conn.Open();            }            return conn;        }        #endregion        #region 执行查询sql语句        /// <summary>        /// 执行查询sql语句        /// </summary>        /// <param name="sql">查询sql语句</param>        /// <returns>返回一个表</returns>        public DataTable ExecuteReader(string sql)        {            DataTable dt = new DataTable();            using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))            {                using (dr = cmd.ExecuteReader())                {                    dt.Load(dr);                }            }            conn.Close();            return dt;        }        #endregion        #region 执行查询带参的sql语句        /// <summary>        /// 执行查询带参的sql语句        /// </summary>        /// <param name="sql">查询sql语句</param>        /// <param name="par">sql语句中的参数</param>        /// <returns>返回一个表</returns>        public DataTable ExecuteReader(string sql, IDataParameter[] par)        {            DataTable dt = new DataTable();            using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))            {                cmd.Parameters.AddRange(par);                using (dr = cmd.ExecuteReader())                {                    dt.Load(dr);                }            }            conn.Close();            return dt;        }        public DataTable ExecuteReader(string sql, IDataParameter par)        {            DataTable dt = new DataTable();            using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))            {                cmd.Parameters.Add(par);                using (dr = cmd.ExecuteReader())                {                    dt.Load(dr);                }            }            conn.Close();            return dt;        }        #endregion        #region 执行增，删，改sql语句        /// <summary>        /// 执行无参的增，删，改sql语句        /// </summary>        /// <param name="sql">增，删，改的sql语句</param>        /// <param name="par">sql语句中的参数</param>        /// <returns>返回所影响的行数</returns>        public int ExecuteNonQuery(string sql)        {            int result = 0;            using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))            {                result = cmd.ExecuteNonQuery();            }            conn.Close();            return result;        }        #endregion        #region 执行带参的增，删，改sql语句        /// <summary>        /// 执行带参的增，删，改sql语句        /// </summary>        /// <param name="sql">增，删，改的sql语句</param>        /// <param name="par">sql语句中的参数</param>        /// <returns>返回所影响的行数</returns>        public int ExecuteNonQuery(string sql, IDbDataParameter[] par)        {            int result = 0;            using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))            {                cmd.Parameters.AddRange(par);                result = cmd.ExecuteNonQuery();            }            conn.Close();            return result;        }        public int ExecuteNonQuery(string sql, IDbDataParameter par)        {            int result = 0;            using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))            {                cmd.Parameters.Add(par);                result = cmd.ExecuteNonQuery();            }            conn.Close();            return result;        }        #endregion        #region 事务        /// <summary>        /// 执行多条SQL语句，实现数据库事务。        /// </summary>        /// <param name="SQLList">SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）</param>        public bool ExecuteTransaction(Hashtable SqlList)        {            CreatConn();            using (IDbTransaction trans = conn.BeginTransaction())            {                IDbCommand cmd = DBFactory.CreateDbCommand(type);                try                {                    //循环                    foreach (DictionaryEntry myDE in SqlList)                    {                        string cmdText = myDE.Key.ToString();                        IDbDataParameter[] cmdParms = (IDbDataParameter[])myDE.Value;                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms);                        int val = cmd.ExecuteNonQuery();                        cmd.Parameters.Clear();                    }                    trans.Commit();                }                catch                {                    trans.Rollback();                    return false;                }                finally                {                    conn.Close();                }            }            return true;        }        private void PrepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, string cmdText, IDataParameter[] cmdParms)        {            CreatConn();            cmd.Connection = conn;            cmd.CommandText = cmdText;            if (trans != null)                cmd.Transaction = trans;            cmd.CommandType = CommandType.Text;//cmdType;            if (cmdParms != null)                cmd.Parameters.AddRange(cmdParms);        }        #endregion    }    #endregion}

上面是核心代码，上面有个扩展。主要是是由于抽象类里面不包含AddRange方法。楼主也是懒得改原来的方法，也是为了和原来的SqlHelper保持一致，干脆就直接扩展了一个AddRange。

好了，既然是全是抽象参数，实际中，还是需要实例化具体某种数据库的实例的，所以还需要一个创建各个数据库实例的工厂了。

using MySql.Data.MySqlClient;using Oracle.DataAccess.Client;using System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Data.SQLite;using System.Linq;using System.Text;namespace WangSql{    public enum DbType    {        //Oracle,SqlServer,MySql,Access,SqlLite        NONE,        ORACLE,        SQLSERVER,        MYSQL,        ACCESS,        SQLLITE    }    public class DBFactory    {        public static IDbConnection CreateDbConnection(DbType type, string connectionString)        {            IDbConnection conn = null;            switch (type)            {                case DbType.ORACLE:                    conn = new OracleConnection(connectionString);                    break;                case DbType.SQLSERVER:                    conn = new SqlConnection(connectionString);                    break;                case DbType.MYSQL:                    conn = new MySqlConnection(connectionString);                    break;                case DbType.ACCESS:                    conn = new OleDbConnection(connectionString);                    break;                case DbType.SQLLITE:                    conn = new SQLiteConnection(connectionString);                    break;                case DbType.NONE:                    throw new Exception("未设置数据库类型");                default:                    throw new Exception("不支持该数据库类型");            }            return conn;        }        public static IDbCommand CreateDbCommand(DbType type)        {            IDbCommand cmd = null;            switch (type)            {                case DbType.ORACLE:                    cmd = new OracleCommand();                    break;                case DbType.SQLSERVER:                    cmd = new SqlCommand();                    break;                case DbType.MYSQL:                    cmd = new MySqlCommand();                    break;                case DbType.ACCESS:                    cmd = new OleDbCommand();                    break;                case DbType.SQLLITE:                    cmd = new SQLiteCommand();                    break;                case DbType.NONE:                    throw new Exception("未设置数据库类型");                default:                    throw new Exception("不支持该数据库类型");            }            return cmd;        }        public static IDbCommand CreateDbCommand(string sql, IDbConnection conn)        {            DbType type = DbType.NONE;            if (conn is OracleConnection)                type = DbType.ORACLE;            else if (conn is SqlConnection)                type = DbType.SQLSERVER;            else if (conn is MySqlConnection)                type = DbType.MYSQL;            else if (conn is OleDbConnection)                type = DbType.ACCESS;            else if (conn is SQLiteConnection)                type = DbType.SQLLITE;            IDbCommand cmd = null;            switch (type)            {                case DbType.ORACLE:                    cmd = new OracleCommand(sql, (OracleConnection)conn);                    break;                case DbType.SQLSERVER:                    cmd = new SqlCommand(sql, (SqlConnection)conn);                    break;                case DbType.MYSQL:                    cmd = new MySqlCommand(sql, (MySqlConnection)conn);                    break;                case DbType.ACCESS:                    cmd = new OleDbCommand(sql, (OleDbConnection)conn);                    break;                case DbType.SQLLITE:                    cmd = new SQLiteCommand(sql, (SQLiteConnection)conn);                    break;                case DbType.NONE:                    throw new Exception("未设置数据库类型");                default:                    throw new Exception("不支持该数据库类型");            }            return cmd;        }    }}