/// <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="condition">筛选条件</param> /// <param name="timeout">超时时间</param> /// <returns></returns> public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, int?timeout = Timeout) where T : IEntity { IEnumerable <T> res = default(IEnumerable <T>); this.Logger(this.GetType(), "根据条件查询出一个集合-FindList", () => { 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) { res = connection.Query <T>(sql, null, null, true, timeout, CommandType.Text); } } else { res = this.BaseDbTransaction.Connection.Query <T>(sql, null, this.BaseDbTransaction, true, timeout, CommandType.Text); } }, e => { this.Rollback(); }); return(res); }
/// <summary> /// 根据条件获取一条数据,返回对象集合 /// </summary> /// <param name="condition">条件</param> /// <returns></returns> public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition) where T : class, new() { List <T> res = new List <T>(); LambdaExpConditions <T> lambda = new LambdaExpConditions <T>(); lambda.AddAndWhere(condition); string where = lambda.Where(); string sql = DatabaseCommon.SelectSql <T>(where).ToString(); if (SqlTransaction == null) { using (var conn = Connection) { DataSet data = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql); if (data.Tables[0].IsExistRows()) { DataTable table = data.Tables[0]; res = table.DataTableToList <T>(); } } } else { DataSet data = SqlHelper.ExecuteDataset(SqlTransaction, CommandType.Text, sql); if (data.Tables[0].IsExistRows()) { DataTable table = data.Tables[0]; res = table.DataTableToList <T>(); } } return(res); }
/// <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> /// <param name="timeout">超时时间</param> /// <returns></returns> public IEnumerable <T> FindList <T>(int?timeout = Timeout) where T : IEntity { IEnumerable <T> res = default(IEnumerable <T>); this.Logger(this.GetType(), "得到一个集合-FindList", () => { string sql = DatabaseCommon.SelectSql <T>("", true).ToString(); if (this.BaseDbTransaction == null) { using (IDbConnection connection = this.BaseDbConnection) { res = connection.Query <T>(sql, null, null, true, timeout, CommandType.Text); } } else { res = this.BaseDbTransaction.Connection.Query <T>(sql, null, this.BaseDbTransaction, true, timeout, CommandType.Text); } }, e => { this.Rollback(); }); 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> /// 根据条件查询一个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 : IEntity { 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> /// 获取IQueryable对象 /// </summary> /// <typeparam name="T">动态对象</typeparam> /// <param name="timeout">超时时间</param> /// <returns></returns> public IQueryable <T> IQueryable <T>(int?timeout = Timeout) where T : class { IQueryable <T> res = default(IQueryable <T>); this.Logger(this.GetType(), "获取IQueryable对象-IQueryable", () => { string sql = DatabaseCommon.SelectSql <T>("", true).ToString(); if (this.BaseDbTransaction == null) { using (IDbConnection connection = this.BaseDbConnection) { res = connection.Query <T>(sql, null, null, true, timeout, CommandType.Text).AsQueryable(); } } else { res = this.BaseDbTransaction.Connection.Query <T>(sql, null, this.BaseDbTransaction, true, timeout, CommandType.Text).AsQueryable(); } }, e => { this.Rollback(); }); 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> /// 根据条件查询一个DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connection"></param> /// <param name="condition"></param> /// <param name="transaction"></param> /// <param name="timeout"></param> /// <returns></returns> public DataTable FindTable <T>(IDbConnection connection, Expression <Func <T, bool> > condition, IDbTransaction transaction = null, int?timeout = Timeout) where T : class { DataTable res = new DataTable(); this.Logger(this.GetType(), "根据条件查询一个DataTable-FindDataTable", () => { LambdaExpConditions <T> lambda = new LambdaExpConditions <T>(); lambda.AddAndWhere(condition); string where = lambda.Where(); string sql = DatabaseCommon.SelectSql <T>(where, true).ToString(); var reader = connection.ExecuteReader(sql, null, transaction, timeout, CommandType.Text); res.Load(reader); }, e => { if (transaction != null) { transaction.Rollback(); } connection.Close(); connection.Dispose(); }, () => { }); return(res); }
/// <summary> /// 根据条件查询出一个集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connection"></param> /// <param name="condition"></param> /// <param name="transaction"></param> /// <param name="timeout"></param> /// <returns></returns> public IEnumerable <T> FindList <T>(IDbConnection connection, Expression <Func <T, bool> > condition, IDbTransaction transaction = null, int?timeout = Timeout) where T : class { IEnumerable <T> res = null; this.Logger(this.GetType(), "根据条件查询出一个集合-FindList", () => { LambdaExpConditions <T> lambda = new LambdaExpConditions <T>(); lambda.AddAndWhere(condition); string where = lambda.Where(); string sql = DatabaseCommon.SelectSql <T>(where, true).ToString(); res = connection.Query <T>(sql, null, transaction, true, timeout); }, e => { if (transaction != null) { transaction.Rollback(); } connection.Close(); connection.Dispose(); }, () => { }); return(res); }
/// <summary> /// 获取IQueryable /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public IQueryable <T> IQueryable <T>() where T : class, new() { string sql = DatabaseCommon.SelectSql <T>("").ToString(); using (var dbConnection = Connection) { return((IQueryable <T>)dbConnection.Query <T>(sql)); } }
/// <summary> /// 得到一个集合 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public IEnumerable <T> FindList <T>() where T : class, new() { string sql = DatabaseCommon.SelectSql <T>("").ToString(); using (var dbConnection = Connection) { return(dbConnection.Query <T>(sql).ToList()); } }
/// <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> /// <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> /// 集合查询(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> /// 根据条件查询出一个集合 /// </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 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> /// <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> /// 根据条件获取分页数据 /// </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="connection"></param> /// <param name="transaction"></param> /// <param name="timeout"></param> /// <returns></returns> public IEnumerable <T> FindList <T>(IDbConnection connection, IDbTransaction transaction = null, int?timeout = Timeout) where T : class { IEnumerable <T> res = null; this.Logger(this.GetType(), "得到一个集合-FindList", () => { string sql = DatabaseCommon.SelectSql <T>("", true).ToString(); res = connection.Query <T>(sql, null, transaction, true, timeout); }, e => { if (transaction != null) { transaction.Rollback(); } connection.Close(); connection.Dispose(); }, () => { }); return(res); }
/// <summary> /// 获取IQueryable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connection"></param> /// <param name="transaction"></param> /// <param name="timeout"></param> /// <returns></returns> public IQueryable <T> IQueryable <T>(IDbConnection connection, IDbTransaction transaction = null, int?timeout = Timeout) where T : class { IQueryable <T> res = null; this.Logger(this.GetType(), "获取IQueryable-IQueryable", () => { string sql = DatabaseCommon.SelectSql <T>("").ToString(); res = (IQueryable <T>)connection.Query <T>(sql, null, transaction, true, timeout); }, e => { if (transaction != null) { transaction.Rollback(); } connection.Close(); connection.Dispose(); }, () => { }); return(res); }
/// <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> /// <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); }
/// <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(); }
public void ImportExcel(string fid, DataTable dt, out DataTable Result) { //获得导入模板 //模板主表 ExcelImportEntity base_excellimport = GetEntity(fid); //string pkName = DatabaseCommon.GetKeyField("LeaRun.Entity." + base_excellimport.ImportTable).ToString(); //模板明细表 var listBase_ExcelImportDetail = GetDetails(fid); //构造导入返回结果表 DataTable Newdt = new DataTable("Result"); foreach (ExcelImportFiledEntity excelImportDetail in listBase_ExcelImportDetail) { if (excelImportDetail.F_RelationType == 0 || excelImportDetail.F_RelationType == 2 || excelImportDetail.F_RelationType == 3) { Newdt.Columns.Add(excelImportDetail.F_ColName, typeof(System.String)); } } Newdt.Columns.Add("learunColOk", typeof(System.String)); //位置 Newdt.Columns.Add("learunColError", typeof(System.String)); //原因 //取出要插入的表名 string tableName = base_excellimport.F_DbTable; //////////////// if (dt != null && dt.Rows.Count > 0) { IRepository db = this.BaseRepository().BeginTrans(); try { #region 遍历Excel数据行 //bool IsOk = true; int learunColOk = 1; string strError = ""; foreach (DataRow item in dt.Rows) { Hashtable entity = new Hashtable();//最终要插入数据库的hashtable StringBuilder sb = new StringBuilder(); //entity[pkName] = Guid.NewGuid().ToString();//首先给主键赋值 DataRow dr = Newdt.NewRow(); dr = Newdt.NewRow(); #region 遍历模板,为每一行中每个字段找到模板列并赋值 int i = 0; foreach (ExcelImportFiledEntity excelImportDetail in listBase_ExcelImportDetail) { string value = ""; if (excelImportDetail.F_RelationType == 0 || excelImportDetail.F_RelationType == 2 || excelImportDetail.F_RelationType == 3) { value = item[excelImportDetail.F_ColName].ToString(); dr[i] = value; } #region 单个字段赋值 switch (excelImportDetail.F_RelationType) { //字符串 case 0: entity[excelImportDetail.F_FliedName] = value; i++; break; //GUID case 1: entity[excelImportDetail.F_FliedName] = Guid.NewGuid().ToString(); break; //数据字典 case 2: i++; entity[excelImportDetail.F_DbSaveFlied] = value; //查询Excel字符串是否存在于外表 sb = DatabaseCommon.SelectSql("Base_DataItem"); sb.Append(" and " + excelImportDetail.F_DataItemEncode + "='" + value + "'"); DataTable dt0 = this.BaseRepository().FindTable(sb.ToString()); if (dt0.Rows.Count == 0) { //不存在此外键 learunColOk = 0; strError += "【 数据字典 】 找不到对应的数据"; } sb.Remove(0, sb.Length); break; //数据表 case 3: i++; entity[excelImportDetail.F_DbSaveFlied] = value; //查询Excel字符串是否存在于外表 sb = DatabaseCommon.SelectSql(tableName); sb.Append(" and " + excelImportDetail.F_DbSaveFlied + "='" + value + "'"); DataTable dt1 = this.BaseRepository().FindTable(sb.ToString()); if (dt1.Rows.Count == 0) { //不存在此外键 learunColOk = 0; strError += "【" + excelImportDetail.F_ColName + "】 找不到对应的数据"; } sb.Remove(0, sb.Length); break; //固定数值 case 4: entity[excelImportDetail.F_FliedName] = excelImportDetail.F_Value; break; //操作人 case 5: entity[excelImportDetail.F_FliedName] = OperatorProvider.Provider.Current().UserId; break; //操作人名字 case 6: entity[excelImportDetail.F_FliedName] = OperatorProvider.Provider.Current().UserName; break; //操作人时间 case 7: entity[excelImportDetail.F_FliedName] = DateTime.Now; break; default: break; } #endregion 单字段赋值结束 } dr[i] = learunColOk; dr[i + 1] = strError; #endregion 遍历模板结束 //写入表 if (learunColOk == 0) { continue; } StringBuilder strSql = DatabaseCommon.InsertSql(tableName, entity); DbParameter[] parameter = DatabaseCommon.GetParameter(entity); Newdt.Rows.Add(dr); db.ExecuteBySql(strSql.ToString(), parameter); } #endregion 遍历Excel数据行结束 db.Commit(); } catch (System.Exception) { db.Rollback(); throw; } } Result = Newdt; }