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());
}
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());
}
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,
});
}
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,
});
}
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);
}
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));
}
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);
}
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));
}
#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,
});
}
public RoutineModel Create(string connectionString, ModuleModelFactoryOptions options)
{
return(GetRoutines(connectionString, options));
}
public RoutineModel Create(string connectionString, ModuleModelFactoryOptions options)
{
throw new NotSupportedException();
}
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,
});
}
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);
}
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);
}
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));
}