Beispiel #1
0
        /// <summary>
        /// 根据SQL获取T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public T GetSingle <T>(string sql, params SqlParameter[] pars)
        {
            var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), GetReader(sql, pars), null).Single();

            return(reval);
        }
Beispiel #2
0
 /// <summary>
 /// 获取DataTable
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
 /// <returns></returns>
 public DataTable GetDataTable(string sql, object pars)
 {
     return(GetDataTable(sql, SqlSugarTool.GetParameters(pars)));
 }
 public object GetScalar(string sql, object pars)
 {
     return(GetScalar(sql, SqlSugarTool.GetParameters(pars)));
 }
        private bool SqlBulkCopy <T>(IEnumerable <T> entities) where T : class
        {
            if (entities == null)
            {
                return(false);
            }
            ;

            Type   type     = typeof(T);
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            string pkName        = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
            var    identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);
            var    isIdentity    = identityNames != null && identityNames.Count > 0;
            var    columnNames   = SqlSugarTool.GetColumnsByTableName(this, typeName);

            if (isIdentity)
            {
                columnNames = columnNames.Where(c => !identityNames.Any(it => it.Value == c)).ToList();//去掉自添列
            }
            StringBuilder sbSql = new StringBuilder("INSERT INTO ");

            sbSql.AppendLine(typeName);
            sbSql.AppendFormat("({0})", string.Join(",", columnNames.Select(it => "[" + it + "]")));

            //属性缓存
            string cachePropertiesKey     = "db." + type.FullName + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props = null;
            if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
            {
                props = cachePropertiesManager[cachePropertiesKey];
            }
            else
            {
                props = type.GetProperties();
                cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
            }
            foreach (var entity in entities)
            {
                sbSql.AppendLine("SELECT ");
                foreach (var name in columnNames)
                {
                    var  isLastName = name == columnNames.Last();
                    var  prop       = props.Single(it => it.Name == name);
                    var  objValue   = prop.GetValue(entity, null);
                    bool isNullable = false;
                    var  underType  = SqlSugarTool.GetUnderType(prop, ref isNullable);
                    if (objValue == null)
                    {
                        objValue = "NULL";
                    }
                    else if (underType == SqlSugarTool.DateType)
                    {
                        objValue = "'" + objValue.ToString() + "'";
                    }
                    else if (underType == SqlSugarTool.BoolType)
                    {
                        objValue = Convert.ToBoolean(objValue) ? 1 : 0;
                    }
                    else if (underType == SqlSugarTool.StringType)
                    {
                        //string参数需要处理注入 (因为SqlParameter参数上限为2100所以无法使用参数化)
                        objValue = "'" + objValue.ToString().ToSqlFilter() + "'";
                    }
                    else
                    {
                        objValue = "'" + objValue.ToString() + "'";
                    }

                    sbSql.Append(objValue + (isLastName ? "" : ","));
                }
                var isLastEntity = entities.Last() == entity;
                if (!isLastEntity)
                {
                    sbSql.AppendLine(" UNION ALL ");
                }
            }
            var reval = base.ExecuteCommand(sbSql.ToString());

            sbSql = null;
            return(reval > 0);
        }
Beispiel #5
0
        /// <summary>
        /// 将实体对象转换成SqlParameter[]
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="pis"></param>
        /// <returns></returns>
        public static SqlParameter[] GetParameters(object obj, PropertyInfo [] pis = null)
        {
            List <SqlParameter> listParams = new List <SqlParameter>();

            if (obj != null)
            {
                var type  = obj.GetType();
                var isDic = type.IsIn(SqlSugarTool.DicArraySO, SqlSugarTool.DicArraySS);
                if (isDic)
                {
                    if (type == SqlSugarTool.DicArraySO)
                    {
                        var newObj = (Dictionary <string, object>)obj;
                        var pars   = newObj.Select(it => new SqlParameter("@" + it.Key, it.Value));
                        foreach (var par in pars)
                        {
                            SetParSize(par);
                        }
                        listParams.AddRange(pars);
                    }
                    else
                    {
                        var newObj = (Dictionary <string, string>)obj;
                        var pars   = newObj.Select(it => new SqlParameter("@" + it.Key, it.Value));
                        foreach (var par in pars)
                        {
                            SetParSize(par);
                        }
                        listParams.AddRange(pars);;
                    }
                }
                else
                {
                    PropertyInfo[] propertiesObj = null;
                    if (pis != null)
                    {
                        propertiesObj = pis;
                    }
                    else
                    {
                        propertiesObj = type.GetProperties();
                    }
                    string replaceGuid = Guid.NewGuid().ToString();
                    foreach (PropertyInfo r in propertiesObj)
                    {
                        var value = r.GetValue(obj, null);
                        if (r.PropertyType.IsEnum)
                        {
                            value = value.ObjToInt();
                        }
                        if (value == null || value.Equals(DateTime.MinValue))
                        {
                            value = DBNull.Value;
                        }
                        if (r.Name.ToLower().Contains("hierarchyid"))
                        {
                            var par = new SqlParameter("@" + r.Name, SqlDbType.Udt);
                            par.UdtTypeName = "HIERARCHYID";
                            par.Value       = value;
                            listParams.Add(par);
                        }
                        else
                        {
                            var par = new SqlParameter("@" + r.Name, value);
                            SetParSize(par);
                            if (value == DBNull.Value)
                            {//防止文件类型报错
                                SqlSugarTool.SetSqlDbType(r, par);
                            }
                            listParams.Add(par);
                        }
                    }
                }
            }
            return(listParams.ToArray());
        }
 /// <summary>
 ///获取没有符号的参数名称
 /// </summary>
 /// <param name="name"></param>
 /// <returns></returns>
 internal static string GetSqlParameterNameNoParSymbol(this string name)
 {
     return(SqlSugarTool.GetSqlParameterNameNoParSymbol(name));
 }
Beispiel #7
0
        /// <summary>
        /// 根据SQL获取T的集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public virtual List <T> GetList <T>(string sql, params SqlParameter[] pars)
        {
            var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), GetReader(sql, pars), null);

            return(reval);
        }
Beispiel #8
0
        /// <summary>
        /// 插入
        /// 使用说明:sqlSugar.Insert(entity);
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">插入对象</param>
        /// <param name="isIdentity">主键是否为自增长,true可以不填,false必填</param>
        /// <returns></returns>
        public object Insert <T>(T entity, bool isIdentity = true) where T : class
        {
            Type   type     = entity.GetType();
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);

            StringBuilder       sbInsertSql = new StringBuilder();
            List <SqlParameter> pars        = new List <SqlParameter>();
            var primaryKeyName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
            //sql语句缓存
            string cacheSqlKey     = "db.Insert." + typeName;
            var    cacheSqlManager = CacheManager <StringBuilder> .GetInstance();

            //属性缓存
            string cachePropertiesKey     = "db." + typeName + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props = null;
            if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
            {
                props = cachePropertiesManager[cachePropertiesKey];
            }
            else
            {
                props = type.GetProperties();
                cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
            }
            var isContainCacheSqlKey = cacheSqlManager.ContainsKey(cacheSqlKey);

            if (isContainCacheSqlKey)
            {
                sbInsertSql = cacheSqlManager[cacheSqlKey];
            }
            else
            {
                //2.获得实体的属性集合


                //实例化一个StringBuilder做字符串的拼接


                sbInsertSql.Append("insert into " + typeName + " (");

                //3.遍历实体的属性集合
                foreach (PropertyInfo prop in props)
                {
                    //EntityState,@EntityKey
                    if (isIdentity == false || (isIdentity && prop.Name != primaryKeyName))
                    {
                        //4.将属性的名字加入到字符串中
                        sbInsertSql.Append(prop.Name + ",");
                    }
                }
                //**去掉最后一个逗号
                sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
                sbInsertSql.Append(" ) values(");
            }

            //5.再次遍历,形成参数列表"(@xx,@xx@xx)"的形式
            foreach (PropertyInfo prop in props)
            {
                //EntityState,@EntityKey
                if (isIdentity == false || (isIdentity && prop.Name != primaryKeyName))
                {
                    if (!cacheSqlManager.ContainsKey(cacheSqlKey))
                    {
                        sbInsertSql.Append("@" + prop.Name + ",");
                    }
                    object val = prop.GetValue(entity, null);
                    if (val == null)
                    {
                        val = DBNull.Value;
                    }
                    var par = new SqlParameter("@" + prop.Name, val);
                    if (par.SqlDbType == SqlDbType.Udt)
                    {
                        par.UdtTypeName = "HIERARCHYID";
                    }
                    pars.Add(par);
                }
            }
            if (!isContainCacheSqlKey)
            {
                //**去掉最后一个逗号
                sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
                if (isIdentity == false)
                {
                    sbInsertSql.Append(");select 'true';");
                }
                else
                {
                    sbInsertSql.Append(");select @@identity;");
                }
                cacheSqlManager.Add(cacheSqlKey, sbInsertSql, cacheSqlManager.Day);
            }
            var sql = sbInsertSql.ToString();

            try
            {
                var lastInsertRowId = GetScalar(sql, pars.ToArray());
                return(lastInsertRowId);
            }
            catch (Exception ex)
            {
                throw new Exception("sql:" + sql + "\n" + ex.Message);
            }
        }
        /// <summary>
        /// 插入
        /// 使用说明:sqlSugar.Insert(entity);
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">插入对象</param>
        /// <param name="isIdentity">该属性已经作废可以不填,主键是否为自增长,true可以不填,false必填</param>
        /// <returns></returns>
        public object Insert <T>(T entity, bool isIdentity = true) where T : class
        {
            Type   type     = entity.GetType();
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);

            StringBuilder       sbInsertSql = new StringBuilder();
            List <SqlParameter> pars        = new List <SqlParameter>();
            var identities = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);

            isIdentity = identities != null && identities.Count > 0;
            //sql语句缓存
            string cacheSqlKey     = "db.Insert." + typeName;
            var    cacheSqlManager = CacheManager <StringBuilder> .GetInstance();

            //属性缓存
            string cachePropertiesKey     = "db." + typeName + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props = null;
            if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
            {
                props = cachePropertiesManager[cachePropertiesKey];
            }
            else
            {
                props = type.GetTypeInfo().GetProperties();
                cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
            }
            var isContainCacheSqlKey = cacheSqlManager.ContainsKey(cacheSqlKey);

            if (isContainCacheSqlKey)
            {
                sbInsertSql = cacheSqlManager[cacheSqlKey];
            }
            else
            {
                //2.获得实体的属性集合


                //实例化一个StringBuilder做字符串的拼接


                sbInsertSql.Append("insert into " + typeName + " (");

                //3.遍历实体的属性集合
                foreach (PropertyInfo prop in props)
                {
                    //EntityState,@EntityKey
                    if (!isIdentity || identities.Any(it => it.Value.ToLower() != prop.Name.ToLower()))
                    {
                        //4.将属性的名字加入到字符串中
                        sbInsertSql.Append("[" + prop.Name + "],");
                    }
                }
                //**去掉最后一个逗号
                sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
                sbInsertSql.Append(" ) values(");
            }

            //5.再次遍历,形成参数列表"(@xx,@xx@xx)"的形式
            foreach (PropertyInfo prop in props)
            {
                //EntityState,@EntityKey
                if (!isIdentity || identities.Any(it => it.Value.ToLower() != prop.Name.ToLower()))
                {
                    if (!cacheSqlManager.ContainsKey(cacheSqlKey))
                    {
                        sbInsertSql.Append("@" + prop.Name + ",");
                    }
                    object val = prop.GetValue(entity, null);
                    if (val == null)
                    {
                        val = DBNull.Value;
                    }
                    if (_serialNumber.IsValuable())
                    {
                        Func <PubModel.SerialNumber, bool> serEexp = it => it.TableName.ToLower() == typeName.ToLower() && it.FieldName.ToLower() == prop.Name.ToLower();
                        var isAnyNum = _serialNumber.Any(serEexp);
                        if (isAnyNum && (val == DBNull.Value || val.IsNullOrEmpty()))
                        {
                            val = _serialNumber.First(serEexp).GetNumFunc();
                        }
                    }

                    if (prop.PropertyType.GetTypeInfo().IsEnum)
                    {
                        val = (int)(val);
                    }

                    var par = new SqlParameter("@" + prop.Name, val);
                    if (par.SqlDbType == SqlDbType.Udt)
                    {
                        par.TypeName = "HIERARCHYID";
                    }
                    pars.Add(par);
                }
            }
            if (!isContainCacheSqlKey)
            {
                //**去掉最后一个逗号
                sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
                if (isIdentity == false)
                {
                    sbInsertSql.Append(");select 'true';");
                }
                else
                {
                    sbInsertSql.Append(");select @@identity;");
                }
                cacheSqlManager.Add(cacheSqlKey, sbInsertSql, cacheSqlManager.Day);
            }
            var sql = sbInsertSql.ToString();

            try
            {
                var lastInsertRowId = GetScalar(sql, pars.ToArray());
                return(lastInsertRowId);
            }
            catch (Exception ex)
            {
                throw new Exception("sql:" + sql + "\n" + ex.Message);
            }
        }
        /// <summary>
        /// 更新
        /// 注意:rowObj为T类型将更新该实体的非主键所有列,如果rowObj类型为匿名类将更新指定列
        /// 使用说明:sqlSugar.Update《T》(rowObj,whereObj);
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rowObj">new T(){name="张三",sex="男"}或者new {name="张三",sex="男"}</param>
        /// <param name="whereIn">new int[]{1,2,3}</param>
        /// <returns></returns>
        public bool Update <T, FiledType>(object rowObj, params FiledType[] whereIn) where T : class
        {
            if (rowObj == null)
            {
                throw new ArgumentNullException("SqlSugarClient.Update.rowObj");
            }

            Type   type     = typeof(T);
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            StringBuilder sbSql = new StringBuilder(string.Format(" UPDATE {0} SET ", typeName));
            Dictionary <string, object> rows = SqlSugarTool.GetObjectToDictionary(rowObj);
            string pkName        = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
            var    identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);

            foreach (var r in rows)
            {
                var isPk                   = pkName != null && pkName.ToLower() == r.Key.ToLower();
                var isIdentity             = identityNames.Any(it => it.Value.ToLower() == r.Key.ToLower());
                var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == r.Key.ToLower());
                if (isPk || isIdentity || isDisableUpdateColumns)
                {
                    if (rowObj.GetType() == type)
                    {
                        continue;
                    }
                }
                sbSql.Append(string.Format(" [{0}] =@{0}  ,", r.Key));
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            if (whereIn.Count() == 0)
            {
                var value = type.GetTypeInfo().GetProperties().Cast <PropertyInfo>().Single(it => it.Name == pkName).GetValue(rowObj, null);
                sbSql.AppendFormat("WHERE {1} IN ('{2}')", typeName, pkName, value);
            }
            else
            {
                sbSql.AppendFormat("WHERE {1} IN ({2})", typeName, pkName, whereIn.ToJoinSqlInVal());
            }
            List <SqlParameter> parsList = new List <SqlParameter>();
            var pars = rows.Select(c => new SqlParameter("@" + c.Key, c.Value));

            if (pars != null)
            {
                foreach (var par in pars)
                {
                    var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == par.ParameterName.TrimStart('@').ToLower());
                    if (par.SqlDbType == SqlDbType.Udt || par.ParameterName.ToLower().Contains("hierarchyid"))
                    {
                        par.TypeName = "HIERARCHYID";
                    }
                    if (!isDisableUpdateColumns)
                    {
                        parsList.Add(par);
                    }
                }
            }
            try
            {
                var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray());
                return(updateRowCount > 0);
            }
            catch (Exception ex)
            {
                throw new Exception("sql:" + sbSql.ToString() + "\n" + ex.Message);
            }
        }
        /// <summary>
        /// 根据SQL语句将结果集映射到List《T》
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="whereObj"></param>
        /// <returns></returns>
        public List <T> SqlQuery <T>(string sql, object whereObj = null)
        {
            var pars = SqlSugarTool.GetParameters(whereObj).ToList();

            return(SqlQuery <T>(sql, pars));
        }
        private bool SqlBulkReplace <T>(IEnumerable <T> entities) where T : class
        {
            if (entities == null)
            {
                return(false);
            }
            ;
            StringBuilder sbSql    = new StringBuilder("");
            Type          type     = typeof(T);
            string        typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            string pkName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);

            Check.Exception(pkName.IsNullOrEmpty(), "没有找到主键。");
            var identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);
            var isIdentity    = identityNames != null && identityNames.Count > 0;
            var columnNames   = SqlSugarTool.GetColumnsByTableName(this, typeName);

            if (isIdentity)
            {
                columnNames = columnNames.Where(c => !identityNames.Any(it => it.Value == c)).ToList();//去掉自添列
            }
            //属性缓存
            string cachePropertiesKey     = "db." + type.FullName + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props = null;
            if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
            {
                props = cachePropertiesManager[cachePropertiesKey];
            }
            else
            {
                props = type.GetProperties();
                cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
            }
            foreach (var entity in entities)
            {
                string pkValue = string.Empty;
                sbSql.Append(" UPDATE ");
                sbSql.Append(typeName);
                sbSql.Append(" SET ");
                pkValue = props.Single(it => it.Name.ToLower() == pkName.ToLower()).GetValue(entity, null).ToString();
                foreach (var name in columnNames)
                {
                    var isPk = pkName != null && pkName.ToLower() == name.ToLower();
                    var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == name.ToLower());
                    var isLastName             = name == columnNames.Last();
                    var prop     = props.Single(it => it.Name == name);
                    var objValue = prop.GetValue(entity, null);
                    if (this.IsIgnoreErrorColumns)
                    {
                        if (!SqlSugarTool.GetColumnsByTableName(this, typeName).Any(it => it.ToLower() == name.ToLower()))
                        {
                            continue;
                        }
                    }
                    if (isPk || isDisableUpdateColumns)
                    {
                        continue;
                    }
                    bool isNullable = false;
                    var  underType  = SqlSugarTool.GetUnderType(prop, ref isNullable);
                    if (objValue == null)
                    {
                        objValue = "NULL";
                    }
                    else if (underType == SqlSugarTool.DateType)
                    {
                        objValue = "'" + objValue.ToString() + "'";
                    }
                    else if (underType == SqlSugarTool.BoolType)
                    {
                        objValue = Convert.ToBoolean(objValue) ? 1 : 0;
                    }
                    else if (underType == SqlSugarTool.StringType)
                    {
                        //string参数需要处理注入 (因为SqlParameter参数上限为2100所以无法使用参数化)
                        objValue = "'" + objValue.ToString().ToSqlFilter() + "'";
                    }
                    else
                    {
                        objValue = "'" + objValue.ToString() + "'";
                    }
                    sbSql.AppendFormat(" [{0}]={1}{2}  ", name, objValue, ",");
                }
                sbSql.Remove(sbSql.ToString().LastIndexOf(","), 1);
                sbSql.AppendFormat("WHERE [{0}]='{1}' ", pkName, pkValue.ToSuperSqlFilter());
            }
            var reval = base.ExecuteCommand(sbSql.ToString());

            sbSql = null;
            return(reval > 0);
        }
        /// <summary>
        /// 根据表达式条件将实体对象更新到数据库
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="rowObj">rowObj为匿名对象时只更新指定列( 例如:new{ name='abc'}只更新name ),为T类型将更新整个实体(排除主键、自增列和禁止更新列)</param>
        /// <param name="expression">表达式条件</param>
        /// <returns>更新成功返回true</returns>
        public bool Update <T>(object rowObj, Expression <Func <T, bool> > expression) where T : class
        {
            if (rowObj == null)
            {
                throw new ArgumentNullException("SqlSugarClient.Update.rowObj");
            }
            if (expression == null)
            {
                throw new ArgumentNullException("SqlSugarClient.Update.expression");
            }


            Type   type     = typeof(T);
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            var rows      = SqlSugarTool.GetParameters(rowObj);
            var isDynamic = rowObj.GetType() != type;
            var isClass   = !isDynamic;

            //sql语句缓存
            string cacheSqlKey     = "db.update." + type.FullName + rows.Length;
            var    cacheSqlManager = CacheManager <StringBuilder> .GetInstance();



            string pkName        = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
            var    identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);


            ResolveExpress re = new ResolveExpress();

            re.ResolveExpression(re, expression);


            StringBuilder sbSql = new StringBuilder();

            if (cacheSqlManager.ContainsKey(cacheSqlKey) && isClass)
            {
                sbSql = cacheSqlManager[cacheSqlKey];
            }
            else
            {
                sbSql = new StringBuilder(string.Format(" UPDATE [{0}] SET ", typeName));
                foreach (var r in rows)
                {
                    var name                   = r.ParameterName.TrimStart('@');
                    var isPk                   = pkName != null && pkName.ToLower() == name.ToLower();
                    var isIdentity             = identityNames.Any(it => it.Value.ToLower() == name.ToLower());
                    var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == name.ToLower());

                    if (this.IsIgnoreErrorColumns)
                    {
                        if (!SqlSugarTool.GetColumnsByTableName(this, typeName).Any(it => it.ToLower() == name.ToLower()))
                        {
                            continue;
                        }
                    }

                    if (isPk || isIdentity || isDisableUpdateColumns)
                    {
                        if (isClass)
                        {
                            continue;
                        }
                    }
                    sbSql.Append(string.Format(" [{0}] =@{0}  ,", name));
                }
                sbSql.Remove(sbSql.Length - 1, 1);
                sbSql.Append(" WHERE  1=1  ");
                sbSql.Append(re.SqlWhere);
                cacheSqlManager.Add(cacheSqlKey, sbSql, cacheSqlManager.Day);
            }

            List <SqlParameter> parsList = new List <SqlParameter>();

            parsList.AddRange(re.Paras);
            var pars = rows;

            if (pars != null)
            {
                foreach (var par in pars)
                {
                    if (par.SqlDbType == SqlDbType.Udt)
                    {
                        par.TypeName = "HIERARCHYID";
                    }
                    SqlSugarTool.SetParSize(par);
                    parsList.Add(par);
                }
            }
            try
            {
                var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray());
                return(updateRowCount > 0);
            }
            catch (Exception ex)
            {
                throw new SqlSugarException(ex.Message, sbSql.ToString(), new { rowObj = rowObj, expression = expression + "" });
            }
        }
Beispiel #14
0
 /// <summary>
 /// 获取DataSet
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="pars"></param>
 /// <returns></returns>
 public DataSet GetDataSetAll(string sql, object pars)
 {
     return(GetDataSetAll(sql, SqlSugarTool.GetParameters(pars)));
 }
Beispiel #15
0
 /// <summary>
 /// 获取第一行第一列
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
 /// <returns></returns>
 public string GetString(string sql, object pars)
 {
     return(GetString(sql, SqlSugarTool.GetParameters(pars)));
 }
        /// <summary>
        /// 获取表结构信息
        /// </summary>
        /// <param name="db"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List <PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName)
        {
            string sql = SqlSugarTool.GetTtableColumnsInfo(tableName);

            return(db.SqlQuery <PubModel.DataTableMap>(sql));
        }
Beispiel #17
0
 /// <summary>
 /// 获取第一行第一列
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
 /// <returns></returns>
 public int GetInt(string sql, object pars)
 {
     return(GetInt(sql, SqlSugarTool.GetParameters(pars)));
 }
Beispiel #18
0
        /// <summary>
        /// 批量假删除
        /// 使用说明::
        /// FalseDelete《T》("is_del",new int[]{1,2,3})或者Delete《T》("is_del",3)
        /// </summary>
        /// <param name="field">更新删除状态字段</param>
        /// <param name="whereIn">delete ids</param>
        public bool FalseDelete <T, FiledType>(string field, params FiledType[] whereIn)
        {
            Type   type     = typeof(T);
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            //属性缓存
            string cachePropertiesKey     = "db." + typeName + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props     = SqlSugarTool.GetGetPropertiesByCache(type, cachePropertiesKey, cachePropertiesManager);
            bool           isSuccess = false;

            if (whereIn != null && whereIn.Length > 0)
            {
                string sql            = string.Format("UPDATE  {0} SET {3}=1 WHERE {1} IN ({2})", typeName, SqlSugarTool.GetPrimaryKeyByTableName(this, typeName), whereIn.ToJoinSqlInVal(), field);
                int    deleteRowCount = ExecuteCommand(sql);
                isSuccess = deleteRowCount > 0;
            }
            return(isSuccess);
        }
Beispiel #19
0
 /// <summary>
 /// 获取DataReader
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
 /// <returns></returns>
 public SqlDataReader GetReader(string sql, object pars)
 {
     return(GetReader(sql, SqlSugarTool.GetParameters(pars)));
 }
 /// <summary>
 /// 获取转释后的表名和列名
 /// </summary>
 /// <param name="tableName"></param>
 /// <returns></returns>
 public static string GetTranslationSqlName(this string tableName)
 {
     return(SqlSugarTool.GetTranslationSqlName(tableName));
 }
Beispiel #21
0
 /// <summary>
 /// 根据SQL获取T的集合
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="sql"></param>
 /// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
 /// <returns></returns>
 public List <T> GetList <T>(string sql, object pars)
 {
     return(GetList <T>(sql, SqlSugarTool.GetParameters(pars)));
 }
Beispiel #22
0
        /// <summary>
        /// 动态获取IDataRecord里面的函数
        /// </summary>
        /// <param name="generator"></param>
        /// <param name="type"></param>
        /// <param name="isNullable"></param>
        /// <param name="pro"></param>
        /// <param name="dbTypeName"></param>
        /// <param name="fieldName"></param>
        private static void GeneratorCallMethod(ILGenerator generator, Type type, bool isNullable, PropertyInfo pro, string dbTypeName, string fieldName)
        {
            List <string> guidThrow = new List <string>()
            {
                "int32", "datetime", "decimal", "double", "byte", "string"
            };                                                                                                         //数据库为GUID有错的实体类形
            List <string> intThrow = new List <string>()
            {
                "datetime", "byte"
            };                                                                //数据库为int有错的实体类形
            List <string> stringThrow = new List <string>()
            {
                "int32", "datetime", "decimal", "double", "byte", "guid"
            };                                                                                                         //数据库为vachar有错的实体类形
            List <string> decimalThrow = new List <string>()
            {
                "datetime", "byte", "guid"
            };
            List <string> doubleThrow = new List <string>()
            {
                "datetime", "byte", "guid"
            };
            List <string> dateThrow = new List <string>()
            {
                "int32", "decimal", "double", "byte", "guid"
            };
            List <string> shortThrow = new List <string>()
            {
                "datetime", "guid"
            };
            List <string> byteThrow = new List <string>()
            {
                "datetime", "guid"
            };
            MethodInfo method      = null;
            var        typeName    = SqlSugarTool.ChangeDBTypeToCSharpType(dbTypeName);
            var        objTypeName = type.Name.ToLower();
            var        isEnum      = type.IsEnum;

            if (isEnum)
            {
                typeName = "ENUMNAME";
            }
            else if (dbTypeName.Contains("hierarchyid") || typeName == "byte[]" || objTypeName == "object")
            {
                generator.Emit(OpCodes.Call, getValueMethod);
                generator.Emit(OpCodes.Unbox_Any, pro.PropertyType);//找不到类型才执行拆箱(类型转换)
                return;
            }
            if (isNullable)
            {
                switch (typeName)
                {
                case "int":
                    CheckType(intThrow, objTypeName, typeName, fieldName);
                    var isNotInt = objTypeName != "int32";
                    if (isNotInt)
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertInt32;
                    } break;

                case "long":
                    CheckType(intThrow, objTypeName, typeName, fieldName);
                    var isNotLong = objTypeName != "int64";
                    if (isNotLong)
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvetInt64;
                    } break;

                case "bool":
                    if (objTypeName != "bool" && objTypeName != "boolean")
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertBoolean;
                    } break;

                case "string":
                    CheckType(stringThrow, objTypeName, typeName, fieldName);
                    method = getString; break;

                case "dateTime":
                    CheckType(dateThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "datetime")
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertDateTime;
                    } break;

                case "decimal":
                    CheckType(decimalThrow, objTypeName, typeName, fieldName);
                    var isNotDecimal = objTypeName != "decimal";
                    if (isNotDecimal)
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertDecimal;
                    } break;

                case "double":
                    CheckType(doubleThrow, objTypeName, typeName, fieldName);
                    var isNotDouble = objTypeName != "double";
                    if (isNotDouble)
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertDouble;
                    } break;

                case "float":
                    CheckType(decimalThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "float" && objTypeName != "single")
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertFloat;
                    } break;

                case "guid":
                    CheckType(guidThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "guid")
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertGuid;
                    } break;

                case "byte":
                    CheckType(byteThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "byte")
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertByte;
                    } break;

                case "ENUMNAME":
                    method = getConvertToEnum_Nullable.MakeGenericMethod(type); break;

                case "short":
                    CheckType(shortThrow, objTypeName, typeName, fieldName);
                    var isNotShort = objTypeName != "int16" && objTypeName != "short";
                    if (isNotShort)
                    {
                        method = getOtherNull.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getConvertInt16;
                    }
                    break;

                default:
                    method = getOtherNull.MakeGenericMethod(type); break;
                }

                generator.Emit(OpCodes.Call, method);
            }
            else
            {
                switch (typeName)
                {
                case "int":
                    CheckType(intThrow, objTypeName, typeName, fieldName);
                    var isNotInt = objTypeName != "int32";
                    if (isNotInt)
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getInt32;
                    } break;

                case "long":
                    CheckType(intThrow, objTypeName, typeName, fieldName);
                    var isNotLong = objTypeName != "int64";
                    if (isNotLong)
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getInt64;
                    } break;

                case "bool":
                    if (objTypeName != "bool" && objTypeName != "boolean")
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getBoolean;
                    } break;

                case "string":
                    CheckType(stringThrow, objTypeName, typeName, fieldName);
                    method = getString; break;

                case "dateTime":
                    CheckType(dateThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "datetime")
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getDateTime;
                    } break;

                case "decimal":
                    CheckType(decimalThrow, objTypeName, typeName, fieldName);
                    var isNotDecimal = objTypeName != "decimal";
                    if (isNotDecimal)
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getDecimal;
                    } break;

                case "double":
                    CheckType(doubleThrow, objTypeName, typeName, fieldName);
                    var isNotDouble = objTypeName != "double";
                    if (isNotDouble)
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getDouble;
                    } break;

                case "float":
                    CheckType(decimalThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "float" && objTypeName != "single")
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getFloat;
                    } break;

                case "guid":
                    CheckType(guidThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "guid")
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getGuid;
                    } break;

                case "byte":
                    CheckType(byteThrow, objTypeName, typeName, fieldName);
                    if (objTypeName != "byte")
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getByte;
                    } break;

                case "ENUMNAME":
                    method = getValueMethod; break;

                case "short":
                    CheckType(shortThrow, objTypeName, typeName, fieldName);
                    var isNotShort = objTypeName != "int16" && objTypeName != "short";
                    if (isNotShort)
                    {
                        method = getOther.MakeGenericMethod(type);
                    }
                    else
                    {
                        method = getInt16;
                    }
                    break;

                default:
                    method = getOther.MakeGenericMethod(type);
                    break;;
                }

                generator.Emit(OpCodes.Call, method);

                if (method == getValueMethod)
                {
                    generator.Emit(OpCodes.Unbox_Any, pro.PropertyType);//找不到类型才执行拆箱(类型转换)
                }
            }
        }
Beispiel #23
0
 /// <summary>
 /// 根据SQL获取T
 /// </summary>
 /// <typeparam name="T">可以是int、string等,也可以是类或者数组、字典</typeparam>
 /// <param name="sql"></param>
 /// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
 /// <returns></returns>
 public T GetSingle <T>(string sql, object pars)
 {
     return(GetSingle <T>(sql, SqlSugarTool.GetParameters(pars)));
 }
 public int ExecuteCommand(string sql, object pars)
 {
     return(ExecuteCommand(sql, SqlSugarTool.GetParameters(pars)));
 }
Beispiel #25
0
        /// <summary>
        /// 批量删除
        /// 注意:whereIn 主键集合
        /// 使用说明:Delete《T》(new int[]{1,2,3}) 或者  Delete《T》(3)
        /// </summary>
        /// <param name="whereIn"> delete ids </param>
        public bool Delete <T>(params dynamic[] whereIn)
        {
            Type type = typeof(T);
            //属性缓存
            string cachePropertiesKey     = "db." + type.Name + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props     = SqlSugarTool.GetGetPropertiesByCache(type, cachePropertiesKey, cachePropertiesManager);
            string         key       = type.FullName;
            bool           isSuccess = false;

            if (whereIn != null && whereIn.Length > 0)
            {
                string sql            = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", type.Name, SqlSugarTool.GetPrimaryKeyByTableName(this, type.Name), whereIn.ToJoinSqlInVal());
                int    deleteRowCount = ExecuteCommand(sql);
                isSuccess = deleteRowCount > 0;
            }
            return(isSuccess);
        }