コード例 #1
0
        public List <TableModel> GetProcedures()
        {
            var result = new List <TableModel>();

            if (_databaseType != DatabaseType.SQLServer && _databaseType != DatabaseType.SQLServerDacpac)
            {
                return(result);
            }

            var procedureModelFactory = _serviceProvider.GetService <IProcedureModelFactory>();

            var procedureModelOptions = new ModuleModelFactoryOptions
            {
                FullModel = false,
                Modules   = new List <string>(),
            };

            var procedureModel = procedureModelFactory.Create(_connectionString, procedureModelOptions);

            foreach (var procedure in procedureModel.Routines)
            {
                result.Add(new TableModel(procedure.Name, procedure.Schema, _databaseType, ObjectType.Procedure, null));
            }

            return(result.OrderBy(c => c.DisplayName).ToList());
        }
コード例 #2
0
        public List <TableModel> GetFunctions()
        {
            var result = new List <TableModel>();

            if (_databaseType != DatabaseType.SQLServer)
            {
                return(result);
            }

            var functionModelFactory = _serviceProvider.GetService <IFunctionModelFactory>();

            var functionModelOptions = new ModuleModelFactoryOptions
            {
                FullModel = false,
                Modules   = new List <string>(),
            };

            var functionModel = functionModelFactory.Create(_connectionString, functionModelOptions);

            foreach (var function in functionModel.Routines)
            {
                result.Add(new TableModel(function.Name, function.Schema, _databaseType, ObjectType.ScalarFunction, null));
            }

            return(result.OrderBy(c => c.DisplayName).ToList());
        }
コード例 #3
0
        private ProcedureModel GetStoredProcedures(string dacpacPath, ModuleModelFactoryOptions options)
        {
            var result = new List <RevEng.Core.Abstractions.Metadata.Procedure>();
            var errors = new List <string>();

            if (options.FullModel && !options.Modules.Any())
            {
                return(new ProcedureModel
                {
                    Procedures = result,
                    Errors = errors,
                });
            }

            var consolidator = new DacpacConsolidator();

            dacpacPath = consolidator.Consolidate(dacpacPath);

            var model = new TSqlTypedModel(dacpacPath);

            var procedures = model.GetObjects <TSqlProcedure>(DacQueryScopes.UserDefined)
                             .ToList();

            var filter = new HashSet <string>(options.Modules);

            foreach (var proc in procedures)
            {
                var procedure = new RevEng.Core.Abstractions.Metadata.Procedure
                {
                    Schema = proc.Name.Parts[0],
                    Name   = proc.Name.Parts[1],
                };

                if (filter.Count == 0 || filter.Contains($"[{procedure.Schema}].[{procedure.Name}]"))
                {
                    if (options.FullModel)
                    {
                        procedure.Parameters = GetStoredProcedureParameters(proc);

                        try
                        {
                            procedure.ResultElements = GetStoredProcedureResultElements(proc);
                        }
                        catch (Exception ex)
                        {
                            errors.Add($"Unable to get result set shape for {procedure.Schema}.{procedure.Name}" + Environment.NewLine + ex.Message);
                            _logger?.Logger.LogWarning(ex, $"Unable to get result set shape for {procedure.Schema}.{procedure.Name}" + Environment.NewLine + ex.Message);
                        }
                    }

                    result.Add(procedure);
                }
            }

            return(new ProcedureModel
            {
                Procedures = result,
                Errors = errors,
            });
        }
コード例 #4
0
        private FunctionModel GetFunctions(string connectionString, ModuleModelFactoryOptions options)
        {
            var result = new List <Function>();
            var found  = new List <Tuple <string, string, int> >();
            var errors = new List <string>();

            var filter = options.Modules.ToHashSet();

            using (var connection = new SqlConnection(connectionString))
            {
                var sql = $@"
SELECT SCHEMA_NAME(schema_id) AS [Schema], name AS [Name], object_id
FROM sys.objects 
WHERE objectproperty(OBJECT_ID,'IsScalarFunction') = 1
AND NULLIF([name], '') IS NOT NULL;";

                using (var command = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            found.Add(new Tuple <string, string, int>(reader.GetString(0), reader.GetString(1), reader.GetInt32(2)));
                        }
                    }
                }

                foreach (var foundFunction in found)
                {
                    if (filter.Count == 0 || filter.Contains($"[{foundFunction.Item1}].[{foundFunction.Item2}]"))
                    {
                        var function = new Function
                        {
                            Schema   = foundFunction.Item1,
                            Name     = foundFunction.Item2,
                            IsScalar = true,
                        };

                        if (options.FullModel)
                        {
                            function.Parameters = GetFunctionParameters(connection, foundFunction.Item3);
                        }

                        result.Add(function);
                    }
                }
            }

            return(new FunctionModel
            {
                Functions = result,
                Errors = errors,
            });
        }
コード例 #5
0
        public SavedModelFiles GenerateFunctions(
            ReverseEngineerCommandOptions options,
            ref List <string> errors,
            string outputContextDir,
            string modelNamespace,
            string contextNamespace,
            bool supportsFunctions)
        {
            if (options == null)
            {
                throw new ArgumentNullException(nameof(options));
            }

            var functionModelScaffolder = functionScaffolder;

            if (functionModelScaffolder != null &&
                supportsFunctions &&
                (options.Tables.Any(t => t.ObjectType == ObjectType.ScalarFunction) ||
                 !options.Tables.Any()))
            {
                var modelFactoryOptions = new ModuleModelFactoryOptions
                {
                    FullModel = true,
                    Modules   = options.Tables.Where(t => t.ObjectType == ObjectType.ScalarFunction).Select(m => m.Name),
                };

                var functionModel = functionModelFactory.Create(options.Dacpac ?? options.ConnectionString, modelFactoryOptions);

                ApplyRenamers(functionModel.Routines, options.CustomReplacers);

                var functionOptions = new ModuleScaffolderOptions
                {
                    ContextDir         = outputContextDir,
                    ContextName        = options.ContextClassName,
                    ContextNamespace   = contextNamespace,
                    ModelNamespace     = modelNamespace,
                    NullableReferences = options.UseNullableReferences,
                    UseAsyncCalls      = options.UseAsyncCalls,
                };

                var functionScaffoldedModel = functionModelScaffolder.ScaffoldModel(functionModel, functionOptions, ref errors);

                if (functionScaffoldedModel != null)
                {
                    return(functionModelScaffolder.Save(
                               functionScaffoldedModel,
                               Path.GetFullPath(Path.Combine(options.ProjectPath, options.OutputPath ?? string.Empty)),
                               contextNamespace,
                               options.UseAsyncCalls));
                }
            }

            return(null);
        }
コード例 #6
0
        public RoutineModel Create(string dacpacPath, ModuleModelFactoryOptions options)
        {
            if (string.IsNullOrEmpty(dacpacPath))
            {
                throw new ArgumentException(@"invalid path", nameof(dacpacPath));
            }
            if (!File.Exists(dacpacPath))
            {
                throw new ArgumentException($"Dacpac file not found: {dacpacPath}");
            }

            return(GetStoredProcedures(dacpacPath, options));
        }
コード例 #7
0
        public static SavedModelFiles GenerateStoredProcedures(
            ReverseEngineerCommandOptions options,
            ref List <string> errors,
            ServiceProvider serviceProvider,
            string outputContextDir,
            string modelNamespace,
            string contextNamespace)
        {
            var procedureModelScaffolder = serviceProvider.GetService <IProcedureScaffolder>();

            if (procedureModelScaffolder != null &&
                (options.Tables.Any(t => t.ObjectType == ObjectType.Procedure) ||
                 !options.Tables.Any()))
            {
                var procedureModelFactory = serviceProvider.GetService <IProcedureModelFactory>();

                var procedureModelFactoryOptions = new ModuleModelFactoryOptions
                {
                    DiscoverMultipleResultSets = options.UseMultipleSprocResultSets,
                    FullModel = true,
                    Modules   = options.Tables.Where(t => t.ObjectType == ObjectType.Procedure).Select(m => m.Name),
                };

                var procedureModel = procedureModelFactory.Create(options.Dacpac ?? options.ConnectionString, procedureModelFactoryOptions);

                ApplyRenamers(procedureModel.Routines, options.CustomReplacers);

                var procedureOptions = new ModuleScaffolderOptions
                {
                    ContextDir         = outputContextDir,
                    ContextName        = options.ContextClassName,
                    ContextNamespace   = contextNamespace,
                    ModelNamespace     = modelNamespace,
                    NullableReferences = options.UseNullableReferences,
                    UseSchemaFolders   = options.UseSchemaFolders,
                };

                var procedureScaffoldedModel = procedureModelScaffolder.ScaffoldModel(procedureModel, procedureOptions, ref errors);

                if (procedureScaffoldedModel != null)
                {
                    return(procedureModelScaffolder.Save(
                               procedureScaffoldedModel,
                               Path.GetFullPath(Path.Combine(options.ProjectPath, options.OutputPath ?? string.Empty)),
                               contextNamespace));
                }
            }

            return(null);
        }
コード例 #8
0
        public static SavedModelFiles GenerateFunctions(
            ReverseEngineerCommandOptions options,
            ref List <string> errors,
            ServiceProvider serviceProvider,
            string outputContextDir,
            string modelNamespace,
            string contextNamespace)
        {
            var functionModelScaffolder = serviceProvider.GetService <IFunctionScaffolder>();

            if (functionModelScaffolder != null &&
                (options.Tables.Any(t => t.ObjectType == ObjectType.ScalarFunction) ||
                 !options.Tables.Any()))
            {
                var functionModelFactory = serviceProvider.GetService <IFunctionModelFactory>();

                var modelFactoryOptions = new ModuleModelFactoryOptions
                {
                    FullModel = true,
                    Modules   = options.Tables.Where(t => t.ObjectType == ObjectType.ScalarFunction).Select(m => m.Name),
                };

                var functionModel = functionModelFactory.Create(options.Dacpac ?? options.ConnectionString, modelFactoryOptions);

                ApplyRenamers(functionModel.Functions, options.CustomReplacers);

                var functionOptions = new ModuleScaffolderOptions
                {
                    ContextDir         = outputContextDir,
                    ContextName        = options.ContextClassName,
                    ContextNamespace   = contextNamespace,
                    ModelNamespace     = modelNamespace,
                    NullableReferences = options.UseNullableReferences,
                };

                var functionScaffoldedModel = functionModelScaffolder.ScaffoldModel(functionModel, functionOptions, ref errors);

                if (functionScaffoldedModel != null)
                {
                    return(functionModelScaffolder.Save(
                               functionScaffoldedModel,
                               Path.GetFullPath(Path.Combine(options.ProjectPath, options.OutputPath ?? string.Empty)),
                               contextNamespace));
                }
            }

            return(null);
        }
        public RoutineModel Create(string connectionString, ModuleModelFactoryOptions options)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentException(@"invalid path", nameof(connectionString));
            }

            if (!File.Exists(connectionString))
            {
                throw new ArgumentException($"Dacpac file not found: {connectionString}");
            }

            if (options is null)
            {
                throw new ArgumentNullException(nameof(options));
            }

            return(GetStoredProcedures(connectionString, options, dacpacOptions.MergeDacpacs));
        }
コード例 #10
0
#pragma warning restore CA1716 // Identifiers should not match keywords

        protected RoutineModel GetRoutines(string connectionString, ModuleModelFactoryOptions options)
        {
            if (options is null)
            {
                throw new ArgumentNullException(nameof(options));
            }

            var result = new List <Routine>();
            var found  = new List <Tuple <string, string, bool> >();
            var errors = new List <string>();

            var filter = options.Modules.ToHashSet();

            using (var connection = new SqlConnection(connectionString))
            {
                var sql = new StringBuilder();
#pragma warning disable CA1305 // Specify IFormatProvider
                sql.AppendLine($@"
SELECT
    ROUTINE_SCHEMA,
    ROUTINE_NAME,
    CAST(CASE WHEN (ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE != 'TABLE') THEN 1 ELSE 0 END AS bit) AS IS_SCALAR
FROM INFORMATION_SCHEMA.ROUTINES
WHERE NULLIF(ROUTINE_NAME, '') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
AND (
            select 
                major_id 
            from 
                sys.extended_properties 
            where 
                major_id = object_id(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)) and 
                minor_id = 0 and 
                class = 1 and 
                name = N'microsoft_database_tools_support'
        ) IS NULL 
AND ROUTINE_TYPE = N'{RoutineType}'");
#pragma warning restore CA1305 // Specify IFormatProvider

#if !CORE50 && !CORE60
                // Filters out table-valued functions without filtering out stored procedures
                sql.AppendLine("AND COALESCE(DATA_TYPE, '') != 'TABLE'");
#endif

                sql.AppendLine("ORDER BY ROUTINE_NAME;");

#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
                using (var command = new SqlCommand(sql.ToString(), connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // Schema, Name, IsScalar
                            found.Add(new Tuple <string, string, bool>(reader.GetString(0), reader.GetString(1), reader.GetBoolean(2)));
                        }
                    }
                }
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities

                var allParameters = options.FullModel ? GetParameters(connection) : new Dictionary <string, List <ModuleParameter> >();

                foreach (var foundModule in found)
                {
                    var key = $"[{foundModule.Item1}].[{foundModule.Item2}]";

                    if (filter.Count == 0 || filter.Contains(key))
                    {
                        var isScalar = foundModule.Item3;

                        var module = RoutineType == "PROCEDURE"
                            ? (Routine) new Procedure()
                            : new Function {
                            IsScalar = isScalar
                        };

                        module.Schema = foundModule.Item1;
                        module.Name   = foundModule.Item2;

                        if (options.FullModel)
                        {
                            module.HasValidResultSet = true;

                            if (options.MappedModules?.ContainsKey(key) ?? false)
                            {
                                module.MappedType = options.MappedModules[key];
                            }

                            if (allParameters.TryGetValue($"[{module.Schema}].[{module.Name}]", out var moduleParameters))
                            {
                                module.Parameters = moduleParameters;
                            }

                            if (RoutineType == "PROCEDURE")
                            {
                                module.Parameters.Add(GetReturnParameter());
                            }

                            if (!isScalar)
                            {
#pragma warning disable CA1031 // Do not catch general exception types
                                try
                                {
                                    var forceLegacy = options.UseLegacyResultSetDiscovery;
                                    if (!forceLegacy)
                                    {
                                        forceLegacy = options.ModulesUsingLegacyDiscovery?.Contains($"[{module.Schema}].[{module.Name}]") ?? false;
                                    }

                                    module.Results.AddRange(GetResultElementLists(connection, module, options.DiscoverMultipleResultSets, forceLegacy));
                                }
                                catch (Exception ex)
                                {
                                    module.HasValidResultSet = false;
                                    module.Results           = new List <List <ModuleResultElement> >
                                    {
                                        new List <ModuleResultElement>(),
                                    };
                                    errors.Add($"Unable to get result set shape for {RoutineType} '{module.Schema}.{module.Name}'.{Environment.NewLine}{ex.Message}{Environment.NewLine}");
                                }
#pragma warning restore CA1031 // Do not catch general exception types
                            }

                            if (module is Function func &&
                                func.IsScalar &&
                                func.Parameters.Count > 0 &&
                                func.Parameters.Any(p => p.StoreType == "table type"))
                            {
                                errors.Add($"Unable to scaffold {RoutineType} '{module.Schema}.{module.Name}' as it has TVP parameters.{Environment.NewLine}");
                                continue;
                            }

                            bool dupesFound = false;
                            foreach (var resultElement in module.Results)
                            {
                                var duplicates = resultElement.GroupBy(x => x.Name)
                                                 .Where(g => g.Count() > 1)
                                                 .Select(y => y.Key)
                                                 .ToList();

                                if (duplicates.Any())
                                {
                                    dupesFound = true;
                                    errors.Add($"Unable to scaffold {RoutineType} '{module.Schema}.{module.Name}' as it has duplicate result column names: '{duplicates[0]}'.{Environment.NewLine}");
                                }
                            }

                            if (dupesFound)
                            {
                                continue;
                            }
                        }

                        result.Add(module);
                    }
                }
            }

            return(new RoutineModel
            {
                Routines = result,
                Errors = errors,
            });
        }
コード例 #11
0
 public RoutineModel Create(string connectionString, ModuleModelFactoryOptions options)
 {
     return(GetRoutines(connectionString, options));
 }
コード例 #12
0
 public RoutineModel Create(string connectionString, ModuleModelFactoryOptions options)
 {
     throw new NotSupportedException();
 }
コード例 #13
0
 public FunctionModel Create(string connectionString, ModuleModelFactoryOptions options)
 {
     return(GetFunctions(connectionString, options));
 }
        private static RoutineModel GetStoredProcedures(string dacpacPath, ModuleModelFactoryOptions options, bool mergeDacpacs)
        {
            var result = new List <RevEng.Core.Abstractions.Metadata.Procedure>();
            var errors = new List <string>();

            if (mergeDacpacs && dacpacPath != null)
            {
                var consolidator = new DacpacConsolidator();
                dacpacPath = consolidator.Consolidate(dacpacPath);
            }

            using var model = new TSqlTypedModel(dacpacPath);

            var procedures = model.GetObjects <TSqlProcedure>(DacQueryScopes.UserDefined)
                             .ToList();

            var filter = new HashSet <string>(options.Modules);

            foreach (var proc in procedures)
            {
                var procedure = new RevEng.Core.Abstractions.Metadata.Procedure
                {
                    Schema = proc.Name.Parts[0],
                    Name   = proc.Name.Parts[1],
                };

                var key = $"[{procedure.Schema}].[{procedure.Name}]";

                if (filter.Count == 0 || filter.Contains(key))
                {
                    if (options.FullModel)
                    {
                        procedure.Parameters = GetStoredProcedureParameters(proc);

                        if (options.MappedModules?.ContainsKey(key) ?? false)
                        {
                            procedure.MappedType = options.MappedModules[key];
                        }

#pragma warning disable CA1031 // Do not catch general exception types
                        try
                        {
                            procedure.Results.Add(GetStoredProcedureResultElements(proc));
                        }
                        catch (Exception ex)
                        {
                            errors.Add($"Unable to get result set shape for {procedure.Schema}.{procedure.Name}" + Environment.NewLine + ex.ToString());
                        }
#pragma warning restore CA1031 // Do not catch general exception types
                    }

                    result.Add(procedure);
                }
            }

            return(new RoutineModel
            {
                Routines = result.Cast <Routine>().ToList(),
                Errors = errors,
            });
        }
コード例 #15
0
        public SavedModelFiles GenerateStoredProcedures(
            ReverseEngineerCommandOptions options,
            ref List <string> errors,
            string outputContextDir,
            string modelNamespace,
            string contextNamespace,
            bool supportsProcedures)
        {
            if (options == null)
            {
                throw new ArgumentNullException(nameof(options));
            }

            var procedureModelScaffolder = procedureModelFactory;

            if (procedureModelScaffolder != null &&
                supportsProcedures &&
                (options.Tables.Any(t => t.ObjectType == ObjectType.Procedure) ||
                 !options.Tables.Any()))
            {
                var procedureModelFactoryOptions = new ModuleModelFactoryOptions
                {
                    DiscoverMultipleResultSets  = options.UseMultipleSprocResultSets,
                    UseLegacyResultSetDiscovery = options.UseLegacyResultSetDiscovery && !options.UseMultipleSprocResultSets,
                    FullModel = true,
                    Modules   = options.Tables.Where(t => t.ObjectType == ObjectType.Procedure).Select(m => m.Name),
                    ModulesUsingLegacyDiscovery = options.Tables
                                                  .Where(t => t.ObjectType == ObjectType.Procedure && t.UseLegacyResultSetDiscovery)
                                                  .Select(m => m.Name),
                    MappedModules = options.Tables
                                    .Where(t => t.ObjectType == ObjectType.Procedure && !string.IsNullOrEmpty(t.MappedType))
                                    .Select(m => new { m.Name, m.MappedType })
                                    .ToDictionary(m => m.Name, m => m.MappedType),
                };

                var procedureModel = procedureModelFactory.Create(options.Dacpac ?? options.ConnectionString, procedureModelFactoryOptions);

                ApplyRenamers(procedureModel.Routines, options.CustomReplacers);

                var procedureOptions = new ModuleScaffolderOptions
                {
                    ContextDir         = outputContextDir,
                    ContextName        = options.ContextClassName,
                    ContextNamespace   = contextNamespace,
                    ModelNamespace     = modelNamespace,
                    NullableReferences = options.UseNullableReferences,
                    UseSchemaFolders   = options.UseSchemaFolders,
                    UseAsyncCalls      = options.UseAsyncCalls,
                };

                var procedureScaffoldedModel = procedureScaffolder.ScaffoldModel(procedureModel, procedureOptions, ref errors);

                if (procedureScaffoldedModel != null)
                {
                    return(procedureScaffolder.Save(
                               procedureScaffoldedModel,
                               Path.GetFullPath(Path.Combine(options.ProjectPath, options.OutputPath ?? string.Empty)),
                               contextNamespace,
                               options.UseAsyncCalls));
                }
            }

            return(null);
        }
コード例 #16
0
        public static ReverseEngineerResult GenerateFiles(ReverseEngineerCommandOptions reverseEngineerOptions)
        {
            var errors   = new List <string>();
            var warnings = new List <string>();
            var reporter = new OperationReporter(
                new OperationReportHandler(
                    m => errors.Add(m),
                    m => warnings.Add(m)));
            var serviceProvider = ServiceProviderBuilder.Build(reverseEngineerOptions);
            var scaffolder      = serviceProvider.GetService <IReverseEngineerScaffolder>();
            var schemas         = new List <string>();

            reverseEngineerOptions.ConnectionString = SqlServerHelper.SetConnectionString(reverseEngineerOptions.DatabaseType, reverseEngineerOptions.ConnectionString);

            if (reverseEngineerOptions.DefaultDacpacSchema != null)
            {
                schemas.Add(reverseEngineerOptions.DefaultDacpacSchema);
            }

            if (reverseEngineerOptions.FilterSchemas)
            {
                schemas.AddRange(reverseEngineerOptions.Schemas.Select(s => s.Name));
            }

            var outputDir = !string.IsNullOrEmpty(reverseEngineerOptions.OutputPath)
               ? Path.IsPathFullyQualified(reverseEngineerOptions.OutputPath)
                    ? reverseEngineerOptions.OutputPath
                    : Path.GetFullPath(Path.Combine(reverseEngineerOptions.ProjectPath, reverseEngineerOptions.OutputPath))
                : reverseEngineerOptions.ProjectPath;

            var outputContextDir = !string.IsNullOrEmpty(reverseEngineerOptions.OutputContextPath)
               ? Path.IsPathFullyQualified(reverseEngineerOptions.OutputContextPath)
                    ? reverseEngineerOptions.OutputContextPath
                    : Path.GetFullPath(Path.Combine(reverseEngineerOptions.ProjectPath, reverseEngineerOptions.OutputContextPath))
                : outputDir;

            var modelNamespace = !string.IsNullOrEmpty(reverseEngineerOptions.ModelNamespace)
                ? reverseEngineerOptions.ProjectRootNamespace + "." + reverseEngineerOptions.ModelNamespace
                : PathHelper.GetNamespaceFromOutputPath(outputDir, reverseEngineerOptions.ProjectPath, reverseEngineerOptions.ProjectRootNamespace);

            var contextNamespace = !string.IsNullOrEmpty(reverseEngineerOptions.ContextNamespace)
                ? reverseEngineerOptions.ProjectRootNamespace + "." + reverseEngineerOptions.ContextNamespace
                : PathHelper.GetNamespaceFromOutputPath(outputContextDir, reverseEngineerOptions.ProjectPath, reverseEngineerOptions.ProjectRootNamespace);

            SavedModelFiles procedurePaths           = null;
            var             procedureModelScaffolder = serviceProvider.GetService <IProcedureScaffolder>();

            if (procedureModelScaffolder != null &&
                reverseEngineerOptions.Tables.Any(t => t.ObjectType == ObjectType.Procedure))
            {
                var procedureModelFactory = serviceProvider.GetService <IProcedureModelFactory>();

                var procedureModelFactoryOptions = new ModuleModelFactoryOptions
                {
                    FullModel = true,
                    Modules   = reverseEngineerOptions.Tables.Where(t => t.ObjectType == ObjectType.Procedure).Select(m => m.Name),
                };

                var procedureModel = procedureModelFactory.Create(reverseEngineerOptions.Dacpac ?? reverseEngineerOptions.ConnectionString, procedureModelFactoryOptions);

                var procedureOptions = new ModuleScaffolderOptions
                {
                    ContextDir         = outputContextDir,
                    ContextName        = reverseEngineerOptions.ContextClassName,
                    ContextNamespace   = contextNamespace,
                    ModelNamespace     = modelNamespace,
                    NullableReferences = reverseEngineerOptions.UseNullableReferences,
                };

                var procedureScaffoldedModel = procedureModelScaffolder.ScaffoldModel(procedureModel, procedureOptions, ref errors);

                if (procedureScaffoldedModel != null)
                {
                    procedurePaths = procedureModelScaffolder.Save(
                        procedureScaffoldedModel,
                        Path.GetFullPath(Path.Combine(reverseEngineerOptions.ProjectPath, reverseEngineerOptions.OutputPath ?? string.Empty)),
                        contextNamespace);
                }
            }

            SavedModelFiles functionPaths           = null;
            var             functionModelScaffolder = serviceProvider.GetService <IFunctionScaffolder>();

            if (functionModelScaffolder != null &&
                reverseEngineerOptions.Tables.Any(t => t.ObjectType == ObjectType.ScalarFunction))
            {
                var functionModelFactory = serviceProvider.GetService <IFunctionModelFactory>();

                var modelFactoryOptions = new ModuleModelFactoryOptions
                {
                    FullModel = true,
                    Modules   = reverseEngineerOptions.Tables.Where(t => t.ObjectType == ObjectType.ScalarFunction).Select(m => m.Name),
                };

                var functionModel = functionModelFactory.Create(reverseEngineerOptions.Dacpac ?? reverseEngineerOptions.ConnectionString, modelFactoryOptions);

                var functionOptions = new ModuleScaffolderOptions
                {
                    ContextDir         = outputContextDir,
                    ContextName        = reverseEngineerOptions.ContextClassName,
                    ContextNamespace   = contextNamespace,
                    ModelNamespace     = modelNamespace,
                    NullableReferences = reverseEngineerOptions.UseNullableReferences,
                };

                var functionScaffoldedModel = functionModelScaffolder.ScaffoldModel(functionModel, functionOptions, ref errors);

                if (functionScaffoldedModel != null)
                {
                    functionPaths = functionModelScaffolder.Save(
                        functionScaffoldedModel,
                        Path.GetFullPath(Path.Combine(reverseEngineerOptions.ProjectPath, reverseEngineerOptions.OutputPath ?? string.Empty)),
                        contextNamespace);
                }
            }

            var modelOptions = new ModelReverseEngineerOptions
            {
                UseDatabaseNames = reverseEngineerOptions.UseDatabaseNames,
#if CORE50
                NoPluralize = !reverseEngineerOptions.UseInflector,
#endif
            };

            var codeOptions = new ModelCodeGenerationOptions
            {
                UseDataAnnotations = !reverseEngineerOptions.UseFluentApiOnly,
                Language           = "C#",
                ContextName        = reverseEngineerOptions.ContextClassName,
                ContextDir         = outputContextDir,
                RootNamespace      = null,
                ContextNamespace   = contextNamespace,
                ModelNamespace     = modelNamespace,
                SuppressConnectionStringWarning = false,
                ConnectionString = reverseEngineerOptions.ConnectionString,
#if CORE50
                SuppressOnConfiguring = !reverseEngineerOptions.IncludeConnectionString,
#endif
            };

            var dbOptions = new DatabaseModelFactoryOptions(reverseEngineerOptions.Tables.Where(t => t.ObjectType.HasColumns()).Select(m => m.Name), schemas);

            var scaffoldedModel = ScaffoldModel(
                reverseEngineerOptions.Dacpac ?? reverseEngineerOptions.ConnectionString,
                dbOptions,
                modelOptions,
                codeOptions,
                reverseEngineerOptions.UseBoolPropertiesWithoutDefaultSql,
                reverseEngineerOptions.UseNoNavigations,
                serviceProvider);

            var filePaths = scaffolder.Save(
                scaffoldedModel,
                Path.GetFullPath(Path.Combine(reverseEngineerOptions.ProjectPath, reverseEngineerOptions.OutputPath ?? string.Empty)),
                overwriteFiles: true);

#if CORE50
#else
            RemoveOnConfiguring(filePaths.ContextFile, reverseEngineerOptions.IncludeConnectionString);
#endif
            foreach (var file in filePaths.AdditionalFiles)
            {
                PostProcess(file);
            }

            PostProcess(filePaths.ContextFile);

            var entityTypeConfigurationPaths = SplitDbContext(filePaths.ContextFile, reverseEngineerOptions.UseDbContextSplitting, contextNamespace);

            var cleanUpPaths = new SavedModelFiles(filePaths.ContextFile, filePaths.AdditionalFiles);
            if (procedurePaths != null)
            {
                cleanUpPaths.AdditionalFiles.Add(procedurePaths.ContextFile);
                foreach (var additionalFile in procedurePaths.AdditionalFiles)
                {
                    cleanUpPaths.AdditionalFiles.Add(additionalFile);
                }
            }
            if (functionPaths != null)
            {
                cleanUpPaths.AdditionalFiles.Add(functionPaths.ContextFile);
            }

            CleanUp(cleanUpPaths, entityTypeConfigurationPaths);

            var result = new ReverseEngineerResult
            {
                EntityErrors                  = errors,
                EntityWarnings                = warnings,
                EntityTypeFilePaths           = filePaths.AdditionalFiles,
                ContextFilePath               = filePaths.ContextFile,
                ContextConfigurationFilePaths = entityTypeConfigurationPaths,
            };

            return(result);
        }
コード例 #17
0
        protected RoutineModel GetRoutines(string connectionString, ModuleModelFactoryOptions options)
        {
            var routineType = this switch
            {
                SqlServerStoredProcedureModelFactory _ => "PROCEDURE",
                SqlServerFunctionModelFactory _ => "FUNCTION",
                _ => throw new InvalidOperationException($"Unknown type '{GetType().Name}'"),
            };

            var result = new List <Routine>();
            var found  = new List <Tuple <string, string, string, bool> >();
            var errors = new List <string>();

            var filter = options.Modules.ToHashSet();

            using (var connection = new SqlConnection(connectionString))
            {
                var sql = new StringBuilder();
                sql.AppendLine($@"
SELECT
    ROUTINE_SCHEMA,
    ROUTINE_NAME,
    ROUTINE_TYPE,
    CAST(CASE WHEN (ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE != 'TABLE') THEN 1 ELSE 0 END AS bit) AS IS_SCALAR
FROM INFORMATION_SCHEMA.ROUTINES
WHERE NULLIF(ROUTINE_NAME, '') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
AND (
            select 
                major_id 
            from 
                sys.extended_properties 
            where 
                major_id = object_id(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)) and 
                minor_id = 0 and 
                class = 1 and 
                name = N'microsoft_database_tools_support'
        ) IS NULL 
AND ROUTINE_TYPE = N'{routineType}'");

#if !CORE50 && !CORE60
                // Filters out table-valued functions without filtering out stored procedures
                sql.AppendLine("AND COALESCE(DATA_TYPE, '') != 'TABLE'");
#endif

                sql.AppendLine("ORDER BY ROUTINE_NAME;");

                using (var command = new SqlCommand(sql.ToString(), connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // Schema, Name, Type, IsScalar
                            found.Add(new Tuple <string, string, string, bool>(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetBoolean(3)));
                        }
                    }
                }

                foreach (var foundModule in found)
                {
                    if (filter.Count == 0 || filter.Contains($"[{foundModule.Item1}].[{foundModule.Item2}]"))
                    {
                        var isScalar = foundModule.Item4;

                        var module = routineType == "procedure"
                            ? (Routine) new Procedure()
                            : new Function {
                            IsScalar = isScalar
                        };

                        module.Schema            = foundModule.Item1;
                        module.Name              = foundModule.Item2;
                        module.HasValidResultSet = true;

                        if (options.FullModel)
                        {
                            module.Parameters = GetParameters(connection, module.Schema, module.Name);

                            if (!isScalar)
                            {
                                try
                                {
                                    module.Results.AddRange(GetResultElementLists(connection, module, options.DiscoverMultipleResultSets));
                                }
                                catch (Exception ex)
                                {
                                    module.HasValidResultSet = false;
                                    errors.Add($"Unable to get result set shape for {routineType} '{module.Schema}.{module.Name}'{Environment.NewLine}{ex.Message}{Environment.NewLine}");
                                    _logger?.Logger.LogWarning(ex, $"Unable to scaffold {module.Schema}.{module.Name}");
                                }
                            }
                        }

                        result.Add(module);
                    }
                }
            }

            return(new RoutineModel
            {
                Routines = result,
                Errors = errors,
            });
        }
        private ProcedureModel GetStoredProcedures(string connectionString, ModuleModelFactoryOptions options)
        {
            var result = new List <Procedure>();
            var found  = new List <Tuple <string, string> >();
            var errors = new List <string>();

            if (options.FullModel && !options.Modules.Any())
            {
                return(new ProcedureModel
                {
                    Procedures = result,
                    Errors = errors,
                });
            }

            var filter = options.Modules.ToHashSet();

            using (var connection = new SqlConnection(connectionString))
            {
                var sql = $@"
SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;";

                using (var command = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            found.Add(new Tuple <string, string>(reader.GetString(0), reader.GetString(1)));
                        }
                    }
                }

                foreach (var foundProcedure in found)
                {
                    if (filter.Count == 0 || filter.Contains($"[{foundProcedure.Item1}].[{foundProcedure.Item2}]"))
                    {
                        var procedure = new Procedure
                        {
                            Schema            = foundProcedure.Item1,
                            Name              = foundProcedure.Item2,
                            HasValidResultSet = true,
                        };

                        if (options.FullModel)
                        {
                            procedure.Parameters = GetStoredProcedureParameters(connection, procedure.Schema, procedure.Name);
                            try
                            {
                                procedure.ResultElements = GetStoredProcedureResultElements(connection, procedure.Schema, procedure.Name);
                            }
                            catch (Exception ex)
                            {
                                procedure.HasValidResultSet = false;
                                errors.Add($"Unable to get result set shape for procedure '{procedure.Schema}.{procedure.Name}'{Environment.NewLine}{ex.Message}{Environment.NewLine}");
                                _logger?.Logger.LogWarning(ex, $"Unable to scaffold {procedure.Schema}.{procedure.Name}");
                            }
                        }

                        result.Add(procedure);
                    }
                }
            }

            return(new ProcedureModel
            {
                Procedures = result,
                Errors = errors,
            });
        }
 public ProcedureModel Create(string connectionString, ModuleModelFactoryOptions options)
 {
     return(GetStoredProcedures(connectionString, options));
 }