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