/// <summary>
/// Converts the package of stored procs.
/// </summary>
/// <param name="SQL">The SQL.</param>
/// <returns></returns>
public static string ConvertPackageOfStoredProcs(string SQL)
{
StringBuilder sbOutput = new StringBuilder();
//Split the SQL into the various sprocs that will make up this package
//Remove the NoRowsUpdatedCheck
SQL = string.Join(Environment.NewLine, RemoveLines(SplitMultiLines(SQL), "IF @@ROWCOUNT = 0 THEN", "END IF"));
//Remove Double Spaces
SQL = string.Join(Environment.NewLine, RemoveDoubleSpaces(SplitMultiLines(SQL)));
if (!CheckValidInput(SQL))
{
return(string.Empty);
}
string[] sprocs = Regex.Split(SQL, "END");
foreach (string individualSproc in sprocs)
{
//convert the data types
string sproc = individualSproc;
sproc = ConvertDataTypes(sproc);
//split the sproc up into lines, delimited by line feeds
string[] strArr = SplitMultiLines(sproc);
int i = 0;
//find the line containing CREATE
foreach (string line in strArr)
{
int posOfCreate = line.ToUpper().IndexOf("CREATE");
if (posOfCreate > 0 || line.ToUpper().Contains("CREATE"))
{
string signatureline = line.Replace("\"", string.Empty);
//Remove the CREATE word
signatureline = signatureline.Substring(0, posOfCreate).Trim() + " " + signatureline.Substring(posOfCreate + 6).Trim();
strArr[i] = signatureline.Trim();
string storedProcName = signatureline.Replace("PROCEDURE", string.Empty);
storedProcName = storedProcName.Replace("(", string.Empty).Trim();
//Call the methods designed for converting particular CRUD operation via sprocs
//Note: This solution is not smart enough for multiple CRUD ops in one SPROC (eg an UPDATE then a DELETE in one Sproc)
if (storedProcName.ToLower().Substring(0, 3) == "add")
{
sproc = SprocOps.convertInsertSproc(storedProcName, strArr);
}
else if (storedProcName.ToLower().Substring(0, 6) == "delete")
{
sproc = SprocOps.convertDeleteSproc(storedProcName, strArr);
}
else if (storedProcName.ToLower().Substring(0, 3) == "get")
{
sproc = SprocOps.convertSelectSproc(storedProcName, strArr);
}
else if (storedProcName.ToLower().Substring(0, 6) == "update")
{
sproc = SprocOps.convertUpdateSproc(storedProcName, strArr);
}
else
{
throw new Exception("Found line with CREATE but it doesn't contain a: add, update, insert or delete Procedure");
}
//Put in the IS statement
sproc = InsertISStatement(sproc);
//save the converted sproc in a stringbuilder
sbOutput.Append(sproc + Environment.NewLine + Environment.NewLine);
//Skip to converting next Sproc
break;
}
i++;
}
}
return(PolishSyntaxErrors(sbOutput.ToString()));
}
