C#/ASP.NET完善的DBHelper，配套Model生成器

支持Oracle、MSSQL、MySQL、SQLite四種資料庫，支持事務，支持對象關係映射；已在多個項目中實際使用。沒有語法糖，學習成本幾乎為0，拿來即用。 DBHelper類完整代碼： using System; using System.Collections.Generic; using ...
支持Oracle、MSSQL、MySQL、SQLite四種資料庫，支持事務，支持對象關係映射；已在多個項目中實際使用。
沒有語法糖，學習成本幾乎為0，拿來即用。
DBHelper類完整代碼：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Objects.DataClasses;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Models;
using MySql.Data.MySqlClient;

/* ---------------------------------------------
 * 作    者：suxiang
 * 創建日期：2016年11月23日
 * --------------------------------------------- */

namespace DBUtil
{
    /// <summary>
    /// 資料庫操作類
    /// </summary>
    public static class DBHelper
    {
        #region 變數
        /// <summary>
        /// 資料庫類型
        /// </summary>
        private static string m_DBType = ConfigurationManager.AppSettings["DBType"];
        /// <summary>
        /// 資料庫類型
        /// </summary>
        private static bool m_AutoIncrement = ConfigurationManager.AppSettings["AutoIncrement"].ToLower() == "true" ? true : false;
        /// <summary>
        /// 資料庫連接字元串
        /// </summary>
        private static string m_ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        /// <summary>
        /// 事務
        /// </summary>
        [ThreadStatic]
        private static DbTransaction m_Tran;
        /// <summary>
        /// 帶參數的SQL插入和修改語句中，參數前面的符號
        /// </summary>
        private static string m_ParameterMark = GetParameterMark();
        #endregion

        #region 生成變數
        #region 生成 IDbCommand
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private static DbCommand GetCommand()
        {
            DbCommand command = null;

            switch (m_DBType)
            {
                case "oracle":
                    command = new OracleCommand();
                    break;
                case "mssql":
                    command = new SqlCommand();
                    break;
                case "mysql":
                    command = new MySqlCommand();
                    break;
                case "sqlite":
                    command = new SQLiteCommand();
                    break;
            }

            return command;
        }
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private static DbCommand GetCommand(string sql, DbConnection conn)
        {
            DbCommand command = null;

            switch (m_DBType)
            {
                case "oracle":
                    command = new OracleCommand(sql);
                    command.Connection = conn;
                    break;
                case "mssql":
                    command = new SqlCommand(sql);
                    command.Connection = conn;
                    break;
                case "mysql":
                    command = new MySqlCommand(sql);
                    command.Connection = conn;
                    break;
                case "sqlite":
                    command = new SQLiteCommand(sql);
                    command.Connection = conn;
                    break;
            }

            return command;
        }
        #endregion

        #region 生成 IDbConnection
        /// <summary>
        /// 生成 IDbConnection
        /// </summary>
        private static DbConnection GetConnection()
        {
            DbConnection conn = null;

            switch (m_DBType)
            {
                case "oracle":
                    conn = new OracleConnection(m_ConnectionString);
                    break;
                case "mssql":
                    conn = new SqlConnection(m_ConnectionString);
                    break;
                case "mysql":
                    conn = new MySqlConnection(m_ConnectionString);
                    break;
                case "sqlite":
                    conn = new SQLiteConnection(m_ConnectionString);
                    break;
            }

            return conn;
        }
        #endregion

        #region 生成 IDbDataAdapter
        /// <summary>
        /// 生成 IDbDataAdapter
        /// </summary>
        private static DbDataAdapter GetDataAdapter(DbCommand cmd)
        {
            DbDataAdapter dataAdapter = null;

            switch (m_DBType)
            {
                case "oracle":
                    dataAdapter = new OracleDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "mssql":
                    dataAdapter = new SqlDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "mysql":
                    dataAdapter = new MySqlDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
                case "sqlite":
                    dataAdapter = new SQLiteDataAdapter();
                    dataAdapter.SelectCommand = cmd;
                    break;
            }

            return dataAdapter;
        }
        #endregion

        #region 生成 m_ParameterMark
        /// <summary>
        /// 生成 m_ParameterMark
        /// </summary>
        private static string GetParameterMark()
        {
            switch (m_DBType)
            {
                case "oracle":
                    return ":";
                case "mssql":
                    return "@";
                case "mysql":
                    return "@";
                case "sqlite":
                    return ":";
            }
            return ":";
        }
        #endregion

        #region 生成 DbParameter
        /// <summary>
        /// 生成 DbParameter
        /// </summary>
        private static DbParameter GetDbParameter(string name, object vallue)
        {
            DbParameter dbParameter = null;

            switch (m_DBType)
            {
                case "oracle":
                    dbParameter = new OracleParameter(name, vallue);
                    break;
                case "mssql":
                    dbParameter = new SqlParameter(name, vallue);
                    break;
                case "mysql":
                    dbParameter = new MySqlParameter(name, vallue);
                    break;
                case "sqlite":
                    dbParameter = new SQLiteParameter(name, vallue);
                    break;
            }

            return dbParameter;
        }
        #endregion
        #endregion

        #region 基礎方法
        #region  執行簡單SQL語句
        #region Exists
        public static bool Exists(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = GetCommand(sqlString, conn))
                {
                    try
                    {
                        conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 執行SQL語句，返回影響的記錄數
        /// <summary>
        /// 執行SQL語句，返回影響的記錄數
        /// </summary>
        /// <param name="sqlString">SQL語句</param>
        /// <returns>影響的記錄數</returns>
        public static int ExecuteSql(string sqlString)
        {
            SqlFilter(ref sqlString);
            DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
            using (DbCommand cmd = GetCommand(sqlString, conn))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                    if (m_Tran != null) cmd.Transaction = m_Tran;
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    if (m_Tran == null) conn.Close();
                }
            }
        }
        #endregion

        #region 執行一條計算查詢結果語句，返回查詢結果
        /// <summary>
        /// 執行一條計算查詢結果語句，返回查詢結果（object）
        /// </summary>
        /// <param name="sqlString">計算查詢結果語句</param>
        /// <returns>查詢結果（object）</returns>
        public static object GetSingle(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = GetCommand(sqlString, conn))
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open) conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
                }
            }
        }
        #endregion

        #region 執行查詢語句，返回IDataReader
        /// <summary>
        /// 執行查詢語句，返回IDataReader ( 註意：調用該方法後，一定要對IDataReader進行Close )
        /// </summary>
        /// <param name="sqlString">查詢語句</param>
        /// <returns>IDataReader</returns>
        public static DbDataReader ExecuteReader(string sqlString)
        {
            SqlFilter(ref sqlString);
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand(sqlString, conn);
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 執行查詢語句，返回DataSet
        /// <summary>
        /// 執行查詢語句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查詢語句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string sqlString)
        {
            SqlFilter(ref sqlString);
            using (DbConnection conn = GetConnection())
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    using (DbCommand cmd = GetCommand(sqlString, conn))
                    {
                        DbDataAdapter adapter = GetDataAdapter(cmd);
                        adapter.Fill(ds, "ds");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
                return ds;
            }
        }
        #endregion

        #region SQL過濾，防註入
        /// <summary>
        /// SQL過濾，防註入
        /// </summary>
        /// <param name="sql">sql</param>
        public static void SqlFilter(ref string sql)
        {
            List<string> keywordList = new List<string>() { 
                "net localgroup",
                "net user",
                "xp_cmdshell",
                "exec",
                "execute",
                "truncate",
                "drop",
                "restore",
                "create",
                "alter",
                "rename",
                "insert",
                "update",
                "delete",
                "select"};
            string ignore = string.Empty;
            string upperSql = sql.ToUpper().Trim();
            foreach (string keyword in keywordList)
            {
                if (upperSql.IndexOf(keyword.ToUpper()) == 0)
                {
                    ignore = keyword;
                }
            }
            foreach (string keyword in keywordList)
            {
                if (ignore.ToUpper() == keyword.ToUpper()) continue;
                Regex regex = new Regex(keyword, RegexOptions.IgnoreCase);
                sql = regex.Replace(sql, string.Empty);
            }
        }
        #endregion
        #endregion

        #region 執行帶參數的SQL語句
        #region 執行SQL語句，返回影響的記錄數
        /// <summary>
        /// 執行SQL語句，返回影響的記錄數
        /// </summary>
        /// <param name="SQLString">SQL語句</param>
        /// <returns>影響的記錄數</returns>
        public static int ExecuteSql(string SQLString, params DbParameter[] cmdParms)
        {
            DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
            using (DbCommand cmd = GetCommand())
            {
                try
                {
                    PrepareCommand(cmd, conn, m_Tran, SQLString, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (m_Tran == null) conn.Close();
                }
            }
        }
        #endregion

        #region 執行查詢語句，返回IDataReader
        /// <summary>
        /// 執行查詢語句，返回IDataReader ( 註意：調用該方法後，一定要對IDataReader進行Close )
        /// </summary>
        /// <param name="strSQL">查詢語句</param>
        /// <returns>IDataReader</returns>
        public static DbDataReader ExecuteReader(string sqlString, params DbParameter[] cmdParms)
        {
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand();
            try
            {
                PrepareCommand(cmd, conn, null, sqlString, cmdParms);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 執行查詢語句，返回DataSet
        /// <summary>
        /// 執行查詢語句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查詢語句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string sqlString, params DbParameter[] cmdParms)
        {
            DbConnection conn = GetConnection();
            DbCommand cmd = GetCommand();
            PrepareCommand(cmd, conn, null, sqlString, cmdParms);
            using (DbDataAdapter da = GetDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
                return ds;
            }
        }
        #endregion

        #region PrepareCommand
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null) cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion
        #endregion
        #endregion

        #region 增刪改查
        #region 獲取最大編號
        /// <summary>
        /// 獲取最大編號
        /// </summary>
        /// <typeparam name="T">實體Model</typeparam>
        /// <param name="key">主鍵</param>
        public static int GetMaxID<T>(string key)
        {
            Type type = typeof(T);

            string sql = null;
            switch (m_DBType)
            {
                case "oracle":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "mssql":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "mysql":
                    sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                    break;
                case "sqlite":
                    sql = string.Format("SELECT Max(cast({0} as int)) FROM {1}", key, type.Name);
                    break;
            }

            using (DbConnection conn = GetConnection())
            {
                using (IDbCommand cmd = GetCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return 1;
                        }
                        else
                        {
                            return int.Parse(obj.ToString()) + 1;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public static void Insert(object obj)
        {
            Insert(obj, m_AutoIncrement);
        }
        /// <summary>
        /// 添加
        /// </summary>
        public static void Insert(object obj, bool autoIncrement)
        {
            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("insert into {0}(", type.Name));

            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            List<string> propertyNameList = new List<string>();
            int savedCount = 0;
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    propertyNameList.Add(propertyInfo.Name);
                    savedCount++;
                }
            }

            strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));
            strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => m_ParameterMark + a).ToArray())));
            DbParameter[] parameters = new DbParameter[savedCount];
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object val = propertyInfo.GetValue(obj, null);
                    DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                    parameters[k++] = param;
                }
            }

            ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public static void Update(object obj)
        {
            object oldObj = Find(obj, false)