public QueryInfo BuildQuery <TValue>(dynamic parameters = null) where TValue : class
{
TValue model = parameters.Model;
IEnumerable <string> desiredFields = parameters.DesiredFields;
Expression <Func <TValue, bool> > predicate = parameters.Predicate;
string tableName = parameters.TableName;
var fields = FieldHelper.BuildFields(desiredFields, model: model, ignoreIdentity: false, tableName: tableName);
var queryBuilder = new StringBuilder();
queryBuilder.Append("UPDATE {0} SET ".FormatString(fields.TableName));
var columns = fields.FieldMappings.Values;
queryBuilder.Append(columns
.Where(f => !f.IsIdentity)
.Select(f => "[{0}] = {1}".FormatString(f.FieldName, f.ParameterName))
.ToDelimitedString(", "));
var dbParameters = FieldHelper.ExtractParameters(fields, false);
queryBuilder.Append(" ");
var container = _predicateBuilder.BuildContainer(predicate, typeof(TValue));
queryBuilder.Append("WHERE {0}".FormatString(container.WhereClause));
queryBuilder.Append(";");
return(new QueryInfo(queryBuilder.ToString().Trim(), fields, dbParameters));
}
public QueryInfo BuildQuery <TValue>(dynamic parameters = null) where TValue : class
{
Expression <Func <TValue, bool> > predicate = parameters.Predicate;
bool canDirtyRead = parameters.CanDirtyRead;
bool includeParameters = parameters.IncludeParameters;
IEnumerable <string> desiredFields = parameters.DesiredFields;
string tableName = parameters.TableName;
var fields = FieldHelper.BuildFields <TValue>(desiredFields, tableName);
var queryBuilder = new StringBuilder();
queryBuilder.Append("SELECT ");
var columns = fields.FieldMappings.Values;
queryBuilder.Append(columns
.Select(f => "[{0}]".FormatString(f.FieldName))
.ToDelimitedString(", "));
queryBuilder.Append(" ");
queryBuilder.Append("FROM {0} ".FormatString(fields.TableName));
if (canDirtyRead)
{
queryBuilder.Append("(NOLOCK)");
}
var dbParameters = new List <IDbDataParameter>();
if (predicate.IsNotNull())
{
var container = _predicateBuilder.BuildContainer(predicate, typeof(TValue), includeParameters);
if (includeParameters)
{
dbParameters = container.Parameters.ToSafeList();
}
queryBuilder.Append(" ");
queryBuilder.Append("WHERE {0}".FormatString(container.WhereClause));
}
queryBuilder.Append(";");
return(new QueryInfo(queryBuilder.ToString().Trim(), new[] { fields }, dbParameters));
}
public QueryInfo BuildQuery <TValue>(dynamic parameters = null) where TValue : class
{
Guard.ThrowIfNull <string>("parameters", parameters);
TValue model = parameters.Model;
bool returnNewId = parameters.ReturnNewId;
bool ignoreIdentity = parameters.IgnoreIdentity;
IEnumerable <string> desiredFields = parameters.DesiredFields;
string tableName = parameters.TableName;
var fields = FieldHelper.BuildFields(desiredFields, model: model, ignoreIdentity: ignoreIdentity, tableName: tableName);
var queryBuilder = new StringBuilder();
queryBuilder.Append("INSERT {0} (".FormatString(fields.TableName));
var columns = fields.FieldMappings.Values;
queryBuilder.Append(columns
.Select(f => "[{0}]".FormatString(f.FieldName))
.ToDelimitedString(", "));
queryBuilder.Append(") VALUES (");
queryBuilder.Append(columns
.Select(f => "{0}".FormatString(f.ParameterName))
.ToDelimitedString(", "));
queryBuilder.Append("); ");
if (returnNewId)
{
queryBuilder.Append("SELECT SCOPE_IDENTITY() AS Id;");
}
var dbParameters = FieldHelper.ExtractParameters(fields, true);
return(new QueryInfo(queryBuilder.ToString().Trim(), new[] { fields }, dbParameters));
}
public QueryInfo BuildQuery <TValue>(dynamic parameters) where TValue : class
{
Guard.ThrowIfNull <string>("parameters", parameters);
Expression <Func <TValue, bool> > predicate = parameters.Predicate;
string tableName = parameters.TableName;
var fields = FieldHelper.BuildFields <TValue>(tableName: tableName);
var queryBuilder = new StringBuilder();
queryBuilder.Append("DELETE ");
queryBuilder.Append("FROM {0}".FormatString(fields.TableName));
queryBuilder.Append(" ");
var container = _predicateBuilder.BuildContainer(predicate, typeof(TValue));
queryBuilder.Append("WHERE {0}".FormatString(container.WhereClause));
queryBuilder.Append(";");
return(new QueryInfo(queryBuilder.ToString().Trim(), new[] { fields }, container.Parameters));
}
public QueryInfo BuildQuery <TValue>(dynamic parameters = null) where TValue : class
{
PagingInfo pagingInfo = parameters?.PagingInfo;
IEnumerable <string> desiredFields = parameters?.DesiredFields;
Expression <Func <TValue, bool> > predicate = parameters?.Predicate;
bool canDirtyRead = parameters?.CanDirtyRead;
bool includeParameters = parameters?.IncludeParameters;
var mapper = _objectMappingFactory.GetMapper(_databaseConfiguration.MappingKind);
var mapping = mapper.GetMappingFor <TValue>();
var queryBuilder = new StringBuilder();
var fields = FieldHelper.BuildFields <TValue>(desiredFields);
var orderByClause = BuildPagedOrderByClause(mapping);
var countClause = BuildCountClause(mapping);
var whereClause = String.Empty;
var databaseParameters = new List <IDbDataParameter>();
if (predicate.IsNotNull())
{
var container = _predicateBuilder.BuildContainer(predicate, typeof(TValue), includeParameters);
whereClause = container.WhereClause;
if (includeParameters)
{
databaseParameters.AddRange(container.Parameters.ToSafeList());
}
}
queryBuilder.Append("SET @sortColumn = LOWER(@sortColumn); ");
queryBuilder.Append("SET @sortOrder = LOWER(@sortOrder); ");
queryBuilder.Append(BuildCommonTableExpression(fields, orderByClause, countClause, canDirtyRead, whereClause));
queryBuilder.Append("SELECT ");
queryBuilder.Append(fields.FieldMappings
.Select(f => f.Value.FieldName)
.ToDelimitedString(", "));
queryBuilder.Append(", ");
queryBuilder.Append("[TotalRecords], ");
queryBuilder.Append("([TotalRecords] + @rowsPerPage - 1) / @rowsPerPage AS NumberOfPages ");
queryBuilder.AppendFormat("FROM {0} ", ItemNames.SortedPageCteName);
queryBuilder.AppendFormat(
"WHERE {0}.RowNumber BETWEEN ((@pageNumber - 1) * @rowsPerPage) + 1 AND @rowsPerPage * @pageNumber ", ItemNames.SortedPageCteName);
if (whereClause.IsNotNullOrEmpty())
{
queryBuilder.Append(" ");
queryBuilder.Append("AND {0} ".FormatString(whereClause));
}
queryBuilder.Append(orderByClause);
queryBuilder.Append(";");
databaseParameters.Add(new SqlParameter(Parameters.RowsPerPage, SqlDbType.Int)
{
Value = pagingInfo.RowsPerPage
});
databaseParameters.Add(new SqlParameter(Parameters.PageNumber, SqlDbType.Int)
{
Value = pagingInfo.PageNumber
});
databaseParameters.Add(new SqlParameter(Parameters.SortColumn, SqlDbType.NVarChar)
{
Value = pagingInfo.SortColumn
});
databaseParameters.Add(new SqlParameter(Parameters.SortOrder, SqlDbType.NVarChar)
{
Value = pagingInfo.SortOrder
});
return(new QueryInfo(queryBuilder.ToString().Trim(), fields, databaseParameters));
}
