public static string NewSqlCmdScript(string scriptsDirectory, DataScriptType dataScriptType) { if (!Directory.Exists(scriptsDirectory)) { throw new Exception($"Direcotry {scriptsDirectory} does not exist."); } string[] files = Directory.GetFiles(scriptsDirectory, "*.sql"); List <string> remainingScripts = new List <string>(); foreach (string file in files) { string fileName = Path.GetFileNameWithoutExtension(file).ToUpper(); // Check for schema and table name if (fileName.Split('.').Length != 2) { // TODO log event continue; } remainingScripts.Add(fileName); } Queue <string> scriptQueue = new Queue <string>(remainingScripts); string disableConstraintTables = string.Empty; string scripts = string.Empty; string dataScriptRootVariable = string.Empty; string dataScriptRoot = string.Empty; string dataScriptTypeString = string.Empty; if (dataScriptType == DataScriptType.Static) { dataScriptTypeString = "Static"; } else if (dataScriptType == DataScriptType.Test) { dataScriptTypeString = "Test"; } dataScriptRootVariable = $"{dataScriptTypeString}DataScriptRoot"; dataScriptRoot = $@".\{dataScriptTypeString}"; while (scriptQueue.Count > 0) { string fileName = scriptQueue.Dequeue(); string[] splitFileName = fileName.Split('.'); string schema = splitFileName[0]; string tableName = splitFileName[1]; //bool tmp = false; //while (!tmp) //{ //foreach (TableDependency dependency in tableDependencies.Where(td => td.ChildTableName.Schema == schema && td.ChildTableName.Name == tableName)) //{ disableConstraintTables += $" (N'{schema}', N'{tableName}')"; scripts += $@":R $({dataScriptRootVariable})\{fileName}.sql"; if (scriptQueue.Count > 0) { disableConstraintTables += ","; disableConstraintTables += System.Environment.NewLine; scripts += System.Environment.NewLine; } //} //} } string sqlCmdScript = string.Empty; sqlCmdScript += $"/*" + Environment.NewLine; sqlCmdScript += $"* {dataScriptTypeString} Data Post-Deployment Script" + Environment.NewLine; sqlCmdScript += $"*" + Environment.NewLine; sqlCmdScript += $"* This file executes all the {dataScriptTypeString.ToLower()} data files for the current database." + Environment.NewLine; sqlCmdScript += $"* SQLCMD Variables must be set in the sql project properties." + Environment.NewLine; sqlCmdScript += $"*/" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"SET NOCOUNT ON" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"PRINT '--------------------------------------------------------------------------------------------------------'" + Environment.NewLine; sqlCmdScript += $"PRINT 'Starting {dataScriptTypeString} Data Post-Deployment Script for $(DatabaseName)...'" + Environment.NewLine; sqlCmdScript += $"PRINT '--------------------------------------------------------------------------------------------------------'" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"DECLARE @{dataScriptTypeString}DataScripts TABLE (TableSchema NVARCHAR(50), TableName NVARCHAR(50))" + Environment.NewLine; sqlCmdScript += $"DECLARE @{dataScriptTypeString}DataTableSchema NVARCHAR(50)" + Environment.NewLine; sqlCmdScript += $"DECLARE @{dataScriptTypeString}DataTableName NVARCHAR(50)" + Environment.NewLine; sqlCmdScript += $"DECLARE @{dataScriptTypeString}DataCommand NVARCHAR(2000) = N''" + Environment.NewLine; sqlCmdScript += $"DECLARE {dataScriptTypeString}DataScriptsCursor CURSOR FOR" + Environment.NewLine; sqlCmdScript += $" SELECT TableSchema, TableName" + Environment.NewLine; sqlCmdScript += $" FROM @{dataScriptTypeString}DataScripts" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"-- Tables for which constraint checks will be disabled" + Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"INSERT INTO @{dataScriptTypeString}DataScripts" + Environment.NewLine; sqlCmdScript += $"VALUES" + Environment.NewLine; sqlCmdScript += $" -- Schema Name Table Name" + Environment.NewLine; sqlCmdScript += $"{disableConstraintTables}" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"-- DISABLE constraint checks for listed tables" + Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"OPEN {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $"FETCH NEXT FROM {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $"INTO @{dataScriptTypeString}DataTableSchema, @{dataScriptTypeString}DataTableName" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"WHILE @@FETCH_STATUS = 0" + Environment.NewLine; sqlCmdScript += $"BEGIN" + Environment.NewLine; sqlCmdScript += $" PRINT '[INFO]: Disabling Constraints [' + @{dataScriptTypeString}DataTableSchema + '].[' + @{dataScriptTypeString}DataTableName + ']'" + Environment.NewLine; sqlCmdScript += $" SET @{dataScriptTypeString}DataCommand += N'ALTER TABLE [' + @{dataScriptTypeString}DataTableSchema + N'].[' + @{dataScriptTypeString}DataTableName + N'] NOCHECK CONSTRAINT ALL; '" + Environment.NewLine; sqlCmdScript += $" FETCH NEXT FROM {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $" INTO @{dataScriptTypeString}DataTableSchema, @{dataScriptTypeString}DataTableName" + Environment.NewLine; sqlCmdScript += $"END" + Environment.NewLine; sqlCmdScript += $"CLOSE {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $"" + Environment.NewLine; sqlCmdScript += $"EXEC sp_executesql @{dataScriptTypeString}DataCommand" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"-- Variable Declarations" + Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $":SETVAR {dataScriptRootVariable} \"{dataScriptRoot}\"" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"-- {dataScriptTypeString} Data Files" + Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"{scripts}" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"-- ENABLE constraint checks for listed tables" + Environment.NewLine; sqlCmdScript += $"--------------------------------------------------------------------------------------" + Environment.NewLine; sqlCmdScript += $"SET @{dataScriptTypeString}DataCommand = N''" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"OPEN {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $"FETCH NEXT FROM {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $"INTO @{dataScriptTypeString}DataTableSchema, @{dataScriptTypeString}DataTableName" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"WHILE @@FETCH_STATUS = 0" + Environment.NewLine; sqlCmdScript += $"BEGIN" + Environment.NewLine; sqlCmdScript += $" PRINT '[INFO]: Re-enabling Constraints [' + @{dataScriptTypeString}DataTableSchema + '].[' + @{dataScriptTypeString}DataTableName + ']'" + Environment.NewLine; sqlCmdScript += $" SET @{dataScriptTypeString}DataCommand += N'ALTER TABLE [' + @{dataScriptTypeString}DataTableSchema + N'].[' + @{dataScriptTypeString}DataTableName + N'] WITH CHECK CHECK CONSTRAINT ALL; '" + Environment.NewLine; sqlCmdScript += $" FETCH NEXT FROM {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $" INTO @{dataScriptTypeString}DataTableSchema, @{dataScriptTypeString}DataTableName" + Environment.NewLine; sqlCmdScript += $"END" + Environment.NewLine; sqlCmdScript += $"CLOSE {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += $"DEALLOCATE {dataScriptTypeString}DataScriptsCursor" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"EXEC sp_executesql @{dataScriptTypeString}DataCommand" + Environment.NewLine; sqlCmdScript += Environment.NewLine; sqlCmdScript += $"SET NOCOUNT OFF" + Environment.NewLine; return(sqlCmdScript); }
private Dictionary <string, Dictionary <DatabaseEnvironment, DataScript> > EnumerateDataScripts(string databaseProjectDirectoryPath, DataScriptType dataScriptType) { DirectoryInfo dataScriptsDirectoryInfo = new DirectoryInfo(Path.Combine(Path.GetDirectoryName(databaseProjectDirectoryPath), "_Data", dataScriptType.ToString())); Dictionary <string, Dictionary <DatabaseEnvironment, DataScript> > dataScripts = new Dictionary <string, Dictionary <DatabaseEnvironment, DataScript> >(); if (dataScriptsDirectoryInfo.Exists) { FileInfo[] dataScriptFiles = dataScriptsDirectoryInfo.GetFiles("*.sql", SearchOption.AllDirectories); List <string> failedScripts = new List <string>(); foreach (FileInfo staticDataFileInfo in dataScriptFiles) { try { DataScript dataScript = new DataScript { Name = Path.GetFileNameWithoutExtension(staticDataFileInfo.Name), Type = dataScriptType, Environment = this.ParseEnvironment(staticDataFileInfo.Directory.Name), Path = staticDataFileInfo.FullName }; // Check whether scripts with same name already exist if (dataScripts.ContainsKey(dataScript.Name)) { dataScripts[dataScript.Name].Add(dataScript.Environment, dataScript); } else { dataScripts.Add(dataScript.Name, new Dictionary <DatabaseEnvironment, DataScript> { { dataScript.Environment, dataScript } }); } } catch (Exception ex) { failedScripts.Add(staticDataFileInfo.FullName); } } } return(dataScripts); }
public void ExportDataToScripts(string query, string _DataDirectory, DataScriptType dataScriptType, SqlScriptType sqlScriptType, bool includeSoftDependencies, bool excludeUnwantedDependencies, bool autoMerge, string environmentName) { Dictionary <string, ExtractedTable> results = ExportDataFromDatabase(query, includeSoftDependencies, excludeUnwantedDependencies); string dataScriptDirectory = string.Empty; if (dataScriptType == DataScriptType.Static) { dataScriptDirectory = Path.Combine(_DataDirectory, "Static"); } else if (dataScriptType == DataScriptType.Test) { dataScriptDirectory = Path.Combine(_DataDirectory, "Test"); } if (!Directory.Exists(dataScriptDirectory)) { Directory.CreateDirectory(dataScriptDirectory); } // Check for existing script files if autoMerge not set if (!autoMerge) { List <string> existingScripts = new List <string>(); foreach (string tableName in results.Keys) { string scriptName = $"{tableName}.sql"; if (File.Exists(Path.Combine(dataScriptDirectory, scriptName))) { existingScripts.Add(scriptName); } } if (existingScripts.Count > 0) { throw new System.Exception($"{sqlScriptType.ToString()} script already exists for: {string.Join(", ", existingScripts)}. Enable auto merge to merge the new data with the existing data."); } } // Cleanse data DataCleanser dataCleanser = new DataCleanser(); dataCleanser.ReplaceImagePathFields(results.Values.Select(et => et.DataTable).ToList()); // Create scripts for each table foreach (string tableName in results.Keys) { FileInfo scriptFileInfo = new FileInfo(Path.Combine(dataScriptDirectory, $"{tableName}.sql")); // If script already exists, merge if (scriptFileInfo.Exists) { DataTable scriptDataTable = null; if (sqlScriptType == SqlScriptType.Insert) { scriptDataTable = SqlDataScriptHelper.NewDataTableFromInsertScript(scriptFileInfo.FullName, DataParser.GetXmlSchemaFromDataTable(results[tableName].DataTable)); } else if (sqlScriptType == SqlScriptType.Merge) { // todo throw new System.Exception("Not implemented"); } results[tableName].DataTable.Merge(scriptDataTable); } string script = null; if (sqlScriptType == SqlScriptType.Insert) { script = SqlDataScriptHelper.NewInsertScriptFromDataTable(results[tableName].TableName.Schema, results[tableName].TableName.Name, results[tableName].DataTable, results[tableName].PrimaryKeyColumnNames); } else if (sqlScriptType == SqlScriptType.Merge) { script = SqlDataScriptHelper.NewMergeScriptFromDataTable(results[tableName].TableName.Schema, results[tableName].TableName.Name, results[tableName].DataTable, false, true, environmentName, results[tableName].PrimaryKeyColumnNames); } SqlDataScriptHelper.WriteScriptToFile(scriptFileInfo.Name, script, dataScriptDirectory, true); } string sqlCmdScript = SqlDataScriptHelper.NewSqlCmdScript(dataScriptDirectory, dataScriptType); string sqlCmdScriptName = string.Empty; if (dataScriptType == DataScriptType.Static) { sqlCmdScriptName = "StaticData.sql"; } else if (dataScriptType == DataScriptType.Test) { sqlCmdScriptName = "TestData.sql"; } SqlDataScriptHelper.WriteScriptToFile(sqlCmdScriptName, sqlCmdScript, _DataDirectory, true); }