private (string, List <DbCommandParameter.DbCommandParameter>, IList <ParseError>) CreateInsertStatement(string tableName, DataTable records, ICollection <DbCommandParameter.DbCommandParameter> parameter = null)
{
var statement = CreateStatement(tableName);
statement = AddColumnReferenceExpression(statement, records);
statement.InsertSpecification.InsertOption = InsertOption.Into;
var listParams = new List <DbCommandParameter.DbCommandParameter>(parameter ?? new DbCommandParameter.DbCommandParameter[] { });
var numberParams = 0;
statement.InsertSpecification.InsertSource = new ValuesInsertSource();
IList <ParseError> parseErrors;
foreach (DataRow row in records.Rows)
{
var rv = new RowValue();
foreach (DataColumn column in records.Columns)
{
var cell = row[column];
if (cell is string name)
{
var exp = new TSql100Parser(false).ParseExpression(
new System.IO.StringReader(name), out parseErrors);
if (exp.GetType() == typeof(VariableReference) || exp.GetType() == typeof(GlobalVariableExpression))
{
rv.ColumnValues.Add(new VariableReference()
{
Name = name
});
continue;
}
}
while (listParams.Any(_ => _.Name == $"p{numberParams}"))
{
numberParams++;
}
rv.ColumnValues.Add(new VariableReference()
{
Name = $"@p{numberParams}"
});
listParams.Add(
new DbCommandParameter.DbCommandParameter(
$"p{numberParams}",
DbType.Object,
cell));
numberParams++;
}
((ValuesInsertSource)statement.InsertSpecification.InsertSource).RowValues.Add(rv);
}
var generator = new Sql100ScriptGenerator();
generator.GenerateScript(statement, out var query, out parseErrors);
return(query, listParams, parseErrors);
}
public void AddRowValue(object rowValue)
{
if (!RowValue.Contains(rowValue))
{
RowValue.Add(rowValue);
}
}
private void BuildInsertAction(MergeSpecification specification)
{
var action = new InsertMergeAction();
var insertSource = action.Source = new ValuesInsertSource();
var row = new RowValue();
foreach (var column in _columnDescriptors)
{
var colRef = new ColumnReferenceExpression();
colRef.ColumnType = ColumnType.Regular;
colRef.MultiPartIdentifier = new MultiPartIdentifier().Create(column.Name);
action.Columns.Add(colRef);
colRef = new ColumnReferenceExpression();
colRef.ColumnType = ColumnType.Regular;
colRef.MultiPartIdentifier = new MultiPartIdentifier().Create(new Identifier {
Value = MergeIdentifierStrings.SourceName
},
column.Name);
row.ColumnValues.Add(colRef);
}
insertSource.RowValues.Add(row);
specification.ActionClauses.Add(new MergeActionClause
{
Action = action,
Condition = MergeCondition.NotMatchedByTarget
});
}
public MatrixCellValue(MatrixKey key, RowValue row, int rowIndex, int colIndex, string colName, object value)
{
MatrixKey = key;
Row = row;
RowIndex = rowIndex;
ColIndex = colIndex;
ColName = colName;
Value = value;
Address = $"{CellAddressCalculator.GetColumnLetters(colIndex)}{rowIndex + 1}";
}
public CreateProcedureStatement GenerateInsertStoredProcedure(Table table, QuoteType quoteType = QuoteType.NotQuoted)
{
Database database = table.Parent;
string storedProcedureName = string.Concat("Insert", table.Name);
List <ColumnReferenceExpression> columns = new List <ColumnReferenceExpression>();
List <ScalarExpression> scalarExpressions = new List <ScalarExpression>();
List <RowValue> rowValues = new List <RowValue>();
RowValue rowValue = ScriptFactory.RowValue();
rowValues.Add(rowValue);
List <ProcedureParameter> parameters = new List <ProcedureParameter>();
foreach (var column in table.Columns)
{
if (column.IsPrimaryKey())
{
continue;
}
scalarExpressions.Add(
ScriptFactory.IdentifierLiteral(
CreateProcedureParameterVariableName(column), QuoteType.NotQuoted));
columns.Add(
ScriptFactory.ColumnReferenceExpression(
ScriptFactory.MultiPartIdentifier(
ScriptFactory.Identifier(column.Name, quoteType))));
parameters.Add(
ScriptFactory.ProcedureParameter(CreateProcedureParameterVariableName(column), false,
GenerateDataTypeReference(SqlDataType.Parse(column.DataType))));
}
rowValue.ColumnValues.AddRange(scalarExpressions);
StatementList statements = ScriptFactory.List(
ScriptFactory.PredicateSet(true, SetOptions.NoCount),
ScriptFactory.Insert(
ScriptFactory.InsertSpecification(
ScriptFactory.ValuesInsertSource(false, rowValues),
ScriptFactory.NamedTableReference(
GenerateSchemaObjectName(table, quoteType)),
null, null, null, InsertOption.Into, columns)),
ScriptFactory.Return(ScriptFactory.IntegerLiteral("1")));
return(ScriptFactory.CreateProcedure(false,
ScriptFactory.ProcedureReference(
GenerateStoredProcedureName(storedProcedureName, table, quoteType)),
ScriptFactory.List(
ScriptFactory.BeginEndBlock(statements)), null, null, parameters));
}
public override void ExplicitVisit(RowValue node)
{
_buffer.Append("\t(");
for (int index = 0, count = node.ColumnValues.Count - 1; index <= count; ++index)
{
var columnValue = node.ColumnValues[index];
columnValue.Accept(this);
if (index < count)
{
_buffer.Append(", ");
}
}
_buffer.Append(")");
}
public void SetInlineTableData(MergeSpecification specification)
{
var table = specification.TableReference as InlineDerivedTable;
if (null == table)
{
throw new NotImplementedException("only support merges from inline table reference");
}
table.RowValues.Clear();
foreach (DataRow row in _merge.Data.Rows)
{
if (row.RowState == DataRowState.Deleted)
{
continue;
}
if (row.HasErrors)
{
continue;
}
var rowValue = new RowValue();
var colIndex = 0;
foreach (var col in _merge.Table.Columns)
{
var value = row[col.Name.GetName()];
if (value == DBNull.Value)
{
rowValue.ColumnValues.Add(new NullLiteral());
}
else
{
var actualColumn = _merge.Data.Columns[col.Name.GetName()];
rowValue.ColumnValues.Add(GetColumnValue(GetStringRepresentation(value, actualColumn), col.DataType, col.IsNText));
}
colIndex++;
}
table.RowValues.Add(rowValue);
}
}
public static void SetInlineTableData(this MergeStatement source, DataTable data, List <ColumnDescriptor> columns)
{
var table = source.MergeSpecification.TableReference as InlineDerivedTable;
if (null == table)
{
throw new NotImplementedException("only support merges from inline table reference");
}
table.RowValues.Clear();
var columnsToRemove = new List <DataColumn>();
for (var i = 0; i < data.Columns.Count; i++)
{
if (columns.FirstOrDefault(p => p.Name.Value == data.Columns[i].ColumnName) == null)
{
columnsToRemove.Add(data.Columns[i]);
}
}
foreach (var column in columnsToRemove)
{
data.Columns.Remove(column);
}
foreach (DataRow row in data.Rows)
{
var rowValue = new RowValue();
for (var i = 0; i < row.ItemArray.Length; i++)
{
if (row.ItemArray[i] == DBNull.Value)
{
rowValue.ColumnValues.Add(new NullLiteral());
}
else
{
rowValue.ColumnValues.Add(GetColumnValue(row.ItemArray[i].ToString(), columns[i].LiteralType));
}
}
table.RowValues.Add(rowValue);
}
}
public override void ExplicitVisit(ValuesInsertSource node)
{
if (node.IsDefaultValues)
{
throw new NotImplementedException();
}
_buffer.Append("values");
_buffer.AppendLine();
for (int index = 0, count = node.RowValues.Count - 1; index <= count; ++index)
{
RowValue rowValue = node.RowValues[index];
rowValue.Accept(this);
if (index < count)
{
_buffer.Append(",");
_buffer.AppendLine();
}
}
_buffer.Append(";");
_buffer.AppendLine();
}
/// <summary>
/// Helper method that creates an INSERT statement to track a batch being completed
/// </summary>
/// <param name="batchId"></param>
/// <param name="batchDescription"></param>
/// <returns></returns>
private static InsertStatement CreateBatchCompleteInsert(int batchId, string batchDescription)
{
InsertStatement insert = new InsertStatement();
NamedTableReference batchesCompleted = new NamedTableReference();
insert.InsertSpecification = new InsertSpecification();
insert.InsertSpecification.Target = batchesCompleted;
batchesCompleted.SchemaObject = CreateCompletedBatchesName();
// Build the columns inserted into
ColumnReferenceExpression batchIdColumn = new ColumnReferenceExpression();
batchIdColumn.MultiPartIdentifier = new MultiPartIdentifier();
batchIdColumn.MultiPartIdentifier.Identifiers.Add(CreateIdentifier(BatchIdColumnName, QuoteType.NotQuoted));
ColumnReferenceExpression descriptionColumn = new ColumnReferenceExpression();
descriptionColumn.MultiPartIdentifier = new MultiPartIdentifier();
descriptionColumn.MultiPartIdentifier.Identifiers.Add(CreateIdentifier(DescriptionColumnName, QuoteType.NotQuoted));
insert.InsertSpecification.Columns.Add(batchIdColumn);
insert.InsertSpecification.Columns.Add(descriptionColumn);
// Build the values inserted
ValuesInsertSource valueSource = new ValuesInsertSource();
insert.InsertSpecification.InsertSource = valueSource;
RowValue values = new RowValue();
values.ColumnValues.Add(new IntegerLiteral {
Value = batchId.ToString()
});
values.ColumnValues.Add(new StringLiteral {
Value = batchDescription
});
valueSource.RowValues.Add(values);
return(insert);
}
public static void SetInlineTableData(this MergeStatement source, DataTable data, List<ColumnDescriptor> columns)
{
var table = source.MergeSpecification.TableReference as InlineDerivedTable;
if (null == table)
throw new NotImplementedException("only support merges from inline table reference");
table.RowValues.Clear();
var columnsToRemove = new List<DataColumn>();
for (var i = 0; i < data.Columns.Count; i++)
{
if (columns.FirstOrDefault(p => p.Name.Value == data.Columns[i].ColumnName) == null)
columnsToRemove.Add(data.Columns[i]);
}
foreach (var column in columnsToRemove)
{
data.Columns.Remove(column);
}
foreach (DataRow row in data.Rows)
{
var rowValue = new RowValue();
for (var i = 0; i < row.ItemArray.Length; i++)
{
if (row.ItemArray[i] == DBNull.Value)
{
rowValue.ColumnValues.Add(new NullLiteral());
}
else
{
rowValue.ColumnValues.Add(GetColumnValue(row.ItemArray[i].ToString(), columns[i].LiteralType));
}
}
table.RowValues.Add(rowValue);
}
}
public void SetInlineTableData(MergeSpecification specification)
{
var table = specification.TableReference as InlineDerivedTable;
if (null == table)
throw new NotImplementedException("only support merges from inline table reference");
table.RowValues.Clear();
foreach (DataRow row in _merge.Data.Rows)
{
if (row.RowState == DataRowState.Deleted)
continue;
if (row.HasErrors)
continue;
var rowValue = new RowValue();
foreach (var col in _merge.Table.Columns)
{
var value = row[col.Name.GetName()];
if (value == DBNull.Value)
{
rowValue.ColumnValues.Add(new NullLiteral());
}
else
{
rowValue.ColumnValues.Add(GetColumnValue(value.ToString(), col.DataType, col.IsNText));
}
}
table.RowValues.Add(rowValue);
}
}
protected override object InternalVisit(RowValue node)
{
return(node.ColumnValues
.Select(cv => EvaluateExpression <object>(cv, Database.GlobalEnvironment))
.ToArray());
}
public ViewValueChangedEventArgs(RoutedEvent routedEvent, RowValue rowValue)
: base(routedEvent)
{
this.rowValue = rowValue;
}
public override void Visit(RowValue node) { this.action(node); }
public override void ExplicitVisit(RowValue fragment)
{
_fragments.Add(fragment);
}
private void BuildInsertAction(MergeSpecification specification)
{
var action = new InsertMergeAction();
var insertSource = action.Source = new ValuesInsertSource();
var row = new RowValue();
foreach (var column in _merge.Table.Columns)
{
var colRef = new ColumnReferenceExpression();
colRef.ColumnType = ColumnType.Regular;
colRef.MultiPartIdentifier = MultiPartIdentifierBuilder.Get(column.Name.GetName());
action.Columns.Add(colRef);
colRef = new ColumnReferenceExpression();
colRef.ColumnType = ColumnType.Regular;
colRef.MultiPartIdentifier = MultiPartIdentifierBuilder.Get(MergeIdentifierStrings.SourceName, column.Name.GetName());
row.ColumnValues.Add(colRef);
}
insertSource.RowValues.Add(row);
specification.ActionClauses.Add(new MergeActionClause
{
Action = action,
Condition = MergeCondition.NotMatchedByTarget
});
}
private WRowValue ParseRowValue(RowValue rowValue)
{
var wrowValue = new WRowValue
{
ColumnValues = new List<WScalarExpression>(rowValue.ColumnValues.Count),
FirstTokenIndex = rowValue.FirstTokenIndex,
LastTokenIndex = rowValue.LastTokenIndex
};
foreach (var expr in rowValue.ColumnValues)
{
wrowValue.ColumnValues.Add(ParseScalarExpression(expr));
}
return wrowValue;
}
public ViewNewRowInsertingEventArgs(RoutedEvent routedEvent, RowValue rowValue)
: base(routedEvent)
{
this.rowValue = rowValue;
}
