public static Statement CreateStatement(Insert insert, IEntityMapping mapping)
{
var writer = new SqlWriter();
IDictionary <string, object> parameters = null;
writer.InsertInto.QuotedName(insert.Into.Name);
var resultType = Statement.ResultType.None;
switch (insert.Type)
{
case Insert.SetType.Values:
writer.OpenBlock.Trim().FieldList(x => x.Comma.Flush(), insert.Values.Keys).Trim().CloseBlock.Flush();
parameters = new Dictionary <string, object>();
writer.Values.OpenBlock.Trim().ParameterList(x => x.Comma.Flush(), insert.Values.Values.Select(x => parameters.AddWithUniquelyNamedKey(x))).
Trim().CloseBlock.Flush();
if (insert.HasIdentityKey)
{
writer.Trim().QuerySeperator.Select.ScopeIdentity(typeof(int));
resultType = Statement.ResultType.Scalar;
}
break;
case Insert.SetType.Query:
var select = SelectWriter <TEntity> .CreateStatement(insert.Query, mapping);
parameters = select.Parameters;
writer.OpenBlock.Trim().FieldList(x => x.Comma.Flush(), SelectWriter <TEntity> .BuildProjection(insert.Query, mapping, parameters)).Trim().CloseBlock.Flush();
writer.Write(select.Text);
break;
}
return(new Statement(writer.ToString(), Statement.StatementType.Text, resultType, parameters));
}
private void VisitExpression(Operator @operator, Operator parent)
{
var isNotNull = @operator.Type == Operator.OperatorType.NotEqual && !IsRightOperandNullConstant(@operator);
if (IsComparisonOperator(parent) && !IsMathOperator(@operator))
{
_sql.Case.When.Flush();
}
_sql.OpenBlock.Trim();
if (isNotNull)
{
_sql.OpenBlock.Trim();
}
VisitOperand(@operator.LeftOperand, @operator);
switch (@operator.Type)
{
case Operator.OperatorType.And: _sql.And.Flush(); break;
case Operator.OperatorType.Or: _sql.Or.Flush(); break;
case Operator.OperatorType.Equal: if (IsRightOperandNullConstant(@operator))
{
_sql.Is.Flush();
}
else
{
_sql.Equal.Flush();
} break;
case Operator.OperatorType.NotEqual: if (IsRightOperandNullConstant(@operator))
{
_sql.Is.Not.Flush();
}
else
{
_sql.NotEqual.Flush();
} break;
case Operator.OperatorType.GreaterThan: _sql.GreaterThan.Flush(); break;
case Operator.OperatorType.GreaterThanOrEqual: _sql.GreaterThanOrEqual.Flush(); break;
case Operator.OperatorType.LessThan: _sql.LessThan.Flush(); break;
case Operator.OperatorType.LessThanOrEqual: _sql.LessThanOrEqual.Flush(); break;
case Operator.OperatorType.Add: _sql.Plus.Flush(); break;
case Operator.OperatorType.Subtract: _sql.Minus.Flush(); break;
case Operator.OperatorType.Multiply: _sql.Multiply.Flush(); break;
case Operator.OperatorType.Divide: _sql.Divide.Flush(); break;
case Operator.OperatorType.Modulo: _sql.Modulo.Flush(); break;
}
VisitOperand(@operator.RightOperand, @operator);
_sql.Trim().CloseBlock.Flush();
if (isNotNull)
{
_sql.Or.Flush();
VisitOperand(@operator.LeftOperand, @operator);
_sql.Is.Null.Trim().CloseBlock.Flush();
}
if (IsComparisonOperator(parent) && !IsMathOperator(@operator))
{
_sql.Then.True.Else.False.End.Flush();
}
}
public static Statement CreateStatement(Select select, IEntityMapping mapping, IEnumerable <string> projectionOverride = null, bool noLock = false)
{
var sql = new SqlWriter();
var parameters = new Dictionary <string, object>();
var projection = projectionOverride ?? BuildProjection(select, mapping, parameters);
var whereClause = BuildWhereClause(select, mapping, parameters);
var orderByClause = select.HasOrderBy ? BuildOrderBy(select.OrderBy, mapping, parameters) : null;
Action <SqlWriter> writeProjection = x => x.Do(projection != null, y => y.FieldList(z => z.Comma.Flush(), projection), y => y.Wildcard.Flush());
if (select.Any)
{
sql.Select.Cast().Trim().OpenBlock.Trim().Case.When.Exists.OpenBlock.Trim();
}
if (select.From.IsTable || select.HasConditions)
{
sql.Select.Flush();
if (select.First || select.FirstOrDefault)
{
sql.Top(1);
}
else if (select.Single)
{
sql.Top(2);
}
else if (select.HasTop && !select.HasStart)
{
sql.Do(select.TopType == Select.TopValueType.Count, x => x.Top(select.Top), x => x.TopPercent(select.Top));
}
if (select.Count)
{
sql.CountWildcard.Flush();
}
else
{
sql.Do(writeProjection);
}
sql.From.Flush();
if (select.HasStart)
{
sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
RowNumber().Over.OpenBlock.Trim().OrderBy.
Do(select.HasOrderBy, x => x.Write(orderByClause), x => x.Do(projection != null, writeProjection, y => y.QuotedName(mapping.Key.GetColumnName()))).Trim().
CloseBlock.As.RowNumberAlias.From.Flush();
}
if (select.HasDuplicates)
{
var duplicateProjection = BuildProjection(select.Duplicates.Distinct, mapping, parameters);
sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
RowNumber().Over.OpenBlock.Trim().Partition.By.Write(duplicateProjection).OrderBy.Write(BuildOrderBy(select.Duplicates.OrderBy, mapping, parameters)).
Trim().CloseBlock.As.PartitionAlias.From.Flush();
}
else if (select.HasDistinct)
{
var distinctProjection = BuildProjections(select.Distinct.Select(x => x.Projection), mapping, parameters);
sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
RowNumber().Over.OpenBlock.Trim().Partition.By.ExpressionList(z => z.Comma.Flush(), distinctProjection).OrderBy.
Write(BuildOrderBy(select.Distinct.Any(x => x.HasOrder) ? select.Distinct.Where(x => x.HasOrder).Select(x => x.Order) :
select.Distinct.Select(x => new OrderBy {
Type = OrderBy.SourceType.Projection,
Projection = x.Projection,
Order = Order.Ascending
}), mapping, parameters)).Trim().
CloseBlock.As.PartitionAlias.From.Flush();
}
}
switch (select.From.Type)
{
case Data.DataType.Table:
sql.QuotedName(select.From.Table.Name).Write(select.From.Alias);
if (noLock)
{
sql.With(x => x.NoLock.Flush());
}
break;
case Data.DataType.Query:
var first = true;
if (select.HasConditions)
{
sql.OpenBlock.Trim().Flush();
}
foreach (var subQuery in select.From.Queries.Select(x => CreateStatement(x, mapping, projection, noLock)))
{
sql.Do(!first, x => x.Union.Flush()).Write(subQuery.Text).Flush();
parameters.AddRange(subQuery.Parameters);
first = false;
}
if (select.HasConditions)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Flush();
}
break;
}
if (select.From.IsTable || select.HasConditions)
{
if (select.HasWhere || select.HasSetOperations)
{
sql.Where.Write(whereClause).Flush();
}
if (select.Randomize)
{
sql.OrderBy.NewId();
}
// The reason why we dont do an order by if there is a start is because the order by is
// already specified in the row number definition. So we dont need to specify it again.
else if (select.HasOrderBy && !select.HasStart && !select.HasDistinct)
{
sql.OrderBy.Write(orderByClause);
}
if (select.HasDuplicates)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.GreaterThan.Value(1, SqlDbType.Int).Flush();
}
else if (select.HasDistinct)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.Equal.Value(1, SqlDbType.Int).Flush();
}
if (select.HasStart)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.RowNumberAlias.Flush();
if (select.HasTop && select.HasStart)
{
sql.Between(select.Start, select.Start + (select.Top - 1));
}
else
{
sql.GreaterThanOrEqual.Value(select.Start, SqlDbType.Int);
}
}
}
if (select.Any)
{
sql.Trim().CloseBlock.Then.Value(1, SqlDbType.Bit).Else.Value(0, SqlDbType.Bit).End.As.Write(DataTypes.Bit.SqlName).Trim().CloseBlock.Flush();
}
return(new Statement(sql.ToString(), Statement.StatementType.Text, GetResultType(select), parameters));
}
private void VisitFunction(Function function)
{
switch (function.Type)
{
case Function.FunctionType.Coalesce:
_sql.Coalesce(x => VisitProjection(function.Coalesce.First),
x => VisitProjection(function.Coalesce.Second));
break;
case Function.FunctionType.Convert:
_sql.Cast(x => VisitProjection(function.Convert.Value), function.Convert.Type, 0, null, null);
break;
case Function.FunctionType.IndexOf:
_sql.IndexOf(x => VisitProjection(function.IndexOf.Text),
x => VisitProjection(function.IndexOf.Value));
break;
case Function.FunctionType.IndexOfAt:
_sql.IndexOf(x => VisitProjection(function.IndexOfAt.Text),
x => VisitProjection(function.IndexOfAt.Value),
x => VisitProjection(function.IndexOfAt.Start));
break;
case Function.FunctionType.Insert:
_sql.Insert(x => VisitProjection(function.Insert.Text),
x => VisitProjection(function.Insert.Value),
x => VisitProjection(function.Insert.Start));
break;
case Function.FunctionType.Length:
_sql.Length(x => VisitProjection(function.Length.Text));
break;
case Function.FunctionType.Replace:
_sql.Replace(x => VisitProjection(function.Replace.Text),
x => VisitProjection(function.Replace.SearchValue),
x => VisitProjection(function.Replace.ReplaceValue));
break;
case Function.FunctionType.Substring:
_sql.Substring(x => VisitProjection(function.Substring.Text),
x => VisitProjection(function.Substring.Start));
break;
case Function.FunctionType.SubstringFixed:
_sql.Substring(x => VisitProjection(function.SubstringFixed.Text),
x => VisitProjection(function.SubstringFixed.Start),
x => VisitProjection(function.SubstringFixed.Length));
break;
case Function.FunctionType.ToLower:
_sql.ToLower(x => VisitProjection(function.ToLower.Text));
break;
case Function.FunctionType.ToString:
_sql.Cast(x => VisitProjection(function.ToString.Value), typeof(string), 0, null, null);
break;
case Function.FunctionType.ToUpper:
_sql.ToUpper(x => VisitProjection(function.ToUpper.Text));
break;
case Function.FunctionType.Trim:
_sql.Trim(x => VisitProjection(function.Trim.Text));
break;
case Function.FunctionType.TrimEnd:
_sql.RightTrim(x => VisitProjection(function.TrimEnd.Text));
break;
case Function.FunctionType.TrimStart:
_sql.LeftTrim(x => VisitProjection(function.TrimStart.Text));
break;
case Function.FunctionType.Hash:
_sql.Hash(x => VisitProjection(function.Hash.Value), function.Hash.Type == Function.HashParameters.HashType.Md5 ?
SqlWriter.HashAlgorithm.Md5 : SqlWriter.HashAlgorithm.Sha1);
break;
case Function.FunctionType.ToHex:
_sql.ToHex(x => VisitProjection(function.ToHex.Value));
break;
}
}
public SqlWriter SubQueryColumn(string name)
{
return(SubQueryAlias.Trim().Period.Trim().QuotedName(name));
}