public Operations(string file, TableData.Operation oper, string fname, string operName)
{
sqlTemplatefile = file;
templateOperation = oper;
fileNameTemplate = fname;
operationNameTamplate = operName;
}
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);
}
}
public static void OperationUniqueIndexFK(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 getAllScript = new StringBuilder();
getAllScript.Append(SqlScriptReader.ReadToEnd());
string indexName = procNameTemplate.Replace("!tblNAM3!", tb.TableName);
string procFileName = procFileNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
getAllScript.Replace("!cr3ationDAT3!", DateTime.Now.ToString(dateformat.Replace("/", "\\/")));
getAllScript.Replace("!cr3ator!", creator);
getAllScript.Replace("!tblNAM3!", tb.TableName);
StringBuilder sqlScriptCode = new StringBuilder();
sqlScriptCode.Append(
"\n CREATE UNIQUE NONCLUSTERED INDEX [!idxN@me!] ON [dbo].[" + tb.TableName + "] "
+ "\n ( ");
bool firstAdded = false;
foreach (ColumnData col in tb.Columns)
{
if (col.isFK)
{
indexName += "_" + col.ColumnName;
procFileName += "_" + col.ColumnName;
sqlScriptCode.Append("\n " + (firstAdded == true ? "," : "") + "[" + col.ColumnName + "] ");
firstAdded = true;
}
}
sqlScriptCode.Replace("!idxN@me!", indexName);
sqlScriptCode.Append("\n ) " + "\n GO");
getAllScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", getAllScript);
}
}
public static void OperationNonClusteredIndexesFKAllTables(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("!Operati0n!", currentOper.ToString()).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 ) "
+ "\n GO"
);
getAllScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
#endregion
CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", getAllScript);
}
}
}
}
/// <summary>
/// Generates all procedures in the selected folder. Creates folder with DB name and sub folders with Operation Name
private void ParseProcs(DatabaseData dbs, bool returnAllData, List <Operations> operations, string destinationDir, string creator, bool useSpecificDates)
{
string finalDir = destinationDir + "\\" + dbs.DatabaseName;
// SQLFormater sqlFormat = new SQLFormater();
//To do implement setting that uses the SQLFormater to format every file based on the format settings
if (!Directory.Exists(finalDir))
{
Directory.CreateDirectory(finalDir);
}
foreach (Operations currentOperation in operations)
{
TableData.Operation oper = currentOperation.templateOperation;
string temlateSQLScriptPath = "";
temlateSQLScriptPath = configurationSQLPath + currentOperation.sqlTemplatefile;
switch (oper)
{
case TableData.Operation.Insert:
OperationsManage.OperationInsert(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.Update:
OperationsManage.OperationUpdate(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.Delete:
OperationsManage.OperationDelete(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.GetAll:
OperationsManage.OperationGetAll(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.GetByID:
OperationsManage.OperationGetByID(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.GetByFilter:
OperationsManage.OperationGetByFilter(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.GetByFK:
OperationsManage.OperationGetByFK(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.NonClusteredCoveringIndexesTables:
OperationsManage.OperationNonClusteredCoveringIndexesBridgeTables(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.NonClusteredIndexesFKAllTables:
OperationsManage.OperationNonClusteredIndexesFKAllTables(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.NonClusteredIndexesFKAllColumnsAllTables:
OperationsManage.OperationNonClusteredIndexesFKAllColumnsAllTables(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
case TableData.Operation.UniqueIndexFK:
OperationsManage.OperationUniqueIndexFK(dbs, currentOperation.operationNameTamplate, currentOperation.fileNameTemplate, returnAllData, creator, tbDateFormat.Text, useSpecificDates, currentOperation.templateOperation, temlateSQLScriptPath, finalDir);
break;
}
}
MessageBox.Show("Procedures Generated!");
}
