/// <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后面使用");
SqlSugarTool.GetSqlableSql(sqlable, fileds, orderByFiled, pageIndex, pageSize, sbSql);
var sqlParams = GetAllParas(sqlable, 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>
/// 根据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>
/// 设置查询列执行查询，并且将结果集转成List《T》
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlable"></param>
/// <param name="fileds">查询列</param>
/// <param name="whereObj">SQL参数,例如:new{id=1,name="张三"}</param>
/// <param name="preSql">在这语句之前可插入自定义SQL</param>
/// <param name="nextSql">在这语句之后可以插自定义SQL</param>
/// <returns></returns>
public static List <T> SelectToList <T>(this Sqlable sqlable, string fileds, object whereObj = null, string preSql = null, string nextSql = 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 = GetAllParas(sqlable, whereObj);
if (preSql != null)
{
sbSql.Insert(0, preSql);
}
if (nextSql != null)
{
sbSql.Append(nextSql);
}
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>
/// 根据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>
/// 设置查询列和分页参数执行查询，并且将结果集转成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>
/// 将Queryable转换为List《T》集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable"></param>
/// <returns></returns>
public static List <T> ToList <T>(this Queryable <T> queryable)
{
StringBuilder sbSql = SqlSugarTool.GetQueryableSql <T>(queryable);
var reader = queryable.DB.GetReader(sbSql.ToString(), queryable.Params.ToArray());
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), reader, queryable.Select.GetSelectFiles());
queryable = null;
sbSql = null;
return(reval);
}
/// <summary>
/// 将Queryable转换为List《T》集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable"></param>
/// <returns></returns>
public static List <T> ToList <T>(this SqlSugar.Queryable <T> queryable)
{
StringBuilder sbSql = new StringBuilder();
try
{
string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null;
var order = queryable.OrderBy.IsValuable() ? (",row_index=ROW_NUMBER() OVER(ORDER BY " + queryable.OrderBy + " )") : null;
sbSql.AppendFormat("SELECT " + queryable.Select.GetSelectFiles() + " {1} FROM {0} {2} WHERE 1=1 {3} {4} ", queryable.TableName.IsNullOrEmpty() ? queryable.TName : queryable.TableName, order, withNoLock, string.Join("", queryable.Where), queryable.GroupBy.GetGroupBy());
if (queryable.Skip == null && queryable.Take != null)
{
sbSql.Insert(0, "SELECT " + queryable.Select.GetSelectFiles() + " FROM ( ");
sbSql.Append(") t WHERE t.row_index<=" + queryable.Take);
}
else if (queryable.Skip != null && queryable.Take == null)
{
sbSql.Insert(0, "SELECT " + queryable.Select.GetSelectFiles() + " FROM ( ");
sbSql.Append(") t WHERE t.row_index>" + (queryable.Skip));
}
else if (queryable.Skip != null && queryable.Take != null)
{
sbSql.Insert(0, "SELECT " + queryable.Select.GetSelectFiles() + " FROM ( ");
sbSql.Append(") t WHERE t.row_index BETWEEN " + (queryable.Skip + 1) + " AND " + (queryable.Skip + queryable.Take));
}
var reader = queryable.DB.GetReader(sbSql.ToString(), queryable.Params.ToArray());
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), reader, queryable.Select.GetSelectFiles());
queryable = null;
return(reval);
}
catch (Exception ex)
{
throw new Exception(string.Format("sql:{0}\r\n message:{1}", ex.Message));
}
finally
{
sbSql = null;
queryable = null;
}
}
public T GetSingle <T>(string sql, params SqlParameter[] pars)
{
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), GetReader(sql, pars), null).Single();
return(reval);
}
/// <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);
}