【C#】通过反射生成sql

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【C#】通过反射生成sql，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含26570字，纯文字阅读大概需要38分钟。

内容图文

1、定义特性标志表或表字段

 public class ExtendAttribute
    {
        /// <summary>
        ///     表示在自动生成SQL语句时，不处理该字段
        /// </summary>
        [AttributeUsage(AttributeTargets.Property)]
        [Serializable]
        public class ExcludeFieldAttribute : Attribute
        {
            /// <summary>
            ///     获取元数据的特性
            /// </summary>
            /// <param name="item"></param>
            /// <returns></returns>
            public static ExcludeFieldAttribute GetAttribute(MemberInfo item)
            {
                var excludeFieldAttribute = (ExcludeFieldAttribute)GetCustomAttribute(item, typeof(ExcludeFieldAttribute));
                return excludeFieldAttribute;
            }
        }

        /// <summary>
        /// 表信息
        /// </summary>
        [AttributeUsage(AttributeTargets.Class)]
        [System.Serializable]
        public class TableInfoAttribute : Attribute
        {
            public TableInfoAttribute(string tableName)
            {
                TableName = tableName;
            }

            /// <summary>
            ///     数据库中表的名称
            /// </summary>
            public string TableName { get; set; }

            /// <summary>
            ///     获取元数据的特性
            /// </summary>
            /// <param name="item"></param>
            /// <returns></returns>
            public static TableInfoAttribute GetAttribute(Type item)
            {
                var excludeFieldAttribute = (TableInfoAttribute)GetCustomAttribute(item, typeof(TableInfoAttribute));
                return excludeFieldAttribute;
            }
        }

        /// <summary>
        ///     标记字段为自增长类型的字段
        /// </summary>
        [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
        [System.Serializable]
        public class IdentityAttribute : Attribute
        {
            public static IdentityAttribute GetAttribute(MemberInfo member)
            {
                return (IdentityAttribute)GetCustomAttribute(member, typeof(IdentityAttribute));
            }
        }

    }

2、实体类

[TableInfo("users")]
    public class Users 
    {
        [Identity]
        public int Id { get; set; }

        public Users()
        {
            this.Account = string.Empty;
            this.Password = string.Empty;
            this.Status = 0;//用户状态: -1失效，0正常，1禁用
            this.Type = 1;//普通用户
            this.CreateDate = this.UpdateDate = DateTime.Now;
            this.CreateUser = this.UpdateUser = -1;//-1为系统添加
        }

        /// <summary>
	    /// 用户名
	    /// </summary>
        public string Account { get; set; }

        /// <summary>
	    /// 
	    /// </summary>
        public string Password { get; set; }

        /// <summary>
	    /// 运维备用密码
	    /// </summary>
        public string RePassword { get; set; }

        /// <summary>
	    /// 用户类型:0超级管理员,1普通成员,2运维人员
	    /// </summary>
        public int Type { get; set; }

        /// <summary>
	    /// 用户状态 -1 冻结 0正常
	    /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime CreateDate { get; set; }


        public int CreateUser { get; set; }

        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime UpdateDate { get; set; }

        public int UpdateUser { get; set; }

        [ExcludeField]
        public string Test { get; set; }

    }

3、生成sql类

public class DatabaseSql
    {

        public DbProviderFactory _factory;
        private static DatabaseSql _dataSql = new DatabaseSql();
        public static DatabaseSql DataSql
        {
            get
            {
                return _dataSql;
            }
        }

        #region 字段
        /// <summary>
        ///     分页查询的外部查询语句
        ///     <para>索引0：实际查询语句</para>
        ///     <para>索引1：排序规则</para>
        ///     <para>索引2：start</para>
        ///     <para>索引3：end</para>
        /// </summary>
        private string _selectSqlString = @" SELECT  *
                                               FROM    ( SELECT    ROW_NUMBER() OVER (ORDER BY {1} ) AS RowIndex ,  *
                                                 FROM      ( {0} ) a
                                               ) v  WHERE v.RowIndex BETWEEN {2} AND {3} ";

        private string _deleteSqlString = @"DELETE FROM {0} ";
        private string _truncateSql = @"TRUNCATE  TABLE {0} ";
        private string _updateSqlString = @"UPDATE {0} SET {1} WHERE {2} ";
        private string _insertSqlString = @"INSERT INTO {0} ({1}) VALUES ({2});";
        private string _identitySqlString = @" SELECT @@Identity AS ID; ";
        private string _paramPrefix = @"@";
        public DbType _dbType { get; set; }
        #endregion

        /// <summary>
        /// 替换特殊函数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string Recombine(string sql)
        {
            //目前只选择sqlserver数据
            _dbType = DbType.SqlServer;
            switch (_dbType)
            {
                case DbType.SqlServer:
                    break;
                case DbType.Oracle:
                    sql = sql.Replace("@", _paramPrefix);
                    sql = sql.Replace("isnull(", "NVL(")
                             .Replace("ISNULL(", "NVL(")
                             .Replace("getDate()", "SYSDATE")
                             .Replace("getdate()", "SYSDATE")
                             .Replace("GETDATE()", "SYSDATE");

                    break;
                case DbType.MySql:
                    sql = sql.Replace("isnull(", "ifnull(")
                             .Replace("ISNULL(", "ifnull(")
                             .Replace("getDate()", "now()")
                             .Replace("getdate()", "now()")
                             .Replace("GETDATE()", "now()");
                    break;
            }
            return sql;
        }

        private dynamic GetTypeValue(object obj)
        {
            var typeName = obj.GetType().Name;
            if (typeName == "String" || typeName == "DateTime")
            {
                return $"‘{obj}‘";
            }
            else if (typeName == "Boolean")
            {
                return ((Boolean)obj) ? 0 : 1;
            }
            else
            {
                return obj;
            }
        }

        #region 删除
        /// <summary>
        /// 数据库截断
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public string TruncateToSql<T>()
        {
            Type type = typeof(T);
            TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
            string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
            string truncateSql = String.Format(_truncateSql, tableName);
            return Recombine(truncateSql);
        }

        /// <summary>
        /// 根据sql条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        public string DeleteToSql<T>(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                throw new Exception("删除条件不能为空");
            }
            if (!where.Trim().StartsWith("where", StringComparison.InvariantCultureIgnoreCase))
            {
                where = " WHERE " + where;
            }
            Type type = typeof(T);
            TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
            string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
            string deleteSql = string.Format(_deleteSqlString,tableName) + where;
            return Recombine(deleteSql);
        }

        public string DeleteToSql<T>(string keyColName, List<long> ids)
        {
            if (string.IsNullOrEmpty(keyColName))
            {
                throw new Exception("指定key列值不能为空");
            }
            if (ids == null || ids.Count <= 0)
            {
                throw new Exception("ids为null或者传参个数小于等于0！");
            }
            Type type = typeof(T);
            TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
            string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
            var idstr = string.Concat(ids.Select(p => p + ",").ToList()).TrimEnd(‘,‘);
            string where = $"where {keyColName} in ({idstr})";
            string deleteSql = string.Format(_deleteSqlString, tableName) + where;
            return Recombine(deleteSql);
        }
        #endregion

        #region 添加

        
        /// <summary>
        /// 获取添加sql
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="paramCount">批量插入时，传递参数值避免重复</param>
        /// <param name="isParams"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public string AddToSql<T>(T entity,int paramCount, bool isParams,List<DbParameter> parameters, DbType dbType = DbType.SqlServer)
        {
            if (entity == null) throw new Exception("类型为空！");
            Type type = entity.GetType();
            TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
            string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
            StringBuilder key = new StringBuilder();
            StringBuilder value = new StringBuilder();
            StringBuilder sql = new StringBuilder();
            bool pk = false;
            int count = 0;
            foreach (PropertyInfo item in type.GetProperties())
            {
                if (ExcludeFieldAttribute.GetAttribute(item) != null) continue;//自增属性不处理
                if (IdentityAttribute.GetAttribute(item) != null)
                {
                    pk = true;
                    continue;
                }//自增长列不处理
                var values = item.GetValue(entity, null);

                if (values == null || values == DBNull.Value) continue;
                if (values == null) continue;
                if (values.ToString().StartsWith("0001")) continue;//空日期
                //if(RequiredAttribute.GetCustomAttribute(item.Module,item.GetType())==null)
                //    throw new Exception("此列属性为必填项");
                if (count != 0)
                {
                    key.Append(‘,‘);
                    value.Append(‘,‘);
                }
                key.Append(item.Name);
                if (!isParams)
                {
                    value.Append(GetTypeValue(values));
                }
                else
                {
                    value.Append(‘@‘ + item.Name + paramCount);
                    if (parameters == null) new Exception("传递sql参数集合为空！");
                    if (_factory==null) throw new Exception("_factory链接数据库类型失败！");
                    DbParameter p = _factory.CreateParameter();
                    p.ParameterName = "@" + item.Name + paramCount;
                    p.Value = item.GetValue(entity);
                    p.DbType = (System.Data.DbType)Enum.Parse(typeof(System.Data.DbType), values.GetType().Name);
                    parameters.Add(p);
                }
                count++;
            }
            sql.Append(string.Format(_insertSqlString, tableName, key, value));
            if (pk)
            {
                switch (dbType)
                {
                    case DbType.Oracle:
                        break;
                    case DbType.SqlServer:
                        sql.AppendFormat(_identitySqlString);
                        break;
                    case DbType.MySql:
                        sql.AppendFormat(";");
                        sql.AppendLine(_identitySqlString);
                        break;
                }
            }
            return Recombine(sql.ToString());
        }

        /// <summary>
        /// 添加一组对象
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="dbType"></param>
        /// <param name="isValue"></param>
        /// <returns></returns>
        public string AddRangeToSql<T>(ICollection<T> entities, bool isParams, List<DbParameter> parameters, DbType dbType = DbType.SqlServer)
        {
            if (entities == null)
            {
                throw new ArgumentNullException("entities", "列表为空");
            }
            StringBuilder insert = new StringBuilder();
            int paramCount = 0;
            foreach (T entity in entities)
            {
                if (dbType == DbType.Oracle) insert.Append("BEGIN");
                if (entity == null) continue;
                //dic.Add(new KeyValuePair<dynamic, string> (entity, AddToSql(entity, dbType)));
                insert.Append(AddToSql<T>(entity, paramCount, isParams, parameters,dbType));
                if (dbType == DbType.Oracle) insert.Append("END");
                paramCount++;
            }
            return Recombine(insert.ToString());
        }

        #endregion

        #region 更新
        /// <summary>
        ///     更新指定表的指定字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="fieldName">字段名称</param>
        /// <param name="value">字段值</param>
        /// <param name="where">更新条件</param>
        /// <returns></returns>
        public string UpdateField(string tableName, string fieldName, string value, string where)
        {
            if (!where.TrimStart().StartsWith("where", StringComparison.OrdinalIgnoreCase))
            {
                where = " WHERE " + where;
            }
            value = "‘" + value + "‘";
            string updateSql = string.Format("UPDATE {0} SET {1}={2} {3}", tableName, fieldName, value, where);
            return updateSql;
        }

        /// <summary>
        ///     更新字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fieldvalues">字段名称和字段值的键值对集合</param>
        /// <param name="where">更新条件</param>
        /// <returns></returns>
        public string UpdateFields(string tableName, List<KeyValuePair<string, string>> fieldvalues, string where)
        {
            if (!string.IsNullOrEmpty(where))
            {
                if (!where.TrimStart().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                {
                    where = " WHERE " + where;
                }
            }
            StringBuilder updateSql = new StringBuilder().AppendFormat("UPDATE {0} SET ", tableName);
            for (int i = 0; i < fieldvalues.Count; i++)
            {
                string value = fieldvalues[i].Value == null ? "NULL" : string.Format("{0}", fieldvalues[i].Value);

                updateSql.AppendFormat("{0}=‘{1}‘", fieldvalues[i].Key, value);

                if (i < fieldvalues.Count - 1)
                    updateSql.Append(",");
                //updateSql.AppendLine();
            }
            updateSql.Append(where);
            return updateSql.ToString();
        }

        /// <summary>
        /// 更新sql
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="isValue"></param>
        /// <returns></returns>
        public string UpdateToSql<T>(dynamic entity,string where, bool isParams, List<DbParameter> parameters, DbType dbType = DbType.SqlServer)
        {
            if (entity == null) throw new Exception(entity.GetType().Name + "类型为空！");
            Type type = entity.GetType();//更新参数
            Type obj = typeof(T);
            TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(obj);
            string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : obj.Name;
            StringBuilder set = new StringBuilder();
            int count = 0;
            foreach (PropertyInfo item in type.GetProperties())
            {
                if (ExcludeFieldAttribute.GetAttribute(item) != null) continue;//自增属性不处理
                if (IdentityAttribute.GetAttribute(item) != null) continue;
                if (count != 0) set.Append(",");
                object values = item.GetValue(entity, null);
                if (!isParams)
                {
                    set.AppendFormat("{0} = ‘{1}‘", item.Name, values);
                }
                else
                {
                    if (values == null) continue;
                    if (values.ToString().StartsWith("0001")) continue;//空日期
                    set.AppendFormat("{0} = @{0}", item.Name);

                    if (parameters == null) new Exception("传递sql参数集合为空！");
                    if (_factory == null) throw new Exception("_factory链接数据库类型失败！");

                    DbParameter p = _factory.CreateParameter();
                    p.ParameterName = "@" + item.Name;
                    p.Value = item.GetValue(entity);
                    p.DbType = (System.Data.DbType)Enum.Parse(typeof(System.Data.DbType), values.GetType().Name);
                    parameters.Add(p);
                }
                count++;
            }
            return Recombine(string.Format(_updateSqlString, tableName, set, where));
        }

        /// <summary>
        /// 更新sql
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="where"></param>
        /// <param name="paramCount"></param>
        /// <param name="isParams"></param>
        /// <returns></returns>
        public string UpdateToSql<T>(dynamic entity, List<KeyValuePair<string, string>> fieldvalues, bool isParams, List<DbParameter> parameters, DbType dbType = DbType.SqlServer)
        {
            StringBuilder where = new StringBuilder("1=1");
            if (!isParams)
            {
                where.Append(string.Concat(fieldvalues.Select(p => $" and {p.Key}=‘{p.Value}‘")));
            }
            else
            {
                foreach(var item in fieldvalues)
                {
                    if (item.Value == null) continue;
                    where.Append($" and {item.Key}=@{item.Key + 000}");//防止与set一致名称几率，顾加000
                    DbParameter p = _factory.CreateParameter();
                    p.ParameterName = "@" + item.Key + 000;
                    p.Value = item.Value;
                    p.DbType = (System.Data.DbType)Enum.Parse(typeof(System.Data.DbType), item.GetType().Name);
                    parameters.Add(p);
                }
            }
            return UpdateToSql<T>(entity,where.ToString(),isParams, parameters);
        }
        #endregion

        #region 查询
        /// <summary>
        /// 单表查询
        /// </summary>
        /// <param name="type"></param>
        /// <param name="where"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public string SelectToSql<T>(string where, bool IsCount = false, DbType dbType = DbType.SqlServer)
        {
            if (!string.IsNullOrEmpty(where))
            {
                if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                {
                    where = "Where " + where;
                }
            }
            StringBuilder sql = new StringBuilder("SELECT ");//不考虑多表操作

            //添加的sql语句
            Type type = typeof(T);
            PropertyInfo[] infos = type.GetProperties();
            if (IsCount)
            {
                sql.Append("COUNT(1) ");
            }
            else
            {
                foreach (PropertyInfo pro in infos)
                {
                    if (ExcludeFieldAttribute.GetAttribute(pro) != null) continue;//自定义扩展属性不处理
                    sql.Append(pro.Name + ‘,‘);
                }
                sql = sql.Remove(sql.Length - 1, 1);
            }

            TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
            string tableName = tableInfo == null ? type.Name : tableInfo.TableName;
            //sql.AppendLine();
            sql.AppendFormat(" FROM {0} ", tableName);
            sql.Append(where);
            return Recombine(sql.ToString());
        }

        /// <summary>
        /// 分页sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page"></param>
        /// <param name="strWhere"></param>
        /// <param name="orderBy"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public string SelectSql<T>(PageHelp page, string strWhere = "1=1", string orderBy = "", DbType dbType = DbType.SqlServer)
        {
            if (string.IsNullOrEmpty(strWhere))
                strWhere = "1=1";
            Type type = typeof(T);
            PropertyInfo pk = type.GetProperties().FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
            if (string.IsNullOrEmpty(orderBy))
            {
                if (pk != null)
                    orderBy = pk.Name;
                else throw new Exception("未设置排序字段");
            }
            if (orderBy.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
            {
                orderBy = orderBy.ToLower().Replace("order by", " ");
            }
            string dataSql = string.Format(_selectSqlString
                , SelectToSql<T>(strWhere)
                , orderBy
                , page.LowerLimit
                , page.UpperLimit);

            return Recombine(dataSql);
        }

        /// <summary>
        /// 根据主键进行查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public string SqlectByIdToSql<T>(long id, DbType dbType = DbType.SqlServer)
        {
            Type t = typeof(T);
            PropertyInfo info = t.GetProperties().First(p => IdentityAttribute.GetAttribute(p) != null);
            if (info != null)
            {
                string str = info.Name + "=" + id;
                return SelectToSql<T>(str);
            }
            throw new Exception("No Find Identity");
        }

        /// <summary>
        /// 根据主键进行查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public string SqlectByIdListToSql<T>(List<long> ids, DbType dbType = DbType.SqlServer)
        {
            Type t = typeof(T);
            PropertyInfo info = t.GetProperties().First(p => IdentityAttribute.GetAttribute(p) != null);
            if (info != null)
            {
                var idstr = string.Concat(ids.Select(p => p + ",").ToList()).TrimEnd(‘,‘);
                string where = $"{info.Name} in ({idstr})";
                return SelectToSql<T>(where);
            }
            throw new Exception("No Find Identity");
        }

        #endregion
    }

4、ADO.NET 执行sql

public class DataBase
    {
        #region 字段
        ///数据库的命名空间名
        private string _connProviderName;

        /// 连接的字符串
        private readonly string _connString;

        ///各个数据库库的抽象工厂
        private DbProviderFactory _factory;

        ///生产Sql语句的对象
        DatabaseSql _dataSql = DatabaseSql.DataSql;

        ///数据库类型
        private DbType _dbType = DbType.SqlServer;

        ///委托
        private delegate dynamic ExecuteTranc(IDbCommand comm);
        #endregion

        #region 构造函数
        public DataBase(string configKey)
        {
            _connProviderName = ConfigHelper.GetConnStringProviderName(configKey);
            if (!string.IsNullOrEmpty(_connProviderName))
            {
                _factory = DbProviderFactories.GetFactory(_connProviderName);
                _dataSql._factory = this._factory;
                _connString = ConfigHelper.GetConnStringStr(configKey);
            }
            else
            {
                throw new Exception("connectString中providerName不存在");
            }
        }
        #endregion

        #region 获取ADO对象
        /// <summary>
        /// 获取链接数据对象
        /// </summary>
        /// <returns>返回Connection对象</returns>
        private IDbConnection GetConnection()
        {
            IDbConnection connection = _factory.CreateConnection();
            connection.ConnectionString = _connString;
            connection.Open();
            return connection;
        }

        /// <summary>
        /// 获取Command对象
        /// </summary>
        /// <param name="sqlString">传送完整的sql语句</param>
        /// <param name="commandType">执行的是sql语句还是，存储过程</param>
        /// <param name="commandTimeout">链接数据的时间限制</param>
        /// <returns>Command对象</returns>
        private IDbCommand GetCommand(string sqlString, CommandType commandType = CommandType.Text,
            int? commandTimeout = 360)
        {
            IDbCommand command = _factory.CreateCommand();
            command.Connection = GetConnection();
            command.CommandText = sqlString;
            command.CommandType = commandType;
            command.CommandTimeout = commandTimeout.Value;
            return command;
        }
        #endregion

        #region 执行sql

        #region 执行sql
        /// <summary>
        /// 执行command命令
        /// </summary>
        /// <param name="etFunc">调用方法名</param>
        /// <param name="sqlString">传送完整的sql语句</param>
        /// <param name="commandType">执行的是sql语句还是，存储过程</param>
        /// <param name="commandTimeout">链接数据的时间限制</param>
        /// <returns></returns>
        private dynamic ExTranc(ExecuteTranc executeFunc, string sqlString, List<DbParameter> parameters = null, bool IsOpenTran = true, CommandType commandType = CommandType.Text, int? commandTimeout = 60)
        {
            using (IDbCommand comm = GetCommand(sqlString, commandType, commandTimeout))
            {
                using (comm.Connection)
                {
                    dynamic result;
                    IDbTransaction tran = null;
                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (var item in parameters)
                        {
                            comm.Parameters.Add(item);
                        }
                    }
                    if (IsOpenTran)
                    {
                        tran = comm.Connection.BeginTransaction();
                    }
                    try
                    {
                        comm.Transaction = tran;
                        result = executeFunc(comm);
                        if (tran != null) tran.Commit();
                        return result;
                    }
                    catch (Exception ex)
                    {
                        MyLogHelper.Erorr(ex.Message);
                        try
                        {
                            if (tran != null) tran.Rollback();
                        }
                        catch (Exception)
                        {
                            string exce = "发生错误，SQL语句为：" + sqlString;
                            MyLogHelper.Erorr(exce + "\r\n" + ex.Message);
                            throw new Exception(exce, ex);
                        }
                    }
                    return 0;
                }
            }
        }

        #endregion

        #region 单行单列
        /// <summary>
        /// 传送sql语句，执行增删改操作
        /// </summary>
        /// <param name="sql">完整sql语句</param>
        /// <returns>受影响行数</returns>
        public int ExecuteSql(string sql)
        {
            return ExTranc(ExecuteNonQuery, sql);
        }

        //public int ExecuteSql(string sql,object obj)
        //{
        //    List<DbParameter> ps = new List<DbParameter>();
        //    //反射对象进行赋值到参数
        //    return ExTranc(ExecuteNonQuery, sql,ps);
        //}

        /// <summary>
        /// 返回首行首列数据
        /// </summary>
        /// <param name="sql">完整sql语句</param>
        /// <returns>受影响行数</returns>
        public int ExecuteScalarSql(string sql)
        {
            return ExTranc(ExecuteScalar, sql);
        }

        /// <summary>
        /// 返回首行首列数据
        /// </summary>
        /// <param name="comm">Command对象</param>
        /// <param name="obj"></param>
        private dynamic ExecuteScalar(IDbCommand comm)
        {
            object obj = comm.ExecuteScalar();
            dynamic dy = obj;
            if (obj.GetType() == typeof(int)) dy = (int)obj;
            if (obj.GetType() == typeof(long)) dy = (long)obj;
            if (obj.GetType() == typeof(decimal)) dy = (decimal)obj;
            if (obj.GetType() == typeof(bool)) dy = (bool)obj;
            if (obj.GetType() == typeof(DateTime)) dy = (DateTime)obj;
            if (obj.GetType() == typeof(string)) dy = obj.ToString();
            return dy;
        }

        /// <summary>
        /// 执行增删改语句命名
        /// </summary>
        /// <param name="comm">Command对象</param>
        /// <param name="obj"></param>
        private dynamic ExecuteNonQuery(IDbCommand comm)
        {
            return comm.ExecuteNonQuery();
        }
        #endregion

        #region 删除
        /// <summary>
        /// 指定字段批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyColName"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int DelectEntity<T>(string keyColName, List<long> ids)
        {
            return ExTranc(ExecuteNonQuery, _dataSql.DeleteToSql<T>(keyColName, ids));
        }
        /// <summary>
        /// 删除实体对象
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="where">删除sql条件</param>
        /// <returns>受影响行数</returns>
        public int DelectEntity<T>(string where)
        {
            return ExTranc(ExecuteNonQuery, _dataSql.DeleteToSql<T>(where));
        }

        /// <summary>
        /// 删除全部数据，自增列重新排序
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <returns>受影响行数</returns>
        public int TruncateTable<T>()
        {
            return ExTranc(ExecuteNonQuery, _dataSql.TruncateToSql<T>());
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加单个对象
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns>受影响行数</returns>
        public int AddEntity<T>(T entity)
        {
            return ExTranc(ExecuteNonQuery, _dataSql.AddToSql<T>(entity, 0, false, null, _dbType));
        }

        public int AddEntity<T>(T entity, bool isParams)
        {
            List<DbParameter> parameters = new List<DbParameter>();
            string insertSql = _dataSql.AddToSql(entity, 0, isParams, parameters, this._dbType);
            return ExTranc(ExecuteNonQuery, insertSql, parameters);
        }

        /// <summary>
        /// 添加一组对象
        /// </summary>
        /// <param name="entities">添加集合</param>
        /// <returns>受影响行数</returns>
        public int AddEntities<T>(ICollection<T> entities)
        {
            //List<KeyValuePair<dynamic,string>> dic = new List<KeyValuePair<dynamic,string>>();
            string insertSql = _dataSql.AddRangeToSql(entities, false, null, _dbType);
            //foreach(KeyValuePair<dynamic,string> k in dic){
            //    Execute(k.Value, k.Key,CommandType.Text);
            //    count++;
            //}
            return ExTranc(ExecuteNonQuery, insertSql);
        }

        public int AddEntities<T>(ICollection<T> entities, bool isParams)
        {
            if (entities!=null && entities.Count > 1000)
            {
                MyLogHelper.Erorr($"{typeof(T).Name}插入行数超过1000条");
                throw new Exception("插入行数超过1000条");
            }
            List<DbParameter> parameters = new List<DbParameter>();
            string insertSql = _dataSql.AddRangeToSql(entities, isParams, parameters, this._dbType);
            return ExTranc(ExecuteNonQuery, insertSql, parameters);
        }
        #endregion

        #region 更新
        /// <summary>
        /// 更新单个对象
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="primaryKeyField"></param>
        /// <returns></returns>
        public int UpdateEntity<T>(object entity, string where)
        {
            return ExTranc(ExecuteNonQuery, _dataSql.UpdateToSql<T>(entity, where, false, null, _dbType));
        }

        public int UpdateEntity<T>(object entity, string where, bool isParams)
        {
            List<DbParameter> parameters = new List<DbParameter>();
            string updateSql = _dataSql.UpdateToSql<T>(entity, where, isParams, parameters, _dbType);
            MyLogHelper.Warn("测试sql是否执行："+ updateSql);

            return ExTranc(ExecuteNonQuery, updateSql, parameters);
        }

        ///// <summary>
        ///// 更新一组对象
        ///// </summary>
        ///// <param name="entity"></param>
        ///// <param name="primaryKeyField"></param>
        ///// <returns></returns>
        public int UpdateEntity<T>(object entity, List<KeyValuePair<string, string>> fieldvalues)
        {
            return ExTranc(ExecuteNonQuery, _dataSql.UpdateToSql<T>(entity, fieldvalues, false, null, _dbType));
        }

        public int UpdateEntity<T>(object entity, List<KeyValuePair<string, string>> fieldvalues, bool isParams)
        {
            List<DbParameter> parameters = new List<DbParameter>();
            string updateSql = _dataSql.UpdateToSql<T>(entity, fieldvalues, isParams, parameters, _dbType);
            return ExTranc(ExecuteNonQuery, updateSql, parameters);
        }

        public int UpdateField(string tableName, string fieldName, string value, string where)
        {
            return ExTranc(ExecuteNonQuery, _dataSql.UpdateField(tableName, fieldName, value, where));
        }

        public int UpdateFields(string tableName, List<KeyValuePair<string, string>> fieldvalues, string where)
        {
            return ExTranc(ExecuteNonQuery, _dataSql.UpdateFields(tableName, fieldvalues, where));
        }
        #endregion

        #region 查询
        /// <summary>
        /// 获取对象的集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> GetListEntity<T>(string sqlString, CommandType commandType = CommandType.Text)
        {
            List<T> result = new List<T>();
            using (IDbCommand comm = GetCommand(_dataSql.Recombine(sqlString), commandType))
            {
                using (comm.Connection)
                {
                    using (IDataReader reader = comm.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result.Add(GetEntity<T>(reader));
                        }
                    }
                }
            }

            return result;
        }

        public List<T> GetAllEntity<T>(CommandType commandType = CommandType.Text)
        {
            return GetListEntity<T>(_dataSql.SelectToSql<T>(null), commandType);
        }

        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public T GetEntity<T>(string sql, object entity = null)
        {
            List<T> list = GetListEntity<T>(sql);
            T result = default(T);
            if (list != null && list.Count > 0)
            {
                result = list.FirstOrDefault<T>();
            }
            return result;
        }

        private T GetEntity<T>(IDataReader dr)
        {
            T t = Activator.CreateInstance<T>();
            string name = "";
            for (int i = 0; i < dr.FieldCount; i++)
            {
                if (!dr[i].Equals(DBNull.Value))
                {
                    name = dr.GetName(i).Substring(0, 1).ToUpper() + dr.GetName(i).Substring(1);
                    if (typeof(T).GetProperty(name) != null)
                        typeof(T).GetProperty(name).SetValue(t, dr[i], null);
                }
            }
            return t;
        }

        public T GetEntityByID<T>(long id)
        {
            string sql = _dataSql.SqlectByIdToSql<T>(id);
            List<T> list = GetListEntity<T>(sql, CommandType.Text);
            T result = default(T);
            if (list != null && list.Count > 0)
            {
                result = list.FirstOrDefault<T>();
            }
            return result;
        }

        public List<T> GetEntityByIDs<T>(List<long> ids)
        {
            string sql = _dataSql.SqlectByIdListToSql<T>(ids, _dbType);
            return GetListEntity<T>(sql);
        }
        #endregion

        #endregion

        #region MyRegion
        public static void SqlBulkCopyInsert(string conStr, string strTableName, DataTable dtData)
        {
            try
            {
                using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(conStr))//引用SqlBulkCopy  
                {
                    sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名  
                    sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;//有几行数据  
                    sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库  
                    sqlRevdBulkCopy.Close();//关闭连接  
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

    }

【C#】通过反射生成sql

标签：equals   select   public   nec   nta   参数   特殊   index   att   

本文系统来源：https://www.cnblogs.com/Seven77yixuan/p/10915365.html

内容总结

以上是互联网集市为您收集整理的【C#】通过反射生成sql全部内容，希望文章能够帮你解决【C#】通过反射生成sql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端