public static IDbCommand BuildJoin <T, T1>(IDbConnection connection, IClassMap <T> classMap, IClassMap <T1> joinMap, Expression <Func <T, T1, bool> > predicate)
{
if (classMap == null)
{
throw new ArgumentNullException("classMap");
}
if (joinMap == null)
{
throw new ArgumentNullException("joinMap");
}
if (predicate == null)
{
throw new ArgumentNullException("predicate");
}
var ctx = new QueryContext(classMap, joinMap);
var columnList = classMap.Properties.Aggregate(string.Empty, GetColumnListAggregator(ctx.AliasForTable(classMap)));
var command = DbCommandDecorator.Create(connection);
var visitor = new PredicateVisitor(ctx);
visitor.Visit(predicate.Body);
foreach (var pair in visitor.Parameters)
{
command.AddParam(pair.Key, pair.Value);
}
command.CommandText = String.Format(SELECTJOIN, columnList, classMap.TableName, ctx.AliasForTable(classMap), joinMap.TableName, ctx.AliasForTable(joinMap), visitor.ToString());
return(command);
}
public static IDbCommand BuildSum <T, TProperty>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, TProperty> > property, Expression <Func <T, bool> > predicate)
{
if (classMap == null)
{
throw new ArgumentNullException("classMap");
}
if (property == null)
{
throw new ArgumentNullException("property");
}
if (predicate == null)
{
throw new ArgumentNullException("predicate");
}
var ctx = new QueryContext(classMap);
var command = DbCommandDecorator.Create(connection);
var visitor = new PredicateVisitor(ctx);
visitor.Visit(predicate.Body);
foreach (var pair in visitor.Parameters)
{
command.AddParam(pair.Key, pair.Value);
}
var propertyMap = classMap.Properties.Single(it => it.Alias == MemberHelper.GetName(property));
var sumStr = String.Format(propertyMap.IsColumnDiffer ? "sum({0})" : "sum({1}.[{0}])", propertyMap.ColumnNameOrFormula, ctx.AliasForTable(classMap));
command.CommandText = String.Format(SELECT, sumStr, classMap.TableName, ctx.AliasForTable(classMap), visitor.ToString());
return(command);
}
public static IDbCommand BuildSelect <T, TProperty>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, bool> > predicate, PageFilter <T, TProperty> pageFilter)
{
if (classMap == null)
{
throw new ArgumentNullException("classMap");
}
if (pageFilter == null)
{
throw new ArgumentNullException("pageFilter");
}
var ctx = new QueryContext(classMap);
var filterColumn = ctx.AliasForColumn();
var columnList = String.Format("row_number() over (order by [{0}] {1}) {2}, {3}",
MemberHelper.GetName(pageFilter.OrderProperty), pageFilter.Desc ? "desc" : "asc", filterColumn,
classMap.Properties.Aggregate(string.Empty, GetColumnListAggregator(ctx.AliasForTable(classMap))));
var command = DbCommandDecorator.Create(connection);
if (predicate != null)
{
var visitor = new PredicateVisitor(ctx);
visitor.Visit(predicate.Body);
foreach (var pair in visitor.Parameters)
{
command.AddParam(pair.Key, pair.Value);
}
command.CommandText = String.Format(SELECT, columnList, classMap.TableName, ctx.AliasForTable(classMap), visitor.ToString());
}
else
{
command.CommandText = String.Format(SELECTALL, columnList, classMap.TableName, ctx.AliasForTable(classMap));
}
var fromParam = command.AddParam(null, pageFilter.From);
var toParam = command.AddParam(null, pageFilter.To);
command.CommandText = String.Format("select {4} from ({0}) d where d.[{1}] between {2} and {3} order by d.[{1}] asc",
command.CommandText, filterColumn, fromParam.ParameterName, toParam.ParameterName,
classMap.Properties.Aggregate(string.Empty, GetPropListAggregator("d")));
return(command);
}
public static IDbCommand BuildCount <T>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, bool> > predicate)
{
if (classMap == null)
{
throw new ArgumentNullException("classMap");
}
if (predicate == null)
{
throw new ArgumentNullException("predicate");
}
var ctx = new QueryContext(classMap);
var command = DbCommandDecorator.Create(connection);
var visitor = new PredicateVisitor(ctx);
visitor.Visit(predicate.Body);
foreach (var pair in visitor.Parameters)
{
command.AddParam(pair.Key, pair.Value);
}
command.CommandText = String.Format(SELECT, "count(1)", classMap.TableName, ctx.AliasForTable(classMap), visitor.ToString());
return(command);
}
