/// <summary>
        /// Builds the WHERE clause from the given expression object.
        /// </summary>
        /// <param name="expression">The expression object.</param>
        /// <returns>The <c>Apworks.Storage.Builders.WhereClauseBuildResult</c> instance
        /// which contains the build result.</returns>
        public WhereClauseBuildResult BuildWhereClause(Expression <Func <TTableObject, bool> > expression)
        {
            this.sb.Clear();
            this.parameterValues.Clear();
            this.Visit(expression.Body);
            WhereClauseBuildResult result = new WhereClauseBuildResult
            {
                ParameterValues = parameterValues,
                WhereClause     = sb.ToString()
            };

            return(result);
        }
        public async Task <IEnumerable <TTableObject> > SelectAsync <TTableObject>(Specification <TTableObject> specification) where TTableObject : class, new()
        {
            try
            {
                using (var connection = this.CreateDatabaseConnection())
                {
                    await connection.OpenAsync();

                    var sql = $"SELECT {this.GetAllFieldNames<TTableObject>()} FROM {this.GetTableName<TTableObject>()}";
                    WhereClauseBuildResult whereClauseBuildResult = null;
                    if (specification != null)
                    {
                        var whereClauseBuilder = this.CreateWhereClauseBuilder <TTableObject>();
                        whereClauseBuildResult = whereClauseBuilder.BuildWhereClause(specification);
                        sql += $" WHERE {whereClauseBuildResult.WhereClause}";
                    }

                    using (var command = this.CreateCommand(sql, connection))
                    {
                        if (whereClauseBuildResult != null)
                        {
                            command.Parameters.Clear();
                            var parameters = this.GetSelectCriteriaDbParameterList <TTableObject>(whereClauseBuildResult.ParameterValues);
                            foreach (var param in parameters)
                            {
                                command.Parameters.Add(param);
                            }
                        }
                        var reader = await command.ExecuteReaderAsync();

                        List <TTableObject> result = new List <TTableObject>();
                        while (await reader.ReadAsync())
                        {
                            result.Add(this.CreateFromReader <TTableObject>(reader));
                        }
                        reader.Close();
                        return(result);
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error("Failed to select the data.", ex);
                throw;
            }
        }
 private async Task DoUpdateAsync <TTableObject>(DbConnection connection, DbTransaction transaction, UpdateCriteria <TTableObject> updateCriteria, Specification <TTableObject> specification) where TTableObject : class, new()
 {
     try
     {
         var whereClauseBuilder = this.CreateWhereClauseBuilder <TTableObject>();
         var sql = $"UPDATE {GetTableName<TTableObject>()} SET {GetUpdateCriteriaParameterNameList<TTableObject>(whereClauseBuilder, updateCriteria)}";
         WhereClauseBuildResult whereClauseBuildResult = null;
         if (specification != null)
         {
             whereClauseBuildResult = whereClauseBuilder.BuildWhereClause(specification);
             sql = $"{sql} WHERE {whereClauseBuildResult.WhereClause}";
         }
         using (var command = this.CreateCommand(sql, connection))
         {
             if (transaction != null)
             {
                 command.Transaction = transaction;
             }
             command.Parameters.Clear();
             var updateParameters = GetUpdateCriteriaParameterList <TTableObject>(updateCriteria);
             foreach (var parameter in updateParameters)
             {
                 command.Parameters.Add(parameter);
             }
             if (whereClauseBuildResult != null)
             {
                 foreach (var kvp in whereClauseBuildResult.ParameterValues)
                 {
                     var parameter = this.CreateParameter();
                     parameter.ParameterName = kvp.Key;
                     parameter.Value         = kvp.Value;
                     command.Parameters.Add(parameter);
                 }
             }
             await command.ExecuteNonQueryAsync();
         }
     }
     catch (Exception ex)
     {
         log.Error("Failed to update.", ex);
     }
 }