MESProductionWeb/Ant.Service.Common/MsSqlHelper/SqlDbHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Reflection;
using System.Data;
using System.Collections;
using System.Web;
using System.Web.Caching;

public class SqlDbHelper
{
    public static string constr = ConfigurationManager.AppSettings["MESProduction"].ToString();

    #region 验证数据库是否连接
    /// <summary>
    /// 测试数据库是否可以正常连接,time如果默认就不填写 如果填写则加timeout连接字符串
    /// </summary>
    /// <returns></returns>
    public static bool ChechSqlServer(int timeout)
    {
        string connnectstring = "";
        if (timeout != 0)
        {
            connnectstring = constr + ";Connect Timeout=" + timeout;
        }
        else { connnectstring = constr; }
        //constr里不要加;Connect Timeout=5000参数如果加参数此处需要修改
        bool succeed = false;
        try
        {
            SqlConnection p_connectionString = new SqlConnection(connnectstring);
            p_connectionString.Open();
            SqlCommand p_selectCommand = new SqlCommand("SELECT 1", p_connectionString);
            SqlDataReader reader = p_selectCommand.ExecuteReader();
            if (reader.HasRows)
                succeed = true;
            reader.Dispose();
            reader.Close();
            p_connectionString.Close();
        }
        catch
        { }
        return succeed;
    }
    #endregion

    #region 根据实体类名获取表名
    /// <summary>
    /// 根据实体类名返回数据库表名和其字段
    /// </summary>
    /// <typeparam name="T">实体类</typeparam>
    /// <param name="t">实体对象</param>
    /// <returns>数据表名和字段</returns>
    public static Dictionary<string, PropertyInfo[]> GetTableName<T>(T t)
    {
        if (t == null)
        {
            return null;
        }
        PropertyInfo[] propertys = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
        if (propertys.Length <= 0)
        {
            return null;
        }
        Dictionary<string, PropertyInfo[]> dic = new Dictionary<string, PropertyInfo[]>();
        string fname = t.ToString();
        string cname = typeof(T).Name;
        dic[cname] = propertys;
        return dic;
    }
    #endregion

    #region 增加一个记录
    /// <summary>
    /// 添加一条数据到数据库,排除主键在SQL中出现
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <param name="constr">数据连接字符串</param>
    /// <returns>是否写入成功</returns>
    public static bool InsertModel<T>(T t, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        InsertSqlParams<T>(t, out sql, out sp);
        return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
    }
    /// <summary>
    /// 添加一条数据到数据库,排除主键在SQL中出现
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <returns>是否写入成功</returns>
    public static bool InsertModel<T>(T t)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        InsertSqlParams<T>(t, out sql, out sp);
        return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
    }

    /// <summary>
    /// 添加一条数据到数据库,主键在SQL中出现
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <param name="constr">数据连接字符串</param>
    /// <returns>是否写入成功</returns>
    public static bool InsertModelGuid<T>(T t)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        InsertSqlParamsNoParimary<T>(t, out sql, out sp, constr);
        return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
    }
    /// <summary>
    /// 添加一条数据到数据库,主键在SQL中出现
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <param name="constr">数据连接字符串</param>
    /// <returns>是否写入成功</returns>
    public static bool InsertModelGuid<T>(T t, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        InsertSqlParamsNoParimary<T>(t, out sql, out sp, constr);
        return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
    }
    /// <summary>
    /// 添加一条数据到数据库,不进行数据表字段对比
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">实体对象</param>
    /// <param name="constr">连接字符串</param>
    /// <returns>是否写入成功</returns>
    public static bool InsertModelnoEq<T>(T t, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        if (InsertSqlParamsnoEp<T>(t, out sql, out sp))
        {
            return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
        }
        return false;
    }
    /// <summary>
    /// 添加一条数据到数据库，并返回主标识id
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <param name="id">数据成功插入后返回主标识id</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns>数据插入是否成功</returns>
    public static bool InsertModel<T>(T t, out int id, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        InsertSqlParamsWithIdentity<T>(t, out sql, out sp);
        id = ExecuteNonQueryWithIdentity(conn, CommandType.Text, sql, sp);
        if (id > 0)
        {
            return true;
        }
        return false;
    }
    /// <summary>
    /// 插入一条数据并返回主键ID
    /// </summary>
    /// <typeparam name="T">数据实体</typeparam>
    /// <param name="t">实体参数</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns>主键</returns>
    public static int InsertModelReturnIdentity<T>(T t, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        InsertSqlParamsWithIdentity<T>(t, out sql, out sp);
        return ExecuteNonQueryWithIdentity(conn, CommandType.Text, sql, sp);
    }
    public static bool InsertSqlParamsWithIdentity<T>(T t, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        try
        {
            string cname = typeof(T).Name;
            //构造参数化SQL命令
            StringBuilder ExeSql = new StringBuilder();
            StringBuilder Params = new StringBuilder();
            ExeSql.Append("insert into " + cname + " (");
            //循环补充字段和参数
            List<SqlParameter> list = new List<SqlParameter>();
            List<CP.ColumnInfo> dic = CP.TC(cname, constr);
            if (dic == null || dic.Count == 0)
            {
                return false;
            }
            int counts = dic.Count;
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
                if (item != null)
                {
                    if (!item.IsPrimary)
                    {
                        object v = pi.GetValue(t, null);
                        if (v != null)//只对赋值了的属性进行操作
                        {
                            ExeSql.Append(pi.Name + ",");
                            Params.Append("@" + pi.Name + ",");
                            list.Add(CP.cPa(item, v));
                        }
                    }
                }
            }
            SqlParameter sp = new SqlParameter("@" + ParameterDirection.ReturnValue.ToString(), SqlDbType.Int);
            sp.Direction = ParameterDirection.Output;
            list.Add(sp);
            sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ");select @" + ParameterDirection.ReturnValue.ToString() + "=SCOPE_IDENTITY()";
            paras = list.ToArray();
            return true;
        }
        catch { return false; }
    }
    /// <summary>
    /// 循环T类型属性，表主键不插入方法
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <param name="sql"></param>
    /// <param name="paras"></param>
    /// <returns></returns>
    public static bool InsertSqlParams<T>(T t, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        try
        {
            StringBuilder ExeSql = new StringBuilder();
            StringBuilder Params = new StringBuilder();
            string cname = typeof(T).Name;
            ExeSql.Append("insert into " + cname + " (");
            //循环补充字段和参数
            List<SqlParameter> list = new List<SqlParameter>();
            List<CP.ColumnInfo> dic = CP.TC(cname, constr);
            if (dic == null || dic.Count == 0)
            {
                return false;
            }
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
                if (item != null)
                {
                    if (!item.IsPrimary)
                    {
                        object v = pi.GetValue(t, null);
                        if (v != null)//只对赋值了的属性进行操作
                        {
                            ExeSql.Append(pi.Name + ",");
                            Params.Append("@" + pi.Name + ",");
                            list.Add(CP.cPa(item, v));
                        }
                    }
                }
            }
            sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ")";
            paras = list.ToArray();
            return true;
        }
        catch { return false; }
    }
    /// <summary>
    /// 循环T类型属性，表主键插入方法
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <param name="sql"></param>
    /// <param name="paras"></param>
    /// <returns></returns>
    public static bool InsertSqlParamsNoParimary<T>(T t, out string sql, out SqlParameter[] paras, string constr)
    {
        sql = ""; paras = null;
        try
        {
            StringBuilder ExeSql = new StringBuilder();
            StringBuilder Params = new StringBuilder();
            string cname = typeof(T).Name;
            ExeSql.Append("insert into " + cname + " (");
            //循环补充字段和参数
            List<SqlParameter> list = new List<SqlParameter>();
            List<CP.ColumnInfo> dic = CP.TC(cname, constr);
            if (dic == null || dic.Count == 0)
            {
                return false;
            }
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
                if (item != null)
                {
                    object v = pi.GetValue(t, null);
                    if (v != null)//只对赋值了的属性进行操作
                    {
                        ExeSql.Append(pi.Name + ",");
                        Params.Append("@" + pi.Name + ",");
                        list.Add(CP.cPa(item, v));
                    }
                }
            }
            sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ")";
            paras = list.ToArray();
            return true;
        }
        catch { return false; }
    }
    /// <summary>
    /// 构造添加执行语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <param name="sql"></param>
    /// <param name="paras"></param>
    /// <returns>是否异常</returns>
    public static bool InsertSqlParamsnoEp<T>(T t, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        StringBuilder ExeSql = new StringBuilder();
        StringBuilder Params = new StringBuilder();
        try
        {
            string cname = typeof(T).Name;
            List<SqlParameter> list = new List<SqlParameter>();
            ExeSql.Append("insert into " + cname + " (");
            foreach (PropertyInfo item in typeof(T).GetProperties())
            {
                object o = item.GetValue(t, null);
                if (o != null)
                {
                    if (item.Name.ToLower() != "id")
                    {
                        ExeSql.Append(item.Name + ",");
                        Params.Append("@" + item.Name + ",");
                        list.Add(createParam(item, o));
                    }
                }
            }
            sql = ExeSql.ToString().TrimEnd(',') + ")values(" + Params.ToString().TrimEnd(',') + ")";
            paras = list.ToArray();
            return true;
        }
        catch
        {
            return false;
        }
    }
    /// <summary>
    /// 添加一条数据到数据库
    /// </summary>
    /// <param name="Fields">表字段</param>
    /// <param name="Table">表名</param>
    /// <param name="constr">数据连接字符串</param>
    /// <returns>是否成功完成操作</returns>
    public static bool InsertRecord(Dictionary<string, object> Fields, string Table, string constr)
    {
        SqlConnection connection = new SqlConnection(constr);
        StringBuilder builder = new StringBuilder("");
        Table = Table.Replace("'", "");
        builder.Append("insert into " + Table + " (");
        IEnumerator<KeyValuePair<string, object>> enumerator = Fields.GetEnumerator();
        StringBuilder builder2 = new StringBuilder();
        List<SqlParameter> pitems = new List<SqlParameter>();
        while (enumerator.MoveNext())
        {
            KeyValuePair<string, object> current = enumerator.Current;
            string key = current.Key;
            object obj = current.Value;
            //只处理已赋值的项
            if (!string.IsNullOrEmpty(key) && (obj != null))
            {
                builder.Append(key + ",");
                builder2.Append("@" + key + ",");
                pitems.Add(createParam(key, obj));
            }
        }
        string sql = builder.ToString().TrimEnd(',') + ") values (" + builder2.ToString().TrimEnd(',') + ")";
        if (ExecuteNonQuery(connection, CommandType.Text, sql, pitems.ToArray()) > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 添加一条数据到数据库
    /// </summary>
    /// <param name="Fields">表字段</param>
    /// <param name="Table">表名</param>
    /// <param name="id">数据插入后返回主标识(自动增长字段值)</param>
    /// <param name="constr">数据连接字符串</param>
    /// <returns>是否成功完成操作</returns>
    public static bool InsertRecord(Dictionary<string, object> Fields, string Table, out int id, string constr)
    {
        id = 0;
        SqlConnection connection = new SqlConnection(constr);
        StringBuilder builder = new StringBuilder("");
        Table = Table.Replace("'", "");
        builder.Append("insert into " + Table + " (");
        IEnumerator<KeyValuePair<string, object>> enumerator = Fields.GetEnumerator();
        StringBuilder builder2 = new StringBuilder();
        List<SqlParameter> pitems = new List<SqlParameter>();
        while (enumerator.MoveNext())
        {
            KeyValuePair<string, object> current = enumerator.Current;
            string key = current.Key;
            object obj = current.Value;
            //只处理已赋值的项
            if (!string.IsNullOrEmpty(key) && (obj != null))
            {
                builder.Append(key + ",");
                builder2.Append("@" + key + ",");
                pitems.Add(createParam(key, obj));
            }
        }
        SqlParameter sp = new SqlParameter("@out_return_id", SqlDbType.Int);
        sp.Direction = ParameterDirection.Output;
        pitems.Add(sp);
        string sql = builder.ToString().TrimEnd(',') + ") values (" + builder2.ToString().TrimEnd(',') + ") select @out_return_id=SCOPE_IDENTITY()";
        if (ExecuteNonQueryOutID(connection, CommandType.Text, sql, pitems.ToArray()) > 0)
            return true;
        return false;
    }
    #endregion

    #region 更新一个记录
    /// <summary>
    /// 更新一个记录，按主键
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns></returns>
    public static bool UpdateModel<T>(T t, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        UpdateSqlParams<T>(t, out sql, out sp);
        return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
    }
    /// <summary>
    /// 更新某条记录，按主键 更新多个字段
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="p">字典参数</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns></returns>
    public static bool UpdateModel<T>(Dictionary<string, object> p, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        UpdateSqlParams<T>(p, out sql, out sp);
        return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
    }
    /// <summary>
    /// 更新某条记录，按主键 更新多个字段
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">实体对象</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns></returns>
    public static bool UpdateModelnoEp<T>(T t, string constr)
    {
        SqlConnection conn = new SqlConnection(constr);
        string sql = string.Empty; SqlParameter[] sp = null;
        if (UpdateSqlParamsnoEp<T>(t, out sql, out sp))
        {
            return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
        }
        return false;
    }
    public static bool UpdateSqlParams<T>(T t, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        PropertyInfo[] propertys = typeof(T).GetProperties();
        string cname = typeof(T).Name;
        //构造参数化SQL命令
        StringBuilder ExeSql = new StringBuilder();
        ExeSql.Append("update " + cname + " set ");
        //循环补充字段和参数
        List<SqlParameter> list = new List<SqlParameter>();
        List<CP.ColumnInfo> dic = CP.TC(cname, constr);
        if (dic == null || dic.Count == 0)
        {
            return false;
        }
        string primaryKey = string.Empty;
        foreach (PropertyInfo pi in propertys)
        {
            var propertyType = Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType;//得到属性定义类型
            string t_p = propertyType.ToString().ToLower();//转化为小写
            t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
            object v = pi.GetValue(t, null);
            if ((t_p == "string" || t_p == "char") && v == null)
                v = "";
            //object v = pi.GetValue(t, null);
            if (v != null)//只对赋值了的属性进行操作
            {
                CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
                if (item != null)
                {
                    if (!item.IsPrimary)
                    {
                        ExeSql.Append(pi.Name + "=@" + pi.Name + ",");
                    }
                    else
                    {
                        primaryKey = pi.Name;
                    }
                    list.Add(CP.cPa(item, v));
                }
            }
        }
        sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey + "=@" + primaryKey;
        paras = list.ToArray();
        return true;
    }
    public static bool UpdateSqlParams<T>(Dictionary<string, object> p, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        try
        {
            PropertyInfo[] propertys = typeof(T).GetProperties();
            string cname = typeof(T).Name;
            StringBuilder ExeSql = new StringBuilder();
            //构造参数化SQL命令
            ExeSql.Append("update " + cname + " set ");
            //循环补充字段和参数
            List<SqlParameter> list = new List<SqlParameter>();
            List<CP.ColumnInfo> dic = CP.TC(cname, constr);
            if (dic == null || dic.Count == 0)
            {
                return false;
            }
            //集合枚举
            IEnumerator<KeyValuePair<string, object>> enumerator = p.GetEnumerator();
            string primaryKey = string.Empty;//主键参数
            foreach (PropertyInfo pi in propertys)
            {
                while (enumerator.MoveNext())
                {
                    KeyValuePair<string, object> current = enumerator.Current;
                    string key = current.Key;
                    object obj = current.Value;
                    if (!string.IsNullOrEmpty(key) && (obj != null))
                    {
                        CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == key.ToLower());
                        if (item != null)
                        {
                            if (item.IsPrimary)
                            {
                                primaryKey = pi.Name;
                            }
                            else
                            {
                                ExeSql.Append(key + "=@" + key + ",");
                            }
                            list.Add(CP.cPa(item, obj));
                        }
                    }
                }
            }
            sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey + "=@" + primaryKey;
            paras = list.ToArray();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateSqlParamsnoEp<T>(T t, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        try
        {
            string cname = typeof(T).Name;
            //构造参数化SQL命令
            StringBuilder ExeSql = new StringBuilder();
            ExeSql.Append("update " + cname + " set ");
            //循环补充字段和参数
            List<SqlParameter> list = new List<SqlParameter>();
            string primaryKey = string.Empty; //主键
            foreach (PropertyInfo item in typeof(T).GetProperties())
            {
                object v = item.GetValue(t, null);
                if (v != null)
                {
                    if (item.Name.ToLower() != "id")
                    {
                        ExeSql.Append(item.Name + "=@" + item.Name + ",");
                    }
                    else
                    {
                        primaryKey = item.Name + "=@" + item.Name;
                    }
                    list.Add(createParam(item, v));
                }
            }
            sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey;
            paras = list.ToArray();
            return true;
        }
        catch { return false; }
    }
    #endregion

    #region 得到一个记录
    /// <summary>
    /// 根据主键标识id获取一个记录
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="id">主键标识id</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns>一个数据实体所有信息</returns>
    public static T GetModel<T>(int id, string constr) where T : new()
    {
        // 获得此模型的类型
        T t = new T();

        string sql = string.Empty; SqlParameter[] sp = null; PropertyInfo[] propertys = null;
        GetSqlParams<T>(id, out sql, out sp, out propertys);
        DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
        if (ds.Tables.Count == 0)
        {
            return default(T);
        }
        DataTable dt = ds.Tables[0];
        if (dt.Rows.Count <= 0)
        {
            return default(T);
        }
        string tempName = "";
        foreach (DataRow dr in dt.Rows)
        {
            // 获得此模型的公共属性
            foreach (PropertyInfo pi in propertys)
            {
                tempName = pi.Name;
                // 检查DataTable是否包含此列
                if (dt.Columns.Contains(tempName))
                {
                    // 判断此属性是否有Setter
                    if (!pi.CanWrite) continue;
                    object value = dr[tempName];
                    if (value != DBNull.Value && value != null)
                        pi.SetValue(t, value, null);
                }
            }
        }
        return t;
    }
    public static bool GetSqlParams<T>(int id, out string sql, out SqlParameter[] paras, out PropertyInfo[] py)
    {
        sql = ""; paras = null;
        //获取数据实体信息
        string cname = typeof(T).Name;
        py = typeof(T).GetProperties();
        List<SqlParameter> list = new List<SqlParameter>();
        List<CP.ColumnInfo> dic = CP.TC(cname, constr);
        string searchCols = string.Empty;
        string primaryKey = string.Empty;
        foreach (PropertyInfo pi in py)
        {
            CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
            if (item != null)
            {
                if (item.IsPrimary)
                {
                    primaryKey = item.ColName;
                    list.Add(CP.cPa(item, id));
                }
                searchCols += pi.Name + ",";
            }

        }
        searchCols = searchCols.TrimEnd(',');
        sql = "select " + searchCols + " from " + cname + " where " + primaryKey + "=@" + primaryKey;
        paras = list.ToArray();
        return true;
    }
    public static bool GetSqlParams<T>(int id, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        // 获得此模型的类型
        string cname = typeof(T).Name;
        //获取数据实体信息
        List<SqlParameter> list = new List<SqlParameter>();
        List<CP.ColumnInfo> dic = CP.TC(cname, constr);
        if (dic == null || dic.Count == 0) { return false; }
        string searchCols = string.Empty;
        string primaryKey = string.Empty;
        foreach (PropertyInfo pi in typeof(T).GetProperties())
        {
            CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
            if (item != null)
            {
                if (item.IsPrimary)
                {
                    primaryKey = item.ColName;
                    list.Add(CP.cPa(item, id));
                }
                searchCols += pi.Name + ",";
            }
        }
        searchCols = searchCols.TrimEnd(',');
        sql = "select " + searchCols + " from " + cname + " where " + primaryKey + "=@" + primaryKey;
        paras = list.ToArray();
        return true;
    }
    /// <summary>
    /// 根据一个或多个实体赋值字段做为查询条件得到一个新的实体
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns>一个数据实体所有信息</returns>
    public static T GetModel<T>(IDictionary<string, object> p, string constr) where T : new()
    {
        string sql = ""; SqlParameter[] sp = null; PropertyInfo[] propertys = null;
        T t = new T();
        GetSqlParams<T>(p, out sql, out sp, out propertys);
        DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
        if (ds.Tables.Count == 0)
        {
            return default(T);
        }
        DataTable dt = ds.Tables[0];
        if (dt.Rows.Count <= 0)
        {
            return default(T);
        }
        string tempName = "";
        foreach (DataRow dr in dt.Rows)
        {
            // 获得此模型的公共属性
            foreach (PropertyInfo pi in propertys)
            {
                tempName = pi.Name;
                // 检查DataTable是否包含此列
                if (dt.Columns.Contains(tempName))
                {
                    // 判断此属性是否有Setter
                    if (!pi.CanWrite) continue;
                    object value = dr[tempName];
                    if (value != DBNull.Value && value != null)
                        pi.SetValue(t, value, null);
                }
            }
        }
        return t;
    }
    public static bool GetSqlParams<T>(IDictionary<string, object> p, out string sql, out SqlParameter[] paras)
    {
        sql = ""; paras = null;
        string cname = typeof(T).Name;
        //获取数据实体信息
        List<SqlParameter> list = new List<SqlParameter>();
        List<CP.ColumnInfo> dic = null;
        //判断T是否为视图
        dic = cname.StartsWith("V") ? CP.TCV(cname, constr) : CP.TC(cname, constr);
        string searchCols = string.Empty;
        string primaryKey = string.Empty;
        string temp = "";
        bool Isfirst = true;
        foreach (PropertyInfo pi in typeof(T).GetProperties())
        {
            CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == pi.Name.ToLower());
            if (item != null)
            {
                searchCols += pi.Name + ",";
            }
        }
        //检查查询字典，如果为空，直接返回
        if (p != null && p.Count != 0)
        {
            IEnumerator<KeyValuePair<string, object>> enumera = p.GetEnumerator();
            while (enumera.MoveNext())
            {
                KeyValuePair<string, object> current = enumera.Current;
                string key = current.Key;
                object v = current.Value;
                if (!string.IsNullOrEmpty(key) && (v != null))
                {
                    CP.ColumnInfo item = dic.Find(m => m.ColName.ToLower() == key.ToLower());
                    if (item != null)
                    {
                        list.Add(CP.cPa(item, v));
                        if (Isfirst)
                        {
                            temp += item.ColName + "=@" + item.ColName;
                            Isfirst = false;
                        }
                        else
                        {
                            temp += " and " + item.ColName + "=@" + item.ColName;
                        }
                    }
                }
            }
        }
        else
        {
            temp = "1=1";
        }
        searchCols = searchCols.TrimEnd(',');
        sql = "select " + searchCols + " from " + cname + " where " + temp;
        paras = list.ToArray();
        return true;
    }
    public static bool GetSqlParams<T>(IDictionary<string, object> p, out string sql, out SqlParameter[] paras, out PropertyInfo[] py)
    {
        sql = ""; paras = null;
        py = typeof(T).GetProperties();
        string cname = typeof(T).Name;
        //获取数据实体信息
        List<SqlParameter> list = new List<SqlParameter>();
        List<CP.ColumnInfo> dic = CP.TC(cname, constr);
        string searchCols = string.Empty;
        string primaryKey = string.Empty;
        string temp = "";
        bool Isfirst = true;
        foreach (PropertyInfo pi in py)
        {
            CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == pi.Name.ToLower());
            if (item != null)
            {
                searchCols += pi.Name + ",";
            }
        }
        //检查查询字典，如果为空，直接返回
        if (p == null || p.Count == 0) { return false; }
        IEnumerator<KeyValuePair<string, object>> enumera = p.GetEnumerator();
        while (enumera.MoveNext())
        {
            KeyValuePair<string, object> current = enumera.Current;
            string key = current.Key;
            object v = current.Value;
            if (!string.IsNullOrEmpty(key) && (v != null))
            {
                CP.ColumnInfo item = dic.Find(m => m.ColName.ToLower() == key.ToLower());
                if (item != null)
                {
                    list.Add(CP.cPa(item, v));
                    if (Isfirst)
                    {
                        temp += item.ColName + "=@" + item.ColName;
                        Isfirst = false;
                    }
                    else
                    {
                        temp += " and " + item.ColName + "=@" + item.ColName;
                    }
                }
            }
        }
        searchCols = searchCols.TrimEnd(',');
        sql = "select " + searchCols + " from " + cname + " where " + temp;
        paras = list.ToArray();
        return true;
    }
    #endregion

    #region 删除一个记录
    /// <summary>
    /// 删除一个记录
    /// </summary>
    /// <param name="id">标示ID</param>
    /// <param name="tableName">数据表名</param>
    /// <returns>成功失败</returns>
    public static bool DelSingle(string sql, string constr)
    {
        using (SqlConnection conn = new SqlConnection(constr))
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, conn, null, CommandType.Text, sql, null);
            int count = 0;
            try
            {
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(conn);
            }
            if (count > 0)
                return true;
            return false;
        }
    }
    /// <summary>
    /// 从数据库表中删除一个记录，按主键标识id
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="t">数据实体对象</param>
    /// <param name="constr">数据连接字符串</param>
    /// <returns>是否删除成功</returns>
    public static bool DeleteModel<T>(T t, string constr)
    {
        if (t == null)
        {
            return false;
        }
        // 获得此模型的公共属性
        PropertyInfo[] propertys = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
        if (propertys.Length <= 0)
        {
            return false;
        }
        string fname = t.ToString();
        string cname = fname.Substring(fname.LastIndexOf(".") + 1, fname.Length - fname.LastIndexOf(".") - 1);
        bool result = false;
        foreach (PropertyInfo pi in propertys)
        {
            object v = pi.GetValue(t, null);
            if (v != null)//只对赋值了的属性进行操作,
            {
                if (pi.Name.ToString().ToLower() == "id")
                {
                    SqlParameter[] pitems = { new SqlParameter("@" + pi.Name, SqlDbType.Int) };
                    pitems[0].Value = int.Parse(v.ToString());
                    string sql = "delete from " + cname + " where id=@" + pi.Name + "";
                    SqlConnection conn = new SqlConnection(constr);
                    if (ExecuteNonQuery(conn, CommandType.Text, sql, pitems) > 0)
                    {
                        result = true;
                    }
                    break;
                }
            }
        }
        return result;
    }
    /// <summary>
    /// 根据主键ID删除一条记录，注：主键必须是ID，且存在
    /// </summary>
    /// <typeparam name="T">删除实体</typeparam>
    /// <param name="id">ID</param>
    /// <param name="constr">数据库连接</param>
    /// <returns>成功失败</returns>
    public static bool DeleteModel<T>(int id, string constr) where T : new()
    {
        if (id == 0)
            return false;
        string cname = typeof(T).Name;
        string sql = "delete from " + cname + " where id=@id";
        SqlParameter para = new SqlParameter("@id", id);
        SqlConnection conn = new SqlConnection(constr);
        if (ExecuteNonQuery(conn, CommandType.Text, sql, para) > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 删除多条记录，按条件
    /// </summary>
    /// <typeparam name="T">要删除的实体类</typeparam>
    /// <param name="p">键值对</param>
    /// <param name="constr">数据库连接</param>
    /// <returns>成功失败</returns>
    public static bool DeleteModel<T>(IDictionary<string, object> p, string constr)
    {
        if (p == null || p.Count == 0)
            return false;
        string tablename = typeof(T).Name;
        StringBuilder ExeSql = new StringBuilder();
        ExeSql.Append("delete from " + tablename);
        IEnumerator<KeyValuePair<string, object>> enumerator = p.GetEnumerator();
        List<SqlParameter> list = new List<SqlParameter>();
        bool isFirst = true;
        while (enumerator.MoveNext())
        {
            KeyValuePair<string, object> current = enumerator.Current;
            string key = current.Key;
            object obj = current.Value;
            if (!string.IsNullOrEmpty(key) && (obj != null))
            {
                if (isFirst)
                {
                    ExeSql.Append(" where " + key + "=@" + key);
                    isFirst = false;
                }
                else
                {
                    ExeSql.Append(" and " + key + "=@" + key);
                }
                list.Add(createParam(key, obj));
            }
        }
        SqlConnection conn = new SqlConnection(constr);
        try
        {
            if (ExecuteNonQuery(conn, CommandType.Text, ExeSql.ToString(), list.ToArray()) > 0)
                return true;
            return false;
        }
        catch { return false; }
    }
    #endregion

    #region 得到多个记录
    /// <summary>
    /// 根据ID得到多个记录
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">SQL查询命令</param>
    /// <param name="constr">数据库连接</param>
    /// <param name="paras">可变参数</param>
    /// <returns></returns>
    public static List<T> GetMoreModel<T>(string sql, string constr, params SqlParameter[] paras) where T : new()
    {
        List<T> list = new List<T>();
        DataSet ds = Query(sql, paras);
        int count = ds.Tables[0].Rows.Count;
        if (count == 0)
        {
            return null;
        }
        string tempName = "";
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            T v = new T();
            // 获得此模型的公共属性
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                tempName = pi.Name;
                // 检查DataTable是否包含此列
                if (ds.Tables[0].Columns.Contains(tempName))
                {
                    // 判断此属性是否有Setter
                    if (!pi.CanWrite) continue;
                    object value = dr[tempName];
                    if (value != DBNull.Value && value != null)
                        pi.SetValue(v, value, null);

                }
            }
            list.Add(v);
        }
        return list;
    }
    /// <summary>
    /// 根据键值对获取多个记录
    /// </summary>
    /// <typeparam name="T">类型</typeparam>
    /// <param name="p">条件</param>
    /// <param name="constr">数据库连接</param>
    /// <param name="param">参数</param>
    /// <returns></returns>
    public static List<T> GetMoreModel<T>(Dictionary<string, object> p, string constr, params SqlParameter[] param) where T : new()
    {
        List<T> list = new List<T>();
        string sql = string.Empty; SqlParameter[] sp = null;
        GetSqlParams<T>(p, out sql, out sp);
        DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
        int count = ds.Tables[0].Rows.Count;
        if (count == 0)
        {
            return null;
        }
        string tempName = "";
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            T v = new T();
            // 获得此模型的公共属性
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                tempName = pi.Name;
                // 检查DataTable是否包含此列
                if (ds.Tables[0].Columns.Contains(tempName))
                {
                    // 判断此属性是否有Setter
                    if (!pi.CanWrite) continue;
                    object value = dr[tempName];
                    if (value != DBNull.Value && value != null)
                        pi.SetValue(v, value, null);

                }
            }
            list.Add(v);
        }
        return list;
    }

    /// <summary>
    /// 根据键值对获取多个记录
    /// </summary>
    /// <typeparam name="T">类型</typeparam>
    /// <param name="p">条件</param>
    /// <param name="sort">条件排序 id desc</param>
    /// <param name="constr">数据库连接</param>
    /// <param name="param">参数</param>
    /// <returns></returns>
    //public static List<T> GetMoreModel<T>(Dictionary<string, object> p,string sort, string constr, params SqlParameter[] param) where T : new()
    //{
    //    List<T> list = new List<T>();
    //    string sql = string.Empty; SqlParameter[] sp = null;
    //    GetSqlParams<T>(p, out sql, out sp);
    //    if (!string.IsNullOrEmpty(sort)) { sql += " order by "+sort; }
    //    DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
    //    int count = ds.Tables[0].Rows.Count;
    //    if (count == 0)
    //    {
    //        return null;
    //    }
    //    string tempName = "";
    //    foreach (DataRow dr in ds.Tables[0].Rows)
    //    {
    //        T v = new T();
    //        // 获得此模型的公共属性
    //        foreach (PropertyInfo pi in typeof(T).GetProperties())
    //        {
    //            tempName = pi.Name;
    //            // 检查DataTable是否包含此列
    //            if (ds.Tables[0].Columns.Contains(tempName))
    //            {
    //                // 判断此属性是否有Setter
    //                if (!pi.CanWrite) continue;
    //                object value = dr[tempName];
    //                if (value != DBNull.Value && value != null)
    //                    pi.SetValue(v, value, null);

    //            }
    //        }
    //        list.Add(v);
    //    }
    //    return list;
    //}
    #endregion

    #region 根据SQL获取单值object
    /// <summary>
    /// 执行一条SQL语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">SQL命令</param>
    /// <param name="constr">数据库连接串</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string SQLString, string constr)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, null);
            try
            {
                object obj = cmd.ExecuteScalar();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(connection);
            }
        }
    }
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    CloseConnection(connection);
                }
            }
        }
    }
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="connection">SqlConnection对象</param>
    /// <param name="trans">SqlTransaction事务</param>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                trans.Rollback();
                throw e;
            }
            finally
            {
                CloseConnection(connection);
            }
        }
    }
    /// <summary>
    /// 是否存在该记录
    /// </summary>
    /// <param name="strSql">语句</param>
    /// <param name="cmdParms">参数</param>
    /// <returns>结果</returns>
    public static bool Exists(string strSql, params SqlParameter[] cmdParms)
    {
        object obj = GetSingle(strSql, cmdParms);
        int cmdresult;
        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString());
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    #endregion

    #region 根据SQL语句执行SQL指令 返回int
    /// <summary>
    /// 执行单纯的SQL语句
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <param name="constr">数据库连接</param>
    /// <returns>结果(int)</returns>
    public static int ExecuteCmd(string SQLString, string constr)
    {
        int result = 0;
        using (SqlConnection conn = new SqlConnection(constr))
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, null, CommandType.Text, SQLString, null);
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(conn);
            }
            return result;
        }
    }
    /// <summary>
    /// 执行SQL指令带参数
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <param name="para">参数</param>
    /// <returns>结果(int)</returns>
    public static int ExecuteCmd(string SQLString, string constr, params SqlParameter[] para)
    {
        int result = 0;
        using (SqlConnection conn = new SqlConnection(constr))
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, conn, null, CommandType.Text, SQLString, para);
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(conn);
            }
            return result;
        }
    }
    #endregion

    #region 执行Sql命令
    /// <summary>
    /// 执行Cmd
    /// </summary>
    /// <param name="connection">连接对象</param>
    /// <param name="commandType">类型</param>
    /// <param name="commandText">内容</param>
    /// <param name="commandParameters">参数</param>
    /// <returns>结果</returns>
    public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
        int retval = 0;
        try
        {
            retval = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
        }
        catch (Exception ee)
        {
            throw ee;
        }
        finally
        {
            CloseConnection(connection);
        }
        return retval;
    }
    /// <summary>
    /// 执行cmd命令返回主键 ID
    /// </summary>
    /// <param name="connection"></param>
    /// <param name="commandType"></param>
    /// <param name="commandText"></param>
    /// <param name="commandParameters"></param>
    /// <returns></returns>
    public static int ExecuteNonQueryWithIdentity(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
        int retval = 0;
        try
        {
            cmd.ExecuteNonQuery();
            retval = int.Parse(cmd.Parameters["@" + ParameterDirection.ReturnValue.ToString()].Value.ToString());
            cmd.Parameters.Clear();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            CloseConnection(connection);
        }
        return retval;
    }
    /// <summary>
    /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQueryOutID(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
        int retval = 0;
        try
        {
            cmd.ExecuteNonQuery();
            retval = int.Parse(cmd.Parameters["@out_return_id"].Value.ToString());
            cmd.Parameters.Clear();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            CloseConnection(connection);
        }
        return retval;
    }
    /// <summary>
    /// 返回带返回参数的过程
    /// </summary>
    /// <param name="connection">数据库连接对象</param>
    /// <param name="commandType">数据类型</param>
    /// <param name="commandText">存储过程名或SQL语句</param>
    /// <param name="commandParameters">参数</param>
    /// <returns>参数</returns>
    public static SqlParameter[] ExecuteNonQueryOutPara(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        SqlParameter[] para = null;
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
        try
        {
            cmd.ExecuteNonQuery();
            if (commandParameters != null)
            {
                para = commandParameters;
                for (int i = 0; i < commandParameters.Length; i++)
                {
                    if (commandParameters[i].Direction == ParameterDirection.Output || commandParameters[i].Direction == ParameterDirection.ReturnValue)
                    {
                        para[i].ParameterName = commandParameters[i].ParameterName;
                        para[i].Value = commandParameters[i].Value;
                    }
                }
            }
            cmd.Parameters.Clear();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            CloseConnection(connection);
        }
        return para;
    }
    /// <summary>
    /// 事务处理，执行SQL语句，返回影响的记录数，只执行一个命令，不关闭连接，连接关闭在调用端执行
    /// </summary>
    /// <param name="connection">SqlConnection对象</param>
    /// <param name="trans">SqlTransaction事件</param>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString)
    {
        int rows = 0;
        using (SqlCommand cmd = new SqlCommand())
        {
            PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, null);
            try
            {
                rows = cmd.ExecuteNonQuery();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                CloseConnection(connection);
            }
            return rows;
        }
    }
    /// <summary>
    /// 事务处理，执行SQL语句，返回影响的记录数，连接关闭在调用端执行
    /// </summary>
    /// <param name="connection">SqlConnection对象</param>
    /// <param name="trans">SqlTransaction事务</param>
    /// <param name="commandType">Sql命令类型</param>
    /// <param name="SQLString">Sql查询语句</param>
    /// <param name="cmdParms">Sql命令参数数组</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

        // Finally, execute the command
        int retval = 0;
        try
        {
            retval = cmd.ExecuteNonQuery();
            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            throw e;
        }
        finally
        {
            CloseConnection(transaction.Connection);
        }
        return retval;
    }
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
    public static void ExecuteSqlTranWithIndentity(List<CommandInfo> SQLStringList, out int identity)
    {
        using (SqlConnection conn = new SqlConnection(constr))
        {
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    identity = 0;
                    //循环
                    foreach (CommandInfo myDE in SQLStringList)
                    {
                        string cmdText = myDE.CommandText;
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                        foreach (SqlParameter q in cmdParms)
                        {
                            if (q.Direction == ParameterDirection.InputOutput)
                            {
                                q.Value = identity;
                            }
                        }
                        PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        foreach (SqlParameter q in cmdParms)
                        {
                            if (q.Direction == ParameterDirection.Output)
                            {
                                identity = Convert.ToInt32(q.Value);
                            }
                        }
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    CloseConnection(conn);
                }
            }
        }
    }
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">SQL命令集合</param>
    public static int ExecuteSqlTran(List<CommandInfo> cmdList)
    {
        using (SqlConnection conn = new SqlConnection(constr))
        {
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    int count = 0;
                    //循环
                    foreach (CommandInfo myDE in cmdList)
                    {
                        string cmdText = myDE.CommandText;
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                        PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        count += val;
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();
                    return count;
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    CloseConnection(conn);
                }
            }
        }
    }
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>		
    public static int ExecuteSqlTran(List<String> SQLStringList)
    {
        using (SqlConnection conn = new SqlConnection(constr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                int count = 0;
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n];
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        count += cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                return count;
            }
            catch
            {
                tx.Rollback();
                return 0;
            }
            finally
            {
                CloseConnection(conn);
            }
        }
    }
    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            int rows = 0;
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
                    rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    CloseConnection(connection);
                }
            }
            return rows;
        }
    }
    /// <summary>
    /// 执行SQL语句，返回影响行数
    /// </summary>
    /// <param name="commandType">操作类型</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <param name="para">参数</param>
    /// <returns>影响的行数</returns>
    public static int ExecuteQuery(CommandType commandType, string commandText, string constr, params SqlParameter[] para)
    {
        using (SqlConnection conn = new SqlConnection(constr))
        {
            return ExecuteNonQuery(conn, commandType, commandText, para);
        }
    }
    /// <summary>
    /// 执行SQL语句，返回影响行数并返回主键ID
    /// </summary>
    /// <param name="commandType">操作类型</param>
    /// <param name="commandText">SQL语句</param>
    /// <param name="constr">数据库连接</param>
    /// <param name="id">抛出的主键ID</param>
    /// <param name="para">参数</param>
    /// <returns>影响的行数</returns>
    public static int ExecuteQuery(CommandType commandType, string commandText, string constr, out int id, params SqlParameter[] para)
    {
        id = 0;
        int result = -1;
        using (SqlConnection conn = new SqlConnection(constr))
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, conn, (SqlTransaction)null, commandType, commandText, para);
            try
            {
                result = cmd.ExecuteNonQuery();
                id = int.Parse(cmd.Parameters["@" + ParameterDirection.ReturnValue.ToString()].Value.ToString());
                cmd.Parameters.Clear();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        return result;
    }
    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="connection">SqlConnection对象</param>
    /// <param name="trans">SqlTransaction对象</param>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            int rows = 0;
            try
            {
                PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
                rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                trans.Rollback();
                throw e;
            }
            finally
            {
                CloseConnection(connection);
            }
            return rows;
        }
    }
    #endregion

    #region 获取数据集，含分页
    /// <summary>
    /// 对应object数组到SqlParameter数组
    /// </summary>
    /// <param name="commandParameters">SqlParameter参数数组</param>
    /// <param name="parameterValues">object参数数组</param>         
    private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
    {
        if ((commandParameters != null) && (parameterValues != null))
        {
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }
            int index = 0;
            int length = commandParameters.Length;
            while (index < length)
            {
                if (parameterValues[index] is IDbDataParameter)
                {
                    IDbDataParameter parameter = (IDbDataParameter)parameterValues[index];
                    if (parameter.Value == null)
                    {
                        commandParameters[index].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[index].Value = parameter.Value;
                    }
                }
                else if (parameterValues[index] == null)
                {
                    commandParameters[index].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[index].Value = parameterValues[index];
                }
                index++;
            }
        }
    }
    /// <summary>
    /// 获取数据集 存储过程使用
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="commandType">SqlCommand命令类型</param>
    /// <param name="commandText">命令内容</param>
    /// <param name="commandParameters">命令用到参数集合</param>
    /// <returns>数据集</returns>
    public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params object[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(connectionString);
        SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connectionString, commandText);
        AssignParameterValues(spParameterSet, commandParameters);
        PrepareCommand(cmd, conn, null, commandType, commandText, spParameterSet);
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            DataSet dataSet = new DataSet();
            try
            {
                adapter.Fill(dataSet);
                cmd.Parameters.Clear();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(conn);
            }
            return dataSet;
        }
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="connection">SqlConnection对象</param>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, conn, (SqlTransaction)null, commandType, commandText, commandParameters);
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                cmd.Parameters.Clear();
            }
            catch
            {
                throw;
            }
            finally
            {
                CloseConnection(conn);
            }
            return ds;
        }
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string SQLString)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, null);
            try
            {
                SqlDataAdapter command = new SqlDataAdapter(cmd);
                command.Fill(ds, "ds");
                command.Dispose();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnection(connection);
            }
            return ds;
        }
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    CloseConnection(connection);
                }
                return ds;
            }
        }
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="connection">SqlConnection对象</param>
    /// <param name="trans">SqlTransaction事务</param>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds, "ds");
                cmd.Parameters.Clear();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                trans.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnection(connection);
            }
            return ds;
        }
    }
    /// <summary>
    /// Pagination 获取分页数据
    /// </summary>
    /// <param name="item">分页实体类，包含分页存储过程所用到的各个参数</param>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="ProName">存储过程名称</param>
    /// <returns>某一页所有记录</returns>
    public static DataSet GetPageContent(Pagination item, string connectionString, string ProName)
    {
        object[] commandParameters = new object[] { item.Tables, item.PrimaryKey, item.Sort, item.CurrentPage, item.PageSize, item.Fields, item.Filter, item.Group };
        return ExecuteDataset(connectionString, CommandType.StoredProcedure, ProName, commandParameters);
    }
    /// <summary>
    /// PaginationByRowNumber获取分页数据
    /// </summary>
    /// <param name="item">分页实体</param>
    /// <param name="constr">数据库链接</param>
    /// <param name="ProName">过程名称</param>
    /// <returns>数据集</returns>
    public static DataSet GetPageContent(PaginationByRowNumber item, string constr, string ProName)
    {
        object[] commandParameter = new object[] { item.Table, item.Sort, item.CurrentPage, item.PageSize, item.Fields, item.Filter };
        return ExecuteDataset(constr, CommandType.StoredProcedure, ProName, commandParameter);
    }
    /// <summary>
    /// 获取分页数据
    /// </summary>
    /// <param name="pt">分页实体类，包含分页存储过程所用到的各个参数</param>
    /// <param name="ProName">存储过程名称</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns>某一页所有记录</returns>
    public static DataSet GetItems(Pagination pt, string ProName, string constr)
    {
        return GetPageContent(pt, constr, ProName);
    }
    /// <summary>
    /// 获取数据集
    /// </summary>
    /// <param name="commandType">Sql命令类型</param>
    /// <param name="commandText">Sql命令内容</param>
    /// <param name="constr">数据库连接字符串</param>
    /// <returns>数据集</returns>
    public static DataSet SqlTextDataset(CommandType commandType, string commandText, string constr)
    {
        return ExecuteDataset(constr, commandType, commandText, null);
    }
    /// <summary>
    /// 获取数据集
    /// </summary>
    /// <param name="commandType">数据类型</param>
    /// <param name="commandText">SQL语句或过程</param>
    /// <param name="constr">数据库连接</param>
    /// <param name="cmdParms">参数</param>
    /// <returns>DataSet</returns>
    public static DataSet SqlTextDataset(CommandType commandType, string commandText, string constr, params SqlParameter[] cmdParms)
    {
        return ExecuteDataset(constr, commandType, commandText, cmdParms);
    }
    /// <summary>
    /// 事务处理，执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="connection">SqlConnection对象</param>
    /// <param name="trans">SqlTransaction事务</param>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(SqlConnection connection, SqlTransaction trans, CommandType commandType, string SQLString, params SqlParameter[] cmdParms)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                PrepareCommand(cmd, connection, trans, commandType, SQLString, cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw e;
            }
            finally
            {
                connection.Close();
            }
        }
    }
    #endregion

    #region 操作帮助方法
    /// <summary>
    /// SqlCommand参数初始化
    /// </summary>
    /// <param name="cmd">Sql命令</param>
    /// <param name="conn">数据库连接对象</param>
    /// <param name="trans">事务对象</param>
    /// <param name="cmdType">Sql命令类型</param>
    /// <param name="cmdText">Sql命令内容</param>
    /// <param name="cmdParms">Sql命令参数集合</param>
    private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        if (trans != null)
        {
            cmd.Transaction = trans;
        }
        cmd.CommandType = cmdType;
        if (cmdParms != null)
        {
            foreach (SqlParameter parameter in cmdParms)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }
    }
    /// <summary>
    /// 关闭数据库连接 释放资源
    /// </summary>
    /// <param name="connection">连接对象</param>
    public static void CloseConnection(SqlConnection connection)
    {
        if (connection.State != ConnectionState.Closed)
        {
            connection.Dispose();
            connection.Close();
        }
    }
    #endregion

    #region 建立一个参数 包括Dictionary和T
    /// <summary>
    /// OOP根据实体类和所属某个属性返回一个SQL参数
    /// </summary>
    /// <typeparam name="T">数据实体类</typeparam>
    /// <param name="p">属性</param>
    /// <param name="t">数据实体对象</param>
    /// <returns>一个SQL参数，已经初始化</returns>
    private static SqlParameter createParam(PropertyInfo p, object v)
    {
        SqlParameter sp = null;
        var propertyType = Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType;//得到属性定义类型
        string t_p = propertyType.ToString().ToLower();//转化为小写
        t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
        // int->Int32  decimal->Decimal float->Single double->Double bool->Boolean datetime->DateTime string->String char->Char
        switch (t_p)
        {
            case "byte":
                sp = new SqlParameter("@" + p.Name, SqlDbType.TinyInt);
                sp.Value = int.Parse(v.ToString());
                break;
            case "int16":
                sp = new SqlParameter("@" + p.Name, SqlDbType.SmallInt);
                sp.Value = int.Parse(v.ToString());
                break;
            case "int32":
                sp = new SqlParameter("@" + p.Name, SqlDbType.Int);
                sp.Value = int.Parse(v.ToString());
                break;
            case "decimal":
                sp = new SqlParameter("@" + p.Name, SqlDbType.Decimal);
                sp.Value = decimal.Parse(v.ToString());
                break;
            case "single":
                sp = new SqlParameter("@" + p.Name, SqlDbType.Float);
                sp.Value = float.Parse(v.ToString());
                break;
            case "double":
                sp = new SqlParameter("@" + p.Name, SqlDbType.Decimal);
                sp.Value = decimal.Parse(v.ToString());
                break;
            case "boolean":
                sp = new SqlParameter("@" + p.Name, SqlDbType.Bit);
                if (v.ToString().ToLower() == "true")
                {
                    sp.Value = 1;
                }
                else if (v.ToString().ToLower() == "false")
                {
                    sp.Value = 0;
                }
                break;
            case "datetime":
                sp = new SqlParameter("@" + p.Name, SqlDbType.DateTime);
                sp.Value = (DateTime)v;
                break;
            case "string":
                //程序测试时注意数据库表中字段是双字节和单字节可变类型情况
                if (System.Text.RegularExpressions.Regex.IsMatch(v.ToString(), @"[^\x00-\xff]+"))
                {
                    string name = p.Name.ToLower();
                    if (name.IndexOf("remark") >= 0 || name.IndexOf("content") >= 0 || name.IndexOf("description") >= 0)
                    {
                        sp = new SqlParameter("@" + p.Name, SqlDbType.NVarChar, 2000);
                    }
                    else
                    {
                        sp = new SqlParameter("@" + p.Name, SqlDbType.VarChar, 50);
                    }
                }
                else
                {
                    sp = new SqlParameter("@" + p.Name, SqlDbType.VarChar, 50);
                }
                sp.Value = v.ToString();
                break;
            case "char":
                sp = new SqlParameter("@" + p.Name, SqlDbType.Char, 1);
                sp.Value = v.ToString();
                break;
        }
        return sp;
    }
    /// <summary>
    /// 利用Key-Value创建一个参数
    /// </summary>
    /// <param name="current">键值对</param>
    /// <returns>SQL参数，已经初始化</returns>
    public static SqlParameter createParam(string key, object obj)
    {
        SqlParameter sp = null;
        string t_p = obj.GetType().ToString().ToLower();//转化为小写
        t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
        // int->Int32  decimal->Decimal float->Single double->Double bool->Boolean datetime->DateTime string->String char->Char
        switch (t_p)
        {
            case "byte":
                sp = new SqlParameter("@" + key, SqlDbType.TinyInt);
                sp.Value = int.Parse(obj.ToString());
                break;
            case "int16":
                sp = new SqlParameter("@" + key, SqlDbType.SmallInt);
                sp.Value = int.Parse(obj.ToString());
                break;
            case "int32":
                sp = new SqlParameter("@" + key, SqlDbType.Int);
                sp.Value = int.Parse(obj.ToString());
                break;
            case "decimal":
                sp = new SqlParameter("@" + key, SqlDbType.Decimal);
                sp.Value = decimal.Parse(obj.ToString());
                break;
            case "single":
                sp = new SqlParameter("@" + key, SqlDbType.Float);
                sp.Value = float.Parse(obj.ToString());
                break;
            case "double":
                sp = new SqlParameter("@" + key, SqlDbType.Decimal);
                sp.Value = decimal.Parse(obj.ToString());
                break;
            case "boolean":
                sp = new SqlParameter("@" + key, SqlDbType.Bit);
                if (obj.ToString().ToLower() == "true")
                {
                    sp.Value = 1;
                }
                else if (obj.ToString().ToLower() == "false")
                {
                    sp.Value = 0;
                }
                break;
            case "datetime":
                sp = new SqlParameter("@" + key, SqlDbType.DateTime);
                sp.Value = (DateTime)obj;
                break;
            case "string":
                if (System.Text.RegularExpressions.Regex.IsMatch(obj.ToString(), @"[^\x00-\xff]+"))
                {
                    string name = key.ToLower();
                    if (name.IndexOf("remark") >= 0 || name.IndexOf("content") >= 0 || name.IndexOf("description") >= 0)
                    {
                        sp = new SqlParameter("@" + key, SqlDbType.NVarChar, 2000);
                    }
                    else
                    {
                        sp = new SqlParameter("@" + key, SqlDbType.VarChar, 50);
                    }
                }
                else
                {
                    sp = new SqlParameter("@" + key, SqlDbType.VarChar, 50);
                }
                sp.Value = obj.ToString();
                break;
            case "char":
                sp = new SqlParameter("@" + key, SqlDbType.Char, 1);
                sp.Value = obj.ToString();
                break;
        }
        return sp;
    }
    #endregion

}
/// <summary>
/// 构造SQL命令和对应参数
/// </summary>
public class CommandInfo
{
    public string CommandText;
    public System.Data.Common.DbParameter[] Parameters;
    public CommandInfo(string sqlText, SqlParameter[] para)
    {
        this.CommandText = sqlText;
        this.Parameters = para;
    }
}

#region 分页类实体,借助存储过程实现
public class Pagination
{
    public int CurrentPage
    {
        get;
        set;
    }

    public string Fields
    {
        get;
        set;
    }

    public string Filter
    {
        get;
        set;
    }

    public string Group
    {
        get;
        set;
    }

    public int PageSize
    {
        get;
        set;
    }

    public string PrimaryKey
    {
        get;
        set;
    }

    public string Sort
    {
        get;
        set;
    }

    public string Tables
    {
        get;
        set;
    }
}
/// <summary>
/// RowNumber分页
/// </summary>
public class PaginationByRowNumber
{
    /// <summary>
    /// 表名
    /// </summary>
    public string Table { get; set; }
    /// <summary>
    /// 排序 需要指明顺序 asc/desc
    /// </summary>
    public string Sort { get; set; }
    /// <summary>
    /// 当前页
    /// </summary>
    public int CurrentPage { get; set; }
    /// <summary>
    /// 行数
    /// </summary>
    public int PageSize { get; set; }
    /// <summary>
    /// 字段
    /// </summary>
    public string Fields { get; set; }
    /// <summary>
    /// 条件
    /// </summary>
    public string Filter { get; set; }
}
#endregion

#region 使用存储过程时缓存参数
public sealed class SqlHelperParameterCache
{
    private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
    private SqlHelperParameterCache()
    {
    }
    public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
    {
        if ((connectionString == null) || (connectionString.Length == 0))
        {
            throw new ArgumentNullException("connectionString");
        }
        if ((commandText == null) || (commandText.Length == 0))
        {
            throw new ArgumentNullException("commandText");
        }
        string str = connectionString + ":" + commandText;
        paramCache[str] = commandParameters;
    }
    private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
    {
        SqlParameter[] parameterArray = new SqlParameter[originalParameters.Length];
        int index = 0;
        int length = originalParameters.Length;
        while (index < length)
        {
            parameterArray[index] = (SqlParameter)((ICloneable)originalParameters[index]).Clone();
            index++;
        }
        return parameterArray;
    }

    private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null)
        {
            throw new ArgumentNullException("connection");
        }
        if ((spName == null) || (spName.Length == 0))
        {
            throw new ArgumentNullException("spName");
        }
        SqlCommand command = new SqlCommand(spName, connection);
        command.CommandType = CommandType.StoredProcedure;
        connection.Open();
        SqlCommandBuilder.DeriveParameters(command);
        connection.Close();
        if (!includeReturnValueParameter)
        {
            command.Parameters.RemoveAt(0);
        }
        SqlParameter[] array = new SqlParameter[command.Parameters.Count];
        command.Parameters.CopyTo(array, 0);
        foreach (SqlParameter parameter in array)
        {
            parameter.Value = DBNull.Value;
        }
        return array;
    }

    public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
    {
        if ((connectionString == null) || (connectionString.Length == 0))
        {
            throw new ArgumentNullException("connectionString");
        }
        if ((commandText == null) || (commandText.Length == 0))
        {
            throw new ArgumentNullException("commandText");
        }
        string str = connectionString + ":" + commandText;
        SqlParameter[] originalParameters = paramCache[str] as SqlParameter[];
        if (originalParameters == null)
        {
            return null;
        }
        return CloneParameters(originalParameters);
    }

    internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
    {
        return GetSpParameterSet(connection, spName, false);
    }

    public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
    {
        return GetSpParameterSet(connectionString, spName, false);
    }

    internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null)
        {
            throw new ArgumentNullException("connection");
        }
        using (SqlConnection connection2 = (SqlConnection)((ICloneable)connection).Clone())
        {
            return GetSpParameterSetInternal(connection2, spName, includeReturnValueParameter);
        }
    }

    public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
    {
        if ((connectionString == null) || (connectionString.Length == 0))
        {
            throw new ArgumentNullException("connectionString");
        }
        if ((spName == null) || (spName.Length == 0))
        {
            throw new ArgumentNullException("spName");
        }
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
        }
    }

    private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null)
        {
            throw new ArgumentNullException("connection");
        }
        if ((spName == null) || (spName.Length == 0))
        {
            throw new ArgumentNullException("spName");
        }
        string str = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
        SqlParameter[] originalParameters = paramCache[str] as SqlParameter[];
        if (originalParameters == null)
        {
            SqlParameter[] parameterArray2 = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
            paramCache[str] = parameterArray2;
            originalParameters = parameterArray2;
        }
        return CloneParameters(originalParameters);
    }
}
#endregion

/// <summary>
/// 创建SqlParameter
/// </summary>
public class CP
{
    public class ColumnInfo
    {
        public string ColName { get; set; }
        public SqlDbType ColType { get; set; }
        public int ColLength { get; set; }
        public bool IsPrimary { get; set; }
        public string Description { get; set; }
    }
    /// <summary>
    /// 读取用户表及每个表字段信息保存到字典对象，加入缓存
    /// </summary>
    /// <param name="specifyTable">指定表名，如果为空，则查询所有表</param>
    /// <param name="connStr">数据库连接串</param>
    /// <returns></returns>
    public static List<ColumnInfo> TC(string specifyTable, string connStr)
    {
        if (string.IsNullOrEmpty(specifyTable))
        {
            return null;
        }

        var vl = HttpRuntime.Cache.Get("utable_" + specifyTable);


        if (vl == null)
        {
            List<ColumnInfo> p = new List<ColumnInfo>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                StringBuilder sb = new StringBuilder(1000);
                sb.Append("SELECT a.name,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '1' else '0' end) isprimary,b.name [type],COLUMNPROPERTY(a.id,a.name,'PRECISION') as [length],isnull(g.value,'') as [description] FROM syscolumns a ");
                sb.Append("left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'");
                sb.Append("left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id where d.name =@tname order by a.id,a.colorder");
                conn.Open();
                SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
                cmd.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 30));
                cmd.Parameters[0].Value = specifyTable;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                DataTable dt = ds.Tables[0];
                int count = dt.Rows.Count;
                if (count > 0)
                {
                    for (int i = 0; i < count; i++)
                    {
                        ColumnInfo item = new ColumnInfo() { ColName = dt.Rows[i]["name"].ToString(), ColLength = int.Parse(dt.Rows[i]["length"].ToString()), ColType = StringToSqlType(dt.Rows[i]["type"].ToString()), IsPrimary = (dt.Rows[i]["isprimary"].ToString() == "1" ? true : false), Description = dt.Rows[i]["description"].ToString() };
                        p.Add(item);
                    }
                    HttpRuntime.Cache.Insert("utable_" + specifyTable, p, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 30, 0));
                }
            }
            return p;
        }
        else
        {
            return (List<ColumnInfo>)HttpRuntime.Cache["utable_" + specifyTable];
        }


    }

    /// <summary>
    /// 读取用户表及每个表字段信息保存到字典对象，加入缓存  只针对视图
    /// </summary>
    /// <param name="specifyTable">指定表名，如果为空，则查询所有表</param>
    /// <param name="connStr">数据库连接串</param>
    /// <returns></returns>
    public static List<ColumnInfo> TCV(string specifyTable, string connStr)
    {
        if (string.IsNullOrEmpty(specifyTable))
        {
            return null;
        }
        if (HttpRuntime.Cache["utable_" + specifyTable] == null)
        {
            List<ColumnInfo> p = new List<ColumnInfo>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                StringBuilder sb = new StringBuilder(1000);
                sb.Append("select c.name ,t.name as type ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as length ");
                sb.Append("from syscolumns c inner join systypes t on c.xusertype=t.xusertype ");
                sb.Append("where objectproperty(c.id,'IsView')=1 and c.id=object_id(@tname) ");
                conn.Open();
                SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
                cmd.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 30));
                cmd.Parameters[0].Value = specifyTable;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                DataTable dt = ds.Tables[0];
                int count = dt.Rows.Count;
                if (count > 0)
                {
                    for (int i = 0; i < count; i++)
                    {
                        ColumnInfo item = new ColumnInfo() { ColName = dt.Rows[i]["name"].ToString(), ColLength = int.Parse(dt.Rows[i]["length"].ToString()), ColType = StringToSqlType(dt.Rows[i]["type"].ToString()), IsPrimary = false, Description = "" };
                        p.Add(item);
                    }
                    HttpRuntime.Cache.Insert("utable_" + specifyTable, p, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 30, 0));
                }
            }
            return p;
        }
        else
        {
            return (List<ColumnInfo>)HttpRuntime.Cache["utable_" + specifyTable];
        }
    }

    //SqlServer数据库类型转换方法
    public static SqlDbType StringToSqlType(string String)
    {
        SqlDbType dbType = SqlDbType.Variant;//默认为Object
        switch (String)
        {
            case "int":
                dbType = SqlDbType.Int;
                break;
            case "varchar":
                dbType = SqlDbType.VarChar;
                break;
            case "bit":
                dbType = SqlDbType.Bit;
                break;
            case "datetime":
                dbType = SqlDbType.DateTime;
                break;
            case "decimal":
                dbType = SqlDbType.Decimal;
                break;
            case "float":
                dbType = SqlDbType.Float;
                break;
            case "image":
                dbType = SqlDbType.Image;
                break;
            case "money":
                dbType = SqlDbType.Money;
                break;
            case "ntext":
                dbType = SqlDbType.NText;
                break;
            case "nvarchar":
                dbType = SqlDbType.NVarChar;
                break;
            case "smalldatetime":
                dbType = SqlDbType.SmallDateTime;
                break;
            case "smallint":
                dbType = SqlDbType.SmallInt;
                break;
            case "text":
                dbType = SqlDbType.Text;
                break;
            case "bigint":
                dbType = SqlDbType.BigInt;
                break;
            case "binary":
                dbType = SqlDbType.Binary;
                break;
            case "char":
                dbType = SqlDbType.Char;
                break;
            case "nchar":
                dbType = SqlDbType.NChar;
                break;
            case "numeric":
                dbType = SqlDbType.Decimal;
                break;
            case "real":
                dbType = SqlDbType.Real;
                break;
            case "smallmoney":
                dbType = SqlDbType.SmallMoney;
                break;
            case "sql_variant":
                dbType = SqlDbType.Variant;
                break;
            case "timestamp":
                dbType = SqlDbType.Timestamp;
                break;
            case "tinyint":
                dbType = SqlDbType.TinyInt;
                break;
            case "uniqueidentifier":
                dbType = SqlDbType.UniqueIdentifier;
                break;
            case "varbinary":
                dbType = SqlDbType.VarBinary;
                break;
            case "xml":
                dbType = SqlDbType.Xml;
                break;
        }
        return dbType;
    }

    #region 建立一个参数
    /// <summary>
    /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数
    /// </summary>
    /// <typeparam name="item">表列信息集合</typeparam>
    /// <param name="property">属性</param>
    /// <param name="v">属性对应值</param>
    /// <param name="pd">参数方向</param>
    /// <returns>一个SQL参数，已经初始化</returns>
    public static SqlParameter cPa(ColumnInfo item, object v, ParameterDirection pd)
    {
        if (item == null)
        {
            return null;
        }
        SqlParameter sp = null;
        if (item != null)
        {
            sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);
            sp.Direction = pd;
            sp.Value = v;
        }
        return sp;
    }
    /// <summary>
    /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数
    /// </summary>
    /// <typeparam name="item">表列信息集合</typeparam>
    /// <param name="property">属性</param>
    /// <param name="v">属性对应值</param>
    /// <returns>一个SQL参数，已经初始化</returns>
    public static SqlParameter cPa(ColumnInfo item, object v)
    {
        if (item == null)
        {
            return null;
        }
        SqlParameter sp = null;
        if (item != null)
        {
            sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);
            sp.Direction = ParameterDirection.Input;
            sp.Value = v;
        }
        return sp;
    }
    #endregion

    /// <summary>
    /// Author:付裕
    /// Date: 2014-09-19
    /// Desc: DataTable与泛型转换类
    /// </summary>
    public class WorkCommon
    {
        /// <summary>
        /// DataTable Convert To List<T>
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="dt">数据集合</param>
        /// <returns>泛型集合</returns>
        public static List<T> ConvertTo<T>(DataTable dt) where T : new()
        {
            if (dt == null) return null;
            if (dt.Rows.Count <= 0) return null;

            List<T> list = new List<T>();
            try
            {
                List<string> columnsName = new List<string>();
                foreach (DataColumn dataColumn in dt.Columns)
                {
                    columnsName.Add(dataColumn.ColumnName);//得到所有的表头
                }
                list = dt.AsEnumerable().ToList().ConvertAll<T>(row => getObject<T>(row, columnsName));  //转换
                return list;
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 转换函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="row"></param>
        /// <param name="columnsName"></param>
        /// <returns></returns>
        private static T getObject<T>(DataRow row, List<string> columnsName) where T : new()
        {
            T obj = new T();
            try
            {
                string columnname = "";
                string value = "";
                PropertyInfo[] Properties = typeof(T).GetProperties();
                foreach (PropertyInfo objProperty in Properties)  //遍历T的属性
                {
                    columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower()); //寻找可以匹配的表头名称
                    if (!string.IsNullOrEmpty(columnname))
                    {
                        value = row[columnname].ToString();
                        if (!string.IsNullOrEmpty(value))
                        {
                            if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null) //存在匹配的表头
                            {
                                value = row[columnname].ToString().Replace("$", "").Replace(",", ""); //从dataRow中提取数据
                                objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null); //赋值操作
                            }
                            else
                            {
                                value = row[columnname].ToString().Replace("%", ""); //存在匹配的表头
                                objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);//赋值操作
                            }
                        }
                    }
                }
                return obj;
            }
            catch
            {
                return obj;
            }
        }

        /// <summary>
        /// 将泛型集合类转换成DataTable
        /// </summary>
        /// <typeparam name="T">集合项类型</typeparam>
        /// <param name="list">集合</param>
        /// <param name="propertyName">需要返回的列的列名</param>
        /// <returns>数据集(表)</returns>
        public static DataTable ListToDataTable<T>(IList<T> list, params string[] propertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (propertyName != null)
                propertyNameList.AddRange(propertyName);
            DataTable result = new DataTable();
            if (list != null && list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                            result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(list[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(list[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }

    }

}