/// <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); foreach (var r in rows) { if (pkName == r.Key) { 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.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) { if (par.SqlDbType == SqlDbType.Udt || par.ParameterName.ToLower().Contains("hierarchyid")) { par.UdtTypeName = "HIERARCHYID"; } 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> /// 更新 /// 注意: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="expression">it.id=100</param> /// <returns></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); StringBuilder sbSql = new StringBuilder(string.Format(" UPDATE {0} SET ", typeName)); var rows = SqlSugarTool.GetParameters(rowObj); string pkName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName); foreach (var r in rows) { if (pkName == r.ParameterName.TrimStart('@')) { if (rowObj.GetType() == type) { continue; } } sbSql.Append(string.Format(" [{0}] =@{0} ,", r.ParameterName.TrimStart('@'))); } sbSql.Remove(sbSql.Length - 1, 1); sbSql.Append(" WHERE 1=1 "); ResolveExpress re = new ResolveExpress(); re.ResolveExpression(re, expression); sbSql.Append(re.SqlWhere);; 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.UdtTypeName = "HIERARCHYID"; } parsList.Add(par); } } var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray()); return(updateRowCount > 0); }
/// <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="expression">new {id=100}</param> /// <returns></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); StringBuilder sbSql = new StringBuilder(string.Format(" UPDATE {0} SET ", type.Name)); Dictionary <string, string> rows = SqlSugarTool.GetObjectToDictionary(rowObj); string pkName = SqlSugarTool.GetPrimaryKeyByTableName(this, type.Name); foreach (var r in rows) { if (pkName == r.Key) { if (rowObj.GetType() == type) { continue; } } sbSql.Append(string.Format(" {0} =@{0} ,", r.Key)); } sbSql.Remove(sbSql.Length - 1, 1); sbSql.Append(" WHERE 1=1 "); ResolveExpress re = new ResolveExpress(); re.ResolveExpression(re, expression); sbSql.Append(re.SqlWhere);; List <SqlParameter> parsList = new List <SqlParameter>(); parsList.AddRange(re.Paras); parsList.AddRange(rows.Select(c => new SqlParameter("@" + c.Key, c.Value))); var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray()); return(updateRowCount > 0); }
/// <summary> /// 根据主键查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="queryable"></param> /// <param name="pkValues">主键集合</param> /// <returns></returns> public static Queryable <T> In <T>(this Queryable <T> queryable, params object[] pkValues) { Check.Exception(pkValues == null || pkValues.Length == 0, "In.pkValues的Count不能为0"); var type = pkValues[0].GetType(); if (type != SqlSugarTool.IntType && type != SqlSugarTool.GuidType && type.FullName.IsCollectionsList()) { var newList = new List <object>(); foreach (var item in (IEnumerable)pkValues[0]) { newList.Add(item); } pkValues = newList.ToArray(); Check.Exception(pkValues == null || pkValues.Length == 0, "In.pkValues的Count不能为0"); } var pkName = SqlSugarTool.GetPrimaryKeyByTableName(queryable.DB, queryable.TableName); queryable.OrderByValue = null; Check.ArgumentNullException(pkName, "In(params object[]PkValue)查询表中不存在主键,请换In的其它重载方法。"); return(queryable.In <T, string>(pkName, pkValues.Select(it => it.ToString()).ToArray())); }
/// <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); }
/// <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> /// 更新 /// 注意: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="expression">it.id=100</param> /// <returns></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); //sql语句缓存 string cacheSqlKey = "db.update." + typeName + 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)) { 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 (isPk || isIdentity || isDisableUpdateColumns) { if (rowObj.GetType() == type) { 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.UdtTypeName = "HIERARCHYID"; } SqlSugarTool.SetParSize(par); parsList.Add(par); } } try { var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray()); return(updateRowCount > 0); } catch (Exception ex) { var cacheManager = CacheManager <string> .GetInstance(); cacheManager.RemoveAll(it => it.Contains("KeyBy")); throw new Exception("sql:" + sbSql.ToString() + "\n" + ex.Message); } }
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); }
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); }
/// <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); }