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; }