/// <summary>
/// 查询数据.
/// 直接从数据库中查询， 不走缓存。
/// </summary>
/// <typeparam name="T">类型值</typeparam>
/// <param name="sqlCriteria">条件集</param>
/// <returns>结果集</returns>
public List <T> Query <T>(SqlCriteria sqlCriteria)
where T : class
{
int recordCount = 0;
return(Query <T>(sqlCriteria, null, -1, -1, ref recordCount));
}
/// <summary>
/// 查询数据.
/// 若不分页查询，并且条件集为null， 并且类型特性上说明缓存的， 则走缓存。
/// 否则直接从数据库中查询， 不走缓存。
/// </summary>
/// <typeparam name="T">类型值</typeparam>
/// <param name="sqlCriteria">条件集</param>
/// <param name="orderbyClause">排序集</param>
/// <param name="pageIndex">起始页</param>
/// <param name="pageSize">页大小</param>
/// <param name="recordCount">结果集大小</param>
/// <returns>结果集</returns>
public List <T> Query <T>(SqlCriteria sqlCriteria, string orderbyClause,
int pageIndex, int pageSize, ref int recordCount)
where T : class
{
//if (string.IsNullOrEmpty(orderbyClause))
//{
// orderbyClause = OrmUtil.GetIdentityColumnName<T>();
//}
List <T> list = new List <T>();
string tableName = OrmUtil.GetTableName <T>();
if (OrmUtil.CheckCacheFlag <T>())
{
RegisterCacheTable(tableName, true);
}
DataTable dataTable = Query(tableName, "*", sqlCriteria, orderbyClause, pageIndex, pageSize, ref recordCount);
if (dataTable != null)
{
list = ConvertUtil.ConvertDataToObject <T>(dataTable);
}
return(list);
}
private void InitParameterName(SqlCriteria sqlCriteria, ref int index)
{
sqlCriteria.blurFlag = false;
foreach (Criterion item in sqlCriteria.innerList)
{
if (item.Value != null && item.NeedParameter)
{
string itemParamName = Regex.Replace(item.LeftExpression, @"([\(\)])+", "_");
item.ParameterName = string.Format(parameterNameTemplate, itemParamName, index);
DbParameter dbParameter = Owner.DbDriver.CreateParameter();
dbParameter.ParameterName = item.ParameterName;
dbParameter.Value = item.Value;
dbParameterList.Add(dbParameter);
index++;
}
}
foreach (SqlCriteria item in sqlCriteria.root.Values)
{
if (item.guid != sqlCriteria.guid)
{
InitParameterName(item, ref index);
}
}
}
public DataTable ExecuteCommandWhere(string cmdTextWithWhere, SqlCriteria sqlCriteria)
{
ThrowExceptionUtil.ArgumentNotNullOrEmpty(cmdTextWithWhere, "cmdTextWithWhere");
CmdTextWithWhereBeeCommand cmdTextBeeCommand = new CmdTextWithWhereBeeCommand(this, cmdTextWithWhere, sqlCriteria);
return(cmdTextBeeCommand.Excute <DataTable>());
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCriteria">条件集</param>
public void Delete(string tableName, SqlCriteria sqlCriteria)
{
ThrowExceptionUtil.ArgumentNotNullOrEmpty(tableName, "tableName");
DeleteBeeCommand deleteBeeCommand =
new DeleteBeeCommand(this, tableName, sqlCriteria);
deleteBeeCommand.Excute <int>();
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="sqlCriteria">条件集</param>
public void Delete <T>(SqlCriteria sqlCriteria)
where T : class
{
string tableName = OrmUtil.GetTableName <T>();
DeleteBeeCommand deleteBeeCommand = new DeleteBeeCommand(this, tableName, sqlCriteria);
deleteBeeCommand.Excute <int>();
}
// Methods
public UpdateBeeCommand(DbSession owner, string tableName, BeeDataAdapter data, SqlCriteria sqlCriteria)
: base(owner, BeeDbCommandBehavior.NoQuery)
{
ThrowExceptionUtil.ArgumentNotNullOrEmpty(tableName, "tableName");
ThrowExceptionUtil.ArgumentConditionTrue(data != null && data.Count != 0, "data", "data should not be null or empty.");
this.tableName = tableName;
this.dataAdapter = data;
this.sqlCriteria = sqlCriteria;
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dataAdapter">数据集</param>
/// <param name="sqlCriteria">条件集</param>
public void Update(string tableName, BeeDataAdapter dataAdapter, SqlCriteria sqlCriteria)
{
ThrowExceptionUtil.ArgumentNotNullOrEmpty(tableName, "tableName");
ThrowExceptionUtil.ArgumentNotNull(dataAdapter, "dataAdapter");
UpdateBeeCommand updateBeeCommand =
new UpdateBeeCommand(this, tableName, dataAdapter, sqlCriteria);
updateBeeCommand.Excute <int>();
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="selectClause">需要查询的字段集</param>
/// <param name="sqlCriteria">条件集</param>
/// <param name="orderbyClause">排序集</param>
/// <param name="pageIndex">起始页</param>
/// <param name="pageSize">页大小</param>
/// <param name="recordCount">结果集大小</param>
/// <returns>结果集</returns>
public DataTable Query(string tableName, string selectClause,
SqlCriteria sqlCriteria, string orderbyClause, int pageIndex, int pageSize, ref int recordCount)
{
ThrowExceptionUtil.ArgumentNotNullOrEmpty(tableName, "tableName");
DataTable cacheData = CacheManager.Instance.GetEntity <DataTable>(Constants.DBTableCacheName.FormatWith(tableName));
// 需要被缓存
if (cacheData != null)
{
if (cacheData.Rows.Count == 0)
{
QueryBeeCommand queryBeeCommand = new QueryBeeCommand(this, tableName, "*",
null, orderbyClause, -1, -1);
cacheData = queryBeeCommand.Excute <DataTable>();
CacheManager.Instance.AddEntity <DataTable>(Constants.DBTableCacheName.FormatWith(tableName), cacheData, TimeSpan.MaxValue);
}
DataTable result = DataUtil.Query(cacheData, sqlCriteria, orderbyClause,
pageIndex, pageSize, ref recordCount);
return(result);
}
else
{
QueryBeeCommand queryBeeCommand = new QueryBeeCommand(this, tableName, selectClause,
sqlCriteria, orderbyClause, pageIndex, pageSize);
DataTable result = queryBeeCommand.Excute <DataTable>();
if (pageSize > 0 && pageIndex >= 0)
{
if (recordCount <= 0)
{
QueryBeeCommand countBeeCommand = new QueryBeeCommand(this, tableName,
"count(*) as beecount", sqlCriteria, null, -1, -1);
DataTable countTable = countBeeCommand.Excute <DataTable>();
if (countTable != null && countTable.Rows.Count == 1)
{
int.TryParse(countTable.Rows[0]["beecount"].ToString(), out recordCount);
}
}
}
else
{
recordCount = result.Rows.Count;
}
return(result);
}
}
public SqlCriteria Or(SqlCriteria sqlCriteria)
{
ThrowExceptionUtil.ArgumentNotNull(sqlCriteria, "sqlCriteria");
blurFlag = true;
if (sqlCriteria.innerList.Count != 0 || sqlCriteria.root.Count != 0)
{
root.Add(sqlCriteria.guid, sqlCriteria);
expression = string.Format("({0} or {1})", expression, sqlCriteria.guid);
}
return(this);
}
public bool IsExist(string tableName, SqlCriteria sqlCriteria)
{
bool result = true;
int recordCount = 0;
if (sqlCriteria != null)
{
DataTable dataTable = Query(tableName, "count(*)", sqlCriteria, null, 0, 0, ref recordCount);
result = int.Parse(dataTable.Rows[0][0].ToString()) > 0;
}
return(result);
}
/// <summary>
/// 构造查询类
/// </summary>
/// <param name="tableName">表名，不能为空</param>
/// <param name="selectClause"></param>
/// <param name="sqlCriteria"></param>
/// <param name="orderbyClause"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
public QueryBeeCommand(DbSession owner, string tableName, string selectClause,
SqlCriteria sqlCriteria, string orderbyClause, int pageIndex, int pageSize)
: base(owner)
{
this.tableName = tableName;
this.selectClause = selectClause;
this.sqlCriteria = sqlCriteria;
this.orderbyClause = orderbyClause == null ? null : orderbyClause.Trim();
this.pageIndex = pageIndex;
this.pageSize = pageSize;
if (string.IsNullOrEmpty(selectClause))
{
this.selectClause = "*";
}
}
private string GetWhereClause(SqlCriteria sqlCriteria)
{
StringBuilder builder = new StringBuilder();
if (sqlCriteria.innerList.Count != 0)
{
for (int i = 0; i < sqlCriteria.innerList.Count; i++)
{
string expression = FormatExpression(sqlCriteria.innerList[i]);
if (i == 0)
{
builder.Append(expression);
}
else
{
builder.AppendFormat(" and {0}", expression);
}
}
}
else
{
builder.Append(" 1=1 ");
}
StringBuilder rootBuilder = new StringBuilder();
rootBuilder.Append(sqlCriteria.expression);
foreach (SqlCriteria item in sqlCriteria.root.Values)
{
if (item.guid == sqlCriteria.guid)
{
rootBuilder.Replace(item.guid.ToString(), builder.ToString());
}
else
{
rootBuilder.Replace(item.guid.ToString(), GetWhereClause(item));
}
}
return(rootBuilder.ToString());
}
private SqlCriteria VisitExpression(Expression expr)
{
SqlCriteria sqlCriteria = new SqlCriteria();
if (expr.NodeType == ExpressionType.Equal) // ==
{
var bin = expr as BinaryExpression;
ThrowExceptionUtil.ArgumentConditionTrue(bin.Left is MemberExpression, "expr", "left expression should be MemberExpression");
if (bin.Right is MemberExpression)
{
return(sqlCriteria.CEqual(this.VisitMember(bin.Left), this.VisitMember(bin.Right)));
}
else
{
return(sqlCriteria.Equal(this.VisitMember(bin.Left), this.VisitValue(bin.Right)));
}
}
else if (expr is MemberExpression && expr.Type == typeof(bool)) // x.Active
{
return(sqlCriteria.Equal(this.VisitMember(expr), true));
}
else if (expr.NodeType == ExpressionType.Not) // !x.Active
{
return(sqlCriteria.Equal(this.VisitMember(expr), false));
}
else if (expr.NodeType == ExpressionType.NotEqual) // !=
{
var bin = expr as BinaryExpression;
ThrowExceptionUtil.ArgumentConditionTrue(bin.Left is MemberExpression, "expr", "left expression should be MemberExpression");
if (bin.Right is MemberExpression)
{
return(sqlCriteria.CNotEqual(this.VisitMember(bin.Left), this.VisitMember(bin.Right)));
}
else
{
return(sqlCriteria.NotEqual(this.VisitMember(bin.Left), this.VisitValue(bin.Right)));
}
}
else if (expr.NodeType == ExpressionType.LessThan) // <
{
var bin = expr as BinaryExpression;
ThrowExceptionUtil.ArgumentConditionTrue(bin.Left is MemberExpression, "expr", "left expression should be MemberExpression");
if (bin.Right is MemberExpression)
{
return(sqlCriteria.CLessThan(this.VisitMember(bin.Left), this.VisitMember(bin.Right)));
}
else
{
return(sqlCriteria.LessThan(this.VisitMember(bin.Left), this.VisitValue(bin.Right)));
}
}
else if (expr.NodeType == ExpressionType.LessThanOrEqual) // <=
{
var bin = expr as BinaryExpression;
ThrowExceptionUtil.ArgumentConditionTrue(bin.Left is MemberExpression, "expr", "left expression should be MemberExpression");
if (bin.Right is MemberExpression)
{
return(sqlCriteria.CLessThanOrEqual(this.VisitMember(bin.Left), this.VisitMember(bin.Right)));
}
else
{
return(sqlCriteria.LessThanOrEqual(this.VisitMember(bin.Left), this.VisitValue(bin.Right)));
}
}
else if (expr.NodeType == ExpressionType.GreaterThan) // >
{
var bin = expr as BinaryExpression;
ThrowExceptionUtil.ArgumentConditionTrue(bin.Left is MemberExpression, "expr", "left expression should be MemberExpression");
if (bin.Right is MemberExpression)
{
return(sqlCriteria.CGreaterThan(this.VisitMember(bin.Left), this.VisitMember(bin.Right)));
}
else
{
return(sqlCriteria.GreaterThan(this.VisitMember(bin.Left), this.VisitValue(bin.Right)));
}
}
else if (expr.NodeType == ExpressionType.GreaterThanOrEqual) // >=
{
var bin = expr as BinaryExpression;
ThrowExceptionUtil.ArgumentConditionTrue(bin.Left is MemberExpression, "expr", "left expression should be MemberExpression");
if (bin.Right is MemberExpression)
{
return(sqlCriteria.CGreaterThanOrEqual(this.VisitMember(bin.Left), this.VisitMember(bin.Right)));
}
else
{
return(sqlCriteria.GreaterThanOrEqual(this.VisitMember(bin.Left), this.VisitValue(bin.Right)));
}
}
else if (expr is MethodCallExpression)
{
var met = expr as MethodCallExpression;
var method = met.Method.Name;
// StartsWith
if (method == "StartsWith")
{
var value = this.VisitValue(met.Arguments[0]).ToString();
return(sqlCriteria.StartWith(this.VisitMember(met.Object), value));
}
// Contains
else if (method == "Contains")
{
var value = this.VisitValue(met.Arguments[0]).ToString();
return(sqlCriteria.Contains(this.VisitMember(met.Object), value));
}
// Equals
else if (method == "Equals")
{
if (met.Arguments[0] is MemberExpression)
{
return(sqlCriteria.CEqual(this.VisitMember(met.Object), this.VisitMember(met.Arguments[0])));
}
else
{
var value = this.VisitValue(met.Arguments[0]);
return(sqlCriteria.Equal(this.VisitMember(met.Object), value));
}
}
// System.Linq.Enumerable methods
else if (met.Method.DeclaringType.FullName == "System.Linq.Enumerable")
{
//return ParseEnumerableExpression(met);
}
}
else if (expr is BinaryExpression && expr.NodeType == ExpressionType.AndAlso)
{
// AND
var bin = expr as BinaryExpression;
var left = this.VisitExpression(bin.Left);
var right = this.VisitExpression(bin.Right);
return(left.And(right));
}
else if (expr is BinaryExpression && expr.NodeType == ExpressionType.OrElse)
{
// OR
var bin = expr as BinaryExpression;
var left = this.VisitExpression(bin.Left);
var right = this.VisitExpression(bin.Right);
return(left.Or(right));
}
throw new NotImplementedException("Not implemented Linq expression");
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCriteria">条件集</param>
/// <returns>结果集</returns>
public DataTable Query(string tableName, SqlCriteria sqlCriteria)
{
int recordCount = 0;
return(Query(tableName, null, sqlCriteria, null, -1, -1, ref recordCount));
}
public List <T> Query(SqlCriteria sqlCriterial, string orderbyClause, int pageIndex, int pageSize, ref int recordCount)
{
return(DbSession.Current.Query <T>(sqlCriterial, orderbyClause, pageIndex, pageSize, ref recordCount));
}
public List <T> Query(SqlCriteria sqlCriterial)
{
return(DbSession.Current.Query <T>(sqlCriterial));
}
public void Delete(SqlCriteria sqlCriteria)
{
DbSession.Current.Delete <T>(sqlCriteria);
}
public CmdTextWithWhereBeeCommand(DbSession owner, string cmdText, SqlCriteria sqlCriteria)
: base(owner)
{
this.cmdText = cmdText;
this.sqlCriteria = sqlCriteria;
}
public QueryBeeCommand(DbSession owner, string tableName, string selectClause, SqlCriteria sqlCriteria, string orderbyClause)
: this(owner, tableName, selectClause, sqlCriteria, orderbyClause, -1, -1)
{
}
// Methods
public DeleteBeeCommand(DbSession owner, string tableName, SqlCriteria sqlCriteria)
: base(owner, BeeDbCommandBehavior.NoQuery)
{
this.tableName = tableName;
this.sqlCriteria = sqlCriteria;
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCriteria">条件集</param>
/// <param name="orderbyClause">排序集</param>
/// <returns>结果集</returns>
public DataTable Query(string tableName, SqlCriteria sqlCriteria, string orderbyClause)
{
int recordCount = 0;
return(Query(tableName, null, sqlCriteria, orderbyClause, -1, -1, ref recordCount));
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCriteria">条件集</param>
/// <param name="orderbyClause">排序集</param>
/// <param name="topNum">条数</param>
/// <returns>结果集</returns>
public DataTable QueryTop(string tableName, SqlCriteria sqlCriteria, string orderbyClause, int topNum)
{
int recordCount = 0;
return(Query(tableName, null, sqlCriteria, orderbyClause, 1, topNum, ref recordCount));
}