public SqlCodeGeneratorExpressionVisitor(StringBuilder output, Dictionary <Expression, ParameterSyntaxType> parameterMappings, IQueryMetadata tableMetadata, IdentifierQuoter quoter)
{
this.output = output;
this.parameterMappings = parameterMappings;
this.tableMetadata = tableMetadata;
this.quoter = quoter;
}
public QueryMetadataXml(IQueryMetadata data, string metadataUrl)
{
var list = data.Results as List <T>;
if (list != null)
{
Results = new List <T>(list);
}
Count = data.Count;
Metadata = metadataUrl;
}
public string GenerateSqlCommand <TEntity, TParams, TResult>(IInsertOrUpdateCommand <TEntity, TParams, TResult> command, IQueryMetadata metadata)
{
var commandText = new StringBuilder();
if (command.Update != null && command.Insert != null)
{
// Generate 'merge ...'
commandText.AppendFormat("merge {0} with (holdlock) as T using ( select ", Quoter.QuoteTableName(metadata.TableSchema, metadata.TableName));
var columns = command.ConflictColumns.ToColumnList();
var parametersPredicateParameter = command.Insert.Parameters[0];
var visitedMembers = new HashSet <MemberInfo>();
new NewObjectExpressionVisitor(
m =>
{
var assignment = m as MemberAssignment;
if (assignment != null && columns.Contains(m.Member))
{
new MemberAccessExpressionVisitor(
ma =>
{
if (ma.Expression == parametersPredicateParameter && visitedMembers.Add(ma.Member))
{
commandText.AppendFormat("@{0} as {1}, ", metadata.GetParameterName(ma), Quoter.QuoteName(ma.Member.Name));
}
}
).Visit(assignment.Expression);
}
}
).Visit(command.Insert);
commandText.Length -= 2; // Remove last comma
commandText.AppendLine(" ) as S");
// Generate 'on (S.A = T.A and ...'
commandText.Append("on (");
new NewObjectExpressionVisitor(
m =>
{
var assignment = m as MemberAssignment;
if (assignment != null && columns.Contains(m.Member))
{
commandText.AppendFormat("T.{0} = ", Quoter.QuoteName(metadata.ColumnMappings[m.Member.Name]));
new SqlCodeGeneratorExpressionVisitor(commandText, new Dictionary <Expression, ParameterSyntaxType>
{
{ command.Insert.Parameters[0], ParameterSyntaxType.Source },
}, metadata, Quoter).Visit(assignment.Expression);
commandText.Append(" and ");
}
}
).Visit(command.Insert);
commandText.Length -= 5; // Remove last 'and'
commandText.AppendLine(")");
// Generate 'when matched then ...'
commandText.Append("when matched");
if (command.UpdatePredicate != null)
{
commandText.Append(" and (");
command.UpdatePredicate.GenerateWhereClause(commandText, Quoter, metadata, ParameterSyntaxType.Target);
commandText.Append(")");
}
commandText.AppendLine(" then update set ");
command.Update.GenerateUpdateSetList(commandText, Quoter, metadata, ParameterSyntaxType.Target);
// Generate 'when not matched then ...'
commandText.AppendLine("when not matched then insert ");
}
else if (command.Insert != null)
{
// Generate 'insert into ...'
commandText.AppendFormat("insert into {0} ", Quoter.QuoteTableName(metadata.TableSchema, metadata.TableName));
}
else if (command.Update != null)
{
// Generate 'update ...'
commandText.AppendFormat("update {0} set ", Quoter.QuoteTableName(metadata.TableSchema, metadata.TableName));
commandText.AppendLine();
command.Update.GenerateUpdateSetList(commandText, Quoter, metadata, ParameterSyntaxType.None);
if (command.Output != null)
{
GenerateOutputClause(command, metadata, commandText);
}
if (command.UpdatePredicate != null)
{
commandText.Append("where ");
command.UpdatePredicate.GenerateWhereClause(commandText, Quoter, metadata, ParameterSyntaxType.None);
commandText.AppendLine();
}
}
if (command.Insert != null)
{
command.Insert.GenerateInsertColumnList(commandText, Quoter, metadata);
if (command.Output != null && command.Update == null)
{
GenerateOutputClause(command, metadata, commandText);
}
command.Insert.GenerateInsertValueList(commandText, Quoter, metadata);
}
if (command.Output != null && command.Update != null && command.Insert != null)
{
GenerateOutputClause(command, metadata, commandText);
}
commandText.Append(';');
return(commandText.ToString());
}
private static void GenerateOutputClause <TEntity, TParams, TResult>(IInsertOrUpdateCommand <TEntity, TParams, TResult> command, IQueryMetadata metadata, StringBuilder commandText)
{
// Generate 'output ...'
commandText.Append("output ");
new MemberAccessExpressionVisitor(
n => commandText.AppendFormat("inserted.{0} as {1}, ", Quoter.QuoteName(metadata.ColumnMappings[n.Member.Name]), n.Member.Name)
).Visit(command.Output);
commandText.Length -= 2; // Remove last comma
commandText.AppendLine();
}
public string GenerateSqlCommand <TEntity, TParams, TResult>(IInsertOrUpdateCommand <TEntity, TParams, TResult> command, IQueryMetadata metadata)
{
var commandText = new StringBuilder();
if (command.Insert != null)
{
// Generate 'insert...'
commandText.AppendFormat("insert into {0} as T ", Quoter.QuoteTableName(metadata.TableSchema, metadata.TableName));
command.Insert.GenerateInsertColumnList(commandText, Quoter, metadata);
command.Insert.GenerateInsertValueList(commandText, Quoter, metadata);
if (command.Update != null)
{
// Generate 'on conflict (...) update ...'
commandText.Append("on conflict (");
var columns = command.ConflictColumns.ToColumnList();
foreach (var column in columns)
{
commandText
.Append(Quoter.QuoteName(metadata.ColumnMappings[column.Name]))
.Append(", ");
}
commandText.Length -= 2; // Remove last comma
commandText.AppendLine(") do update set ");
}
}
else if (command.Update != null)
{
// Generate 'update ...'
commandText.AppendFormat("update {0} as T set ", Quoter.QuoteTableName(metadata.TableSchema, metadata.TableName));
commandText.AppendLine();
}
if (command.Update != null)
{
command.Update.GenerateUpdateSetList(commandText, Quoter, metadata, ParameterSyntaxType.Target);
if (command.UpdatePredicate != null)
{
commandText.Append("where ");
command.UpdatePredicate.GenerateWhereClause(commandText, Quoter, metadata, ParameterSyntaxType.Target);
commandText.AppendLine();
}
}
if (command.Output != null)
{
// Generate 'returning ...'
commandText.Append("returning ");
new MemberAccessExpressionVisitor(
n => commandText.AppendFormat("{0} as {1}, ", Quoter.QuoteName(metadata.ColumnMappings[n.Member.Name]), n.Member.Name)
).Visit(command.Output);
commandText.Length -= 2; // Remove last comma
commandText.AppendLine();
}
return(commandText.ToString());
}
public string GenerateSqlCommand <TEntity, TParams>(IDeleteCommand <TEntity, TParams> command, IQueryMetadata metadata)
{
var commandText = new StringBuilder();
// Generate 'delete from ...'
commandText
.Append("delete from ")
.Append(Quoter.QuoteTableName(metadata.TableSchema, metadata.TableName));
// Generate 'where ...'
commandText.Append(" where ");
command.Predicate.GenerateWhereClause(commandText, Quoter, metadata, ParameterSyntaxType.None);
return(commandText.ToString());
}
public static void GenerateWhereClause <TEntity, TParams>(this Expression <PredicateSpecificationDelegate <TEntity, TParams> > predicate, StringBuilder commandText, IdentifierQuoter quoter, IQueryMetadata metadata, ParameterSyntaxType entityParameterType)
{
var parameterMappings = new Dictionary <Expression, ParameterSyntaxType>
{
{ predicate.Parameters[0], entityParameterType },
{ predicate.Parameters[1], ParameterSyntaxType.Argument },
};
new SqlCodeGeneratorExpressionVisitor(commandText, parameterMappings, metadata, quoter).Visit(predicate.Body);
}
public static void GenerateUpdateSetList <TEntity, TParams>(this Expression <UpdateSpecificationDelegate <TEntity, TParams> > update, StringBuilder commandText, IdentifierQuoter quoter, IQueryMetadata metadata, ParameterSyntaxType entityParameterType)
{
new NewObjectExpressionVisitor(
n =>
{
commandText.AppendFormat("{0} = ", quoter.QuoteName(metadata.ColumnMappings[n.Member.Name]));
new SqlCodeGeneratorExpressionVisitor(commandText, new Dictionary <Expression, ParameterSyntaxType>
{
{ update.Parameters[0], entityParameterType },
{ update.Parameters[1], ParameterSyntaxType.Argument },
}, metadata, quoter).Visit(((MemberAssignment)n).Expression);
commandText.Append(",");
}
).Visit(update);
commandText.Length--; // Remove last comma
commandText.AppendLine();
}
public static void GenerateInsertValueList <TEntity, TParams>(this Expression <InsertSpecificationDelegate <TEntity, TParams> > insert, StringBuilder commandText, IdentifierQuoter quoter, IQueryMetadata metadata)
{
// Generate 'values ...'
commandText.Append("values ( ");
new NewObjectExpressionVisitor(
n =>
{
new SqlCodeGeneratorExpressionVisitor(commandText, null, metadata, quoter).Visit(((MemberAssignment)n).Expression);
commandText.Append(", ");
}
).Visit(insert);
commandText.Length -= 2; // Remove last comma
commandText.AppendLine(" )");
}
public static void GenerateInsertColumnList <TEntity, TParams>(this Expression <InsertSpecificationDelegate <TEntity, TParams> > insert, StringBuilder commandText, IdentifierQuoter quoter, IQueryMetadata metadata)
{
// Generate insert values
commandText.Append("( ");
new NewObjectExpressionVisitor(
n => commandText.AppendFormat("{0}, ", quoter.QuoteName(metadata.ColumnMappings[n.Member.Name]))
).Visit(insert);
commandText.Length -= 2; // Remove last comma
commandText.AppendLine(" )");
}
