private IEnumerable <KStoredProcedure> GetStoredProcedures(string solutionName) { _sl.SelectWorksheet("StoredProcedures"); var storedProcedures = new List <KStoredProcedure>(); var colSolutionName = GetColumnIndex("SolutionName"); var colSchema = GetColumnIndex("Schema"); var colStoredProcedureName = GetColumnIndex("StoredProcedureName"); var colResultSetName = GetColumnIndex("ResultSetName"); var colReturnsMultipleRows = GetColumnIndex("ReturnsMultipleRows"); var currentRow = 2; while (!string.IsNullOrEmpty(_sl.GetCellValueAsString(currentRow, colSolutionName))) { if (_sl.GetCellValueAsString(currentRow, colSolutionName) != solutionName) { currentRow++; continue; } var storedProcedure = new KStoredProcedure { Schema = _sl.GetCellValueAsString(currentRow, colSchema), StoredProcedureName = _sl.GetCellValueAsString(currentRow, colStoredProcedureName), ResultSetName = _sl.GetCellValueAsString(currentRow, colResultSetName), ReturnsMultipleRows = _sl.GetCellValueAsBoolean(currentRow, colReturnsMultipleRows) }; // storedProcedure.GeneratedStoredProcedure = // new CStoredProcedure {StoredProcedureName = storedProcedure.StoredProcedureName}; storedProcedure.GeneratedStoredProcedure.Schema = new CSchema { SchemaName = storedProcedure.Schema }; storedProcedures.Add(storedProcedure); currentRow++; } foreach (var storedProcedure in storedProcedures) { storedProcedure.GeneratedStoredProcedure.Parameter.AddRange( GetStoredProcedureParameters(solutionName, storedProcedure.StoredProcedureName)); } return(storedProcedures); }
private List <KStoredProcedure> CreateSqlStoredProcsInDb(KDataStoreProject kDataStoreProject, string connectionString, string sqlText) { var storedProcedures = new List <KStoredProcedure>(); var scriptGen = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions { IncludeSemicolons = true }); using (var sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); var parser = new TSql120Parser(false); var script2 = parser.Parse(new StringReader(sqlText), out var errors) as TSqlScript; if (errors.Count > 0) { var errorList = new StringBuilder(); foreach (var error in errors) { errorList.AppendLine($"{error.Message}<br/>"); } throw new ApplicationException(errorList.ToString()); } for (var batchNo = 0; batchNo < script2.Batches.Count; batchNo++) { var batch2 = script2.Batches[batchNo]; //get the doc above the stored proc var betweenBatchStart = 0; if (batchNo > 0) { betweenBatchStart = script2.Batches[batchNo - 1].StartOffset + script2.Batches[batchNo - 1].FragmentLength; } var betweenBatchEnd = batch2.StartOffset - 1; string batchText = null; if (betweenBatchEnd > 0) { batchText = sqlText.Substring(betweenBatchStart, betweenBatchEnd - betweenBatchStart); //clean up the doc batchText = batchText.Replace("GO", ""); batchText = batchText.Replace(Environment.NewLine, ""); batchText = batchText.Replace("\r", ""); batchText = batchText.Replace("/*", ""); batchText = batchText.Replace("*/", ""); batchText = batchText.Trim(); } foreach (var statement in batch2.Statements) { var createProcedureStatement = statement as CreateProcedureStatement; if (createProcedureStatement == null) { continue; } var viewSchemaName = createProcedureStatement.ProcedureReference.Name.SchemaIdentifier.Value; var createSchemaCommand = new SqlCommand( $@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}') BEGIN EXEC sp_executesql N'CREATE SCHEMA {viewSchemaName}' END", sqlConnection); createSchemaCommand.ExecuteNonQuery(); scriptGen.GenerateScript(statement, out var scriptOut, out var errors2); //fixup CTE //var tempScript = scriptOut.Replace(Environment.NewLine, " ").ToUpper(); //might insert extra ; it won't hurt anything if (scriptOut.Contains(" WITH ")) { scriptOut = scriptOut.Replace(" WITH ", "; WITH "); } else if (scriptOut.Contains(Environment.NewLine + "WITH ")) { scriptOut = scriptOut.Replace(Environment.NewLine + "WITH ", ";" + Environment.NewLine + " WITH "); } var sqlCommand = new SqlCommand(scriptOut, sqlConnection); sqlCommand.ExecuteNonQuery(); var storedProcedure = new KStoredProcedure { Schema = createProcedureStatement.ProcedureReference.Name.SchemaIdentifier.Value, StoredProcedureName = createProcedureStatement.ProcedureReference.Name.BaseIdentifier.Value, StoredProcedureText = scriptOut, StoredProcedureDescription = batchText, ResultSetName = BuildResultSetName(createProcedureStatement.ProcedureReference.Name.BaseIdentifier.Value) }; var oldStoredProc = kDataStoreProject.OldStoredProcedure.FirstOrDefault(s => s.StoredProcedureName == storedProcedure.StoredProcedureName); //todo: compare schema if (oldStoredProc != null) { storedProcedure.DerivedFrom = oldStoredProc.DerivedFrom; } storedProcedures.Add(storedProcedure); } } sqlConnection.Close(); } return(storedProcedures); }