/// <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(); }