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