public override void Visit(TSqlBatch node) { var variableVisitor = new VariableVisitor(); node.Accept(variableVisitor); VariableValues = variableVisitor.VariableValues; }
public override void ExplicitVisit(TSqlBatch node) { if (node.Statements.Count > 0) { if (node.Statements[0] is PrintStatement print) { if (print.Expression is StringLiteral literal) { if (literal.Value.StartsWith("initializing", StringComparison.InvariantCultureIgnoreCase)) { _buffer.AppendLine("do $$"); _buffer.AppendLine("begin"); for (int index = 1; index < node.Statements.Count; ++index) { node.Statements[index].Accept(this); } _buffer.AppendLine("end"); _buffer.AppendLine("$$ language plpgsql;"); return; } } } } base.ExplicitVisit(node); }
public string Convert(CSchema schema) { string[] parts = { schema.SchemaName }; var createSchemaStatement = new CreateSchemaStatement(); createSchemaStatement.Name = new Identifier { Value = schema.SchemaName }; //generate DDL var script = new TSqlScript(); var batch = new TSqlBatch(); script.Batches.Add(batch); batch.Statements.Add(createSchemaStatement); var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()); var existing = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All); if (existing != null) { return(existing.GetScript()); } dacpacModel.AddObjects(script); existing = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All); return(existing.GetScript()); }
/// <summary> /// This is the entry point into the Visitor pattern and where all the work occurs /// Note we are explicitly triggering on SqlStmt node type /// This is because we only need to parameterize the search conditions /// </summary> /// <param name="node">This is the TsqlFrament sent to us from the Vistor.Accept method</param> public override void ExplicitVisit(TSqlBatch node) { // First determine which tokens belongs to our where clause int index = node.FirstTokenIndex; int end = node.LastTokenIndex; // Now process each token in an appropriate manner while (index <= end) { // Use the TokenType to decide what processing needs to occur TSqlParserToken token = node.ScriptTokenStream[index]; // Emit the token, and if necessary add a parameter to the parameters collection EmitToken(node, token, index); // Until we have processed all the tokens associated with the where clause index++; } // Now we must emit the rest of the tokens to get our entire T-SQL script if (reparse) { // Just a bonus - part of the ScriptDom namespace; side effect it revalidates our new code FormatSQL(); } // let the base class finish up base.ExplicitVisit(node); }
private Task ExecuteSqlAsync(TSqlBatch sqlBatch) { using (var command = _connection.CreateCommand()) { command.CommandText = Generator.GetSql(sqlBatch); return(command.ExecuteNonQueryAsync()); } }
private TSqlFragment GetTablesScript(Entity[] entities, string namespaceName) { var batch = new TSqlBatch(); foreach (var entity in entities) { batch.Statements.Add(GetTableScript(entity, namespaceName)); } return(batch); }
public static TSqlBatch Batch(params TSqlStatement[] statements) { TSqlBatch batch = new TSqlBatch(); foreach (var statement in statements) { batch.Statements.Add(statement); } return(batch); }
public string Convert(CTable table) { string[] parts = { table.Schema.SchemaName, table.TableName }; var createTable = new CreateTableStatement(); ///set schema and table name createTable.SchemaObjectName = new SchemaObjectName(); createTable.SchemaObjectName.Identifiers.Add(new Identifier { Value = table.Schema.SchemaName }); createTable.SchemaObjectName.Identifiers.Add(new Identifier { Value = table.TableName }); //add columns createTable.Definition = new TableDefinition(); foreach (var col in table.Column) { var dataType = new SqlDataTypeReference { SqlDataTypeOption = GetSqlDataTypeOption(col.ColumnType) }; if (col.ColumnLength > 0) { dataType.Parameters.Add(new IntegerLiteral { Value = col.ColumnLength.ToString() }); } var column = new ColumnDefinition { ColumnIdentifier = new Identifier { Value = col.ColumnName }, DataType = dataType }; createTable.Definition.ColumnDefinitions.Add(column); } //generate DDL var script = new TSqlScript(); var batch = new TSqlBatch(); script.Batches.Add(batch); batch.Statements.Add(createTable); var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()); dacpacModel.AddObjects(script); var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All); return(existing.GetScript()); }
public override void Visit(TSqlBatch node) { var childTransactionVisitor = new ChildTransactionVisitor(); node.Accept(childTransactionVisitor); if (childTransactionVisitor.TransactionLists.Exists(x => x.Commit == null)) { var failed_transaction = childTransactionVisitor.TransactionLists.Where(x => x.Commit == null).First(); errorCallback(RULE_NAME, RULE_TEXT, failed_transaction.Begin.StartLine, GetColumnNumber(failed_transaction)); } }
public override void Visit(TSqlBatch node) { var childTransactionVisitor = new ChildTransactionVisitor(); node.Accept(childTransactionVisitor); foreach (var transaction in childTransactionVisitor.TransactionLists) { var childInsertUpdateQueryVisitor = new ChildInsertUpdateQueryVisitor(transaction); node.Accept(childInsertUpdateQueryVisitor); if (childInsertUpdateQueryVisitor.DatabasesUpdated.Count > 1) { errorCallback( RULE_NAME, RULE_TEXT, transaction.Begin.StartLine, transaction.Begin.StartColumn); } } }
/// <summary> /// This is the logic for how to handle the tokens - i.e. copy to output stream, add to parameter collection etc. /// </summary> /// <param name="node">This is the TsqlFrament sent to the ExplicitVisit method</param> /// <param name="token">This is the current token that we are processing</param> /// <param name="index">The index to process</param> //protected void EmitToken(WhereClause node, TSqlParserToken token, int index) protected void EmitToken(TSqlBatch node, TSqlParserToken token, int index) { switch (token.TokenType) { // for the majority of TokenTypes we just pass the token to our StringBuilder for inclusion default: SqlStmt.Append(node.ScriptTokenStream[index].Text); break; // for those token types that may need to be parameterized we capture name, data type and value for the parameters collection case TSqlTokenType.AsciiStringLiteral: case TSqlTokenType.Real: case TSqlTokenType.Integer: case TSqlTokenType.Money: case TSqlTokenType.Numeric: case TSqlTokenType.UnicodeStringLiteral: // We just use a simple naming scheme - i.e. @p1, @p2 and so on string p = "@p" + (++parameterNumber).ToString(); SqlStmt.Append(p); // Now create the entry in the SqlCommand Parameters collection AddToParameterCollection(token, parameterNumber); break; } }
public override void ExplicitVisit(TSqlBatch fragment) { _fragments.Add(fragment); }
protected override object InternalVisit(TSqlBatch node) { return(VisitCollection <SQLExecutionResult>(node.Statements).ToArray()); }
public void WriteBatch(TSqlBatch batch) { _generator.GenerateScript(batch, _writer); WriteFragment(GO()); }
public override void Visit(TSqlBatch node) { this.action(node); }
public string GetSql(TSqlBatch sqlBatch) { _generator.GenerateScript(sqlBatch, out string sql); return(sql); }
protected override void OnExecute(DeploymentPlanContributorContext context) { // Obtain the first step in the Plan from the provided context int batchId = 0; DeploymentStep nextStep = context.PlanHandle.Head; BeginPreDeploymentScriptStep beforePreDeploy = null; while (nextStep != null) { DeploymentStep currentStep = nextStep; nextStep = currentStep.Next; if (currentStep is BeginPreDeploymentScriptStep) { beforePreDeploy = (BeginPreDeploymentScriptStep)currentStep; continue; } if (currentStep is SqlPrintStep) { continue; } if (currentStep is BeginPostDeploymentScriptStep) { break; } if (beforePreDeploy == null) { continue; } DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep; if (domStep == null) { continue; } TSqlScript script = domStep.Script as TSqlScript; if (script == null) { continue; } // Loop through all the batches in the script for this step. All the statements // in the batch will be enclosed in an if statement that will check the // table to ensure that the batch has not already been executed TSqlObject sqlObject; string stepDescription; GetStepInfo(domStep, out stepDescription, out sqlObject); int batchCount = script.Batches.Count; for (int batchIndex = 0; batchIndex < batchCount; batchIndex++) { // Create the if statement that will contain the batch's contents IfStatement ifBatchNotExecutedStatement = CreateIfNotExecutedStatement(batchId); BeginEndBlockStatement statementBlock = new BeginEndBlockStatement(); ifBatchNotExecutedStatement.ThenStatement = statementBlock; statementBlock.StatementList = new StatementList(); TSqlBatch batch = script.Batches[batchIndex]; int statementCount = batch.Statements.Count; // Loop through all statements in the batch, embedding those in an sp_execsql // statement that must be handled this way (schemas, stored procedures, // views, functions, and triggers). for (int statementIndex = 0; statementIndex < statementCount; statementIndex++) { TSqlStatement smnt = batch.Statements[statementIndex]; if (IsStatementEscaped(sqlObject)) { // "escape" this statement by embedding it in a sp_executesql statement string statementScript; domStep.ScriptGenerator.GenerateScript(smnt, out statementScript); ExecuteStatement spExecuteSql = CreateExecuteSql(statementScript); smnt = spExecuteSql; } statementBlock.StatementList.Statements.Add(smnt); } // Add an insert statement to track that all the statements in this // batch were executed. Turn on nocount to improve performance by // avoiding row inserted messages from the server string batchDescription = string.Format(CultureInfo.InvariantCulture, "{0} batch {1}", stepDescription, batchIndex); PredicateSetStatement noCountOff = new PredicateSetStatement(); noCountOff.IsOn = false; noCountOff.Options = SetOptions.NoCount; PredicateSetStatement noCountOn = new PredicateSetStatement(); noCountOn.IsOn = true; noCountOn.Options = SetOptions.NoCount; InsertStatement batchCompleteInsert = CreateBatchCompleteInsert(batchId, batchDescription); statementBlock.StatementList.Statements.Add(noCountOn); statementBlock.StatementList.Statements.Add(batchCompleteInsert); statementBlock.StatementList.Statements.Add(noCountOff); // Remove all the statements from the batch (they are now in the if block) and add the if statement // as the sole statement in the batch batch.Statements.Clear(); batch.Statements.Add(ifBatchNotExecutedStatement); // Next batch batchId++; } } // if we found steps that required processing, set up a temporary table to track the work that you are doing if (beforePreDeploy != null) { // Declare a SqlCmd variables. // // CompletedBatches variable - defines the name of the table in tempdb that will track // all the completed batches. The temporary table's name has the target database name and // a guid embedded in it so that: // * Multiple deployment scripts targeting different DBs on the same server // * Failed deployments with old tables do not conflict with more recent deployments // // TotalBatchCount variable - the total number of batches surrounded by if statements. Using this // variable pre/post deployment scripts can also use the CompletedBatches table to make their // script rerunnable if there is an error during execution StringBuilder sqlcmdVars = new StringBuilder(); sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, CompletedBatchesSqlCmd, context.Options.TargetDatabaseName, Guid.NewGuid().ToString("D")); sqlcmdVars.AppendLine(); sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, TotalBatchCountSqlCmd, batchId); DeploymentScriptStep completedBatchesSetVarStep = new DeploymentScriptStep(sqlcmdVars.ToString()); base.AddBefore(context.PlanHandle, beforePreDeploy, completedBatchesSetVarStep); // Create the temporary table we will use to track the work that we are doing DeploymentScriptStep createStatusTableStep = new DeploymentScriptStep(CreateCompletedBatchesTable); base.AddBefore(context.PlanHandle, beforePreDeploy, createStatusTableStep); } // Cleanup and drop the table // DeploymentScriptStep dropStep = new DeploymentScriptStep(DropCompletedBatchesTable); // base.AddAfter(context.PlanHandle, context.PlanHandle.Tail, dropStep); }
public void WriteBatch(TSqlBatch batch) { _generator.GenerateScript(batch, _writer); }