/// <summary>
/// Not Exists查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="NotExistsT"></typeparam>
/// <param name="filter"></param>
/// <param name="notExistsFilter"></param>
/// <param name="fields"></param>
/// <returns></returns>
public static List <T> SelectNotExists <T, NotExistsT>(Expression <Func <T, NotExistsT, bool> > notExistsFilter, Expression <Func <T, bool> > filter = null, params Expression <Func <T, object> >[] fields)
{
string strSql = SelectScript(filter, null, fields);
var model = Activator.CreateInstance <T>();
Type t = model.GetType(); //获得该类的Type
string tbName = GetTableName(t); //表名
string nickName = " as " + notExistsFilter.Parameters[0];
strSql = strSql.Replace(tbName, tbName + nickName);
if (strSql.IndexOf(" where ") > 0)
{
strSql += " and " + NotExists(notExistsFilter);
}
else
{
strSql += " where " + NotExists(notExistsFilter);
}
List <T> list = new List <T>();
var dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
list = ModelUtil.DataTableParse <T>(dt);
return(list);
}
/// <summary>
/// 根据主键值查询获取一条数据
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="keyValue">主键值</param>
/// <returns></returns>
public static T GetModel <T>(object keyValue)
{
List <SqlParameter> parameters = new List <SqlParameter>();
var model = Activator.CreateInstance <T>();
StringBuilder strSql = new StringBuilder();
string tbName = string.Empty; //表名
List <string> keyNames = new List <string>(); //主键字段名
CreateKeyParameter <T>(keyValue, ref parameters, ref tbName, ref keyNames);
strSql.AppendFormat("select * from {0}", tbName);
if (keyNames.Count > 0)
{
strSql.Append(" where ");
}
for (int i = 0; i < keyNames.Count; i++)
{
if (i > 0)
{
strSql.Append(" and ");
}
strSql.AppendFormat("{0} = @{0}", keyNames[i]);
}
var dt = DbHelperSQL.Query(strSql.ToString(), parameters.ToArray()).Tables[0];
model = ModelUtil.DataTableParseSingle <T>(dt);
return(model);
}
/// <summary>
/// 根据条件查询
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="filter">查询条件：lambda条件过滤表达式</param>
/// <param name="order">排序表达式</param>
/// <param name="fields">查询字段：lambda字段表达式【可多组】</param>
/// <returns>列表实体</returns>
public static List <T> Select <T>(Expression <Func <T, bool> > filter = null, OrderBy <T> order = null, params Expression <Func <T, object> >[] fields)
{
string strWhere = string.Empty;
string strFields = string.Empty;
string orderBy = string.Empty;
if (filter != null)
{
strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer);
}
if (order != null)
{
orderBy = OrderByUtil.GetOrderBy <T>(order.GetOrderByList());
}
foreach (var f in fields)
{
var fieldName = ExpressionField.GetFieldName <T>(f);
strFields = strFields + "," + fieldName;
}
if (string.IsNullOrEmpty(strFields))
{
strFields = "*";
}
else
{
strFields = strFields.Trim(',');
}
List <T> list = new List <T>();
StringBuilder strSql = new StringBuilder();
var model = Activator.CreateInstance <T>();
Type t = model.GetType(); //获得该类的Type
string tbName = GetTableName(t); //表名
strSql.AppendFormat("select {0} ", strFields);
strSql.AppendFormat(" from {0} ", tbName);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.AppendFormat(" where {0} ", strWhere);
}
if (!string.IsNullOrEmpty(orderBy))
{
strSql.Append(orderBy);
}
var dt = DbHelperSQL.Query(strSql.ToString(), null).Tables[0];
list = ModelUtil.DataTableParse <T>(dt);
return(list);
}
/// <summary>
/// 多表联合查询(left join)
/// </summary>
/// <typeparam name="T">返回的数据实体类型</typeparam>
/// <typeparam name="T1">表1</typeparam>
/// <typeparam name="T2">表2</typeparam>
/// <typeparam name="T3">表3</typeparam>
/// <typeparam name="T4">表4</typeparam>
/// <typeparam name="T5">表5</typeparam>
/// <typeparam name="T6">表6</typeparam>
/// <param name="joinExpression">联接条件</param>
/// <param name="filter">查询条件</param>
/// <param name="order">排序</param>
/// <param name="fields">查询字段</param>
/// <returns>数据实体列表</returns>
public static List <T> Join <T, T1, T2, T3, T4, T5, T6>(Expression <Func <T1, T2, T3, T4, T5, T6, bool> > joinExpression, Expression <Func <T1, T2, T3, T4, T5, T6, bool> > filter = null, OrderBy <T> order = null,
params Expression <Func <T1, T2, T3, T4, T5, T6, object> >[] fields)
{
string strSql = JoinScript(joinExpression, filter, order, fields);
List <T> list = new List <T>();
var dt = DbHelperSQL.Query(strSql).Tables[0];
list = ModelUtil.DataTableParse <T>(dt);
return(list);
}
/// <summary>
/// 根据条件查询
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="filter">查询条件：lambda条件过滤表达式</param>
/// <param name="order">排序表达式</param>
/// <param name="fields">查询字段：lambda字段表达式【可多组】</param>
/// <returns>列表实体</returns>
public static List <T> Select <T>(Expression <Func <T, bool> > filter = null, OrderBy <T> order = null, params Expression <Func <T, object> >[] fields)
{
string strSql = SelectScript(filter, order, fields);
List <T> list = new List <T>();
var dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
list = ModelUtil.DataTableParse <T>(dt);
return(list);
}
/// <summary>
/// 根据条件查询一条记录
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="filter">查询条件：lambda条件过滤表达式</param>
/// <param name="order">排序表达式</param>
/// <param name="fields">查询字段：lambda字段表达式【可多组】</param>
/// <returns>列表实体</returns>
public static T SelectSingleRecord <T>(Expression <Func <T, bool> > filter = null, OrderBy <T> order = null, params Expression <Func <T, object> >[] fields)
{
string strSql = SelectScript(filter, order, fields);
List <T> list = new List <T>();
var dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
list = ModelUtil.DataTableParse <T>(dt);
if (list != null && list.Count > 0)
{
return(list[0]);
}
else
{
return(default(T));
}
}
/// <summary>
/// SqlBulkCopy批量提交数据[复制]
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="modelList">列表实体</param>
/// <returns></returns>
public static void SqlBulkCopy <T>(List <T> modelList)
{
DataTable dataTable = ModelUtil.ModelList2DataTable <T>(modelList);
DbHelperSQL.SqlBulkCopyByDataTable(dataTable.TableName, dataTable);
}
/// <summary>
/// SqlDataAdapter批量更新
/// </summary>
/// <typeparam name="T">表实体类</typeparam>
/// <param name="modelList">列表实体</param>
/// <returns>返回更新影响的行数</returns>
public static int BatchUpdate <T>(List <T> modelList)
{
DataTable dataTable = ModelUtil.ModelList2DataTable <T>(modelList);
return(DbHelperSQL.BatchUpdate(dataTable.TableName, dataTable));
}