/// <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>
/// 更新
/// 注意：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.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 <MySqlParameter> parsList = new List <MySqlParameter>();
var pars = rows.Select(c => new MySqlParameter("@" + 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 (!isDisableUpdateColumns)
{
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);
}
}
/// <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 <MySqlParameter> parsList = new List <MySqlParameter>();
parsList.AddRange(re.Paras);
var pars = rows;
if (pars != null)
{
foreach (var par in pars)
{
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);
}
}