public static string MigrateDataScript(string SQL, string TableName)
{
int posOfCreateTable = SQL.IndexOf("CREATE TABLE");
string s = SQL.Substring(posOfCreateTable + 12);
string[] strArr = SplitMultiLines(s);
strArr[0] = string.Empty;
strArr = SprocOps.RemoveInputParameterPrecision(strArr);
int i = 1;
for (i = 1; i < strArr.Length; i++)
{
string columnName = RemoveDataTypes(strArr[i]).Replace(",", string.Empty).Trim();
if (strArr[i].Contains("NUMBER") || strArr[i].Contains("BOOLEAN") || strArr[i].Contains("IDENTITY"))
{
strArr[i - 1] += "' + ";
strArr[i] = "IsNULL(CONVERT(" + columnName + ", SQL_VARCHAR),'NULL') + ',";
}
else if (strArr[i].Contains("DATE"))
{
strArr[i - 1] += "' + ";
//strArr[i] = "to_date(" + columnName + " AS CHAR(19)) + ''', ";
strArr[i] = "'to_date(''' + CAST(COALESCE(" + columnName + ",'1900/01/01 12:00:00')" + " AS CHAR(19)) + ''',''yyyy/mm/dd HH24:MI:SS'')' + ',";
}
else if (strArr[i].Contains("PRIMARY KEY") || strArr[i].Contains(")"))
{
strArr[i] = string.Empty;
}
else
{
strArr[i - 1] += "''' + ";
strArr[i] = "IsNULL(REPLACE(" + columnName + ", '''', ''''''),'NULL') + ''', ";
}
}
strArr[0] = string.Empty;
for (i = strArr.Length - 1; i > 0; i--)
{
if (strArr[i].Length > 0)
{
strArr[i] = strArr[i].Replace(" + ''', ", string.Empty);
strArr[i] += " + ''');' AS VARCHAR(4000)) AS SQL";
strArr[i] += Environment.NewLine + "FROM " + (char)34 + TableName + (char)34;
break;
}
}
s = "SELECT CAST('INSERT INTO " + TableName + " VALUES (' + " + Environment.NewLine;
s += string.Join(Environment.NewLine, SplitMultiLines(string.Join(Environment.NewLine, strArr)));
s = s.Replace("', + ''');' AS VARCHAR(4000))", "');' AS VARCHAR(4000))");
return(s);
}
/// <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()));
}