轻量级O君越M

   
不亮堂只怕称的上是OTiguanM,其实就是3个DBHelper。看到网上海人民广播电视台湾大学人写本身的OHighlanderM,但笔者认为都不太好。

   
不晓得只怕称的上是O安德拉M,其实正是三个DBHelper。看到网上海人民广播电视台湾大学人写自个儿的OOdysseyM,但本人觉着都不太好。

支撑Oracle、MSSQL、MySQL、SQLite多种数据库,辅助职业,辅助对象关联映射;已在多个种类中实际应用。

帮忙Oracle、MSSQL、MySQL、SQLite各个数据库,扶助职业,帮衬对象关联映射;已在多个品种中其实应用。

   
小编这几个O揽胜极光M,学习费用相当的低,常用的办法仅有多少个,在行使的长河中,你不要求有太多要注意的地点,也不会有“笔者怎么贯彻连表查询”的问号。反射捐躯局地性质,可是下跌了贯彻和应用的复杂度。

轻量级O君越M。   
笔者这一个O中华VM,学费极低,常用的办法仅有多少个,在接纳的进度中,你不供给有太多要留意的地点,也不会有“小编怎么着贯彻连表查询”的疑点。反射捐躯局地属性,可是降低了完成和接纳的复杂度。

平素不语法糖,学习开销大致为0,拿来即用。

未曾语法糖,学习花费差不离为0,拿来即用。

    援助Oracle、MSSQL、MySQL、SQLite各样数据库,并配有Model生成器。

    帮忙Oracle、MSSQL、MySQL、SQLite各样数据库,并配有Model生成器。

DBHelper类完整代码:

DBHelper类完整代码:

Model层的设计:

Model层的安顿性:

亚洲必赢官网 1亚洲必赢官网 2

亚洲必赢官网 3亚洲必赢官网 4

亚洲必赢官网 5

亚洲必赢官网 6

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)
        {
            sql = sql.Trim();
            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();
            foreach (string keyword in keywordList)
            {
                if (upperSql.IndexOf(keyword.ToUpper()) == 0)
                {
                    ignore = keyword;
                }
            }
            foreach (string keyword in keywordList)
            {
                if (ignore == "select" && ignore == keyword) continue;
                Regex regex = new Regex(keyword, RegexOptions.IgnoreCase);
                sql = sql.Substring(0, ignore.Length) + regex.Replace(sql.Substring(ignore.Length), 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);
            if (oldObj == null) throw new Exception("无法获取到旧数据");

            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("update {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(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        propertyNameList.Add(propertyInfo.Name);
                        savedCount++;
                    }
                }
            }

            strSql.Append(string.Format(" set "));
            DbParameter[] parameters = new DbParameter[savedCount];
            StringBuilder sbPros = new StringBuilder();
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));
                        DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                        parameters[k++] = param;
                    }
                }
            }
            if (sbPros.Length > 0)
            {
                strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
            }
            strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));

            if (savedCount > 0)
            {
                ExecuteSql(strSql.ToString(), parameters);
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据Id删除
        /// </summary>
        public static void Delete<T>(int id)
        {
            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            DbParameter[] cmdParms = new DbParameter[1];
            cmdParms[0] = GetDbParameter(m_ParameterMark + GetIdName(type), id);
            sbSql.Append(string.Format("delete from {0} where {2}={1}{2}", type.Name, m_ParameterMark, GetIdName(type)));

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据Id集合删除
        /// </summary>
        public static void BatchDelete<T>(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            string[] idArr = ids.Split(',');
            DbParameter[] cmdParms = new DbParameter[idArr.Length];
            sbSql.AppendFormat("delete from {0} where {1} in (", type.Name, GetIdName(type));
            for (int i = 0; i < idArr.Length; i++)
            {
                cmdParms[i] = GetDbParameter(m_ParameterMark + GetIdName(type) + i, idArr[i]);
                sbSql.AppendFormat("{1}{2}{3},", type.Name, m_ParameterMark, GetIdName(type), i);
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            sbSql.Append(")");

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public static void Delete<T>(string conditions)
        {
            if (string.IsNullOrWhiteSpace(conditions)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            SqlFilter(ref conditions);
            sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));

            ExecuteSql(sbSql.ToString());
        }
        #endregion

        #region 获取实体
        #region 根据实体获取实体
        /// <summary>
        /// 根据实体获取实体
        /// </summary>
        private static object Find(object obj, bool readCache = true)
        {
            Type type = obj.GetType();

            object result = Activator.CreateInstance(type);
            bool hasValue = false;
            IDataReader rd = null;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        private static object FindById(Type type, int id)
        {
            object result = Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        public static T FindById<T>(string id) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region 根据sql获取实体
        /// <summary>
        /// 根据sql获取实体
        /// </summary>
        public static T FindBySql<T>(string sql) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql, cmdParms);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString());
                pagerModel.result = list;
            }

            return pagerModel;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
                pagerModel.result = list;
            }

            return pagerModel;
        }


        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms)
        {
            DataSet ds = null;

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                totalCount = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                ds = Query(sql, cmdParms);
            }
            return ds;
        }
        #endregion

        #region getReaderValue 转换数据
        /// <summary>
        /// 转换数据
        /// </summary>
        private static Object getReaderValue(Object rdValue, Type ptype)
        {
            if (ptype == typeof(double))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(decimal))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(int))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(long))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(DateTime))
                return Convert.ToDateTime(rdValue);

            if (ptype == typeof(Nullable<double>))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(Nullable<decimal>))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(Nullable<int>))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(Nullable<long>))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(Nullable<DateTime>))
                return Convert.ToDateTime(rdValue);

            return rdValue;
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static string GetIdName(Type type)
        {
            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
                {
                    return propertyInfo.Name;
                }
            }
            return "Id";
        }
        #endregion

        #region 获取主键值
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static object GetIdVal(object val)
        {
            string idName = GetIdName(val.GetType());
            if (!string.IsNullOrWhiteSpace(idName))
            {
                return val.GetType().GetProperty(idName).GetValue(val, null);
            }
            return 0;
        }
        #endregion

        #region 获取实体类属性
        /// <summary>
        /// 获取实体类属性
        /// </summary>
        private static PropertyInfo[] GetEntityProperties(Type type)
        {
            List<PropertyInfo> result = new List<PropertyInfo>();
            PropertyInfo[] propertyInfoList = type.GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
                    && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
                {
                    result.Add(propertyInfo);
                }
            }
            return result.ToArray();
        }
        #endregion

        #region 获取基类
        /// <summary>
        /// 获取基类
        /// </summary>
        public static Type GetBaseType(Type type)
        {
            while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
            {
                type = type.BaseType;
            }
            return type;
        }
        #endregion
        #endregion

        #region 事务
        #region 开始事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public static void BeginTransaction()
        {
            DbConnection conn = GetConnection();
            if (conn.State != ConnectionState.Open) conn.Open();
            m_Tran = conn.BeginTransaction();
        }
        #endregion

        #region 提交事务
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void CommitTransaction()
        {
            if (m_Tran == null) return; //防止重复提交
            DbConnection conn = m_Tran.Connection;
            try
            {
                m_Tran.Commit();
            }
            catch (Exception ex)
            {
                m_Tran.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                m_Tran.Dispose();
                m_Tran = null;
            }
        }
        #endregion

        #region 回滚事务(出错时调用该方法回滚)
        /// <summary>
        /// 回滚事务(出错时调用该方法回滚)
        /// </summary>
        public static void RollbackTransaction()
        {
            if (m_Tran == null) return; //防止重复回滚
            DbConnection conn = m_Tran.Connection;
            m_Tran.Rollback();
            if (conn.State == ConnectionState.Open) conn.Close();
        }
        #endregion
        #endregion
    }
}
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)
        {
            sql = sql.Trim();
            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();
            foreach (string keyword in keywordList)
            {
                if (upperSql.IndexOf(keyword.ToUpper()) == 0)
                {
                    ignore = keyword;
                }
            }
            foreach (string keyword in keywordList)
            {
                if (ignore == "select" && ignore == keyword) continue;
                Regex regex = new Regex(keyword, RegexOptions.IgnoreCase);
                sql = sql.Substring(0, ignore.Length) + regex.Replace(sql.Substring(ignore.Length), 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);
            if (oldObj == null) throw new Exception("无法获取到旧数据");

            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("update {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(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        propertyNameList.Add(propertyInfo.Name);
                        savedCount++;
                    }
                }
            }

            strSql.Append(string.Format(" set "));
            DbParameter[] parameters = new DbParameter[savedCount];
            StringBuilder sbPros = new StringBuilder();
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));
                        DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                        parameters[k++] = param;
                    }
                }
            }
            if (sbPros.Length > 0)
            {
                strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
            }
            strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));

            if (savedCount > 0)
            {
                ExecuteSql(strSql.ToString(), parameters);
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据Id删除
        /// </summary>
        public static void Delete<T>(int id)
        {
            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            DbParameter[] cmdParms = new DbParameter[1];
            cmdParms[0] = GetDbParameter(m_ParameterMark + GetIdName(type), id);
            sbSql.Append(string.Format("delete from {0} where {2}={1}{2}", type.Name, m_ParameterMark, GetIdName(type)));

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据Id集合删除
        /// </summary>
        public static void BatchDelete<T>(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            string[] idArr = ids.Split(',');
            DbParameter[] cmdParms = new DbParameter[idArr.Length];
            sbSql.AppendFormat("delete from {0} where {1} in (", type.Name, GetIdName(type));
            for (int i = 0; i < idArr.Length; i++)
            {
                cmdParms[i] = GetDbParameter(m_ParameterMark + GetIdName(type) + i, idArr[i]);
                sbSql.AppendFormat("{1}{2}{3},", type.Name, m_ParameterMark, GetIdName(type), i);
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            sbSql.Append(")");

            ExecuteSql(sbSql.ToString(), cmdParms);
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public static void Delete<T>(string conditions)
        {
            if (string.IsNullOrWhiteSpace(conditions)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            SqlFilter(ref conditions);
            sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));

            ExecuteSql(sbSql.ToString());
        }
        #endregion

        #region 获取实体
        #region 根据实体获取实体
        /// <summary>
        /// 根据实体获取实体
        /// </summary>
        private static object Find(object obj, bool readCache = true)
        {
            Type type = obj.GetType();

            object result = Activator.CreateInstance(type);
            bool hasValue = false;
            IDataReader rd = null;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        private static object FindById(Type type, int id)
        {
            object result = Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        public static T FindById<T>(string id) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region 根据sql获取实体
        /// <summary>
        /// 根据sql获取实体
        /// </summary>
        public static T FindBySql<T>(string sql) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public static List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql, cmdParms);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString());
                pagerModel.result = list;
            }

            return pagerModel;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
                pagerModel.result = list;
            }

            return pagerModel;
        }


        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public static DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms)
        {
            DataSet ds = null;

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                totalCount = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                ds = Query(sql, cmdParms);
            }
            return ds;
        }
        #endregion

        #region getReaderValue 转换数据
        /// <summary>
        /// 转换数据
        /// </summary>
        private static Object getReaderValue(Object rdValue, Type ptype)
        {
            if (ptype == typeof(double))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(decimal))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(int))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(long))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(DateTime))
                return Convert.ToDateTime(rdValue);

            if (ptype == typeof(Nullable<double>))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(Nullable<decimal>))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(Nullable<int>))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(Nullable<long>))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(Nullable<DateTime>))
                return Convert.ToDateTime(rdValue);

            return rdValue;
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static string GetIdName(Type type)
        {
            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
                {
                    return propertyInfo.Name;
                }
            }
            return "Id";
        }
        #endregion

        #region 获取主键值
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public static object GetIdVal(object val)
        {
            string idName = GetIdName(val.GetType());
            if (!string.IsNullOrWhiteSpace(idName))
            {
                return val.GetType().GetProperty(idName).GetValue(val, null);
            }
            return 0;
        }
        #endregion

        #region 获取实体类属性
        /// <summary>
        /// 获取实体类属性
        /// </summary>
        private static PropertyInfo[] GetEntityProperties(Type type)
        {
            List<PropertyInfo> result = new List<PropertyInfo>();
            PropertyInfo[] propertyInfoList = type.GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
                    && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
                {
                    result.Add(propertyInfo);
                }
            }
            return result.ToArray();
        }
        #endregion

        #region 获取基类
        /// <summary>
        /// 获取基类
        /// </summary>
        public static Type GetBaseType(Type type)
        {
            while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
            {
                type = type.BaseType;
            }
            return type;
        }
        #endregion
        #endregion

        #region 事务
        #region 开始事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public static void BeginTransaction()
        {
            DbConnection conn = GetConnection();
            if (conn.State != ConnectionState.Open) conn.Open();
            m_Tran = conn.BeginTransaction();
        }
        #endregion

        #region 提交事务
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void CommitTransaction()
        {
            if (m_Tran == null) return; //防止重复提交
            DbConnection conn = m_Tran.Connection;
            try
            {
                m_Tran.Commit();
            }
            catch (Exception ex)
            {
                m_Tran.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                m_Tran.Dispose();
                m_Tran = null;
            }
        }
        #endregion

        #region 回滚事务(出错时调用该方法回滚)
        /// <summary>
        /// 回滚事务(出错时调用该方法回滚)
        /// </summary>
        public static void RollbackTransaction()
        {
            if (m_Tran == null) return; //防止重复回滚
            DbConnection conn = m_Tran.Connection;
            m_Tran.Rollback();
            if (conn.State == ConnectionState.Open) conn.Close();
        }
        #endregion
        #endregion
    }
}

   
Models目录中的类及其天性和数据库中的表和字段是完全对应的,Models全体由生成器生成,并且不容许手动修改。ExtModels目录中的类是增加类,首要用以查询与展现,比如表中存的是code,但您须要关联合检查询另一张表中的name,就能够在这些扩张类中增添2个用以呈现的name字段。Models和ExtModels目录的中类都以partial修饰。

   
Models目录中的类及其天性和数据库中的表和字段是一心对应的,Models全体由生成器生成,并且不允许手动修改。ExtModels目录中的类是增加类,首要用来查询与展现,比如表中存的是code,但您供给关联合检查询另一张表中的name,就可以在这些扩充类中扩大二个用来呈现的name字段。Models和ExtModels目录的中类都以partial修饰。

View Code

View Code

   
例(由于是SQLite数据库,所以生成的Model未有注释,别的三种数据库有注释):

亚洲必赢官网 ,   
例(由于是SQLite数据库,所以生成的Model未有注释,其余二种数据库有注释):

表明:DBHelper中对业务变量private static
DbTransaction
m_Tran使用了[ThreadStatic]标签,以支持多用户并发;不过要是是单个用户采用多线程并发请求服务器,可能那种艺术的数据库事务是不协理的,可是貌似品种未有那种要求,倘诺有请使用HttpContext.Current.Items改写只怕其余格局改写。

证实:DBHelper中对业务变量private static
DbTransaction
m_Tran使用了[ThreadStatic]标签,以援救多用户并发;可是一旦是单个用户使用二十四线程并发请求服务器,大概那种情势的数据库事务是不帮忙的,可是貌似项目并没有那种供给,即便有请使用HttpContext.Current.Items改写恐怕其余措施改写。

Model:

Model:

Web.config配置:

Web.config配置:

亚洲必赢官网 7亚洲必赢官网 8

亚洲必赢官网 9亚洲必赢官网 10

亚洲必赢官网 11亚洲必赢官网 12

亚洲必赢官网 13亚洲必赢官网 14

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 
    /// </summary>
    [Serializable]
    public partial class BS_Template
    {
        /// <summary>
        /// 
        /// </summary>
        [IsId]
        [IsDBField]
        public string id { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string typeCode { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string type { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string code { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string name { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string path { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string folder { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string remarks { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 
    /// </summary>
    [Serializable]
    public partial class BS_Template
    {
        /// <summary>
        /// 
        /// </summary>
        [IsId]
        [IsDBField]
        public string id { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string typeCode { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string type { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string code { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string name { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string path { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string folder { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [IsDBField]
        public string remarks { get; set; }
    }
}
<connectionStrings>
  <add name="DefaultConnection"  connectionString="server=localhost;database=netcms3.0;user id=root;password=root;character set=gbk;" />
</connectionStrings>
<appSettings>
  <!--数据库类型-->
  <add key="DBType" value="mysql"/>
  <!--数据库自增-->
  <add key="AutoIncrement" value="false"/>
</appSettings>
<connectionStrings>
  <add name="DefaultConnection"  connectionString="server=localhost;database=netcms3.0;user id=root;password=root;character set=gbk;" />
</connectionStrings>
<appSettings>
  <!--数据库类型-->
  <add key="DBType" value="mysql"/>
  <!--数据库自增-->
  <add key="AutoIncrement" value="false"/>
</appSettings>

View Code

View Code

View Code

View Code

ExtModel:

ExtModel:

证实:对于SQL Server数据库,通过<add
key=”AutoIncrement” value=”false”/>来设置是不是采用数据库自增。

注明:对于SQL Server数据库,通过<add
key=”AutoIncrement” value=”false”/>来设置是或不是使用数据库自增。

亚洲必赢官网 15亚洲必赢官网 16

亚洲必赢官网 17亚洲必赢官网 18

DBHelper类库必要引用的先后集:

DBHelper类库须要引用的次序集:

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 
    /// </summary>
    public partial class BS_Template
    {
        //暂没有扩展字段
    }
}
using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 
    /// </summary>
    public partial class BS_Template
    {
        //暂没有扩展字段
    }
}

亚洲必赢官网 19

亚洲必赢官网 20

View Code

View Code

除VS二零一一自带的DLL外部须要要的DLL:

除VS2011自带的DLL外部供给要的DLL:

 

 

MySql.Data.dll

MySql.Data.dll

DBHelper代码:

DBHelper代码:

System.Data.SQLite.dll

System.Data.SQLite.dll

亚洲必赢官网 21亚洲必赢官网 22

亚洲必赢官网 23亚洲必赢官网 24

中间Models类库如下:

里头Models类库如下:

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 Models;
using MySql.Data.MySqlClient;

namespace DBUtil
{
    /// <summary>
    /// 数据库操作类
    /// 2016年09月09日
    /// </summary>
    public 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 构造函数
        /// <summary>
        /// 数据库操作类
        /// </summary>
        public DBHelper()
        {
        }
        #endregion

        #region 生成变量
        #region 生成 IDbCommand
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private 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 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 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 DbParameter GetDbParameter(string name, object value)
        {
            DbParameter dbParameter = null;

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

            return dbParameter;
        }
        #endregion
        #endregion

        #region 基础方法
        #region  执行简单SQL语句
        #region Exists
        public bool Exists(string 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 int ExecuteSql(string 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 object GetSingle(string 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 DbDataReader ExecuteReader(string 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 DataSet Query(string 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
        #endregion

        #region 执行带参数的SQL语句
        #region 执行SQL语句,返回影响的记录数
        /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public 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 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 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 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 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 void Insert(object obj)
        {
            Insert(obj, m_AutoIncrement);
        }
        /// <summary>
        /// 添加
        /// </summary>
        public 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 void Update(object obj)
        {
            object oldObj = Find(obj, false);
            if (oldObj == null) throw new Exception("无法获取到旧数据");

            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("update {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(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        propertyNameList.Add(propertyInfo.Name);
                        savedCount++;
                    }
                }
            }

            strSql.Append(string.Format(" set "));
            DbParameter[] parameters = new DbParameter[savedCount];
            StringBuilder sbPros = new StringBuilder();
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));
                        DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                        parameters[k++] = param;
                    }
                }
            }
            if (sbPros.Length > 0)
            {
                strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
            }
            strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));

            if (savedCount > 0)
            {
                ExecuteSql(strSql.ToString(), parameters);
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据Id删除
        /// </summary>
        public void Delete<T>(int id)
        {
            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {2}='{1}'", type.Name, id, GetIdName(type)));

            ExecuteSql(sbSql.ToString());
        }
        /// <summary>
        /// 根据Id集合删除
        /// </summary>
        public void BatchDelete<T>(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {2} in ({1})", type.Name, ids, GetIdName(type)));

            ExecuteSql(sbSql.ToString());
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public void Delete<T>(string conditions)
        {
            if (string.IsNullOrWhiteSpace(conditions)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));

            ExecuteSql(sbSql.ToString());
        }
        #endregion

        #region 获取实体
        #region 根据实体获取实体
        /// <summary>
        /// 根据实体获取实体
        /// </summary>
        private object Find(object obj, bool readCache = true)
        {
            Type type = obj.GetType();

            object result = Activator.CreateInstance(type);
            bool hasValue = false;
            IDataReader rd = null;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        private object FindById(Type type, int id)
        {
            object result = Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        public T FindById<T>(string id) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region 根据sql获取实体
        /// <summary>
        /// 根据sql获取实体
        /// </summary>
        public T FindBySql<T>(string sql) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<T> FindListBySql<T>(string sql) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql, cmdParms);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString());
                pagerModel.result = list;
            }

            return pagerModel;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
                pagerModel.result = list;
            }

            return pagerModel;
        }


        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms)
        {
            DataSet ds = null;

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                totalCount = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                ds = Query(sql, cmdParms);
            }
            return ds;
        }
        #endregion

        #region getReaderValue 转换数据
        /// <summary>
        /// 转换数据
        /// </summary>
        private Object getReaderValue(Object rdValue, Type ptype)
        {
            if (ptype == typeof(double))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(decimal))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(int))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(long))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(DateTime))
                return Convert.ToDateTime(rdValue);

            if (ptype == typeof(Nullable<double>))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(Nullable<decimal>))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(Nullable<int>))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(Nullable<long>))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(Nullable<DateTime>))
                return Convert.ToDateTime(rdValue);

            return rdValue;
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public string GetIdName(Type type)
        {
            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
                {
                    return propertyInfo.Name;
                }
            }
            return "Id";
        }
        #endregion

        #region 获取主键值
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public object GetIdVal(object val)
        {
            string idName = GetIdName(val.GetType());
            if (!string.IsNullOrWhiteSpace(idName))
            {
                return val.GetType().GetProperty(idName).GetValue(val, null);
            }
            return 0;
        }
        #endregion

        #region 获取实体类属性
        /// <summary>
        /// 获取实体类属性
        /// </summary>
        private PropertyInfo[] GetEntityProperties(Type type)
        {
            List<PropertyInfo> result = new List<PropertyInfo>();
            PropertyInfo[] propertyInfoList = type.GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
                    && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
                {
                    result.Add(propertyInfo);
                }
            }
            return result.ToArray();
        }
        #endregion

        #region 获取基类
        /// <summary>
        /// 获取基类
        /// </summary>
        public Type GetBaseType(Type type)
        {
            while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
            {
                type = type.BaseType;
            }
            return type;
        }
        #endregion
        #endregion

        #region 事务
        #region 开始事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public static void BeginTransaction()
        {
            DbConnection conn = GetConnection();
            if (conn.State != ConnectionState.Open) conn.Open();
            m_Tran = conn.BeginTransaction();
        }
        #endregion

        #region 提交事务
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void CommitTransaction()
        {
            DbConnection conn = m_Tran.Connection;
            try
            {
                m_Tran.Commit();
            }
            catch (Exception ex)
            {
                m_Tran.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                m_Tran.Dispose();
                m_Tran = null;
            }
        }
        #endregion

        #region 回滚事务(出错时调用该方法回滚)
        /// <summary>
        /// 回滚事务(出错时调用该方法回滚)
        /// </summary>
        public static void RollbackTransaction()
        {
            DbConnection conn = m_Tran.Connection;
            m_Tran.Rollback();
            if (conn.State == ConnectionState.Open) conn.Close();
        }
        #endregion
        #endregion
    }
}
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 Models;
using MySql.Data.MySqlClient;

namespace DBUtil
{
    /// <summary>
    /// 数据库操作类
    /// 2016年09月09日
    /// </summary>
    public 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 构造函数
        /// <summary>
        /// 数据库操作类
        /// </summary>
        public DBHelper()
        {
        }
        #endregion

        #region 生成变量
        #region 生成 IDbCommand
        /// <summary>
        /// 生成 IDbCommand
        /// </summary>
        private 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 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 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 DbParameter GetDbParameter(string name, object value)
        {
            DbParameter dbParameter = null;

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

            return dbParameter;
        }
        #endregion
        #endregion

        #region 基础方法
        #region  执行简单SQL语句
        #region Exists
        public bool Exists(string 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 int ExecuteSql(string 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 object GetSingle(string 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 DbDataReader ExecuteReader(string 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 DataSet Query(string 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
        #endregion

        #region 执行带参数的SQL语句
        #region 执行SQL语句,返回影响的记录数
        /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public 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 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 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 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 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 void Insert(object obj)
        {
            Insert(obj, m_AutoIncrement);
        }
        /// <summary>
        /// 添加
        /// </summary>
        public 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 void Update(object obj)
        {
            object oldObj = Find(obj, false);
            if (oldObj == null) throw new Exception("无法获取到旧数据");

            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            strSql.Append(string.Format("update {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(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        propertyNameList.Add(propertyInfo.Name);
                        savedCount++;
                    }
                }
            }

            strSql.Append(string.Format(" set "));
            DbParameter[] parameters = new DbParameter[savedCount];
            StringBuilder sbPros = new StringBuilder();
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
                {
                    object oldVal = propertyInfo.GetValue(oldObj, null);
                    object val = propertyInfo.GetValue(obj, null);
                    if (!object.Equals(oldVal, val))
                    {
                        sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));
                        DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
                        parameters[k++] = param;
                    }
                }
            }
            if (sbPros.Length > 0)
            {
                strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
            }
            strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));

            if (savedCount > 0)
            {
                ExecuteSql(strSql.ToString(), parameters);
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据Id删除
        /// </summary>
        public void Delete<T>(int id)
        {
            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {2}='{1}'", type.Name, id, GetIdName(type)));

            ExecuteSql(sbSql.ToString());
        }
        /// <summary>
        /// 根据Id集合删除
        /// </summary>
        public void BatchDelete<T>(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {2} in ({1})", type.Name, ids, GetIdName(type)));

            ExecuteSql(sbSql.ToString());
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public void Delete<T>(string conditions)
        {
            if (string.IsNullOrWhiteSpace(conditions)) return;

            Type type = typeof(T);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));

            ExecuteSql(sbSql.ToString());
        }
        #endregion

        #region 获取实体
        #region 根据实体获取实体
        /// <summary>
        /// 根据实体获取实体
        /// </summary>
        private object Find(object obj, bool readCache = true)
        {
            Type type = obj.GetType();

            object result = Activator.CreateInstance(type);
            bool hasValue = false;
            IDataReader rd = null;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        private object FindById(Type type, int id)
        {
            object result = Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        public T FindById<T>(string id) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region 根据sql获取实体
        /// <summary>
        /// 根据sql获取实体
        /// </summary>
        public T FindBySql<T>(string sql) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<T> FindListBySql<T>(string sql) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            try
            {
                rd = ExecuteReader(sql, cmdParms);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            return list;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString());
                pagerModel.result = list;
            }

            return pagerModel;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
                pagerModel.result = list;
            }

            return pagerModel;
        }


        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity,尽量少的字段)
        /// </summary>
        public DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms)
        {
            DataSet ds = null;

            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                IDbCommand cmd = null;
                StringBuilder sb = new StringBuilder();
                string commandText = null;
                int startRow = 0;
                int endRow = 0;
                totalCount = 0;
                switch (m_DBType)
                {
                    case "oracle":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);
                        endRow = startRow + pageSize;

                        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.Append(" ) row_limit where rownum <= ");
                        sb.Append(endRow);
                        sb.Append(" ) where rownum_ >");
                        sb.Append(startRow);
                        #endregion
                        break;
                    case "mssql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1) + 1;
                        endRow = startRow + pageSize - 1;

                        sb.Append(string.Format(@"
                            select * from 
                            (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
                            where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
                        #endregion
                        break;
                    case "mysql":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append("select * from (");
                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
                        #endregion
                        break;
                    case "sqlite":
                        #region 分页查询语句
                        commandText = string.Format("select count(*) from ({0}) T", sql);
                        cmd = GetCommand(commandText, connection);
                        totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                        startRow = pageSize * (currentPage - 1);

                        sb.Append(sql);
                        if (!string.IsNullOrWhiteSpace(orderby))
                        {
                            sb.Append(" ");
                            sb.Append(orderby);
                        }
                        sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
                        #endregion
                        break;
                }

                ds = Query(sql, cmdParms);
            }
            return ds;
        }
        #endregion

        #region getReaderValue 转换数据
        /// <summary>
        /// 转换数据
        /// </summary>
        private Object getReaderValue(Object rdValue, Type ptype)
        {
            if (ptype == typeof(double))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(decimal))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(int))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(long))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(DateTime))
                return Convert.ToDateTime(rdValue);

            if (ptype == typeof(Nullable<double>))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(Nullable<decimal>))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(Nullable<int>))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(Nullable<long>))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(Nullable<DateTime>))
                return Convert.ToDateTime(rdValue);

            return rdValue;
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public string GetIdName(Type type)
        {
            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
                {
                    return propertyInfo.Name;
                }
            }
            return "Id";
        }
        #endregion

        #region 获取主键值
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public object GetIdVal(object val)
        {
            string idName = GetIdName(val.GetType());
            if (!string.IsNullOrWhiteSpace(idName))
            {
                return val.GetType().GetProperty(idName).GetValue(val, null);
            }
            return 0;
        }
        #endregion

        #region 获取实体类属性
        /// <summary>
        /// 获取实体类属性
        /// </summary>
        private PropertyInfo[] GetEntityProperties(Type type)
        {
            List<PropertyInfo> result = new List<PropertyInfo>();
            PropertyInfo[] propertyInfoList = type.GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
                    && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
                {
                    result.Add(propertyInfo);
                }
            }
            return result.ToArray();
        }
        #endregion

        #region 获取基类
        /// <summary>
        /// 获取基类
        /// </summary>
        public Type GetBaseType(Type type)
        {
            while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
            {
                type = type.BaseType;
            }
            return type;
        }
        #endregion
        #endregion

        #region 事务
        #region 开始事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public static void BeginTransaction()
        {
            DbConnection conn = GetConnection();
            if (conn.State != ConnectionState.Open) conn.Open();
            m_Tran = conn.BeginTransaction();
        }
        #endregion

        #region 提交事务
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void CommitTransaction()
        {
            DbConnection conn = m_Tran.Connection;
            try
            {
                m_Tran.Commit();
            }
            catch (Exception ex)
            {
                m_Tran.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                m_Tran.Dispose();
                m_Tran = null;
            }
        }
        #endregion

        #region 回滚事务(出错时调用该方法回滚)
        /// <summary>
        /// 回滚事务(出错时调用该方法回滚)
        /// </summary>
        public static void RollbackTransaction()
        {
            DbConnection conn = m_Tran.Connection;
            m_Tran.Rollback();
            if (conn.State == ConnectionState.Open) conn.Close();
        }
        #endregion
        #endregion
    }
}

亚洲必赢官网 25

亚洲必赢官网 26

View Code

View Code

表明:Models目录中的类及其性质和数据库中的表和字段是全然对应的,Models全部由生成器生成,并且不容许手动修改。ExtModels目录中的类是扩充类,主要用以查询与显示,比如表中存的是code,但你需求关联合检查询另一张表中的name,就足以在那些增加类中增加学一年级个用以显示的name字段。Models和ExtModels目录的中类都以partial修饰。

注明:Models目录中的类及其性质和数据库中的表和字段是一心对应的,Models全体由生成器生成,并且不一致意手动修改。ExtModels目录中的类是扩充类,重要用来查询与体现,比如表中存的是code,但您供给关联合检查询另一张表中的name,就足以在这些扩张类中扩大一个用于体现的name字段。Models和ExtModels目录的中类都以partial修饰。

 

 

PagerModel类:

PagerModel类:

哪些接纳:

怎么着利用:

亚洲必赢官网 27亚洲必赢官网 28

亚洲必赢官网 29亚洲必赢官网 30

    添加:

    添加:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Models
{
    /// <summary>
    /// 分页
    /// </summary>
    public class PagerModel
    {
        #region 字段
        /// <summary>
        /// 当前页数
        /// </summary>
        public int page { get; set; }
        /// <summary>
        /// 每页记录数
        /// </summary>
        public int rows { get; set; }
        /// <summary>
        /// 排序字段
        /// </summary>
        public string sort { get; set; }
        /// <summary>
        /// 排序的方式asc,desc
        /// </summary>
        public string order { get; set; }
        /// <summary>
        /// 记录
        /// </summary>
        public object result { get; set; }
        /// <summary>
        /// 记录数
        /// </summary>
        public int totalRows { get; set; }
        #endregion

        #region 构造函数
        public PagerModel()
        {

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="page">当前页数</param>
        /// <param name="rows">每页记录数</param>
        public PagerModel(int page, int rows)
        {
            this.page = page;
            this.rows = rows;
        }
        #endregion

        #region 扩展字段
        /// <summary>
        /// 总页数
        /// </summary>
        public int pageCount
        {
            get
            {
                return (totalRows - 1) / rows + 1;
            }
        }
        /// <summary>
        /// 上一页
        /// </summary>
        public int prePage
        {
            get
            {
                if (page - 1 > 0)
                {
                    return page - 1;
                }
                return 1;
            }
        }
        /// <summary>
        /// 下一页
        /// </summary>
        public int nextPage
        {
            get
            {
                if (page + 1 < pageCount)
                {
                    return page + 1;
                }
                return pageCount;
            }
        }
        #endregion

    }
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Models
{
    /// <summary>
    /// 分页
    /// </summary>
    public class PagerModel
    {
        #region 字段
        /// <summary>
        /// 当前页数
        /// </summary>
        public int page { get; set; }
        /// <summary>
        /// 每页记录数
        /// </summary>
        public int rows { get; set; }
        /// <summary>
        /// 排序字段
        /// </summary>
        public string sort { get; set; }
        /// <summary>
        /// 排序的方式asc,desc
        /// </summary>
        public string order { get; set; }
        /// <summary>
        /// 记录
        /// </summary>
        public object result { get; set; }
        /// <summary>
        /// 记录数
        /// </summary>
        public int totalRows { get; set; }
        #endregion

        #region 构造函数
        public PagerModel()
        {

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="page">当前页数</param>
        /// <param name="rows">每页记录数</param>
        public PagerModel(int page, int rows)
        {
            this.page = page;
            this.rows = rows;
        }
        #endregion

        #region 扩展字段
        /// <summary>
        /// 总页数
        /// </summary>
        public int pageCount
        {
            get
            {
                return (totalRows - 1) / rows + 1;
            }
        }
        /// <summary>
        /// 上一页
        /// </summary>
        public int prePage
        {
            get
            {
                if (page - 1 > 0)
                {
                    return page - 1;
                }
                return 1;
            }
        }
        /// <summary>
        /// 下一页
        /// </summary>
        public int nextPage
        {
            get
            {
                if (page + 1 < pageCount)
                {
                    return page + 1;
                }
                return pageCount;
            }
        }
        #endregion

    }
}

亚洲必赢官网 31亚洲必赢官网 32

亚洲必赢官网 33亚洲必赢官网 34

View Code

View Code

/// <summary>
/// 添加
/// </summary>
public void Insert(object obj)
{
    dbHelper.Insert(obj);
}
/// <summary>
/// 添加
/// </summary>
public void Insert(object obj)
{
    dbHelper.Insert(obj);
}

IsIdAttribute类:

IsIdAttribute类:

View Code

View Code

亚洲必赢官网 35亚洲必赢官网 36

亚洲必赢官网 37亚洲必赢官网 38

    修改:

    修改:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是主健
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsIdAttribute : Attribute
    {
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是主健
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsIdAttribute : Attribute
    {
    }
}

亚洲必赢官网 39亚洲必赢官网 40

亚洲必赢官网 41亚洲必赢官网 42

View Code

View Code

/// <summary>
/// 修改
/// </summary>
public void Update(object obj)
{
    dbHelper.Update(obj);
}
/// <summary>
/// 修改
/// </summary>
public void Update(object obj)
{
    dbHelper.Update(obj);
}

IsDBFieldAttribute类:

IsDBFieldAttribute类:

View Code

View Code

亚洲必赢官网 43亚洲必赢官网 44

亚洲必赢官网 45亚洲必赢官网 46

    根据ID删除:

    根据ID删除:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是数据库字段
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsDBFieldAttribute : Attribute
    {
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Models
{
    /// <summary>
    /// 标识该属性是数据库字段
    /// </summary>
    [Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
    public class IsDBFieldAttribute : Attribute
    {
    }
}

亚洲必赢官网 47亚洲必赢官网 48

亚洲必赢官网 49亚洲必赢官网 50

View Code

View Code

/// <summary>
/// 删除
/// </summary>
public void Del(int id)
{
    dbHelper.Delete<BS_Template>(id);
}
/// <summary>
/// 删除
/// </summary>
public void Del(int id)
{
    dbHelper.Delete<BS_Template>(id);
}

Models示例:

Models示例:

View Code

View Code

亚洲必赢官网 51亚洲必赢官网 52

亚洲必赢官网 53亚洲必赢官网 54

    依据ID批量删除:

    依照ID批量删除:

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    [Serializable]
    public partial class cms_content
    {
        /// <summary>
        /// 编号
        /// </summary>
        [IsId]
        [IsDBField]
        public int id { get; set; }
        /// <summary>
        /// 所属栏目ID
        /// </summary>
        [IsDBField]
        public int? channelId { get; set; }
        /// <summary>
        /// 标题
        /// </summary>
        [IsDBField]
        public string title { get; set; }
        /// <summary>
        /// 内容
        /// </summary>
        [IsDBField]
        public string contents { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        [IsDBField]
        public string author { get; set; }
        /// <summary>
        /// 阅读次数
        /// </summary>
        [IsDBField]
        public int? readCount { get; set; }
        /// <summary>
        /// 发布时间
        /// </summary>
        [IsDBField]
        public DateTime? publishTime { get; set; }
        /// <summary>
        /// 发布者
        /// </summary>
        [IsDBField]
        public int? publishUserId { get; set; }
        /// <summary>
        /// 审核(0待审1通过2不通过)
        /// </summary>
        [IsDBField]
        public int? audit { get; set; }
        /// <summary>
        /// 审核人
        /// </summary>
        [IsDBField]
        public int? auditUserId { get; set; }
        /// <summary>
        /// 审核时间
        /// </summary>
        [IsDBField]
        public DateTime? auditTime { get; set; }
        /// <summary>
        /// 页面关键词
        /// </summary>
        [IsDBField]
        public string keywords { get; set; }
        /// <summary>
        /// 页面描述
        /// </summary>
        [IsDBField]
        public string description { get; set; }
        /// <summary>
        /// 页面链接
        /// </summary>
        [IsDBField]
        public string pageUrl { get; set; }
        /// <summary>
        /// 内容封面
        /// </summary>
        [IsDBField]
        public string imgUrl { get; set; }
        /// <summary>
        /// 是否链接(0否1是)
        /// </summary>
        [IsDBField]
        public int? isPageUrl { get; set; }
        /// <summary>
        /// 模板(模板文件名,例:content.html)
        /// </summary>
        [IsDBField]
        public string template { get; set; }
        /// <summary>
        /// 推荐(1推荐0不推荐)
        /// </summary>
        [IsDBField]
        public int? recommend { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    [Serializable]
    public partial class cms_content
    {
        /// <summary>
        /// 编号
        /// </summary>
        [IsId]
        [IsDBField]
        public int id { get; set; }
        /// <summary>
        /// 所属栏目ID
        /// </summary>
        [IsDBField]
        public int? channelId { get; set; }
        /// <summary>
        /// 标题
        /// </summary>
        [IsDBField]
        public string title { get; set; }
        /// <summary>
        /// 内容
        /// </summary>
        [IsDBField]
        public string contents { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        [IsDBField]
        public string author { get; set; }
        /// <summary>
        /// 阅读次数
        /// </summary>
        [IsDBField]
        public int? readCount { get; set; }
        /// <summary>
        /// 发布时间
        /// </summary>
        [IsDBField]
        public DateTime? publishTime { get; set; }
        /// <summary>
        /// 发布者
        /// </summary>
        [IsDBField]
        public int? publishUserId { get; set; }
        /// <summary>
        /// 审核(0待审1通过2不通过)
        /// </summary>
        [IsDBField]
        public int? audit { get; set; }
        /// <summary>
        /// 审核人
        /// </summary>
        [IsDBField]
        public int? auditUserId { get; set; }
        /// <summary>
        /// 审核时间
        /// </summary>
        [IsDBField]
        public DateTime? auditTime { get; set; }
        /// <summary>
        /// 页面关键词
        /// </summary>
        [IsDBField]
        public string keywords { get; set; }
        /// <summary>
        /// 页面描述
        /// </summary>
        [IsDBField]
        public string description { get; set; }
        /// <summary>
        /// 页面链接
        /// </summary>
        [IsDBField]
        public string pageUrl { get; set; }
        /// <summary>
        /// 内容封面
        /// </summary>
        [IsDBField]
        public string imgUrl { get; set; }
        /// <summary>
        /// 是否链接(0否1是)
        /// </summary>
        [IsDBField]
        public int? isPageUrl { get; set; }
        /// <summary>
        /// 模板(模板文件名,例:content.html)
        /// </summary>
        [IsDBField]
        public string template { get; set; }
        /// <summary>
        /// 推荐(1推荐0不推荐)
        /// </summary>
        [IsDBField]
        public int? recommend { get; set; }
    }
}

亚洲必赢官网 55亚洲必赢官网 56

亚洲必赢官网 57亚洲必赢官网 58

View Code

View Code

/// <summary>
/// 删除
/// </summary>
public void BatchDelete(string ids)
{
    dbHelper.BatchDelete<BS_Template>(ids);
}
/// <summary>
/// 删除
/// </summary>
public void BatchDelete(string ids)
{
    dbHelper.BatchDelete<BS_Template>(ids);
}

ExtModels示例:

ExtModels示例:

View Code

View Code

亚洲必赢官网 59亚洲必赢官网 60

亚洲必赢官网 61亚洲必赢官网 62

    遵照标准删除:

    依据条件删除:

using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    public partial class cms_content
    {
        /// <summary>
        /// 栏目名称
        /// </summary>
        public string channelName { get; set; }
        /// <summary>
        /// 用户显示名
        /// </summary>
        public string showName { get; set; }
        /// <summary>
        /// 审核状态
        /// </summary>
        public string dispAudit
        {
            get
            {
                switch (this.audit ?? 0)
                {
                    case 0:
                        return "待审核";
                    case 1:
                        return "审核通过";
                    case 2:
                        return "审核不通过";
                }
                return "error";
            }
            set { }
        }
        /// <summary>
        /// 在当前页中的索引
        /// </summary>
        public int curPageSort { get; set; }
        /// <summary>
        /// 是否为空,模板使用,0不为空1为空
        /// </summary>
        public int isNull { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.Linq;

namespace Models
{
    /// <summary>
    /// 内容详情
    /// </summary>
    public partial class cms_content
    {
        /// <summary>
        /// 栏目名称
        /// </summary>
        public string channelName { get; set; }
        /// <summary>
        /// 用户显示名
        /// </summary>
        public string showName { get; set; }
        /// <summary>
        /// 审核状态
        /// </summary>
        public string dispAudit
        {
            get
            {
                switch (this.audit ?? 0)
                {
                    case 0:
                        return "待审核";
                    case 1:
                        return "审核通过";
                    case 2:
                        return "审核不通过";
                }
                return "error";
            }
            set { }
        }
        /// <summary>
        /// 在当前页中的索引
        /// </summary>
        public int curPageSort { get; set; }
        /// <summary>
        /// 是否为空,模板使用,0不为空1为空
        /// </summary>
        public int isNull { get; set; }
    }
}

亚洲必赢官网 63亚洲必赢官网 64

亚洲必赢官网 65亚洲必赢官网 66

View Code

View Code

/// <summary>
/// 删除
/// </summary>
public void Delete(string conditions)
{
    dbHelper.Delete<BS_Template>(conditions);
}
/// <summary>
/// 删除
/// </summary>
public void Delete(string conditions)
{
    dbHelper.Delete<BS_Template>(conditions);
}

怎么使用:

怎样运用:

View Code

View Code

证实:扶助参数化的增加和删除改查,推荐使用参数化的增加和删除改查;非参数化的增加和删除改查过滤了1部分数据库重点字以幸免SQL注入,但大概照旧不安全。下边例子中的增多、修改、依照ID删除、依据ID集合批量剔除都以参数化的,示例中的查询和其余措施的去除不是参数化的,DBHelper提供了连带的参数化查询和进行SQL。

表达:帮忙参数化的增加和删除改查,推荐应用参数化的增加和删除改查;非参数化的增加和删除改查过滤了有个别数据库重点字以免备SQL注入,但或许照样不安全。下边例子中的增多、修改、依据ID删除、根据ID集合批量剔除都以参数化的,示例中的查询和任何措施的删减不是参数化的,DBHelper提供了连带的参数化查询和施行SQL。

   
获取最大ID(当然,ID一般选用自增,对于并发量极少的系统,或单机系统,为了便利,能够那样做):

   
获取最大ID(当然,ID一般采用自增,对于并发量极少的系统,或单机系统,为了便利,能够那样做):

添加:

添加:

亚洲必赢官网 67亚洲必赢官网 68

亚洲必赢官网 69亚洲必赢官网 70

亚洲必赢官网 71亚洲必赢官网 72

亚洲必赢官网 73亚洲必赢官网 74

/// <summary>
/// GetMaxId
/// </summary>
public int GetMaxId()
{
    return dbHelper.GetMaxID<BS_Template>("id");
}
/// <summary>
/// GetMaxId
/// </summary>
public int GetMaxId()
{
    return dbHelper.GetMaxID<BS_Template>("id");
}
/// <summary>
/// 添加
/// </summary>
public void Insert(object obj)
{
    DBHelper.Insert(obj);
}
/// <summary>
/// 添加
/// </summary>
public void Insert(object obj)
{
    DBHelper.Insert(obj);
}

View Code

View Code

View Code

View Code

    依照标准查询实体:

    依照条件查询实体:

证实:SQL
Server数据库能够采纳自增,Oracle数据库能够选取Sequence,小系统可以使用DBHelper自带的Get马克斯ID方法。

注解:SQL
Server数据库能够采取自增,Oracle数据库可以运用Sequence,小系统能够行使DBHelper自带的Get马克斯ID方法。

亚洲必赢官网 75亚洲必赢官网 76

亚洲必赢官网 77亚洲必赢官网 78

获得最大ID(当然,ID一般选择自增,对于并发量极少的种类,或单机系统,为了方便,能够这么做):

获得最大ID(当然,ID一般选拔自增,对于并发量极少的种类,或单机系统,为了便利,能够这么做):

public BS_Template Get(string typeCode, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template 
        where typeCode='{0}' 
        and type='{1}'", typeCode, (int)templateType));
    return dbHelper.FindBySql<BS_Template>(sql.ToString());
}
public BS_Template Get2(string templateId, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template 
        where id='{0}' 
        and type='{1}'", templateId, (int)templateType));
    return dbHelper.FindBySql<BS_Template>(sql.ToString());
}
public BS_Template Get(string typeCode, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template 
        where typeCode='{0}' 
        and type='{1}'", typeCode, (int)templateType));
    return dbHelper.FindBySql<BS_Template>(sql.ToString());
}
public BS_Template Get2(string templateId, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template 
        where id='{0}' 
        and type='{1}'", templateId, (int)templateType));
    return dbHelper.FindBySql<BS_Template>(sql.ToString());
}

亚洲必赢官网 79亚洲必赢官网 80

亚洲必赢官网 81亚洲必赢官网 82

View Code

View Code

/// <summary>
/// GetMaxId
/// </summary>
public int GetMaxId()
{
    return DBHelper.GetMaxID<BS_Template>("id");
}
/// <summary>
/// GetMaxId
/// </summary>
public int GetMaxId()
{
    return DBHelper.GetMaxID<BS_Template>("id");
}

    依照ID查询实体:

    依据ID查询实体:

View Code

View Code

亚洲必赢官网 83亚洲必赢官网 84

亚洲必赢官网 85亚洲必赢官网 86

修改:

修改:

public BS_Test Get(string id)
{
    return dbHelper.FindById<BS_Test>(id);
}
public BS_Test Get(string id)
{
    return dbHelper.FindById<BS_Test>(id);
}

亚洲必赢官网 87亚洲必赢官网 88

亚洲必赢官网 89亚洲必赢官网 90

View Code

View Code

/// <summary>
/// 修改
/// </summary>
public void Update(object obj)
{
    DBHelper.Update(obj);
}
/// <summary>
/// 修改
/// </summary>
public void Update(object obj)
{
    DBHelper.Update(obj);
}

    查询列表:

    查询列表:

View Code

View Code

亚洲必赢官网 91亚洲必赢官网 92

亚洲必赢官网 93亚洲必赢官网 94

删除:

删除:

/// <summary>
/// 查询列表
/// </summary>
public List<BS_Test> GetList(string name)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Test t
        where 1=1 "));

    if (!string.IsNullOrWhiteSpace(name))
    {
        sql.AppendFormat(" and t.name like '%{0}%'", name);
    }

    return dbHelper.FindListBySql<BS_Test>(sql.ToString());
}
/// <summary>
/// 查询列表
/// </summary>
public List<BS_Test> GetList(string name)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Test t
        where 1=1 "));

    if (!string.IsNullOrWhiteSpace(name))
    {
        sql.AppendFormat(" and t.name like '%{0}%'", name);
    }

    return dbHelper.FindListBySql<BS_Test>(sql.ToString());
}

根据ID删除:

根据ID删除:

View Code

View Code

亚洲必赢官网 95亚洲必赢官网 96

亚洲必赢官网 97亚洲必赢官网 98

    分页查询列表:

    分页查询列表:

/// <summary>
/// 删除
/// </summary>
public void Del(int id)
{
    DBHelper.Delete<BS_Template>(id);
}
/// <summary>
/// 删除
/// </summary>
public void Del(int id)
{
    DBHelper.Delete<BS_Template>(id);
}

亚洲必赢官网 99亚洲必赢官网 100

亚洲必赢官网 101亚洲必赢官网 102

View Code

View Code

/// <summary>
/// 分页获取模板集合
/// </summary>
public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template t
        where 1=1 "));

    if (!string.IsNullOrWhiteSpace(noticeType))
    {
        sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);
    }

    if (!string.IsNullOrWhiteSpace(coreType))
    {
        sql.AppendFormat(" and ct.name like '%{0}%'", coreType);
    }

    if (!string.IsNullOrWhiteSpace(name))
    {
        sql.AppendFormat(" and t.name like '%{0}%'", name);
    }

    sql.AppendFormat(" and t.type = '{0}'", (int)templateType);

    string orderby = "order by cast(id as int)";
    pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<BS_Template>;
}
/// <summary>
/// 分页获取模板集合
/// </summary>
public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template t
        where 1=1 "));

    if (!string.IsNullOrWhiteSpace(noticeType))
    {
        sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);
    }

    if (!string.IsNullOrWhiteSpace(coreType))
    {
        sql.AppendFormat(" and ct.name like '%{0}%'", coreType);
    }

    if (!string.IsNullOrWhiteSpace(name))
    {
        sql.AppendFormat(" and t.name like '%{0}%'", name);
    }

    sql.AppendFormat(" and t.type = '{0}'", (int)templateType);

    string orderby = "order by cast(id as int)";
    pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<BS_Template>;
}

依据ID集合批量去除:

基于ID集合批量删减:

View Code

View Code

亚洲必赢官网 103亚洲必赢官网 104

亚洲必赢官网 105亚洲必赢官网 106

    完整DAL:

    完整DAL:

/// <summary>
/// 删除
/// </summary>
public void BatchDelete(string ids)
{
    DBHelper.BatchDelete<BS_Template>(ids);
}
/// <summary>
/// 删除
/// </summary>
public void BatchDelete(string ids)
{
    DBHelper.BatchDelete<BS_Template>(ids);
}

亚洲必赢官网 107亚洲必赢官网 108

亚洲必赢官网 109亚洲必赢官网 110

View Code

View Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtil;
using Models;

namespace DAL
{
    /// <summary>
    /// 模板
    /// </summary>
    public class TemplateDal
    {
        #region 变量
        private DBHelper dbHelper = new DBHelper();
        #endregion

        #region 分页获取模板集合
        /// <summary>
        /// 分页获取模板集合
        /// </summary>
        public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template t
                where 1=1 "));

            if (!string.IsNullOrWhiteSpace(noticeType))
            {
                sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);
            }

            if (!string.IsNullOrWhiteSpace(coreType))
            {
                sql.AppendFormat(" and ct.name like '%{0}%'", coreType);
            }

            if (!string.IsNullOrWhiteSpace(name))
            {
                sql.AppendFormat(" and t.name like '%{0}%'", name);
            }

            sql.AppendFormat(" and t.type = '{0}'", (int)templateType);

            string orderby = "order by cast(id as int)";
            pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);
            return pager.result as List<BS_Template>;
        }
        #endregion

        #region 获取字段关联模板集合
        /// <summary>
        /// 获取字段关联模板集合
        /// </summary>
        public List<BS_Template> GetList(string fieldId)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template t
                left join BS_TplFieldRelation r on r.templateId=t.id
                left join BS_TplField f on f.id=r.fieldId  
                where f.id='{0}'", fieldId));
            return dbHelper.FindListBySql<BS_Template>(sql.ToString());
        }
        #endregion

        #region 获取
        public BS_Template Get(string typeCode, Enums.TemplateType templateType)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template 
                where typeCode='{0}' 
                and type='{1}'", typeCode, (int)templateType));
            return dbHelper.FindBySql<BS_Template>(sql.ToString());
        }
        public BS_Template Get2(string templateId, Enums.TemplateType templateType)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template 
                where id='{0}' 
                and type='{1}'", templateId, (int)templateType));
            return dbHelper.FindBySql<BS_Template>(sql.ToString());
        }
        #endregion

        #region GetMaxId
        /// <summary>
        /// GetMaxId
        /// </summary>
        public int GetMaxId()
        {
            return dbHelper.GetMaxID<BS_Template>("id");
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public void Insert(object obj)
        {
            dbHelper.Insert(obj);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public void Update(object obj)
        {
            dbHelper.Update(obj);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        public void Del(int id)
        {
            dbHelper.Delete<BS_Template>(id);
        }
        /// <summary>
        /// 删除
        /// </summary>
        public void BatchDelete(string ids)
        {
            dbHelper.BatchDelete<BS_Template>(ids);
        }
        /// <summary>
        /// 删除
        /// </summary>
        public void Delete(string conditions)
        {
            dbHelper.Delete<BS_Template>(conditions);
        }
        #endregion

    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtil;
using Models;

namespace DAL
{
    /// <summary>
    /// 模板
    /// </summary>
    public class TemplateDal
    {
        #region 变量
        private DBHelper dbHelper = new DBHelper();
        #endregion

        #region 分页获取模板集合
        /// <summary>
        /// 分页获取模板集合
        /// </summary>
        public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template t
                where 1=1 "));

            if (!string.IsNullOrWhiteSpace(noticeType))
            {
                sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);
            }

            if (!string.IsNullOrWhiteSpace(coreType))
            {
                sql.AppendFormat(" and ct.name like '%{0}%'", coreType);
            }

            if (!string.IsNullOrWhiteSpace(name))
            {
                sql.AppendFormat(" and t.name like '%{0}%'", name);
            }

            sql.AppendFormat(" and t.type = '{0}'", (int)templateType);

            string orderby = "order by cast(id as int)";
            pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);
            return pager.result as List<BS_Template>;
        }
        #endregion

        #region 获取字段关联模板集合
        /// <summary>
        /// 获取字段关联模板集合
        /// </summary>
        public List<BS_Template> GetList(string fieldId)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template t
                left join BS_TplFieldRelation r on r.templateId=t.id
                left join BS_TplField f on f.id=r.fieldId  
                where f.id='{0}'", fieldId));
            return dbHelper.FindListBySql<BS_Template>(sql.ToString());
        }
        #endregion

        #region 获取
        public BS_Template Get(string typeCode, Enums.TemplateType templateType)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template 
                where typeCode='{0}' 
                and type='{1}'", typeCode, (int)templateType));
            return dbHelper.FindBySql<BS_Template>(sql.ToString());
        }
        public BS_Template Get2(string templateId, Enums.TemplateType templateType)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select *
                from BS_Template 
                where id='{0}' 
                and type='{1}'", templateId, (int)templateType));
            return dbHelper.FindBySql<BS_Template>(sql.ToString());
        }
        #endregion

        #region GetMaxId
        /// <summary>
        /// GetMaxId
        /// </summary>
        public int GetMaxId()
        {
            return dbHelper.GetMaxID<BS_Template>("id");
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public void Insert(object obj)
        {
            dbHelper.Insert(obj);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public void Update(object obj)
        {
            dbHelper.Update(obj);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        public void Del(int id)
        {
            dbHelper.Delete<BS_Template>(id);
        }
        /// <summary>
        /// 删除
        /// </summary>
        public void BatchDelete(string ids)
        {
            dbHelper.BatchDelete<BS_Template>(ids);
        }
        /// <summary>
        /// 删除
        /// </summary>
        public void Delete(string conditions)
        {
            dbHelper.Delete<BS_Template>(conditions);
        }
        #endregion

    }
}

依据条件删除:

基于规则删除:

View Code

View Code

亚洲必赢官网 111亚洲必赢官网 112

亚洲必赢官网 113亚洲必赢官网 114

    查询sql书写规范:

    查询sql书写规范:

/// <summary>
/// 删除
/// </summary>
public void Delete(string conditions)
{
    DBHelper.Delete<BS_Template>(conditions);
}
/// <summary>
/// 删除
/// </summary>
public void Delete(string conditions)
{
    DBHelper.Delete<BS_Template>(conditions);
}

    SQL不能够写的乱七8糟,该换行换行,该对齐对齐,例:

    SQL无法写的乱七八糟,该换行换行,该对齐对齐,例:

View Code

View Code

亚洲必赢官网 115亚洲必赢官网 116

亚洲必赢官网 117亚洲必赢官网 118

任何格局的删减请使用:

其它措施的去除请使用:

/// <summary>
/// 分页获取模板集合
/// </summary>
public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template t
        where 1=1 "));

    if (!string.IsNullOrWhiteSpace(noticeType))
    {
        sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);
    }

    if (!string.IsNullOrWhiteSpace(coreType))
    {
        sql.AppendFormat(" and ct.name like '%{0}%'", coreType);
    }

    if (!string.IsNullOrWhiteSpace(name))
    {
        sql.AppendFormat(" and t.name like '%{0}%'", name);
    }

    sql.AppendFormat(" and t.type = '{0}'", (int)templateType);

    string orderby = "order by cast(id as int)";
    pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<BS_Template>;
}
/// <summary>
/// 分页获取模板集合
/// </summary>
public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select *
        from BS_Template t
        where 1=1 "));

    if (!string.IsNullOrWhiteSpace(noticeType))
    {
        sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);
    }

    if (!string.IsNullOrWhiteSpace(coreType))
    {
        sql.AppendFormat(" and ct.name like '%{0}%'", coreType);
    }

    if (!string.IsNullOrWhiteSpace(name))
    {
        sql.AppendFormat(" and t.name like '%{0}%'", name);
    }

    sql.AppendFormat(" and t.type = '{0}'", (int)templateType);

    string orderby = "order by cast(id as int)";
    pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<BS_Template>;
}

public static int ExecuteSql(string sqlString)

public static int ExecuteSql(string sqlString)

View Code

View Code

听大人讲ID查询实体:

依据ID查询实体:

    select换行,from换行,join换行,where换行,and换行。

    select换行,from换行,join换行,where换行,and换行。

亚洲必赢官网 119亚洲必赢官网 120

亚洲必赢官网 121亚洲必赢官网 122

    

    

public cms_content Get(int id)
{
    return DBHelper.FindById<cms_content>(id.ToString());
}
public cms_content Get(int id)
{
    return DBHelper.FindById<cms_content>(id.ToString());
}

    数据库事务:

    数据库事务:

View Code

View Code

亚洲必赢官网 123亚洲必赢官网 124

亚洲必赢官网 125亚洲必赢官网 126

查询:

查询:

try
{
    DBHelper.BeginTransaction();

    BS_Template model = new BS_Template();
    model.id = m_TemplateDal.GetMaxId().ToString();
    model.code = k.ToString("0000");
    model.name = "测试" + k.ToString();
    model.remarks = "测试" + k.ToString();
    model.type = ((int)Enums.TemplateType.Notice).ToString();
    m_TemplateDal.Insert(model);
    //throw new Exception("a");

    BS_Test test = new BS_Test();
    test.id = m_TestDal.GetMaxId().ToString();
    test.code = "测试" + k.ToString();
    test.name = "测试" + k.ToString();
    test.remarks = "测试" + k.ToString();
    m_TestDal.Insert(test);

    DBHelper.CommitTransaction();
    MessageBox.Show("成功");
}
catch (Exception ex)
{
    DBHelper.RollbackTransaction();
    MessageBox.Show(ex.Message);
}
try
{
    DBHelper.BeginTransaction();

    BS_Template model = new BS_Template();
    model.id = m_TemplateDal.GetMaxId().ToString();
    model.code = k.ToString("0000");
    model.name = "测试" + k.ToString();
    model.remarks = "测试" + k.ToString();
    model.type = ((int)Enums.TemplateType.Notice).ToString();
    m_TemplateDal.Insert(model);
    //throw new Exception("a");

    BS_Test test = new BS_Test();
    test.id = m_TestDal.GetMaxId().ToString();
    test.code = "测试" + k.ToString();
    test.name = "测试" + k.ToString();
    test.remarks = "测试" + k.ToString();
    m_TestDal.Insert(test);

    DBHelper.CommitTransaction();
    MessageBox.Show("成功");
}
catch (Exception ex)
{
    DBHelper.RollbackTransaction();
    MessageBox.Show(ex.Message);
}

亚洲必赢官网 127亚洲必赢官网 128

亚洲必赢官网 129亚洲必赢官网 130

View Code

View Code

public List<cms_content> GetListAll()
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where content.audit=1 
        order by publishTime desc,id desc"));

    return DBHelper.FindListBySql<cms_content>(sql.ToString());
}
public List<cms_content> GetListAll()
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where content.audit=1 
        order by publishTime desc,id desc"));

    return DBHelper.FindListBySql<cms_content>(sql.ToString());
}

 

 

View Code

View Code

    代码下载:

    代码下载:

表明:ExtModels下的恢弘Model,能够支撑查询数据库中不设有的字段,并映射填充到实体类。

注解:ExtModels下的增添Model,能够支撑查询数据库中不设有的字段,并映射填充到实体类。

   
Model生成器下载:

   
Model生成器下载:

分页查询:

分页查询:

 

 

亚洲必赢官网 131亚洲必赢官网 132

亚洲必赢官网 133亚洲必赢官网 134

public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where 1=1 "));

    if (channelId != -1)
    {
        sql.AppendFormat(" and content.channelId = {0}", channelId);
    }
    if (!string.IsNullOrWhiteSpace(title))
    {
        sql.AppendFormat(" and content.title like '%{0}%'", title);
    }
    if (audit != -1)
    {
        sql.AppendFormat(" and content.audit = {0}", audit);
    }

    string orderby = string.Format("order by content.publishTime desc,id desc");
    pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<cms_content>;
}
public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join CMS_sys_user user on user.id=content.publishUserId
        where 1=1 "));

    if (channelId != -1)
    {
        sql.AppendFormat(" and content.channelId = {0}", channelId);
    }
    if (!string.IsNullOrWhiteSpace(title))
    {
        sql.AppendFormat(" and content.title like '%{0}%'", title);
    }
    if (audit != -1)
    {
        sql.AppendFormat(" and content.audit = {0}", audit);
    }

    string orderby = string.Format("order by content.publishTime desc,id desc");
    pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
    return pager.result as List<cms_content>;
}

View Code

View Code

数据库事务:

数据库事务:

亚洲必赢官网 135亚洲必赢官网 136

亚洲必赢官网 137亚洲必赢官网 138

try
{
    DBHelper.BeginTransaction(); //开启数据库事务

    //在这里写增删改操作

    DBHelper.CommitTransaction(); //提交数据库事务
}catch(Exception ex)
{
    DBHelper.RollbackTransaction(); //回滚数据库事务
}
try
{
    DBHelper.BeginTransaction(); //开启数据库事务

    //在这里写增删改操作

    DBHelper.CommitTransaction(); //提交数据库事务
}catch(Exception ex)
{
    DBHelper.RollbackTransaction(); //回滚数据库事务
}

View Code

View Code

DAL层增加和删除改查示例:

DAL层增加和删除改查示例:

亚洲必赢官网 139亚洲必赢官网 140

亚洲必赢官网 141亚洲必赢官网 142

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DBUtil;
using Models;

namespace DAL
{
    /// <summary>
    /// 内容详情管理
    /// </summary>
    public class ContentDal
    {
        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where 1=1 "));

            if (channelId != -1)
            {
                sql.AppendFormat(" and content.channelId = {0}", channelId);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql.AppendFormat(" and content.title like '%{0}%'", title);
            }
            if (audit != -1)
            {
                sql.AppendFormat(" and content.audit = {0}", audit);
            }

            string orderby = string.Format("order by content.publishTime desc,id desc");
            pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
            return pager.result as List<cms_content>;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pager">分页</param>
        /// <param name="channel">栏目,可以是栏目ID或栏目名称</param>
        /// <param name="where">where语句</param>
        public List<cms_content> GetList(ref PagerModel pager, string channel, string where)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 "));

            if (!string.IsNullOrWhiteSpace(where))
            {
                sql.AppendFormat(" and {0}", where);
            }

            if (!string.IsNullOrWhiteSpace(channel))
            {
                ChannelDal channelDal = new ChannelDal();
                if (Regex.IsMatch(channel, @"^\d+$")) //数字,即栏目ID
                {
                    string channelIds = channelDal.GetChildIds(Convert.ToInt32(channel));
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
                else //非数字,即栏目名称
                {
                    string channelIds = channelDal.GetChildIds(channel);
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
            }

            string orderby = string.Format("order by publishTime desc,id desc");
            if (pager.rows > 0)
            {
                PagerModel pagerModel = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
                pager.totalRows = pagerModel.totalRows;
                pager.result = pagerModel.result;
                List<cms_content> list = pagerModel.result as List<cms_content>;
                int i = 1;
                list.ForEach(a =>
                {
                    a.curPageSort = i++;
                });
                return pagerModel.result as List<cms_content>;
            }
            else
            {
                return DBHelper.FindListBySql<cms_content>(sql.ToString() + orderby);
            }
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetListAll()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 
                order by publishTime desc,id desc"));

            return DBHelper.FindListBySql<cms_content>(sql.ToString());
        }
        #endregion

        #region 获取总数
        /// <summary>
        /// 获取总数
        /// </summary>
        public int GetAllCount()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select count(*)
                from cms_content content
                where content.audit=1"));

            return Convert.ToInt32(DBHelper.GetSingle(sql.ToString()));
        }
        #endregion

        #region 获取
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content Get(int id)
        {
            return DBHelper.FindById<cms_content>(id.ToString());
        }
        /// <summary>
        /// 根据channelId获取一条内容详情
        /// </summary>
        public cms_content GetByChannelId(int channelId)
        {
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where channelId={0} and audit=1", channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetNext(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id<{0} and channelId={1} and audit=1 order by id desc limit 0,1", current.id, current.channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetPre(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id>{0} and channelId={1} and audit=1 order by id asc limit 0,1", current.id, current.channelId));
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public void Insert(cms_content model)
        {
            model.id = DBHelper.GetMaxID<cms_content>("id");
            DBHelper.Insert(model);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public void Update(cms_content model)
        {
            DBHelper.Update(model);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        public void Del(string ids)
        {
            DBHelper.BatchDelete<cms_content>(ids);
        }
        #endregion

    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DBUtil;
using Models;

namespace DAL
{
    /// <summary>
    /// 内容详情管理
    /// </summary>
    public class ContentDal
    {
        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where 1=1 "));

            if (channelId != -1)
            {
                sql.AppendFormat(" and content.channelId = {0}", channelId);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql.AppendFormat(" and content.title like '%{0}%'", title);
            }
            if (audit != -1)
            {
                sql.AppendFormat(" and content.audit = {0}", audit);
            }

            string orderby = string.Format("order by content.publishTime desc,id desc");
            pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
            return pager.result as List<cms_content>;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pager">分页</param>
        /// <param name="channel">栏目,可以是栏目ID或栏目名称</param>
        /// <param name="where">where语句</param>
        public List<cms_content> GetList(ref PagerModel pager, string channel, string where)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 "));

            if (!string.IsNullOrWhiteSpace(where))
            {
                sql.AppendFormat(" and {0}", where);
            }

            if (!string.IsNullOrWhiteSpace(channel))
            {
                ChannelDal channelDal = new ChannelDal();
                if (Regex.IsMatch(channel, @"^\d+$")) //数字,即栏目ID
                {
                    string channelIds = channelDal.GetChildIds(Convert.ToInt32(channel));
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
                else //非数字,即栏目名称
                {
                    string channelIds = channelDal.GetChildIds(channel);
                    sql.AppendFormat(" and channelId in ({0})", channelIds);
                }
            }

            string orderby = string.Format("order by publishTime desc,id desc");
            if (pager.rows > 0)
            {
                PagerModel pagerModel = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
                pager.totalRows = pagerModel.totalRows;
                pager.result = pagerModel.result;
                List<cms_content> list = pagerModel.result as List<cms_content>;
                int i = 1;
                list.ForEach(a =>
                {
                    a.curPageSort = i++;
                });
                return pagerModel.result as List<cms_content>;
            }
            else
            {
                return DBHelper.FindListBySql<cms_content>(sql.ToString() + orderby);
            }
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<cms_content> GetListAll()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select content.*, channel.title as channelName, user.showName
                from cms_content content
                left join cms_channel channel on channel.id=content.channelId
                left join CMS_sys_user user on user.id=content.publishUserId
                where content.audit=1 
                order by publishTime desc,id desc"));

            return DBHelper.FindListBySql<cms_content>(sql.ToString());
        }
        #endregion

        #region 获取总数
        /// <summary>
        /// 获取总数
        /// </summary>
        public int GetAllCount()
        {
            StringBuilder sql = new StringBuilder(string.Format(@"
                select count(*)
                from cms_content content
                where content.audit=1"));

            return Convert.ToInt32(DBHelper.GetSingle(sql.ToString()));
        }
        #endregion

        #region 获取
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content Get(int id)
        {
            return DBHelper.FindById<cms_content>(id.ToString());
        }
        /// <summary>
        /// 根据channelId获取一条内容详情
        /// </summary>
        public cms_content GetByChannelId(int channelId)
        {
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where channelId={0} and audit=1", channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetNext(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id<{0} and channelId={1} and audit=1 order by id desc limit 0,1", current.id, current.channelId));
        }
        /// <summary>
        /// 获取
        /// </summary>
        public cms_content GetPre(int id)
        {
            cms_content current = Get(id);
            return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id>{0} and channelId={1} and audit=1 order by id asc limit 0,1", current.id, current.channelId));
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public void Insert(cms_content model)
        {
            model.id = DBHelper.GetMaxID<cms_content>("id");
            DBHelper.Insert(model);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public void Update(cms_content model)
        {
            DBHelper.Update(model);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        public void Del(string ids)
        {
            DBHelper.BatchDelete<cms_content>(ids);
        }
        #endregion

    }
}

View Code

View Code

事例代码下载:

事例代码下载:

证实:例子程序是Winform,该DBHelper也适用于Web项目。

证实:例子程序是Winform,该DBHelper也适用于Web项目。

配套Model生成器下载:

配套Model生成器下载:

要是你不通晓本身何以要写DBHelper,为何一向不语法糖,为啥查询和分页查询要利用原生SQL,看看上面大家ERP项目中的代码,你会分晓,当然,那个种类规划的真正倒霉:

比方你不清楚自身干什么要写DBHelper,为何一贯不语法糖,为何查询和分页查询要动用原生SQL,看看上面我们ERP项目中的代码,你会清楚,当然,这一个种类规划的实在不佳:

1、

1、

亚洲必赢官网 143亚洲必赢官网 144

亚洲必赢官网 145亚洲必赢官网 146

/// <summary>
/// 统计SQL
/// </summary>
public override string GetReportSql(string formData)
{
    ProductInvoiceModel search = JsonConvert.DeserializeObject<ProductInvoiceModel>(formData);

    StringBuilder sql = new StringBuilder(string.Format(@"
        select * from 
        (select distinct 
        '出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        ma.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        mad.model as '规格型号',
        dic1.dicItem as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOutStorageDet posd
        left join Pro_ProductOutStorage pos on pos.id=posd.parentId

        left join Pro_MatAllotDet mad on posd.matAllotDetId=mad.id
        left join Pro_MatAllot ma on ma.id=mad.parentID and ma.status=2
        left join Pro_MatStock ms on ms.id=mad.matStockId and mad.memberType=1
        left join Pro_ConStock cs on cs.id=mad.matStockId and mad.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mad.unitCode  

        union all

        select distinct 
        'XXX出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        '' as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOut2StorageDet posd
        left join Pro_ProductOut2Storage pos on pos.id=posd.parentId

        left join Pro_MatStock ms on ms.id=posd.matStockId
        left join Pro_ConStock cs on cs.id=posd.conInventoryDetId
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  

        union all

        select distinct 
        '生产消耗单' as '报表类型',
        pc.billCode as '单据编号',
        pro.projectNum as '项目编号',
        pro.projectName as '项目名称',
        ll.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        pcd.consumeQty as '数量',

        CONVERT(varchar(100), pc.billDate, 23) as '日期'

        from Pro_ProductConsume pc
        left join Pro_ProductConsumeDet pcd on pcd.parentId=pc.id

        left join Pro_LingLiaoDet lld on lld.id=pcd.lingLiaoDetId
        left join Pro_LingLiao ll on ll.id=lld.parentId

        left join Pro_MatStock ms on ms.id=lld.matStockId and lld.memberType=1
        left join Pro_ConStock cs on cs.id=lld.matStockId and lld.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_ProTask pt on pt.billCode=ll.proTaskCode
        left join Pro_Info pro on pt.proInfCode=pro.billCode

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  
        ) T where 1=1 "));

    if (!string.IsNullOrEmpty(search.projectName))
    {
        sql.AppendFormat("  and T.项目名称 like '%{0}%' ", search.projectName.Trim());
    }
    if (!string.IsNullOrEmpty(search.projectCode))
    {
        sql.AppendFormat("  and T.项目编号 like '%{0}%' ", search.projectCode.Trim());
    }
    if (!string.IsNullOrEmpty(search.btime) && !string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  >=  '{0}'", search.btime);
    }
    if (!string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  <=  '{0}'", search.etime);
    }

    return sql.ToString();
}
/// <summary>
/// 统计SQL
/// </summary>
public override string GetReportSql(string formData)
{
    ProductInvoiceModel search = JsonConvert.DeserializeObject<ProductInvoiceModel>(formData);

    StringBuilder sql = new StringBuilder(string.Format(@"
        select * from 
        (select distinct 
        '出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        ma.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        mad.model as '规格型号',
        dic1.dicItem as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOutStorageDet posd
        left join Pro_ProductOutStorage pos on pos.id=posd.parentId

        left join Pro_MatAllotDet mad on posd.matAllotDetId=mad.id
        left join Pro_MatAllot ma on ma.id=mad.parentID and ma.status=2
        left join Pro_MatStock ms on ms.id=mad.matStockId and mad.memberType=1
        left join Pro_ConStock cs on cs.id=mad.matStockId and mad.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mad.unitCode  

        union all

        select distinct 
        'XXX出库单' as '报表类型',
        pos.billNo as '单据编号',
        pos.projectCode as '项目编号',
        pro.projectName as '项目名称',
        '' as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        posd.qty as '数量',

        CONVERT(varchar(100), pos.billDate, 23) as '日期'

        from Pro_ProductOut2StorageDet posd
        left join Pro_ProductOut2Storage pos on pos.id=posd.parentId

        left join Pro_MatStock ms on ms.id=posd.matStockId
        left join Pro_ConStock cs on cs.id=posd.conInventoryDetId
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_Info pro on pos.projectCode=pro.projectNum

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  

        union all

        select distinct 
        '生产消耗单' as '报表类型',
        pc.billCode as '单据编号',
        pro.projectNum as '项目编号',
        pro.projectName as '项目名称',
        ll.subProject as '项目子项',

        case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '构件编码',
        case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名称',
        case when cs.conMemberCode is not NULL then '构件' else '物料' end as '类型',

        case when cmd.model is not NULL then cmd.model else mat.model end as '规格型号',
        case when dic1.dicItem  is not NULL then dic1.dicItem  else dic2.dicItem  end as '单位',
        pcd.consumeQty as '数量',

        CONVERT(varchar(100), pc.billDate, 23) as '日期'

        from Pro_ProductConsume pc
        left join Pro_ProductConsumeDet pcd on pcd.parentId=pc.id

        left join Pro_LingLiaoDet lld on lld.id=pcd.lingLiaoDetId
        left join Pro_LingLiao ll on ll.id=lld.parentId

        left join Pro_MatStock ms on ms.id=lld.matStockId and lld.memberType=1
        left join Pro_ConStock cs on cs.id=lld.matStockId and lld.memberType=0
        left join sys_material mat on mat.materialCode=ms.materailCode

        left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
        left join Pro_ProductInstorageDet pid on pid.parentId=pi.id

        left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
        left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId

        left join Pro_ProTask pt on pt.billCode=ll.proTaskCode
        left join Pro_Info pro on pt.proInfCode=pro.billCode

        left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode  
        left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit  
        ) T where 1=1 "));

    if (!string.IsNullOrEmpty(search.projectName))
    {
        sql.AppendFormat("  and T.项目名称 like '%{0}%' ", search.projectName.Trim());
    }
    if (!string.IsNullOrEmpty(search.projectCode))
    {
        sql.AppendFormat("  and T.项目编号 like '%{0}%' ", search.projectCode.Trim());
    }
    if (!string.IsNullOrEmpty(search.btime) && !string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  >=  '{0}'", search.btime);
    }
    if (!string.IsNullOrEmpty(search.etime))
    {
        sql.AppendFormat(" and  T.日期  <=  '{0}'", search.etime);
    }

    return sql.ToString();
}

View Code

View Code

表明:查询SQL书写规范:SQL无法写的乱7捌糟,该换行换行,该对齐对齐。

证实:查询SQL书写规范:SQL不能写的乱七捌糟,该换行换行,该对齐对齐。

2、

2、

亚洲必赢官网 147亚洲必赢官网 148

亚洲必赢官网 149亚洲必赢官网 150

        public List<MatAllotModel> GeProMatAllotList(ref PagerModel pager, string billCode, string projectNum, string projectName, string remarks, int status)
        {
            string sql = @"select v.*,ISNULL(v.IsFinish,(case when ISNULL(v.pqty,0)= ISNULL(v.mqty,0) then 1 else 0 end)) as IsEnd from (
select ma.*,e.name + '('+isnull(e.mobilephone,'')+')' as billCreatorName,i.projectName,s.storeHouseName as inStoreName, 
(select stuff((select distinct ','+s.storeHouseName from Pro_MatAllotDet mad
join sys_storehouse s on mad.outStorageCode=s.storeHouseCode
where parentId =ma.id for xml path('')),1,1,'')) as outStoreName,I.projectSim,D.dicItem AS typename,ppi.id as InvoiceId,i.projectNum,
(select sum(md.qty) from Pro_MatAllotDet md  where md.parentId =ma.id) as mqty,
(select sum(qty) from Pro_ProductOutStorageDet pod 
left join Pro_ProductOutStorage po on po.id = pod.parentID
where po.matAllotBillCode = ma.billCode) as pqty
from Pro_MatAllot ma
join sys_storehouse s on ma.inStoreCode=s.storeHouseCode
join sys_employee e on ma.billCreator=e.employeeCode
left join Pro_ProductInvoice ppi on ppi.MatAllotBillCode=ma.billCode
left join Pro_Info i on ma.projectCode = i.projectNum
LEFT JOIN sys_dicDetail D ON D.dicItemcode = I.projectType
) as v where 1=1 ";
            if (!string.IsNullOrEmpty(billCode))
            {
                sql += " and v.billCode like '%" + billCode.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectName))
            {
                sql += " and v.projectName like '%" + projectName.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectNum))
            {
                sql += " and v.projectNum like '%" + projectNum.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(remarks))
            {
                sql += " and v.remarks like '%" + remarks.Trim() + "%'";
            }
            sql += " and v.status=" + status + "";
            List<MatAllotModel> matAllot = EntityHelper.Default.GetPageEntities<MatAllotModel>(pager.page, pager.rows, sql, "id", pager.sort, pager.order);
            pager.totalRows = DBHelper.Default.Count(sql);
            pager.result = matAllot;
            return matAllot;
        }
        public List<MatAllotModel> GeProMatAllotList(ref PagerModel pager, string billCode, string projectNum, string projectName, string remarks, int status)
        {
            string sql = @"select v.*,ISNULL(v.IsFinish,(case when ISNULL(v.pqty,0)= ISNULL(v.mqty,0) then 1 else 0 end)) as IsEnd from (
select ma.*,e.name + '('+isnull(e.mobilephone,'')+')' as billCreatorName,i.projectName,s.storeHouseName as inStoreName, 
(select stuff((select distinct ','+s.storeHouseName from Pro_MatAllotDet mad
join sys_storehouse s on mad.outStorageCode=s.storeHouseCode
where parentId =ma.id for xml path('')),1,1,'')) as outStoreName,I.projectSim,D.dicItem AS typename,ppi.id as InvoiceId,i.projectNum,
(select sum(md.qty) from Pro_MatAllotDet md  where md.parentId =ma.id) as mqty,
(select sum(qty) from Pro_ProductOutStorageDet pod 
left join Pro_ProductOutStorage po on po.id = pod.parentID
where po.matAllotBillCode = ma.billCode) as pqty
from Pro_MatAllot ma
join sys_storehouse s on ma.inStoreCode=s.storeHouseCode
join sys_employee e on ma.billCreator=e.employeeCode
left join Pro_ProductInvoice ppi on ppi.MatAllotBillCode=ma.billCode
left join Pro_Info i on ma.projectCode = i.projectNum
LEFT JOIN sys_dicDetail D ON D.dicItemcode = I.projectType
) as v where 1=1 ";
            if (!string.IsNullOrEmpty(billCode))
            {
                sql += " and v.billCode like '%" + billCode.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectName))
            {
                sql += " and v.projectName like '%" + projectName.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(projectNum))
            {
                sql += " and v.projectNum like '%" + projectNum.Trim() + "%'";
            }
            if (!string.IsNullOrEmpty(remarks))
            {
                sql += " and v.remarks like '%" + remarks.Trim() + "%'";
            }
            sql += " and v.status=" + status + "";
            List<MatAllotModel> matAllot = EntityHelper.Default.GetPageEntities<MatAllotModel>(pager.page, pager.rows, sql, "id", pager.sort, pager.order);
            pager.totalRows = DBHelper.Default.Count(sql);
            pager.result = matAllot;
            return matAllot;
        }

View Code

View Code

3、

3、

亚洲必赢官网 151亚洲必赢官网 152

亚洲必赢官网 153亚洲必赢官网 154

public static List<ConStockDialogMod> GetMaterialData(ref PagerModel pager, string memberName, string storeCode, string parentId, string store)
{
    StringBuilder where = new StringBuilder(" and 1=1 ");
    if (!string.IsNullOrEmpty(memberName))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.memberName like '%{0}%' ", memberName.Trim());
    if (!string.IsNullOrEmpty(storeCode))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and SSH.storeHouseCode='{0}' ", storeCode);
    if (!string.IsNullOrEmpty(parentId))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.parentId = {0} ", parentId);
    if (!string.IsNullOrEmpty(store))
        where.AppendFormat("  and SSH.storeHouseName like '%{0}%'  ", store.Trim());
    string sql = @"SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ConPurInstore cpd on PM.billNo=cpd.billCode
        join Pro_ConPurInstoreDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConGoodsDet t on t.id=cp.conGoodsDetId
        join dbo.Pro_ConPurchaseContractDet t1 on t1.id=t.conContractDetId
        join dbo.Pro_ConPurchasePlanDet t2 on t2.id=t1.conPlanDetID
        join dbo.Pro_ConMemberDet t3 on t3.id=t2.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit 
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0  " + where +
    @" union 
        SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,
        SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ProductInstorage cpd on PM.billNo=cpd.billNo
        join Pro_ProductInstorageDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConMemberDet t3 on t3.id=cp.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit  
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0 " + where;


    List<ConStockDialogMod> model = EntityHelper.Default.GetPageEntities<ConStockDialogMod>(pager.page, pager.rows, sql.ToString(), "id", pager.sort, pager.order);
    pager.totalRows = DBHelper.Default.Count(sql.ToString());
    pager.result = model;
    return model;

}
public static List<ConStockDialogMod> GetMaterialData(ref PagerModel pager, string memberName, string storeCode, string parentId, string store)
{
    StringBuilder where = new StringBuilder(" and 1=1 ");
    if (!string.IsNullOrEmpty(memberName))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.memberName like '%{0}%' ", memberName.Trim());
    if (!string.IsNullOrEmpty(storeCode))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and SSH.storeHouseCode='{0}' ", storeCode);
    if (!string.IsNullOrEmpty(parentId))              //当物料为空时,查询出所有的结果集,否则查询出此种相似物料的结果集
        where.AppendFormat("  and t3.parentId = {0} ", parentId);
    if (!string.IsNullOrEmpty(store))
        where.AppendFormat("  and SSH.storeHouseName like '%{0}%'  ", store.Trim());
    string sql = @"SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ConPurInstore cpd on PM.billNo=cpd.billCode
        join Pro_ConPurInstoreDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConGoodsDet t on t.id=cp.conGoodsDetId
        join dbo.Pro_ConPurchaseContractDet t1 on t1.id=t.conContractDetId
        join dbo.Pro_ConPurchasePlanDet t2 on t2.id=t1.conPlanDetID
        join dbo.Pro_ConMemberDet t3 on t3.id=t2.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit 
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0  " + where +
    @" union 
        SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
        PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,
        SSH.storeHouseName AS storeName,
        t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity, 
        d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
        d3.dicItem as qulityName,d4.dicItem as weightUnitName,
        (select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty  
        from Pro_ConStock PM
        join Pro_ProductInstorage cpd on PM.billNo=cpd.billNo
        join Pro_ProductInstorageDet cp on cpd.id=cp.parentId and PM.billId=cp.id
        join dbo.Pro_ConMemberDet t3 on t3.id=cp.conMemberDetId
        left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
        left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
        left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
        left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
        left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit  
        WHERE  PM.qty-PM.lockQty-PM.usedQty > 0 " + where;


    List<ConStockDialogMod> model = EntityHelper.Default.GetPageEntities<ConStockDialogMod>(pager.page, pager.rows, sql.ToString(), "id", pager.sort, pager.order);
    pager.totalRows = DBHelper.Default.Count(sql.ToString());
    pager.result = model;
    return model;

}

View Code

View Code

4、

4、

亚洲必赢官网 155亚洲必赢官网 156

亚洲必赢官网 157亚洲必赢官网 158

        /// <summary>
        /// Sql
        /// </summary>
        /// <param name="formData"></param>
        /// <returns></returns>
        public override string GetReportSql(string formData)
        {
            TaskAllocationModel search = JsonConvert.DeserializeObject<TaskAllocationModel>(formData);
            StringBuilder where = new StringBuilder(" where 1=1 ");
            if (!string.IsNullOrEmpty(search.projectCode))
            {
                where.AppendFormat("  and ta.projectCode like '%{0}%' ", search.projectCode.Trim());
            }
            if (!string.IsNullOrEmpty(search.projectName))
            {
                where.AppendFormat("  and pro.projectName like '%{0}%' ", search.projectName.Trim());
            }
            string sql = @"select ta.billCode as '单号',pro.projectName as '项目名称',pro.projectNum as '项目编号',ta.desingBillCode as '项目设计任务单',
 CASE ta.violent WHEN '1' THEN '是' ELSE '否' END as '是否加急',
 CASE ta.taskType WHEN '0' THEN '载荷图' WHEN '1' THEN '方案图' WHEN '2' THEN '工程量统计' ELSE '载荷图与工程量统计' END as '任务类型',
ta.desingContent as '设计内容',ta.auditorScore as '审核人打分',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='设计' and oper.OperationID != -3) as '设计者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='校对' and oper.OperationID != -3) as '校对者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审核' and oper.OperationID != -3) as '审核者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审定' and oper.OperationID != -3) as '审定者',
e.name as '创建人',ta.creatTime as '创建时间'
from Pro_TaskAllocation ta
left join Pro_Info pro on ta.projectCode=pro.projectNum
left join sys_employee e on ta.creator=e.employeeCode 
                 " + where;

            return sql.ToString();
        }
        /// <summary>
        /// Sql
        /// </summary>
        /// <param name="formData"></param>
        /// <returns></returns>
        public override string GetReportSql(string formData)
        {
            TaskAllocationModel search = JsonConvert.DeserializeObject<TaskAllocationModel>(formData);
            StringBuilder where = new StringBuilder(" where 1=1 ");
            if (!string.IsNullOrEmpty(search.projectCode))
            {
                where.AppendFormat("  and ta.projectCode like '%{0}%' ", search.projectCode.Trim());
            }
            if (!string.IsNullOrEmpty(search.projectName))
            {
                where.AppendFormat("  and pro.projectName like '%{0}%' ", search.projectName.Trim());
            }
            string sql = @"select ta.billCode as '单号',pro.projectName as '项目名称',pro.projectNum as '项目编号',ta.desingBillCode as '项目设计任务单',
 CASE ta.violent WHEN '1' THEN '是' ELSE '否' END as '是否加急',
 CASE ta.taskType WHEN '0' THEN '载荷图' WHEN '1' THEN '方案图' WHEN '2' THEN '工程量统计' ELSE '载荷图与工程量统计' END as '任务类型',
ta.desingContent as '设计内容',ta.auditorScore as '审核人打分',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='设计' and oper.OperationID != -3) as '设计者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='校对' and oper.OperationID != -3) as '校对者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审核' and oper.OperationID != -3) as '审核者',
(select e.RealName
from Wf2Operate oper 
left join Wf2Node n on n.id = oper.Wf2NoteID 
left join employee e on e.employeecode = oper.Operator 
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='审定' and oper.OperationID != -3) as '审定者',
e.name as '创建人',ta.creatTime as '创建时间'
from Pro_TaskAllocation ta
left join Pro_Info pro on ta.projectCode=pro.projectNum
left join sys_employee e on ta.creator=e.employeeCode 
                 " + where;

            return sql.ToString();
        }

View Code

View Code

5、

5、

亚洲必赢官网 159亚洲必赢官网 160

亚洲必赢官网 161亚洲必赢官网 162

            string sql = string.Format(@"select t.*,t.haveQty as weichukuNum,(t.qty - t.chukuNum) as shenyuNum  from (SELECT o.*,(
 case memberType when 1 then (SELECT (qty- lockQty- usedQty) 
 FROM Pro_MatStock s where s.id=o.matStockId )
 when 0 then ((SELECT (qty- lockQty- usedQty) 
 FROM Pro_ConStock s where s.id=o.matStockId )) else 0 end) as haveQty,
 (case memberType when 1 then (SELECT materialName from sys_material where materialCode = o.code )
 when 0 then (select DISTINCT memberName from Pro_ConMemberDet v LEFT join Pro_ConMember g on g.id=v.parentId where v.memberCode=o.code and g.billCode=ma.conMemberBillNo) else '' end) as materialName
 ,sh.storeHouseName as outStorageName ,
isnull((case memberType when 1 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.matStockId=o.matStockId and p.status in (1,2))
 when 0 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.conInventoryDetId=o.matStockId and p.status in (1,2)) else 0 end),0) as chukuNum,
  (case memberType when 1 then (select SUM(ms.qty-ms.lockQty-ms.usedQty) from Pro_MatStock ms where ms.storeCode=ma.inStoreCode AND ms.materailCode =o.code)
 when 0 then (select SUM(cs.qty-cs.lockQty-cs.usedQty) from Pro_ConStock cs where cs.storeCode=ma.inStoreCode AND cs.conMemberCode =o.code) else 0 end) as xiangmukuNum
 from  Pro_MatAllotDet o 
 JOIN Pro_MatAllot ma on o.parentId = ma.id
 join sys_storehouse sh on o.outStorageCode=sh.storeHouseCode  
 where o.parentId in ({0})) t where t.chukuNum < t.qty", parentId);
            string sql = string.Format(@"select t.*,t.haveQty as weichukuNum,(t.qty - t.chukuNum) as shenyuNum  from (SELECT o.*,(
 case memberType when 1 then (SELECT (qty- lockQty- usedQty) 
 FROM Pro_MatStock s where s.id=o.matStockId )
 when 0 then ((SELECT (qty- lockQty- usedQty) 
 FROM Pro_ConStock s where s.id=o.matStockId )) else 0 end) as haveQty,
 (case memberType when 1 then (SELECT materialName from sys_material where materialCode = o.code )
 when 0 then (select DISTINCT memberName from Pro_ConMemberDet v LEFT join Pro_ConMember g on g.id=v.parentId where v.memberCode=o.code and g.billCode=ma.conMemberBillNo) else '' end) as materialName
 ,sh.storeHouseName as outStorageName ,
isnull((case memberType when 1 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.matStockId=o.matStockId and p.status in (1,2))
 when 0 then (select SUM(pt.qty) from Pro_ProductOutstorageDet pt LEFT join Pro_ProductOutstorage p on p.id=pt.parentID where pt.conInventoryDetId=o.matStockId and p.status in (1,2)) else 0 end),0) as chukuNum,
  (case memberType when 1 then (select SUM(ms.qty-ms.lockQty-ms.usedQty) from Pro_MatStock ms where ms.storeCode=ma.inStoreCode AND ms.materailCode =o.code)
 when 0 then (select SUM(cs.qty-cs.lockQty-cs.usedQty) from Pro_ConStock cs where cs.storeCode=ma.inStoreCode AND cs.conMemberCode =o.code) else 0 end) as xiangmukuNum
 from  Pro_MatAllotDet o 
 JOIN Pro_MatAllot ma on o.parentId = ma.id
 join sys_storehouse sh on o.outStorageCode=sh.storeHouseCode  
 where o.parentId in ({0})) t where t.chukuNum < t.qty", parentId);

View Code

View Code

 

 

网站地图xml地图