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