/// <summary>
/// 根据主键查询一个实体
/// </summary>
/// <typeparam name="T">动态对象</typeparam>
/// <param name="keyValue">主键</param>
/// <param name="timeout">超时时间</param>
/// <returns></returns>
public T FindEntity <T>(object keyValue, int?timeout = Timeout) where T : IEntity
{
T res = default(T);
this.Logger(this.GetType(), "根据主键查询一个实体-FindEntity", () =>
{
Type type = keyValue.GetType();
string key = EntityAttributeHelper.GetEntityKey <T>();
string whereStr = string.Format(type == typeof(int) ? " WHERE {0} = {1}" : " WHERE {0} = '{1}'", key, keyValue);
string sql = DatabaseCommon.SelectSql <T>(whereStr, true).ToString();
if (this.BaseDbTransaction == null)
{
using (IDbConnection connection = this.BaseDbConnection)
{
res = connection.Query <T>(sql, null, null, true, timeout, CommandType.Text).FirstOrDefault();
}
}
else
{
res = this.BaseDbTransaction.Connection.Query <T>(sql, null, this.BaseDbTransaction, true, timeout, CommandType.Text).FirstOrDefault();
}
}, e =>
{
this.Rollback();
});
return(res);
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">动态对象</typeparam>
/// <returns></returns>
public int Delete <T>(int?timeout = Timeout) where T : IEntity
{
int res = 0;
this.Logger(this.GetType(), "删除-Delete", () =>
{
string tableName = EntityAttributeHelper.GetEntityTable <T>();
string sql = DatabaseCommon.DeleteSql(tableName).ToString();
if (this.BaseDbTransaction == null)
{
using (IDbConnection connection = this.BaseDbConnection)
{
res = connection.Execute(sql, null, null, timeout, CommandType.Text);
}
}
else
{
res = this.BaseDbTransaction.Connection.Execute(sql, null, this.BaseDbTransaction, timeout, CommandType.Text);
}
}, e =>
{
this.Rollback();
});
return(res);
}
/// <summary>
/// 根据主键删除
/// </summary>
/// <typeparam name="T">动态对象</typeparam>
/// <param name="keyValue">主键</param>
/// <param name="timeout">超时时间</param>
/// <returns></returns>
public int Delete <T>(object keyValue, int?timeout = Timeout)
{
int res = 0;
this.Logger(this.GetType(), "根据主键删除-Delete", () =>
{
Type type = keyValue.GetType();
string key = EntityAttributeHelper.GetEntityKey <T>();
string whereStr = string.Format(type == typeof(int) ? " WHERE {0} = {1}" : " WHERE {0} = '{1}'", key, keyValue);
string sql = DatabaseCommon.DeleteSql <T>(whereStr).ToString();
if (this.BaseDbTransaction == null)
{
using (IDbConnection connection = this.BaseDbConnection)
{
res = connection.Execute(sql, null, null, timeout, CommandType.Text);
}
}
else
{
res = this.BaseDbTransaction.Connection.Execute(sql, null, this.BaseDbTransaction, timeout, CommandType.Text);
}
}, e =>
{
this.Rollback();
});
return(res);
}
/// <summary>
/// 得到一个集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public IEnumerable <T> FindList <T>() where T : class, new()
{
using (var dbConnection = Connection)
{
return(dbConnection.Query <T>(string.Format("SELECT * FROM {0} ", EntityAttributeHelper.GetEntityTable <T>())).ToList());
}
}
/// <summary>
/// 根据主键删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="keyValue"></param>
/// <param name="transaction"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public int Delete <T>(IDbConnection connection, object keyValue, IDbTransaction transaction = null, int?timeout = Timeout) where T : class
{
int res = 0;
this.Logger(this.GetType(), "根据主键删除-Delete", () =>
{
Type type = keyValue.GetType();
string key = EntityAttributeHelper.GetEntityKey <T>();
string whereStr = " WHERE 1 = 1 ";
whereStr = string.Format(type == typeof(int) ? " WHERE {0} = {1}" : " WHERE {0} = '{1}'", key, keyValue);
string sql = DatabaseCommon.DeleteSql <T>(whereStr).ToString();
res = connection.Execute(sql, null, transaction, timeout);
}, e =>
{
if (transaction != null)
{
transaction.Rollback();
}
connection.Close();
connection.Dispose();
}, () =>
{
});
return(res);
}
/// <summary>
/// 根据主键查询一个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="keyValue"></param>
/// <param name="transaction"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public T FindEntity <T>(IDbConnection connection, object keyValue, IDbTransaction transaction = null, int?timeout = Timeout) where T : class
{
T res = null;
this.Logger(this.GetType(), "根据主键查询一个实体-FindEntity", () =>
{
Type type = keyValue.GetType();
string key = EntityAttributeHelper.GetEntityKey <T>();
string whereStr = " WHERE 1 = 1 ";
whereStr = string.Format(type == typeof(int) ? " WHERE {0} = {1}" : " WHERE {0} = '{1}'", key, keyValue);
string sql = DatabaseCommon.SelectSql <T>(whereStr, true).ToString();
res = connection.Query <T>(sql, null, transaction, true, timeout).FirstOrDefault();
}, e =>
{
if (transaction != null)
{
transaction.Rollback();
}
connection.Close();
connection.Dispose();
}, () =>
{
});
return(res);
}
/// <summary>
/// 根据主键获取一条数据
/// </summary>
/// <param name="keyValue">主键值</param>
/// <returns></returns>
public T FindEntity <T>(object keyValue) where T : class, new()
{
T res = default(T);
string keyField = EntityAttributeHelper.GetEntityKey <T>();
string where = $"Where [{keyField}] = ";
if (keyValue is int)
{
where += $"{keyValue}";
}
else if (keyValue is string)
{
where += $"'{keyValue}'";
}
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
using (var conn = Connection)
{
DataSet data = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
if (data.Tables[0].IsExistRows())
{
DataTable table = data.Tables[0];
res = table.DataTableToObject <T>();
}
}
return(res);
}
/// <summary>
/// 根据条件获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <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="transaction"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public Tuple <IEnumerable <T>, int> FindList <T>(IDbConnection connection, Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, IDbTransaction transaction = null, int?timeout = Timeout) where T : class
{
IEnumerable <T> res = null;
int temp = 0;
this.Logger(this.GetType(), "根据条件获取分页数据-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";
temp = (int)connection.ExecuteScalar(selectCountSql, null, transaction);
res = connection.Query <T>(sb.ToString(), null, transaction, true, timeout);
}, e =>
{
if (transaction != null)
{
transaction.Rollback();
}
connection.Close();
connection.Dispose();
}, () =>
{
});
return(new Tuple <IEnumerable <T>, int>(res, temp));
}
/// <summary>
/// 根据主键删除一条数据
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public int Delete <T>(object keyValue) where T : class
{
int res = 0;
string keyField = EntityAttributeHelper.GetEntityKey <T>();
string where = $"Where [{keyField}] = ";
if (keyValue is int)
{
where += $"{keyValue}";
}
else if (keyValue is string)
{
where += $"'{keyValue}'";
}
string sql = DatabaseCommon.DeleteSql <T>(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);
}
public override void Apply(AttributeContext context, Attribute attribute, EntityInfo entity)
{
if (string.IsNullOrWhiteSpace(this.MemberNames))
{
context.Log.Error("Entity {0}: primary key must specify property name(s) when used on Entity interface.", entity.FullName);
return;
}
if (!CreatePrimaryKey(context, entity))
{
return;
}
var error = false;
var pkMembers = EntityAttributeHelper.ParseMemberNames(entity, this.MemberNames, errorAction: mn => {
context.Log.Error("Entity {0}: property {1} listed in PrimaryKey attribute not found.", entity.FullName, mn);
error = true;
});
if (error)
{
return;
}
foreach (var km in pkMembers)
{
km.Member.Flags |= EntityMemberFlags.PrimaryKey;
entity.PrimaryKey.KeyMembers.Add(km);
}
}
/// <summary>
/// 拼接 查询 SQL语句，自定义条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where">条件</param>
/// <param name="allFieid">是否查询所有字段</param>
/// <returns></returns>
public static StringBuilder SelectSql <T>(string where, bool allFieid = false) where T : new()
{
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
PropertyInfo[] props = EntityAttributeHelper.GetProperties(typeof(T));
StringBuilder sbColumns = new StringBuilder();
if (allFieid)
{
sbColumns.Append(" * ");
}
else
{
foreach (PropertyInfo prop in props)
{
//string propertytype = prop.PropertyType.ToString();
sbColumns.Append("[" + prop.Name + "],");
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
}
if (string.IsNullOrWhiteSpace(where))
{
where = " WHERE 1 = 1";
}
string strSql = "SELECT {0} FROM {1} {2}";
strSql = string.Format(strSql, sbColumns.ToString(), table + " ", where);
return(new StringBuilder(strSql));
}
/// <summary>
/// 根据主键删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="keyValue"></param>
/// <returns></returns>
public int Delete <T>(object keyValue) where T : class
{
int res = 0;
Type type = keyValue.GetType();
string key = EntityAttributeHelper.GetEntityKey <T>();
string where = " WHERE 1 = 1";
if (type == typeof(int))
{
where = $" WHERE {key} = {keyValue}";
}
else
{
where = $" WHERE {key} = '{keyValue}'";
}
string sql = DatabaseCommon.DeleteSql <T>(where).ToString();
using (var connection = Connection)
{
res = connection.Execute(sql);
}
return(res);
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public int Delete <T>() where T : class
{
int res = 0;
string tableName = EntityAttributeHelper.GetEntityTable <T>();
string sql = DatabaseCommon.DeleteSql(tableName).ToString();
res = ExecuteBySql(sql);
return(res);
}
/// <summary>
/// 拼接删除语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="allFieid"></param>
/// <returns></returns>
public static StringBuilder DeleteSql <T>(string where, bool allFieid = false) where T : new()
{
if (string.IsNullOrWhiteSpace(where))
{
where = " WHERE 1 = 1";
}
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
var sql = $"DELETE FROM {table} {where}";
return(new StringBuilder(sql));
}
/// <summary>
/// 根据T-SQL获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSql">T-SQL语句</param>
/// <param name="dbParameter">DbCommand参数</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>(string strSql, DbParameter[] dbParameter, 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 = "";
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
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 " + table + " WHERE 1 = 1";
total = (int)SqlHelper.ExecuteScalar(dbConnection, CommandType.Text, selectCountSql);
DataSet dataSet = SqlHelper.ExecuteDataset(dbConnection, CommandType.Text, sb.ToString(), DatabaseCommon.DbParameterToSqlParameter(dbParameter));
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].IsExistRows())
{
DataTable dataTable = dataSet.Tables[0];
data = dataTable.DataTableToList <T>();
}
return(data);
}
}
/// <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);
}
}
//This is a special case - OrderBy attribute specifies the order of entities in list property.
public override void Apply(AttributeContext context, Attribute attribute, EntityMemberInfo member)
{
var entity = member.Entity;
if (member.Kind != MemberKind.EntityList)
{
context.Log.Error("OrderBy attribute may be used only on entities or list properties. Entity: {0}, property: {1}",
entity.Name, member.MemberName);
return;
}
var fromKey = member.ChildListInfo.ParentRefMember.ReferenceInfo.FromKey;
fromKey.OrderByForSelect = EntityAttributeHelper.ParseMemberNames(member.ChildListInfo.TargetEntity, this.OrderByList, ordered: true,
errorAction: spec => {
context.Log.Error("Invalid member/spec: {0} in {1} attribute, property {2}.{3}", spec, this.GetAttributeName(), entity.Name, member.MemberName);
});
}
/// <summary>
/// 根据T-SQL获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSql">T-SQL语句</param>
/// <param name="dbParameter">DbCommand参数</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>(string strSql, DbParameter[] dbParameter, 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 = "";
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
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);
T t = dbParameter.DbParameterToObject <T>();
IEnumerable <T> data = dbConnection.Query <T>(sb.ToString(), t).ToList();
return(data);
}
}
/// <summary>
/// 根据主键查询一个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="keyValue"></param>
/// <returns></returns>
public T FindEntity <T>(object keyValue) where T : class, new()
{
Type type = keyValue.GetType();
string key = EntityAttributeHelper.GetEntityKey <T>();
string where = " WHERE 1 = 1";
if (type == typeof(int))
{
where = $" WHERE {key} = {keyValue}";
}
else
{
where = $" WHERE {key} = '{keyValue}'";
}
string sql = DatabaseCommon.SelectSql <T>(where).ToString();
using (var dbConnection = Connection)
{
var data = dbConnection.Query <T>(sql);
return(data.FirstOrDefault());
}
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public int Delete <T>(IDbConnection connection, IDbTransaction transaction = null, int?timeout = Timeout) where T : class
{
int res = 0;
this.Logger(this.GetType(), "删除-Delete", () =>
{
string tableName = EntityAttributeHelper.GetEntityTable <T>();
string sql = DatabaseCommon.DeleteSql(tableName).ToString();
res = connection.Execute(sql, null, transaction, timeout);
}, e =>
{
if (transaction != null)
{
transaction.Rollback();
}
connection.Close();
connection.Dispose();
}, () =>
{
});
return(res);
}
private void ConstructOrderBy(AttributeContext context, EntityInfo entity)
{
bool error = false;
entity.DefaultOrderBy = EntityAttributeHelper.ParseMemberNames(entity, OrderByList, ordered: true,
errorAction: spec => {
context.Log.Error("Invalid member/spec: {0} in {1} attribute on entity {2}", spec, this.GetAttributeName(), entity.Name);
error = true;
});
if (error)
{
return;
}
//Check that they are real cols
foreach (var ordM in entity.DefaultOrderBy)
{
if (ordM.Member.Kind != MemberKind.Column)
{
context.Log.Error("Invalid property {0} in OrderBy attribute in entity {1} - must be a plain property.",
ordM.Member.MemberName, entity.Name);
}
}
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public int Delete <T>() where T : class
{
return(ExecuteBySql(DatabaseCommon.DeleteSql(EntityAttributeHelper.GetEntityTable <T>()).ToString()));
}
/// <summary>
/// 根据属性名称删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="propertyValue"></param>
/// <param name="propertyName"></param>
/// <returns></returns>
public int Delete <T>(object propertyValue, string propertyName) where T : class
{
bool isTrans = true;
if (DbTransaction == null)
{
BeginTrans();
isTrans = false;
}
IEnumerable <T> entitys = DbTransaction.Connection.Query <T>(string.Format("select * from {0} where {1}=?propertyValue", EntityAttributeHelper.GetEntityTable <T>(), propertyName), new { propertyValue = propertyValue });
foreach (var entity in entitys)
{
Delete <T>(entity);
}
if (!isTrans)
{
return(Commit());
}
return(0);
}
/// <summary>
/// 根据主键查询一个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="keyValue"></param>
/// <returns></returns>
public T FindEntity <T>(object keyValue) where T : class, new()
{
using (var dbConnection = Connection)
{
var data = dbConnection.Query <T>(string.Format("select * from {0} where {1}=?key", EntityAttributeHelper.GetEntityTable <T>(), EntityAttributeHelper.GetEntityKey <T>()), new { key = keyValue });
return(data.FirstOrDefault());
}
}
/// <summary>
/// 执行sql语句，获取分页数据
/// </summary>
/// <typeparam name="T">动态对象</typeparam>
/// <param name="strSql">T-SQL语句</param>
/// <param name="parameters">参数</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>(string strSql, object parameters, 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 = "";
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
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(), parameters, null, true, timeout, CommandType.Text);
}
}
else
{
temp = (int)this.BaseDbTransaction.Connection.ExecuteScalar(selectCountSql, null, this.BaseDbTransaction);
res = this.BaseDbTransaction.Connection.Query <T>(sb.ToString(), parameters, this.BaseDbTransaction, true, timeout, CommandType.Text);
}
}, e =>
{
this.Rollback();
});
total = temp;
return(res);
}
/// <summary>
/// 根据主键删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="keyValue"></param>
/// <returns></returns>
public int Delete <T>(object keyValue) where T : class
{
T entity = DbTransaction.Connection.Query <T>(string.Format("select * from {0} where {1}=?primarykey", EntityAttributeHelper.GetEntityTable <T>(), EntityAttributeHelper.GetEntityKey <T>()), new { primarykey = keyValue }).FirstOrDefault();
return(Delete <T>(entity));
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <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>(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 = "";
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
string strSql = DatabaseCommon.SelectSql(table).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 " + table + " WHERE 1 = 1";
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);
}
//IQueryable<T> tempData = this.FindList<T>().AsQueryable();
//string[] order = !string.IsNullOrEmpty(orderField) ? orderField.Split(',') : new[] { "" };
//MethodCallExpression resultExp = null;
//try
//{
// if (!string.IsNullOrEmpty(order[0]))
// {
// 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);
// tempData = tempData.Skip<T>(pageSize * (pageIndex - 1)).Take<T>(pageSize).AsQueryable();
//}
//catch (Exception e)
//{
// Console.WriteLine(e);
//}
//total = tempData.Count();
//return tempData.ToList();
}
