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