public WhereClauseBuildResult BuildWhereClause(Expression <Func <TModel, bool> > expression)
{
this._builder.Clear();
this._parameterValues.Clear();
this.Visit(expression.Body);
var result = new WhereClauseBuildResult(_builder.ToString(), _parameterValues);
return(result);
}
/// <summary>
/// Selects the first-only object from the current storage.
/// </summary>
/// <typeparam name="T">The type of the object.</typeparam>
/// <param name="specification">The instance of <see cref="Apworks.Specifications.ISpecification<T>"/>
/// which represents the filter criteria.</param>
/// <param name="connection">The instance of <see cref="System.Data.Common.DbConnection"/> which represents
/// the connection to a database.</param>
/// <param name="transaction">The instance of <see cref="System.Data.Common.DbTransaction"/> which represents
/// the database transaction.</param>
/// <returns>The first-only object that exists in the current storage.</returns>
protected virtual T DoSelectFirstOnly <T>(ISpecification <T> specification, DbConnection connection, DbTransaction transaction = null)
where T : class, new()
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("The database connection is not in an Open state.", "connection");
}
Expression <Func <T, bool> > expression = null;
WhereClauseBuildResult whereBuildResult = null;
string sql = string.Format("SELECT {0} FROM {1}",
GetFieldNameList <T>(), GetTableName <T>());
if (specification != null)
{
expression = specification.Expression;
whereBuildResult = GetWhereClauseBuilder <T>().BuildWhereClause(expression);
sql += " WHERE " + whereBuildResult.WhereClause;
}
using (DbCommand command = CreateCommand(sql, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (specification != null && whereBuildResult != null)
{
command.Parameters.Clear();
var parameters = GetSelectCriteriaDbParameterList <T>(whereBuildResult.ParameterValues);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
DbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
var ret = CreateFromReader <T>(reader);
reader.Close(); // Very important: reader MUST be closed !!!
return(ret);
}
reader.Close(); // Very important: reader MUST be closed !!!
return(null);
}
}
public void Test_LambdaBuilderWhereClause_QueryValueWithoutFieldAndPropertyNoCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
WhereClauseBuildResult result = builder.BuildWhereClause(p => p.TopicId == 1000 && p.Content == "足球");
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual("足球", result.ParameterValues.Values.ToList()[1]);
}
/// <summary>
/// Updates the object with new values.
/// </summary>
/// <typeparam name="T">The type of the object.</typeparam>
/// <param name="newValues">The <see cref="Apworks.Storage.PropertyBag"/> which contains the new values.</param>
/// <param name="specification">The instance of <see cref="Apworks.Specifications.ISpecification<T>"/>
/// which represents the filter criteria.</param>
/// <param name="connection">The instance of <see cref="System.Data.Common.DbConnection"/> which represents
/// the connection to a database.</param>
/// <param name="transaction">The instance of <see cref="System.Data.Common.DbTransaction"/> which represents
/// the database transaction.</param>
protected virtual void DoUpdate <T>(PropertyBag newValues, ISpecification <T> specification, DbConnection connection, DbTransaction transaction = null)
where T : class, new()
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("The database connection is not in an Open state.", "connection");
}
Expression <Func <T, bool> > expression = null;
WhereClauseBuildResult whereBuildResult = null;
string sql = string.Format("UPDATE {0} SET {1}",
GetTableName <T>(), GetUpdateFieldList <T>(newValues));
if (specification != null)
{
expression = specification.Expression;
whereBuildResult = GetWhereClauseBuilder <T>().BuildWhereClause(expression);
sql += " WHERE " + whereBuildResult.WhereClause;
}
using (DbCommand command = CreateCommand(sql, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
command.Parameters.Clear();
var updateParams = GetUpdateDbParameterList <T>(newValues);
foreach (var updateParam in updateParams)
{
command.Parameters.Add(updateParam);
}
if (specification != null)
{
var criParams = GetUpdateCriteriaDbParameterList <T>(whereBuildResult.ParameterValues);
foreach (var criParam in criParams)
{
command.Parameters.Add(criParam);
}
}
command.ExecuteNonQuery();
}
}
/// <summary>
/// Gets the number of records existing in the current storage.
/// </summary>
/// <typeparam name="T">The type of the object.</typeparam>
/// <param name="specification">The instance of <see cref="ISpecification{T}"/>
/// which represents the filter criteria.</param>
/// <param name="connection">The instance of <see cref="System.Data.Common.DbConnection"/> which represents
/// the connection to a database.</param>
/// <param name="transaction">The instance of <see cref="System.Data.Common.DbTransaction"/> which represents
/// the database transaction.</param>
/// <returns>The number of records.</returns>
protected virtual int DoGetRecordCount <T>(ISpecification <T> specification, DbConnection connection, DbTransaction transaction = null)
where T : class, new()
{
var result = 0;
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("The database connection is not in an Open state.", "connection");
}
WhereClauseBuildResult whereBuildResult = null;
var sql = string.Format("SELECT COUNT(*) FROM {0}", GetTableName <T>());
if (specification != null)
{
var expression = specification.GetExpression();
whereBuildResult = GetWhereClauseBuilder <T>().BuildWhereClause(expression);
sql += " WHERE " + whereBuildResult.WhereClause;
}
using (var command = this.CreateCommand(sql, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (specification != null)
{
command.Parameters.Clear();
var parameters = GetSelectCriteriaDbParameterList <T>(whereBuildResult.ParameterValues);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
result = Convert.ToInt32(command.ExecuteScalar());
}
return(result);
}
public void Test_LambdaBuilderWhereClause_CombineCondition_QueryValueWithoutFieldAndPropertyWithCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
WhereClauseBuildResult result = builder.BuildWhereClause(p => (p.TopicId < 1000 || p.TopicId > 2000) &&
(p.Content.Contains("足球") || p.Content.Contains("篮球")));
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual(2000, result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual("%足球%", result.ParameterValues.Values.ToList()[2]);
Assert.AreEqual("%篮球%", result.ParameterValues.Values.ToList()[3]);
}
/// <summary>
/// Deletes an object from current storage.
/// </summary>
/// <typeparam name="T">The type of the object.</typeparam>
/// <param name="specification">The instance of <see cref="Apworks.Specifications.ISpecification<T>"/>
/// which represents the filter criteria.</param>
/// <param name="connection">The instance of <see cref="System.Data.Common.DbConnection"/> which represents
/// the connection to a database.</param>
/// <param name="transaction">The instance of <see cref="System.Data.Common.DbTransaction"/> which represents
/// the database transaction.</param>
protected virtual void DoDelete <T>(ISpecification <T> specification, DbConnection connection, DbTransaction transaction = null)
where T : class, new()
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("The database connection is not in an Open state.", "connection");
}
Expression <Func <T, bool> > expression = null;
WhereClauseBuildResult whereBuildResult = null;
string sql = string.Format("DELETE FROM {0}",
GetTableName <T>());
if (specification != null)
{
expression = specification.Expression;
whereBuildResult = GetWhereClauseBuilder <T>().BuildWhereClause(expression);
sql += " WHERE " + whereBuildResult.WhereClause;
}
using (DbCommand command = CreateCommand(sql, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (specification != null)
{
command.Parameters.Clear();
var parameters = GetDeleteDbParameterList <T>(whereBuildResult.ParameterValues);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
command.ExecuteNonQuery();
}
}
public void Test_LambdaBuilderWhereClause_QueryValueWithoutFieldAndPropertyWithCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
DateTime datetime = DateTimeUtils.ToDateTime("2015-1-22").Value;
WhereClauseBuildResult result = builder.BuildWhereClause(p => p.TopicId.Equals(1000) &&
p.Content.Contains("足球") &&
p.CreationDateTime < datetime);
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual("%足球%", result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual(datetime, result.ParameterValues.Values.ToList()[2]);
}
public void Test_LambdaBuilderWhereClause_QueryValueWithFieldNoCallMethod()
{
Database db = new Database(Eagle.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
int topicId = 1000;
string content = "足球";
DateTime datetime = DateTimeUtils.ToDateTime("2015-1-22").Value;
WhereClauseBuildResult result = builder.BuildWhereClause(p => p.TopicId == topicId &&
p.Content == content &&
p.CreationDateTime < datetime);
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual("足球", result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual(datetime, result.ParameterValues.Values.ToList()[2]);
}
/// <summary>
/// Selects all the object from the current storage.
/// </summary>
/// <typeparam name="T">The type of the object.</typeparam>
/// <param name="specification">The instance of <see cref="ISpecification{T}"/>
/// which represents the filter criteria.</param>
/// <param name="orders">The <see cref="PropertyBag"/> which represents the fields for sorting.</param>
/// <param name="sortOrder">The <see cref="Apworks.Storage.SortOrder"/> value which represents the sort order.</param>
/// <param name="connection">The instance of <see cref="System.Data.Common.DbConnection"/> which represents
/// the connection to a database.</param>
/// <param name="transaction">The instance of <see cref="System.Data.Common.DbTransaction"/> which represents
/// the database transaction.</param>
/// <returns>All the objects selected.</returns>
protected virtual IEnumerable <T> DoSelect <T>(ISpecification <T> specification, PropertyBag orders, Storage.SortOrder sortOrder,
DbConnection connection, DbTransaction transaction = null)
where T : class, new()
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("The database connection is not in an Open state.", "connection");
}
WhereClauseBuildResult whereBuildResult = null;
var sql = string.Format("SELECT {0} FROM {1}", GetFieldNameList <T>(), GetTableName <T>());
if (specification != null)
{
var expression = specification.GetExpression();
whereBuildResult = GetWhereClauseBuilder <T>().BuildWhereClause(expression);
sql += " WHERE " + whereBuildResult.WhereClause;
}
if (orders != null && sortOrder != Storage.SortOrder.Unspecified)
{
sql += " ORDER BY " + GetOrderByFieldList <T>(orders);
switch (sortOrder)
{
case Storage.SortOrder.Ascending:
sql += " ASC";
break;
case Storage.SortOrder.Descending:
sql += " DESC";
break;
default: break;
}
}
using (var command = CreateCommand(sql, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (specification != null)
{
command.Parameters.Clear();
var parameters = GetSelectCriteriaDbParameterList <T>(whereBuildResult.ParameterValues);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
using (var reader = command.ExecuteReader())
{
var ret = new List <T>();
while (reader.Read())
{
ret.Add(CreateFromReader <T>(reader));
}
return(ret);
}
}
}
public override PagedResult <TEntity, TKey> Select(int pageNumber, int pageSize,
IDbConnection connection,
Sort <TEntity, TKey> sorting,
Expression <Func <TEntity, bool> > expression = null,
IDbTransaction transaction = null)
{
WhereClauseBuildResult whereClauseBuildResult = expression != null?this.BuildWhereClause(expression) : null;
var sqlBuilder = new StringBuilder();
sqlBuilder.Append($"SELECT (SELECT COUNT(*) FROM {this.mapping.GetEscapedTableName<TEntity, TKey>(this.dialectSettings)}");
if (whereClauseBuildResult != null)
{
sqlBuilder.Append($" WHERE {whereClauseBuildResult.WhereClause} ");
}
sqlBuilder.AppendLine(") AS _TotalNumberOfRecords,");
sqlBuilder.AppendLine($" * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {this.BuildOrderByClause(sorting)}) AS _RowNumber, * FROM {this.mapping.GetEscapedTableName<TEntity, TKey>(this.dialectSettings)}");
if (whereClauseBuildResult != null)
{
sqlBuilder.AppendLine($" WHERE {whereClauseBuildResult.WhereClause} ");
}
sqlBuilder.AppendLine($") AS PagedResult WHERE _RowNumber >= {(pageNumber - 1) * pageSize + 1} AND _RowNumber < {pageNumber * pageSize + 1} ORDER BY _RowNumber");
var sql = sqlBuilder.ToString();
var pagedResult = new PagedResult <TEntity, TKey>
{
PageNumber = pageNumber,
PageSize = pageSize
};
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
if (transaction != null)
{
command.Transaction = transaction;
}
if (whereClauseBuildResult != null)
{
command.Parameters.Clear();
foreach (var kvp in whereClauseBuildResult.ParameterValues)
{
var param = command.CreateParameter();
param.ParameterName = kvp.Key;
param.Value = kvp.Value;
command.Parameters.Add(param);
}
}
using (var reader = command.ExecuteReader())
{
var totalNumOfRecordsRead = false;
while (reader.Read())
{
if (!totalNumOfRecordsRead)
{
pagedResult.TotalRecords = Convert.ToInt32(reader["_TotalNumberOfRecords"]);
pagedResult.TotalPages = (pagedResult.TotalRecords - 1) / pageSize + 1;
totalNumOfRecordsRead = true;
}
var entity = new TEntity();
typeof(TEntity)
.GetTypeInfo()
.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(p => p.CanWrite && p.PropertyType.IsSimpleType())
.ToList()
.ForEach(x =>
{
var value = reader[mapping.GetColumnName <TEntity, TKey>(x)];
x.SetValue(entity, EvaluatePropertyValue(x, value));
});
pagedResult.Add(entity);
}
reader.Close();
}
}
return(pagedResult);
}
