private static void CheckSchemaForUpgradeTables() { Logger.Log("Checking for UpgradeScript tables in database"); if (!TableExists(UpgradeScriptRunTableName)) { Logger.Log($"Creating table {UpgradeScriptRunTableName}"); string createTable = $"{UseDatabase} " + $"CREATE TABLE {UpgradeScriptRunTableName} ( " + $"{UpgradeScriptRunTableName}Id INTEGER NOT NULL UNIQUE IDENTITY(1, 1), " + "ScriptFile VARCHAR(255) NOT NULL, " + "ExecutedTime DATETIME NOT NULL DEFAULT GETUTCDATE(), " + "IsSuccessful BIT NOT NULL DEFAULT 0 " + "PRIMARY KEY (UpgradeScriptRunId) )"; SQLInterface.ExecuteNonQuery(createTable); Logger.LogVerbose($"Table {UpgradeScriptRunTableName} created"); } if (!TableExists(UpgradeScriptRunErrorTableName)) { Logger.Log($"Creating table {UpgradeScriptRunErrorTableName}"); string createTable = $"{UseDatabase} " + $"CREATE TABLE {UpgradeScriptRunErrorTableName} ( " + $"{UpgradeScriptRunErrorTableName}Id INTEGER NOT NULL UNIQUE IDENTITY(1, 1), " + $"{UpgradeScriptRunTableName}Id INTEGER NOT NULL, " + "ErrorMessage NTEXT, " + $"PRIMARY KEY ({UpgradeScriptRunErrorTableName}Id), " + $"FOREIGN KEY ({UpgradeScriptRunTableName}Id) REFERENCES {UpgradeScriptRunTableName}({UpgradeScriptRunTableName}Id) )"; SQLInterface.ExecuteNonQuery(createTable); Logger.LogVerbose($"Table {UpgradeScriptRunErrorTableName} created"); } }
private static void GetDatabaseSchema() { Logger.Log("Retrieving table schema"); string tableQueryFile = Path.Combine(TemplateDirectory, "TableSchemaQuery.txt"); string tableSchemaQuery = File.ReadAllText(tableQueryFile); tableSchemaQuery = tableSchemaQuery.Replace("%_DATABASE_%", Database); tableSchemaQuery = tableSchemaQuery.Replace("%_SCHEMA_%", currentSchema); DatabaseSchema = SQLInterface.ExecuteQueryIntoDataTable(tableSchemaQuery); }
private static void BuildFunctionClass() { Logger.Log("Retrieving function schema"); string outputFileName = "DbFunctions.cs"; string functionSchemaQuery = ParseTemplate("FunctionSchemaQuery.txt"); functionSchemaQuery = functionSchemaQuery.Replace("%_DATABASE_%", Database); functionSchemaQuery = functionSchemaQuery.Replace("%_SCHEMA_%", currentSchema); var functionSchema = SQLInterface.ExecuteQueryIntoDataTable(functionSchemaQuery); if (functionSchema.Rows.Count == 0) { Logger.Log("No functions found"); return; } headerLines.Clear(); fieldLines.Clear(); footerLines.Clear(); string fileText = ParseTemplate("FunctionHeader.txt"); int i = 0; List <DataRow> parameters = new List <DataRow>(); DataRow currentRow; while (i < functionSchema.Rows.Count) { currentRow = functionSchema.Rows[i]; currentFunction = currentRow["FunctionName"].ToString(); parameters.Add(currentRow); i++; if (i < functionSchema.Rows.Count) { currentRow = functionSchema.Rows[i]; while (i < functionSchema.Rows.Count && currentFunction.Equals(currentRow["FunctionName"].ToString())) { parameters.Add(currentRow); i++; if (i < functionSchema.Rows.Count) { currentRow = functionSchema.Rows[i]; } } } fileText += BuildFunctionMethodDefinition(parameters); } fileText += ParseTemplate("FunctionFooter.txt"); string outputPath = Path.Combine(OutputDirectory, outputFileName); Logger.Log($"Writing code to {outputPath}"); File.WriteAllText(outputPath, fileText); }
private static void CheckDatabase() { string databaseQuery = $"SELECT COUNT(*) FROM sys.databases WHERE [name] = '{Database}'"; var queryResults = SQLInterface.ExecuteQueryIntoDataTable(databaseQuery); int count = (int)queryResults.Rows[0].ItemArray[0]; if (count == 0) { string createDatabase = $"CREATE DATABASE [{Database}]"; SQLInterface.ExecuteNonQuery(createDatabase); } }
private static bool TableExists(string tableName) { Logger.LogVerbose($"Checking for table {tableName} in database"); SQLInterface.ExecuteNonQuery(UseDatabase); string tableQuery = $"SELECT COUNT(*) FROM sys.tables WHERE [name] = '{tableName}'"; var queryResult = SQLInterface.ExecuteQueryIntoDataTable(tableQuery); bool result = (int)queryResult.Rows[0].ItemArray[0] == 1; if (result) { Logger.LogVerbose($"Table {tableName} found"); } else { Logger.LogVerbose($"Table {tableName} not found"); } return(result); }
private static void LogUpgradeScriptRun(string filePath, Exception exception = null) { Logger.LogVerbose($"Logging upgrade script for script {filePath}"); int isSuccessful = (exception == null ? 1 : 0); filePath = Path.GetFileName(filePath); string query = $"INSERT INTO UpgradeScriptRun (ScriptFile, IsSuccessful) VALUES " + $"('{filePath}', {isSuccessful})"; SQLInterface.ExecuteNonQuery(query); if (isSuccessful == 0) { string message = exception.Message.Replace("'", "''"); query = "INSERT INTO UpgradeScriptRunError (UpgradeScriptRunId, ErrorMessage) " + $"SELECT TOP 1 UpgradeScriptRunId, '{message}' FROM UpgradeScriptRun WHERE ScriptFile = '{filePath}' " + "ORDER BY ExecutedTime DESC"; SQLInterface.ExecuteNonQuery(query); } }
private static void RunNewDatabaseUpgradeScripts() { if (NewUpgradeScripts.Count == 0) { Logger.Log("No new upgrade scripts to run"); return; } SQLInterface.ExecuteNonQuery(UseDatabase); string fileContent; string[] statements; var goSplit = new Regex("\r\n[ \t]{0,}GO([ \t]{0,}\r\n{0,}){1,}"); foreach (string file in NewUpgradeScripts) { Logger.Log($"Executing script {file}"); fileContent = $"{UseDatabase}\r\n GO\r\n {File.ReadAllText(file)}"; statements = goSplit.Split(fileContent); try { foreach (string statement in statements) { if (!string.IsNullOrEmpty(statement)) { SQLInterface.ExecuteNonQuery(statement); } } LogUpgradeScriptRun(file); } catch (Exception e) { Logger.Log($"Error when running script: {e.Message}"); LogUpgradeScriptRun(file, e); } } }
private static void GetNewUpgradeScripts() { Logger.Log("Determining upgrade scripts to run"); NewUpgradeScripts = Directory.GetFiles(UpgradeScriptDirectory) .Where(e => CorrectFileNameRegex.IsMatch(Path.GetFileName(e)) && UpgradeScriptFilterRegex.IsMatch(Path.GetFileName(e))) .OrderBy(e => Path.GetFileName(e).Split('_')[0]) .Select(e => Path.GetFileName(e)) .ToList(); Logger.LogVerbose($"{NewUpgradeScripts.Count} valid scripts found in source directory"); string successfulScriptsQuery = $"SELECT DISTINCT ScriptFile FROM {UpgradeScriptRunTableName} WHERE IsSuccessful = 1"; var queryResults = SQLInterface.ExecuteQueryIntoDataTable(successfulScriptsQuery); Logger.LogVerbose($"{queryResults.Rows.Count} successful scripts found in database records"); foreach (DataRow row in queryResults.Rows) { string upgradeScript = row.ItemArray[0].ToString(); Logger.LogVerbose($"Removing script {upgradeScript} from list"); if (NewUpgradeScripts.Contains(upgradeScript)) { NewUpgradeScripts.Remove(upgradeScript); Logger.LogVerbose("Script removed"); } else { Logger.LogVerbose("Script does not exist in directory"); } } Logger.LogVerbose($"{NewUpgradeScripts.Count} new scripts found in directory"); NewUpgradeScripts = NewUpgradeScripts .OrderBy(e => int.Parse(e.Split('_')[0])) .Select(e => Path.Combine(UpgradeScriptDirectory, e)) .ToList(); }
public static void ProcessArguments(string[] args) { SetDefaultConfiguration(); try { for (int i = 0; i < args.Length; i++) { string arg = args[i].ToLower(); switch (arg) { case HelpSwitch: PrintHelpPage(); Environment.Exit((int)ExitCode.OK); break; case ConfigFileSwitch: AppConfigReader.LoadConfigurationFromFile(args[++i]); ImportFromConfigFile(); break; // Upgrade Script options case UpgradeScriptSwitch: RunUpgradeScripts = true; break; case UpgradeScriptDirectorySwitch: UpgradeScriptDirectory = args[++i]; break; case UpgradeScriptFileRegexSwitch: UpgradeScriptFileRegex = args[++i]; break; case UpgradeScriptTemplateGuidMonikerSwitch: UpgradeScriptTemplateGuidMonikerPrefix = args[++i]; break; // Entity code generation options case EntityCodeGenerationSwitch: RunEntityCodeGeneration = true; break; case TemplateDirectorySwitch: TemplateDirectory = args[++i]; break; case DatabaseSchemaListSwitch: DatabaseSchemas = args[++i].Split(';'); break; case CodeGenOutputSwitch: EntityCodeOutputDirectory = args[++i]; break; // Code build options case CodeBuildSwitch: RunCodeBuild = true; break; case ProjectNameSwitch: ProjectName = args[++i]; break; // Logging options case LogFileSwitch: DoLogging = true; SetLogFile(args[++i]); break; case DebugSwitch: case VerboseLoggingSwitch: VerboseLogging = true; break; case LogMessagePrefixSwitch: LogMessagePrefix = args[++i]; break; // Database options case DatabaseConnectionStringSwitch: ConnectionString = args[++i]; break; case DatabaseSwitch: Database = args[++i]; break; } } CheckDatabase(); if (DatabaseSchemas.Contains("*")) { string schemaQuery = $"USE [{Database}] " + "SELECT DISTINCT s.[name] FROM sys.tables t " + "INNER JOIN sys.schemas s ON s.schema_id = t.schema_id " + "WHERE s.[name] != 'dbo' " + "UNION ALL SELECT 'dbo'"; var queryResults = SQLInterface.ExecuteQueryIntoDataTable(schemaQuery); string result = ""; foreach (DataRow row in queryResults.Rows) { result += row.ItemArray[0].ToString() + ","; } DatabaseSchemas = result.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); } } catch (Exception e) { Console.WriteLine($"ERROR: {e.Message}"); PrintHelpPage(); Environment.Exit((int)ExitCode.ArgumentError); } }