private ProcedureModel GetStoredProcedures(string dacpacPath, ProcedureModelFactoryOptions options)
{
var result = new List <RevEng.Core.Abstractions.Metadata.Procedure>();
var errors = new List <string>();
if (options.FullModel && !options.Procedures.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.Procedures);
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,
});
}
public List <Tuple <string, bool, List <string> > > GetTableDefinitions()
{
var consolidator = new DacpacConsolidator();
var dacpacPath = consolidator.Consolidate(_dacpacPath);
using (var model = new TSqlTypedModel(dacpacPath))
{
var result = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
.Select(m => new Tuple <string, bool, List <string> >($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]",
m.PrimaryKeyConstraints.Any(),
m.Columns.Where(i => !i.GetProperty <bool>(Column.IsHidden) &&
i.ColumnType != ColumnType.ColumnSet &&
i.ColumnType != ColumnType.ComputedColumn)
.Select(c => c.Name.Parts[2])
.ToList()))
.OrderBy(m => m.Item1)
.ToList();
var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
.Select(m => new Tuple <string, bool, List <string> >($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", false, null))
.OrderBy(m => m.Item1)
.ToList();
result = result.Concat(views).ToList();
return(result);
}
}
public void CanConsolidate_Issue_274()
{
// Arrange
var consolidator = new DacpacConsolidator();
// Act
var result = consolidator.Consolidate(@"C:\Users\Erik\Downloads\CompositeDatabase\CompositeDatabase\CompositeDatabase\bin\Debug\CompositeDatabase.dacpac");
// Assert
Assert.IsTrue(result.Contains("\\efpt-"));
}
public void CanConsolidate_Without_References_Issue_274()
{
// Arrange
var consolidator = new DacpacConsolidator();
var dacPath = TestPath("Chinook.dacpac");
// Act
var result = consolidator.Consolidate(dacPath);
// Assert
Assert.AreEqual(result, dacPath);
}
public List <Tuple <string, bool> > GetTableDefinitions()
{
var consolidator = new DacpacConsolidator();
var dacpacPath = consolidator.Consolidate(_dacpacPath);
using (var model = new TSqlTypedModel(dacpacPath))
{
var result = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
.Select(m => new Tuple <string, bool>($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", m.PrimaryKeyConstraints.Any()))
.OrderBy(m => m.Item1)
.ToList();
var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
.Select(m => new Tuple <string, bool>($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", false))
.OrderBy(m => m.Item1)
.ToList();
result = result.Concat(views).ToList();
return(result);
}
}
public DatabaseModel Create(string dacpacPath, DatabaseModelFactoryOptions options)
{
if (string.IsNullOrEmpty(dacpacPath))
{
throw new ArgumentException(@"invalid path", nameof(dacpacPath));
}
if (!File.Exists(dacpacPath))
{
throw new ArgumentException("Dacpac file not found");
}
var schemas = options.Schemas;
var tables = options.Tables;
var dbModel = new DatabaseModel
{
DatabaseName = Path.GetFileNameWithoutExtension(dacpacPath),
DefaultSchema = schemas.Count() > 0 ? schemas.First() : "dbo"
};
dbModel["Scaffolding:ConnectionString"] = $"Data Source=(local);Initial Catalog={dbModel.DatabaseName};Integrated Security=true";
//Sequences not created - not needed for scaffolding
var consolidator = new DacpacConsolidator();
dacpacPath = consolidator.Consolidate(dacpacPath);
var model = new TSqlTypedModel(dacpacPath);
var typeAliases = GetTypeAliases(model, dbModel);
var items = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
.Where(t => !t.GetProperty <bool>(Table.IsAutoGeneratedHistoryTable))
.Where(t => tables == null || !tables.Any() || tables.Contains($"[{t.Name.Parts[0]}].[{t.Name.Parts[1]}]"))
.Where(t => $"{t.Name.Parts[1]}" != HistoryRepository.DefaultTableName)
.ToList();
foreach (var item in items)
{
var dbTable = new DatabaseTable
{
Name = item.Name.Parts[1],
Schema = item.Name.Parts[0],
};
if (item.MemoryOptimized)
{
dbTable["SqlServer:MemoryOptimized"] = true;
}
GetColumns(item, dbTable, typeAliases, model.GetObjects <TSqlDefaultConstraint>(DacQueryScopes.UserDefined).ToList(), model);
GetPrimaryKey(item, dbTable);
var description = model.GetObjects <TSqlExtendedProperty>(DacQueryScopes.UserDefined)
.Where(p => p.Name.Parts.Count == 4)
.Where(p => p.Name.Parts[0] == "SqlTableBase")
.Where(p => p.Name.Parts[1] == dbTable.Schema)
.Where(p => p.Name.Parts[2] == dbTable.Name)
.Where(p => p.Name.Parts[3] == "MS_Description")
.FirstOrDefault();
dbTable.Comment = FixExtendedPropertyValue(description?.Value);
dbModel.Tables.Add(dbTable);
}
foreach (var item in items)
{
GetForeignKeys(item, dbModel);
GetUniqueConstraints(item, dbModel);
GetIndexes(item, dbModel);
}
var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
.Where(t => tables == null || !tables.Any() || tables.Contains($"[{t.Name.Parts[0]}].[{t.Name.Parts[1]}]"))
.ToList();
foreach (var view in views)
{
var dbView = new DatabaseTable
{
Name = view.Name.Parts[1],
Schema = view.Name.Parts[0],
};
GetViewColumns(view, dbView, typeAliases);
dbModel.Tables.Add(dbView);
}
return(dbModel);
}
public DatabaseModel Create(string dacpacPath, DatabaseModelFactoryOptions options)
{
if (string.IsNullOrEmpty(dacpacPath))
{
throw new ArgumentException(@"invalid path", nameof(dacpacPath));
}
if (!File.Exists(dacpacPath))
{
throw new ArgumentException("Dacpac file not found");
}
var schemas = options.Schemas;
var tables = options.Tables;
var dbModel = new DatabaseModel
{
DatabaseName = Path.GetFileNameWithoutExtension(dacpacPath),
DefaultSchema = schemas.Count() > 0 ? schemas.First() : "dbo"
};
//Sequences not created - not needed for scaffolding
var consolidator = new DacpacConsolidator();
dacpacPath = consolidator.Consolidate(dacpacPath);
var model = new TSqlTypedModel(dacpacPath);
var typeAliases = GetTypeAliases(model, dbModel);
var items = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
.Where(t => !t.GetProperty <bool>(Table.IsAutoGeneratedHistoryTable))
.Where(t => tables == null || !tables.Any() || tables.Contains($"[{t.Name.Parts[0]}].[{t.Name.Parts[1]}]"))
.Where(t => $"{t.Name.Parts[1]}" != HistoryRepository.DefaultTableName)
.ToList();
foreach (var item in items)
{
var dbTable = new DatabaseTable
{
Name = item.Name.Parts[1],
Schema = item.Name.Parts[0],
};
if (item.MemoryOptimized)
{
dbTable["SqlServer:MemoryOptimized"] = true;
}
GetColumns(item, dbTable, typeAliases, model.GetObjects <TSqlDefaultConstraint>(DacQueryScopes.UserDefined).ToList());
GetPrimaryKey(item, dbTable);
dbModel.Tables.Add(dbTable);
}
foreach (var item in items)
{
GetForeignKeys(item, dbModel);
GetUniqueConstraints(item, dbModel);
GetIndexes(item, dbModel);
}
return(dbModel);
}
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,
});
}