/// <summary>
/// 根据条件查询一个DataTable
/// </summary>
/// <typeparam name="T">动态对象</typeparam>
/// <param name="condition">筛选条件</param>
/// <param name="timeout">超时时间</param>
/// <returns></returns>
public DataTable FindTable <T>(Expression <Func <T, bool> > condition, int?timeout = Timeout) where T : class
{
DataTable res = new DataTable(typeof(T).Name);
this.Logger(this.GetType(), "根据条件查询一个DataTable-FindTable", () =>
{
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.SelectSql <T>(where, true).ToString();
if (this.BaseDbTransaction == null)
{
using (IDbConnection connection = this.BaseDbConnection)
{
var reader = connection.ExecuteReader(sql, null, null, timeout, CommandType.Text);
res.Load(reader);
}
}
else
{
var reader = this.BaseDbTransaction.Connection.ExecuteReader(sql, null, this.BaseDbTransaction, timeout, CommandType.Text);
res.Load(reader);
}
}, e =>
{
this.Rollback();
});
return(res);
}
/// <summary>
/// 根据条件以及指定属性名称更新
/// </summary>
/// <typeparam name="T">动态对象</typeparam>
/// <param name="entity">待更新实体</param>
/// <param name="condition">筛选条件</param>
/// <param name="timeout">超时时间</param>
/// <returns></returns>
public int Update <T>(object entity, Expression <Func <T, bool> > condition, int?timeout = Timeout) where T : class
{
int res = 0;
this.Logger(this.GetType(), "根据条件以及指定属性名称更新-Update", () =>
{
if (entity != null)
{
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.UpdateSql <T>(entity, "", where).ToString();
if (this.BaseDbTransaction == null)
{
using (IDbConnection connection = this.BaseDbConnection)
{
res = connection.Execute(sql, entity, null, timeout, CommandType.Text);
}
}
else
{
res = this.BaseDbTransaction.Connection.Execute(sql, entity, this.BaseDbTransaction, timeout, CommandType.Text);
}
}
}, e =>
{
this.Rollback();
});
return(res);
}
/// <summary>
/// 集合查询（Lambda）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public T FindEntity <T>(Expression <Func <T, bool> > condition)
where T : class, new()
{
var lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
return(FindEntity <T>(sql));
}
/// <summary>
/// 集合查询（Lambda）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition)
where T : new()
{
var lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
return(this.FindList <T>(sql));
}
/// <summary>
/// 分页查询-Lambda
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <param name="pagination"></param>
/// <returns></returns>
public PaginationDTO <IEnumerable <T> > FindPageList <T>(Expression <Func <T, bool> > condition, PaginationQuery pagination)
where T : new()
{
var lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
return(FindPageList <T>(sql, pagination));
}
/// <summary>
/// 根据条件查询出一个集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
using (var dbConnection = Connection)
{
return(dbConnection.Query <T>(sql).ToList());
}
}
/// <summary>
/// 根据条件查询一个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public T FindEntity <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
using (var dbConnection = Connection)
{
var data = dbConnection.Query <T>(sql);
return(data.FirstOrDefault());
}
}
/// <summary>
/// lambdas删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
public void Delete <T>(Expression <Func <T, bool> > condition)
where T : class, new()
{
var lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.DeleteSql <T>(where).ToString();
using (var db = Connection)
{
db.Execute(sql);
}
}
/// <summary>
/// 数据条数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public int Count <T>(Expression <Func <T, bool> > condition)
where T : class, new()
{
var lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
using (var db = Connection)
{
return(db.Execute(sql));
}
}
/// <summary>
/// 根据条件获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <param name="orderField">排序字段，多个用英文逗号隔开，类似：Id Asc,Name Desc</param>
/// <param name="isAsc">是否升序</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">索引</param>
/// <param name="total">总记录</param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
StringBuilder sb = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
string orderBy = "";
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
string strSql = DatabaseCommon.SelectSql <T>(where, true).ToString();
if (!string.IsNullOrEmpty(orderField))
{
if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
{
orderBy = "Order By " + orderField;
}
else
{
orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
orderBy = "Order By (Select 0)";
}
sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
using (var dbConnection = Connection)
{
string selectCountSql = "Select Count(*) From " + table + " WHERE 1 = 1";
total = (int)dbConnection.ExecuteScalar(selectCountSql);
IEnumerable <T> data = dbConnection.Query <T>(sb.ToString()).ToList();
return(data);
}
}
/// <summary>
/// 根据条件以及指定属性名称更新
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="modelModifyProps"></param>
/// <param name="condition"></param>
/// <returns></returns>
public int Update <T>(T modelModifyProps, Expression <Func <T, bool> > condition) where T : class, new()
{
int res = 0;
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.UpdateSql <T>(modelModifyProps, "", where).ToString();
//DbParameter[] parameter = DatabaseCommon.GetParameter<T>(modelModifyProps);
res = ExecuteBySql(sql, modelModifyProps);
return(res);
}
/// <summary>
/// 根据条件删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public int Delete <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
int res = 0;
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.DeleteSql <T>(where).ToString();
using (var connection = Connection)
{
res = connection.Execute(sql);
}
return(res);
}
public void TestMethod()
{
LambdaExpConditions <UserEntity> lambda = new LambdaExpConditions <UserEntity>();
lambda.Add(u => u.Where(o => o.Id == 1 && o.IsEnable == false && o.Birthday > new DateTime(2017, 10, 12)).OrderByDescending(o => o.Id));
Console.WriteLine("Where：" + lambda.Where() + "，OrderBy：" + lambda.OrderBy());
Console.WriteLine("=========================================================");
UserEntity user = new UserEntity
{
Id = 1
};
lambda = new LambdaExpConditions <UserEntity>();
lambda.AddAndWhere(() => { return(user.Id > 1); }, u => u.UserName.Contains("大师兄") && u.IsEnable == false, u => u.UserName == "大师兄" && u.IsEnable == false);
lambda.AddOrderBy(u => u.Id);
Console.WriteLine("Where：" + lambda.Where() + "，OrderBy：" + lambda.OrderBy());
Console.WriteLine("=========================================================");
}
/// <summary>
/// 根据条件更新
/// </summary>
/// <param name="modelModifyProps">要修改的列及修改后列的值集合</param>
/// <param name="condition">修改的条件</param>
/// <returns>返回受影响行数</returns>
public int Update <T>(T modelModifyProps, Expression <Func <T, bool> > condition) where T : class, new()
{
int res = 0;
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
string sql = DatabaseCommon.UpdateSql <T>(modelModifyProps, "", where).ToString();
if (SqlTransaction == null)
{
using (var conn = Connection)
{
res = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
}
else
{
res = SqlHelper.ExecuteNonQuery(SqlTransaction, CommandType.Text, sql);
//res = this.Commit();
}
return(res);
}
/// <summary>
/// 根据条件获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <param name="orderField">排序字段，多个用英文逗号隔开，类似：Id Asc,Name Desc</param>
/// <param name="isAsc">是否升序</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">索引</param>
/// <param name="total">总记录</param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
StringBuilder sb = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
string orderBy = "";
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
//表名
//string table = EntityAttributeHelper.GetEntityTable<T>();
string strSql = DatabaseCommon.SelectSql <T>(where, true).ToString();
if (!string.IsNullOrEmpty(orderField))
{
if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
{
orderBy = "Order By " + orderField;
}
else
{
orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
orderBy = "Order By (Select 0)";
}
sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
using (var dbConnection = Connection)
{
IEnumerable <T> data = new List <T>();
string selectCountSql = "Select Count(*) From (" + strSql + ") AS t";
total = (int)SqlHelper.ExecuteScalar(dbConnection, CommandType.Text, selectCountSql);
DataSet dataSet = SqlHelper.ExecuteDataset(dbConnection, CommandType.Text, sb.ToString());
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].IsExistRows())
{
DataTable dataTable = dataSet.Tables[0];
data = dataTable.DataTableToList <T>();
}
return(data);
}
//MethodCallExpression resultExp = null;
//var tempData = this.FindList<T>(condition).AsQueryable();
//string[] order = orderField.Split(',');
//foreach (string item in order)
//{
// string orderPart = item;
// orderPart = Regex.Replace(orderPart, @"\s+", " ");
// string[] orderArry = orderPart.Split(' ');
// bool sort = isAsc;
// if (orderArry.Length == 2)
// {
// sort = orderArry[1].ToUpper() == "ASC" ? true : false;
// }
// var parameter = Expression.Parameter(typeof(T), "t");
// var property = typeof(T).GetProperty(orderArry[0]);
// if (property != null)
// {
// var propertyAccess = Expression.MakeMemberAccess(parameter, property);
// var orderByExp = Expression.Lambda(propertyAccess, parameter);
// resultExp = Expression.Call(typeof(Queryable), sort ? "OrderBy" : "OrderByDescending", new Type[] { typeof(T), property.PropertyType }, tempData.Expression, Expression.Quote(orderByExp));
// }
//}
//if (resultExp != null)
// tempData = tempData.Provider.CreateQuery<T>(resultExp);
//total = tempData.Count();
//tempData = tempData.Skip<T>(pageSize * (pageIndex - 1)).Take<T>(pageSize).AsQueryable();
//return tempData.ToList();
}
/// <summary>
/// 根据条件获取分页数据
/// </summary>
/// <typeparam name="T">动态对象</typeparam>
/// <param name="condition">筛选条件</param>
/// <param name="orderField">排序字段，多个用英文逗号隔开，类似：Id Asc,Name Desc</param>
/// <param name="isAsc">是否升序</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">索引</param>
/// <param name="total">总页数</param>
/// <param name="timeout">超时时间</param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total,
int?timeout = Timeout) where T : IEntity
{
IEnumerable <T> res = default(IEnumerable <T>);
int temp = 0;
this.Logger(this.GetType(), "执行sql语句，获取分页数据-FindList", () =>
{
StringBuilder sb = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
string orderBy = "";
LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
lambda.AddAndWhere(condition);
string where = lambda.Where();
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
string strSql = DatabaseCommon.SelectSql <T>(where, true).ToString();
if (!string.IsNullOrEmpty(orderField))
{
if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
{
orderBy = "Order By " + orderField;
}
else
{
orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
orderBy = "Order By (Select 0)";
}
sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
//查询总记录数
string selectCountSql = "Select Count(*) From " + table + " WHERE 1 = 1";
if (this.BaseDbTransaction == null)
{
using (IDbConnection connection = this.BaseDbConnection)
{
temp = (int)connection.ExecuteScalar(selectCountSql);
res = connection.Query <T>(sb.ToString(), null, null, true, timeout, CommandType.Text);
}
}
else
{
temp = (int)this.BaseDbTransaction.Connection.ExecuteScalar(selectCountSql, null, this.BaseDbTransaction);
res = this.BaseDbTransaction.Connection.Query <T>(sb.ToString(), null, this.BaseDbTransaction, true, timeout, CommandType.Text);
}
}, e =>
{
this.Rollback();
});
total = temp;
return(res);
}