public void TestInsert_AliasedTable()
{
Table table = new Table("Table");
ValueList values = new ValueList();
InsertBuilder builder = new InsertBuilder(table, values, "t");
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "INSERT INTO Table t VALUES()";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
/// <summary>
/// Generates the text for an Insert builder.
/// </summary>
/// <param name="item">The Insert builder to generate the text for.</param>
protected internal override void VisitInsert(InsertBuilder item)
{
forCommandType(CommandType.Insert).visitInsert(item);
}
private void visitInsert(InsertBuilder item)
{
writer.Write("INSERT INTO ");
forSourceContext(SourceReferenceType.Declaration).visitAliasedSource(item.Table);
if (item.Columns.Any())
{
writer.Write(" (");
forValueContext(ValueReferenceType.Reference).join(", ", item.Columns);
writer.Write(")");
}
writer.Write(" ");
if (item.Values.IsValueList)
{
writer.Write("VALUES");
}
item.Values.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference));
}
/// <summary>
/// Visits an Insert builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitInsert(InsertBuilder item)
{
}
private ICommand buildInsertStatement(MatchResult result)
{
MatchResult tableResult = result.Matches[SqlGrammar.InsertStatement.Table];
Table table = buildTable(tableResult);
IValueProvider valueProvider = null;
MatchResult valuesResult = result.Matches[SqlGrammar.InsertStatement.Values.Name];
if (valuesResult.IsMatch)
{
ValueList values = new ValueList();
MatchResult valueListResult = valuesResult.Matches[SqlGrammar.InsertStatement.Values.ValueList];
if (valueListResult.IsMatch)
{
buildValueList(valueListResult, values);
}
valueProvider = values;
}
MatchResult selectResult = result.Matches[SqlGrammar.InsertStatement.Select.Name];
if (selectResult.IsMatch)
{
MatchResult selectStatementResult = selectResult.Matches[SqlGrammar.InsertStatement.Select.SelectStatement];
ISelectBuilder selectBuilder = buildSelectStatement(selectStatementResult);
valueProvider = selectBuilder;
}
string alias = null;
MatchResult aliasExpressionResult = result.Matches[SqlGrammar.InsertStatement.AliasExpression.Name];
if (aliasExpressionResult.IsMatch)
{
MatchResult aliasResult = aliasExpressionResult.Matches[SqlGrammar.InsertStatement.AliasExpression.Alias];
alias = getToken(aliasResult);
}
InsertBuilder builder = new InsertBuilder(table, valueProvider, alias);
SourceCollection collection = new SourceCollection();
collection.AddSource(builder.Table.GetSourceName(), builder.Table);
scope.Push(collection);
MatchResult columnsResult = result.Matches[SqlGrammar.InsertStatement.Columns.Name];
if (columnsResult.IsMatch)
{
MatchResult columnListResult = columnsResult.Matches[SqlGrammar.InsertStatement.Columns.ColumnList];
buildColumnsList(columnListResult, builder);
}
scope.Pop();
return builder;
}
private void buildColumnsList(MatchResult result, InsertBuilder builder)
{
MatchResult multiple = result.Matches[SqlGrammar.ColumnList.Multiple.Name];
if (multiple.IsMatch)
{
MatchResult first = multiple.Matches[SqlGrammar.ColumnList.Multiple.First];
Column column = buildColumn(first);
builder.AddColumn(column);
MatchResult remaining = multiple.Matches[SqlGrammar.ColumnList.Multiple.Remaining];
buildColumnsList(remaining, builder);
return;
}
MatchResult single = result.Matches[SqlGrammar.ColumnList.Single];
if (single.IsMatch)
{
Column column = buildColumn(single);
builder.AddColumn(column);
return;
}
throw new InvalidOperationException();
}
public void TestInsert_AliasedTable_WithAsKeyword()
{
Table table = new Table("Table");
ValueList values = new ValueList();
InsertBuilder builder = new InsertBuilder(table, values, "t");
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder, new CommandOptions() { AliasColumnSourcesUsingAs = true });
string expected = "INSERT INTO Table AS t VALUES()";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestInsert_SelectAsSource()
{
Table table = new Table("Table");
SelectBuilder select = new SelectBuilder();
InsertBuilder builder = new InsertBuilder(table, select);
builder.AddColumn(builder.Table.Column("Column"));
select.AddProjection(new NumericLiteral(1));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder, new CommandOptions() { AliasColumnSourcesUsingAs = true });
string expected = "INSERT INTO Table (Column) (SELECT 1)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestInsert_MultipleColumns()
{
Table table = new Table("Table");
ValueList values = new ValueList();
InsertBuilder builder = new InsertBuilder(table, values);
builder.AddColumn(builder.Table.Column("Column1"));
builder.AddColumn(builder.Table.Column("Column2"));
values.AddValue(new NumericLiteral(1));
values.AddValue(new NullLiteral());
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder, new CommandOptions() { AliasColumnSourcesUsingAs = true });
string expected = "INSERT INTO Table (Column1, Column2) VALUES(1, NULL)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
protected override void VisitInsert(InsertBuilder item)
{
var visitor = new CreateRequestBuilderVisitor(Parameters, CrmMetadataProvider, Settings);
IVisitableBuilder visitable = item;
visitable.Accept(visitor);
SetOperation(visitor.GetCommand());
}
/// <summary>
/// Visits an Insert builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitInsert(InsertBuilder item)
{
}