Esempio n. 1
0
        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);
        }
Esempio n. 3
0
        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);
        }