/// <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)
{
SqlDataReader reader = null;
var pars = SqlSugarTool.GetParameters(whereObj);
var type = typeof(T);
sql = string.Format(@"
--{0}
{1}
", type.Name, sql);
reader = GetReader(sql, pars);
if (type.IsIn(SqlSugarTool.IntType, SqlSugarTool.StringType))
{
List <T> strReval = new List <T>();
using (SqlDataReader re = reader)
{
while (re.Read())
{
strReval.Add((T)Convert.ChangeType(re.GetValue(0), type));
}
}
return(strReval);
}
string fields = sql;
if (sql.Length > 51)
{
fields = sql.Substring(0, 50);
}
var reval = SqlSugarTool.DataReaderToList <T>(type, reader, fields);
return(reval);
}
/// <summary>
/// 根据SQL语句将结果集映射到List《T》
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="reader"></param>
/// <param name="pars"></param>
/// <returns></returns>
public List <T> SqlQuery <T>(string sql, List <SqlParameter> pars)
{
SqlDataReader reader = null;
//全局过滤器
if (CurrentFilterKey.IsValuable())
{
if (_filterFuns.IsValuable() && _filterFuns.ContainsKey(CurrentFilterKey))
{
var filterInfo = _filterFuns[CurrentFilterKey];
var filterValue = filterInfo();
sql += string.Format(" AND {0} ", filterValue.Key);
if (filterValue.Value != null)
{
pars.AddRange(SqlSugarTool.GetParameters(filterValue.Value));
}
}
}
var type = typeof(T);
sql = string.Format(@"--{0}
{1}", type.Name, sql);
reader = GetReader(sql, pars.ToArray());
string fields = sql;
if (sql.Length > 101)
{
fields = sql.Substring(0, 100);
}
var reval = SqlSugarTool.DataReaderToList <T>(type, reader, fields);
fields = null;
sql = null;
return(reval);
}
/// <summary>
/// 创建单表查询对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public Queryable <T> Queryable <T>() where T : new()
{
var queryable = new Queryable <T>()
{
DB = this
};
//别名表
if (_mappingTableList.IsValuable())
{
string name = typeof(T).Name;
if (_mappingTableList.Any(it => it.Key == name))
{
queryable.TableName = _mappingTableList.First(it => it.Key == name).Value;
}
}
//全局过滤器
if (CurrentFilterKey.IsValuable())
{
if (_filterFuns.IsValuable() && _filterFuns.ContainsKey(CurrentFilterKey))
{
var filterInfo = _filterFuns[CurrentFilterKey];
var filterValue = filterInfo();
string whereStr = string.Format(" AND {0} ", filterValue.Key);
queryable.Where.Add(whereStr);
if (filterValue.Value != null)
{
queryable.Params.AddRange(SqlSugarTool.GetParameters(filterValue.Value));
}
return(queryable);
}
}
return(queryable);
}
/// <summary>
/// 创建多表查询对象
/// </summary>
public Sqlable Sqlable()
{
var sqlable = new Sqlable()
{
DB = this
};
//全局过滤器
if (CurrentFilterKey.IsValuable())
{
if (_filterFuns.IsValuable() && _filterFuns.ContainsKey(CurrentFilterKey))
{
var filterInfo = _filterFuns[CurrentFilterKey];
var filterVlue = filterInfo();
string whereStr = string.Format(" AND {0} ", filterVlue.Key);
sqlable.Where.Add(whereStr);
if (filterVlue.Value != null)
{
sqlable.Params.AddRange(SqlSugarTool.GetParameters(filterVlue.Value));
}
return(sqlable);
}
}
return(sqlable);
}
/// <summary>
/// 设置查询列和分页参数执行查询，并且将结果集转成List《T》
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlable"></param>
/// <param name="fileds">查询列</param>
/// <param name="orderByFiled">Order By字段，可以多个</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="whereObj">SQL参数,例如:new{id=1,name="张三"}</param>
/// <returns></returns>
public static List <T> SelectToPageList <T>(this Sqlable sqlable, string fileds, string orderByFiled, int pageIndex, int pageSize, object whereObj = null) where T : class
{
StringBuilder sbSql = new StringBuilder(sqlable.Sql.ToString());
try
{
if (pageIndex == 0)
{
pageIndex = 1;
}
Check.ArgumentNullException(sqlable.Sql, "语法错误，SelectToSql必需要在.Form后面使用");
sbSql.Insert(0, string.Format("SELECT {0},row_index=ROW_NUMBER() OVER(ORDER BY {1} )", fileds, orderByFiled));
sbSql.Append(" WHERE 1=1 ").Append(string.Join(" ", sqlable.Where));
sbSql.Append(sqlable.OrderBy);
sbSql.Append(sqlable.GroupBy);
int skip = (pageIndex - 1) * pageSize + 1;
int take = pageSize;
sbSql.Insert(0, "SELECT * FROM ( ");
sbSql.AppendFormat(") t WHERE t.row_index BETWEEN {0} AND {1} ", skip, skip + take - 1);
var sqlParams = SqlSugarTool.GetParameters(whereObj);
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), sqlable.DB.GetReader(sbSql.ToString(), sqlParams), fileds);
return(reval);
}
catch (Exception ex)
{
Check.Exception(true, "sql:{0} \r\n message:{1}", sbSql.ToString(), ex.Message);
throw;
}
finally
{
sbSql = null;
sqlable = null;
}
}
/// <summary>
/// 反回记录数
/// </summary>
/// <param name="sqlable"></param>
/// <returns></returns>
public static int Count(this Sqlable sqlable, object whereObj = null)
{
StringBuilder sbSql = new StringBuilder(sqlable.Sql.ToString());
try
{
Check.ArgumentNullException(sqlable.Sql, "语法错误，Count必需要在.Form后面使用");
sbSql.Insert(0, string.Format("SELECT COUNT(1) "));
sbSql.Append(" WHERE 1=1").Append(string.Join(" ", sqlable.Where));
sbSql.Append(sqlable.OrderBy);
sbSql.Append(sqlable.GroupBy);
var sqlParams = SqlSugarTool.GetParameters(whereObj);
return(sqlable.DB.GetInt(sbSql.ToString(), sqlParams));
}
catch (Exception ex)
{
Check.Exception(true, "sql:{0} \r\n message:{1}", sbSql.ToString(), ex.Message);
throw;
}
finally
{
sqlable = null;
sbSql = null;
}
}
/// <summary>
/// 设置查询列执行查询，并且将结果集转成List《T》
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlable"></param>
/// <param name="fileds">查询列</param>
/// <param name="whereObj">SQL参数,例如:new{id=1,name="张三"}</param>
/// <returns></returns>
public static List <T> SelectToList <T>(this Sqlable sqlable, string fileds, object whereObj = null) where T : class
{
StringBuilder sbSql = new StringBuilder(sqlable.Sql.ToString());
try
{
Check.ArgumentNullException(sqlable.Sql, "语法错误，SelectToSql必需要在.Form后面使用");
sbSql.Insert(0, string.Format("SELECT {0} ", fileds));
sbSql.Append(" WHERE 1=1").Append(string.Join(" ", sqlable.Where));
sbSql.Append(sqlable.OrderBy);
sbSql.Append(sqlable.GroupBy);
var sqlParams = SqlSugarTool.GetParameters(whereObj);
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), sqlable.DB.GetReader(sbSql.ToString(), sqlParams), fileds);
return(reval);
}
catch (Exception ex)
{
Check.Exception(true, "sql:{0} \r\n message:{1}", sbSql.ToString(), ex.Message);
throw;
}
finally
{
sqlable = null;
sbSql = null;
}
}
/// <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>
/// 条件筛选
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable"></param>
/// <param name="whereString"></param>
/// <returns></returns>
public static Queryable <T> Where <T>(this Queryable <T> queryable, string whereString, object whereObj = null)
{
var type = queryable.Type;
string whereStr = string.Format(" AND {0} ", whereString);
queryable.Where.Add(whereStr);
if (whereObj != null)
{
queryable.Params.AddRange(SqlSugarTool.GetParameters(whereObj));
}
return(queryable);
}
/// <summary>
/// 获取页面参数
/// </summary>
/// <param name="sqlable"></param>
/// <param name="whereObj"></param>
/// <returns></returns>
private static SqlParameter[] GetAllParas(Sqlable sqlable, object whereObj)
{
List <SqlParameter> allParams = new List <SqlParameter>();
var selectParas = SqlSugarTool.GetParameters(whereObj).ToList();
if (selectParas.IsValuable())
{
allParams.AddRange(selectParas);
}
if (sqlable.Params.IsValuable())
{
allParams.AddRange(sqlable.Params);
}
return(allParams.ToArray());
}
/// <summary>
/// 联表查询根据字符串
/// </summary>
/// <typeparam name="T">第一个表的对象</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="tableName">表名（可是以表或也可以是SQL语句加括号）</param>
/// <param name="shortName">表名简写</param>
/// <param name="onWhere">on后面的条件</param>
/// <param name="whereObj">匿名参数(例如:new{id=1,name="张三"})</param>
/// <param name="type">Join的类型</param>
/// <returns>Queryable</returns>
public static Queryable <T> JoinTable <T>(this Queryable <T> queryable, string tableName, string shortName, string onWhere, object whereObj, JoinType type = JoinType.LEFT)
{
queryable.WhereIndex = queryable.WhereIndex + 100;;
string joinType = type.ToString();
string joinStr = string.Format(" {0} JOIN {1} {2} ON {3} ",
/*0*/ joinType,
/*1*/ tableName,
/*2*/ shortName,
/*3*/ onWhere
);
queryable.JoinTableValue.Add(joinStr);
if (whereObj != null)
{
queryable.Params.AddRange(SqlSugarTool.GetParameters(whereObj));
}
return(queryable);
}
/// <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));
}
public int GetInt(string sql, object pars)
{
return(GetInt(sql, SqlSugarTool.GetParameters(pars)));
}
public string GetString(string sql, object pars)
{
return(GetString(sql, SqlSugarTool.GetParameters(pars)));
}
public DataTable GetDataTable(string sql, object pars)
{
return(GetDataTable(sql, SqlSugarTool.GetParameters(pars)));
}
public T GetSingle <T>(string sql, object[] pars)
{
return(GetSingle <T>(sql, SqlSugarTool.GetParameters(pars)));
}
public List <T> GetList <T>(string sql, object pars)
{
return(GetList <T>(sql, SqlSugarTool.GetParameters(pars)));
}
public SqlDataReader GetReader(string sql, object pars)
{
return(GetReader(sql, SqlSugarTool.GetParameters(pars)));
}
public int ExecuteCommand(string sql, object pars)
{
return(ExecuteCommand(sql, SqlSugarTool.GetParameters(pars)));
}
public object GetScalar(string sql, object pars)
{
return(GetScalar(sql, SqlSugarTool.GetParameters(pars)));
}
/// <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)));
}
/// <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);
}
}
/// <summary>
/// 根据SQL获取T的集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
/// <returns></returns>
public virtual IEnumerable <T> GetList <T>(string sql, object pars)
{
return(GetList <T>(sql, SqlSugarTool.GetParameters(pars)));
}
