public static void OperationNonClusteredCoveringIndexesBridgeTables(DatabaseData dbs, string procNameTemplate, string procFileNameTemplate, bool returnAllData, string creator, string dateformat, bool questarSpecificDates, TableData.Operation currentOper, string temlateSQLScriptPath, string finalDir)
{
if (!Directory.Exists(finalDir + @"\" + currentOper.ToString().ToUpper()))
{
Directory.CreateDirectory(finalDir + @"\" + currentOper.ToString().ToUpper());
}
foreach (TableData tb in dbs.Tables)
{
foreach (ColumnData col in tb.Columns)
{
if (col.isFK)
{
FileInfo file = new FileInfo(temlateSQLScriptPath);
StreamReader SqlScriptReader = file.OpenText();
StringBuilder getAllScript = new StringBuilder();
getAllScript.Append(SqlScriptReader.ReadToEnd());
string indexName = procNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!C0lumn!", col.ColumnName.ToString());
string procFileName = procFileNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!C0lumn!", col.ColumnName.ToString());
getAllScript.Replace("!cr3ationDAT3!", DateTime.Now.ToString(dateformat.Replace("/", "\\/")));
getAllScript.Replace("!cr3ator!", creator);
getAllScript.Replace("!tblNAM3!", tb.TableName);
#region scriptCode
StringBuilder sqlScriptCode = new StringBuilder();
sqlScriptCode.Append(
"\n CREATE NONCLUSTERED INDEX [" + indexName + "] ON [dbo].[" + tb.TableName + "] "
+ "\n ( "
+ "\n [" + col.ColumnName + "] "
+ "\n )"
+ "\nINCLUDE"
+ "\n (");
bool firstAdded = false;
foreach (ColumnData colIncl in tb.Columns)
{
if (colIncl.isFK && colIncl.ColumnName != col.ColumnName)
{
sqlScriptCode.Append("\n " + (firstAdded == true ? "," : "") + " [" + colIncl.ColumnName + "]");
firstAdded = true;
}
}
sqlScriptCode.Append("\n )"
+ "\n GO"
);
if (firstAdded)
{
getAllScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
#endregion
CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", getAllScript);
}
}
}
}
}
public static void OperationDelete(DatabaseData dbs, string procNameTemplate, string procFileNameTemplate, bool returnAllData, string creator, string dateformat, bool questarSpecificDates, TableData.Operation currentOper, string temlateSQLScriptPath, string finalDir)
{
if (!Directory.Exists(finalDir + "\\" + currentOper.ToString().ToUpper()))
{
Directory.CreateDirectory(finalDir + "\\" + currentOper.ToString().ToUpper());
}
foreach (TableData tb in dbs.Tables)
{
FileInfo file = new FileInfo(temlateSQLScriptPath);
StreamReader SqlScriptReader = file.OpenText();
StringBuilder deleteScript = new StringBuilder();
deleteScript.Append(SqlScriptReader.ReadToEnd());
string procName = procNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
string procFileName = procFileNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
deleteScript.Replace("!pr0cNam3!", procName);
deleteScript.Replace("!cr3ationDAT3!", DateTime.Now.ToString(dateformat.Replace("/", "\\/")));
deleteScript.Replace("!cr3ator!", creator);
deleteScript.Replace("!Operati0n!", currentOper.ToString().ToUpper());
deleteScript.Replace("!tblNAM3!", tb.TableName);
#region TestedStrings
StringBuilder testedStrings = tb.TestedStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Delete, null);
deleteScript.Replace("!T3sTStrings!", testedStrings.ToString());
#endregion
#region scriptParameters
StringBuilder parameters = tb.ParameterStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Delete, null);
deleteScript.Replace("!param3t3rs!", parameters.ToString());
#endregion
#region scriptCode
StringBuilder sqlScriptCode = new StringBuilder();
sqlScriptCode.Append(" DELETE \n");
sqlScriptCode.Append(" FROM [dbo].[" + tb.TableName + "] \n");
sqlScriptCode.Append(" WHERE [" + tb.PrimaryKey + "] = @" + tb.PrimaryKey + "; \n");
sqlScriptCode.Append(" SELECT @@ROWCOUNT AS [RowCount];");
deleteScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
#endregion
CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", deleteScript);
}
}
public static void OperationInsert(DatabaseData dbs, string procNameTemplate, string procFileNameTemplate, bool returnAllData, string creator, string dateformat, bool questarSpecificDates, TableData.Operation currentOper, string temlateSQLScriptPath, string finalDir)
{
if (!Directory.Exists(finalDir + "\\" + currentOper.ToString().ToUpper()))
{
Directory.CreateDirectory(finalDir + "\\" + currentOper.ToString().ToUpper());
}
foreach (TableData tb in dbs.Tables)
{
FileInfo file = new FileInfo(temlateSQLScriptPath);
StreamReader SqlScriptReader = file.OpenText();
StringBuilder insertScript = new StringBuilder();
insertScript.Append(SqlScriptReader.ReadToEnd());
string procName = procNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
string procFileName = procFileNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
insertScript.Replace("!pr0cNam3!", procName);
insertScript.Replace("!cr3ationDAT3!", DateTime.Now.ToString(dateformat.Replace("/", "\\/")));
insertScript.Replace("!cr3ator!", creator);
insertScript.Replace("!Operati0n!", currentOper.ToString());
insertScript.Replace("!tblNAM3!", tb.TableName);
#region TestedStrings
StringBuilder testedStrings = tb.TestedStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Insert, dbs.ManagedInsertColumns);
insertScript.Replace("!T3sTStrings!", testedStrings.ToString());
#endregion
#region scriptParameters
StringBuilder parameters = tb.ParameterStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Insert, dbs.ManagedInsertColumns);
insertScript.Replace("!param3t3rs!", parameters.ToString());
#endregion
#region scriptCode
StringBuilder sqlScriptCode = tb.SqlScriptCodeStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Insert, dbs.ManagedInsertColumns);
insertScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
#endregion
//save file for table
CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", insertScript);
}
}