private static void AddScriptsToModel(TSqlModel model, IEnumerable <string> scripts)
{
foreach (string script in scripts)
{
model.AddObjects(script);
}
}
public void LoadModelFromDacpac(string dacpacFile)
{
_Model = TSqlModel.LoadFromDacpac(
dacpacFile,
new ModelLoadOptions(DacSchemaModelStorageType.Memory, loadAsScriptBackedModel: true)
);
}
private static void Main(string[] args)
{
if (args.Length < 1 || CheckArg(args[0], "?"))
{
PrintArgs();
return;
}
if (!File.Exists(args[0]))
{
Console.WriteLine("File: \"{0}\" does not exist :(", args[0]);
PrintArgs();
return;
}
var model = new TSqlModel(args[0], DacSchemaModelStorageType.File);
foreach (
TSqlObject index in
model.GetObjects(DacQueryScopes.Default, Index.TypeClass /*Index lets you access different types,
* Table.TypeClass will give tables and Procedures.TypeClass gives procedures*/)
)
{
//index is a weakly typed TSqlObject that can be queried to get the properties and relationships
DumpIndex(index);
}
}
public void TestDateTimeColumnWithoutScale7()
{
string[] scripts = new[]
{
"CREATE TABLE t1 (c1 DATETIME2(2) NOT NULL)"
};
using (TSqlModel model = new TSqlModel(SqlServerVersion.SqlAzure, new TSqlModelOptions()))
{
// Adding objects to the model.
foreach (string script in scripts)
{
model.AddObjects(script);
}
var ruleSettings = new CodeAnalysisRuleSettings()
{
new RuleConfiguration(DateTimeColumnsWith7ScaleRule.RuleId)
};
ruleSettings.DisableRulesNotInSettings = true;
CodeAnalysisService service = new CodeAnalysisServiceFactory().CreateAnalysisService(model.Version,
new CodeAnalysisServiceSettings()
{
RuleSettings = ruleSettings
});
CodeAnalysisResult analysisResult = service.Analyze(model);
Assert.AreEqual(0, analysisResult.Problems.Count, "Expect 1 problems to be found");
}
}
public void Sets_KeyColumns_To_Empty_List_When_No_Primary_Key()
{
var path = Path.Combine(Environment.CurrentDirectory, "dacServicesTest.dac");
if (File.Exists(path))
{
File.Delete(path);
}
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions {
}))
{
model.AddObjects("CREATE TABLE t1 (c1 NVARCHAR(30) NOT NULL, id int)", new TSqlObjectOptions());
DacPackageExtensions.BuildPackage(path, model,
new PackageMetadata()
{
Description = "Test Package", Name = "ssss", Version = "1"
});
}
var dac = new DacServices.DacParser(path);
Assert.AreEqual(1, dac.GetTableDefinitions().Count);
var table = dac.GetTableDefinitions().FirstOrDefault();
Assert.AreEqual(0, table.KeyColumns.Count);
}
/// <summary>
/// Creates a new filtered model by copying elements from an existing model
/// </summary>
public TSqlModel CreateFilteredModel(TSqlModel model)
{
// CloneModelOptions copies the database options of the existing model so that it can be used during
// model creation
TSqlModelOptions options = model.CloneModelOptions();
TSqlModel filteredModel = new TSqlModel(model.Version, options);
// A call to GetObjects with no ModelTypeClasses specified returns all top-level objects.
// These are objects such as Tables, Views, Indexes - anything that can be defined by itself in TSQL.
// Examples of non-top level objects are Columns.
IEnumerable <TSqlObject> allObjects = model.GetObjects(QueryScopes);
// Filter the objects and copy them to the new model.
// Note that some objects such as DatabaseOptions, and any inlined constraints, will
// not support being scripted out. DatabaseOptions don't get a TSQL representation (hence the clone method),
// and inline constraints get scripted out with table/view definitions so to avoid duplication errors they
// can't be scripted
IFilter allFilters = new CompositeFilter(_filters);
foreach (TSqlObject tsqlObject in allFilters.Filter(allObjects))
{
string script;
if (tsqlObject.TryGetScript(out script))
{
// Some objects such as the DatabaseOptions can't be scripted out.
filteredModel.AddObjects(script);
}
}
return(filteredModel);
}
public void Build(string dacpacPath, string packageName, IEnumerable <string> scripts)
{
using (var model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions {
})) {
// Adding objects to the model.
foreach (string script in scripts)
{
model.AddObjects(script);
}
try {
// save the model to a new .dacpac
// Note that the PackageOptions can be used to specify RefactorLog and contributors to include
DacPackageExtensions.BuildPackage(dacpacPath, model,
new PackageMetadata {
Name = packageName, Description = string.Empty, Version = "1.0"
},
new PackageOptions());
var message = Environment.NewLine +
string.Format(CultureInfo.InvariantCulture, Resources.SqlPublish_PublishDacpacSuccess, dacpacPath) +
Environment.NewLine;
_outputWindow.WriteAsync(MessageCategory.General, message).DoNotWait();
} catch (DacServicesException ex) {
var error = Environment.NewLine +
string.Format(CultureInfo.InvariantCulture, Resources.SqlPublishDialog_UnableToBuildDacPac, ex.Message) +
Environment.NewLine;
_outputWindow.WriteAsync(MessageCategory.Error, error).DoNotWait();
// _coreShell.ShowErrorMessage(error);
}
}
}
public static IEnumerable <string> GetReferencedDacPackages(this TSqlModel model)
{
var result = new List <string>();
var dataSchemaModel = GetDataSchemaModel(model);
var getCustomDataMethod = dataSchemaModel.GetType().GetMethod("GetCustomData", BindingFlags.Public | BindingFlags.Instance, null, new[] { typeof(string), typeof(string) }, null);
var references = (IEnumerable)getCustomDataMethod.Invoke(dataSchemaModel, new object[] { "Reference", "SqlSchema" });
MethodInfo getMetadataMethod = null;
foreach (var reference in references)
{
if (getMetadataMethod == null)
{
getMetadataMethod = reference.GetType().GetMethod("GetMetadata", BindingFlags.Public | BindingFlags.Instance, null, new[] { typeof(string) }, null);
}
var fileName = (string)getMetadataMethod.Invoke(reference, new object[] { "FileName" });
if (!string.IsNullOrEmpty(fileName))
{
result.Add(fileName);
}
}
return(result);
}
private TSqlModel BuildActualModel()
{
var model = new TSqlModel(_outputPath);
foreach (var source in _sourceFolder)
{
var finder = new ScriptFinder(source.Path, source.Filter);
foreach (var script in finder.GetScripts(ScriptFixer))
{
try
{
if (!_alreadyAdded.ContainsKey(script))
{
model.AddObjects(script);
}
_alreadyAdded[script] = true;
}
catch (Exception e)
{
Console.WriteLine("Error adding script: {0}, script:\r\n{1}", e.Message, script);
}
}
}
return(model);
}
public DataSchemaModel Compile(string projectFilePath, string dacpacPath)
{
Debug.Assert(projectFilePath != null, "projectFilePath != null");
var projectReplicationsDirectory = Path.Combine(Path.GetDirectoryName(projectFilePath) ?? string.Empty, "Replications");
if (!Directory.Exists(projectReplicationsDirectory))
{
return new DataSchemaModel {
Model = new Model.Compiled.Model()
}
}
;
var sourceSerializer = new XmlSerializer(typeof(Source.Publication));
var publications = Directory.EnumerateFiles(projectReplicationsDirectory, "*.xml")
.Where(x => Path.GetFileName(x) != "Replication.DefaultSettings.xml")
.Select(x => (Source.Publication)sourceSerializer.Deserialize(new FileStream(x, FileMode.Open)))
.ToList();
var sqlModel = new TSqlModel(dacpacPath);
return(Compile(publications, sqlModel));
}
private static void AddScriptsToModel(TSqlModel model, IEnumerable <Tuple <string, string> > scriptsWithNamedSources)
{
foreach (var scriptWithName in scriptsWithNamedSources)
{
model.AddOrUpdateObjects(scriptWithName.Item1, scriptWithName.Item2, null);
}
}
public void Run()
{
DacServices services = new DacServices(@"Server=ITK\DEV17;Integrated Security=true;");
DacPackage package = DacPackage.Load(file01, DacSchemaModelStorageType.Memory, FileAccess.ReadWrite);
string dbName = @"MojaBazaDAC";
bool updateExisting = true;
TSqlModel tm01 = new TSqlModel(file01);
TSqlModel newModel = new TSqlModel(tm01.Version, tm01.CopyModelOptions());
//package.UpdateModel(filteredModel, new PackageMetadata())
DacDeployOptions opts = new DacDeployOptions
{
ExcludeObjectTypes = new ObjectType[]
{
ObjectType.Users,
ObjectType.RoleMembership
}
};
services.Deploy(package,
dbName,
updateExisting,
opts
);
}
public string Convert(CSchema schema)
{
string[] parts = { schema.SchemaName };
var createSchemaStatement = new CreateSchemaStatement();
createSchemaStatement.Name = new Identifier {
Value = schema.SchemaName
};
//generate DDL
var script = new TSqlScript();
var batch = new TSqlBatch();
script.Batches.Add(batch);
batch.Statements.Add(createSchemaStatement);
var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
var existing = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
if (existing != null)
{
return(existing.GetScript());
}
dacpacModel.AddObjects(script);
existing = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
return(existing.GetScript());
}
protected override void ProcessRecord()
{
string script = string.Empty;
try
{
// create an instance of a tsqmodel to store the sql records
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql130, new TSqlModelOptions()))
{
// iterate through each file and add the sql script to the model
foreach (string file in System.IO.Directory.GetFiles(FileDirectory, "*.sql", SearchOption.AllDirectories))
{
using (StreamReader sr = new StreamReader(file))
{
script = sr.ReadToEnd();
model.AddObjects(script);
}
}
// build the sql directory and add to the output path
DacPackageExtensions.BuildPackage(
OutputPath,
model,
new PackageMetadata(), // Describes the dacpac.
new PackageOptions()); // Use this to specify the deployment contributors, refactor log to include in package
}
}
catch (Exception ex)
{
WriteWarning(ex.ToString());
WriteWarning(script);
throw (ex);
}
}
public static object GetObjectService(this TSqlModel model)
{
MethodInfo getObjectService = model.GetType()
.GetMethod("GetObjectService", BindingFlags.NonPublic | BindingFlags.Instance);
return(getObjectService.Invoke(model, null));
}
public void ReadModel(string srcConString)
{
var decFilePath = GetDacFileName(srcConString) + ".bacpac";
using (TSqlModel model = new TSqlModel(decFilePath))
{
// This will get all tables. Note the use of Table.TypeClass!
var tables = model.GetObjects(DacQueryScopes.Default, Table.TypeClass).ToList();
// Look up a specific table by ID. Note that if no schema is defined when creating
// an element the default "dbo" schema is used
var t1 = model.GetObjects(Table.TypeClass, new ObjectIdentifier("dbo", "t1"), DacQueryScopes.Default).FirstOrDefault();
// Get a the column referenced by this table, and query its length
TSqlObject column = t1.GetReferenced(Table.Columns).First(col => col.Name.Parts[2].Equals("c1"));
int columnLength = column.GetProperty <int>(Column.Length);
Console.WriteLine("Column c1 has length {0}", columnLength);
// Verify the ColumnType of this column. This can help indicate which
// properties will return meaningful values.
// For instance since Column.Collation is only available on a simple column,
// and Column.Persisted is only on computed columns
ColumnType columnType = column.GetMetadata <ColumnType>(Column.ColumnType);
Console.WriteLine("Column c1 is of type '{0}'", columnType);
}
}
public void Prc(string DacPac, string DatabaseName, string SchemaName)
{
this.databaseName = DatabaseName;
this.schemaName = SchemaName;
using (TSqlModel model = TSqlModel.LoadFromDacpac(DacPac,
new ModelLoadOptions(DacSchemaModelStorageType.Memory, loadAsScriptBackedModel: true)))
{
ModelTypeClass[] Filter = new[] {
ModelSchema.View,
ModelSchema.Procedure,
ModelSchema.Table
};
foreach (var vw in model.GetObjects(DacQueryScopes.All, Filter))
{
TSqlFragment frg;
if (TSqlModelUtils.TryGetFragmentForAnalysis(vw, out frg))
{
ProcessTsqlFragment(frg);
}
int g = 0;
}
}
}
internal static void Merge()
{
var samplePackagagePath = @"C:\Dev\ed\DacMergeExample\SampleSQLProj\bin\Debug\Sample.dacpac";
TSqlModel sampleModel = new TSqlModel(samplePackagagePath, DacSchemaModelStorageType.Memory);
var targetPackagePath = @"merged.dacpac";
DisposableList disposables = new DisposableList();
var newModel = new TSqlModel(sampleModel.Version, sampleModel.CopyModelOptions());
foreach (var item in sampleModel.GetObjects(DacQueryScopes.UserDefined))
{
string script;
if (item.TryGetScript(out script))
{
newModel.AddObjects(script);
}
}
DacPackageExtensions.BuildPackage(
targetPackagePath,
newModel,
new PackageMetadata(), // Describes the dacpac.
new PackageOptions()); // Use this to specify the deployment contributors, refactor log to include in package
//DacPackage package = disposables.Add(DacPackage.Load(targetPackagePath, DacSchemaModelStorageType.Memory, FileAccess.ReadWrite));
//package.UpdateModel(cilModel, new PackageMetadata());
}
private async Task <(DefaultConstraint[] DefaultConstraints, string[] Errors)> GetDefaultConstraintsInternalAsync(string dacpacPath)
{
var(constraints, errors) = await Task.Run <(DefaultConstraint[] Result, string[] Errors)>(() =>
{
try
{
using (var sqlModel = TSqlModel.LoadFromDacpac(dacpacPath, new ModelLoadOptions(DacSchemaModelStorageType.Memory, false)))
{
// Process the input
return(GetDefaultConstraintsFromModel(sqlModel), null);
}
}
catch (DacModelException e)
{
var errorMessages = new List <string> {
e.GetBaseException().Message
};
if (e.Messages != null && e.Messages.Any())
{
errorMessages.AddRange(e.Messages.Select(m => m.ToString()));
}
return(null, errorMessages.ToArray());
}
catch (Exception e)
{
return(null, new[] { e.GetBaseException().Message });
}
});
return(constraints, errors);
}
internal Package(ILogger logger, IModelFactory modelFactory, string fileName)
{
_logger = logger;
_modelFactory = modelFactory;
_package = DacPackage.Load(fileName);
_model = new TSqlModel(fileName);
}
private static void Main(string[] args)
{
if (args.Length < 1 || CheckArg(args[0], "?"))
{
PrintArgs();
return;
}
if (!File.Exists(args[0]))
{
Console.WriteLine("File: \"{0}\" does not exist :(", args[0]);
PrintArgs();
return;
}
var model = new TSqlModel(args[0], DacSchemaModelStorageType.File);
foreach (
TSqlObject index in
model.GetObjects(DacQueryScopes.Default, Index.TypeClass /*Index lets you access different types,
*Table.TypeClass will give tables and Procedures.TypeClass gives procedures*/)
)
{
//index is a weakly typed TSqlObject that can be queried to get the properties and relationships
DumpIndex(index);
}
}
private static void RunAnalysis(TSqlModel model, string OutFile)
{
CodeAnalysisService service = new CodeAnalysisServiceFactory().CreateAnalysisService(model.Version);
service.ResultsFile = OutFile;
CodeAnalysisResult result = service.Analyze(model);
}
public void TestTSqlDataType()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()
{
}))
{
model.AddObjects(@"
CREATE PARTITION FUNCTION [pf1]
(
int
)
AS RANGE LEFT
FOR VALUES (1,100,1000)
");
model.AddObjects(@"
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
TSqlPartitionFunction function = typedModel.GetObject <TSqlPartitionFunction>(
new ObjectIdentifier("pf1"), DacQueryScopes.UserDefined);
foreach (var parameterType in function.ParameterType)
{
Assert.AreEqual(SqlDataType.Int, parameterType.SqlDataType, "DataType on partition function is not correct");
}
var boundaryValues = function.BoundaryValues.ToList();
Assert.AreEqual(3, boundaryValues.Count, "Incorrect number of boundary values");
Assert.AreEqual("1", boundaryValues[0].Expression, "incorrect boundary value");
Assert.AreEqual("100", boundaryValues[1].Expression, "incorrect boundary value");
Assert.AreEqual("1000", boundaryValues[2].Expression, "incorrect boundary value");
}
}
public static void AddReference(this TSqlModel model,
string filename,
string logicalName,
string externalParts,
bool suppressMissingDependenciesErrors)
{
var dataSchemaModel = model.GetDataSchemaModel();
MethodInfo addCustomData = dataSchemaModel.GetType()
.GetMethod("AddCustomData", BindingFlags.NonPublic | BindingFlags.Instance);
Type customSchemaDataType = dataSchemaModel.GetType().Assembly
.GetType("Microsoft.Data.Tools.Schema.SchemaModel.CustomSchemaData");
object newCustomSchemaData = customSchemaDataType
.GetConstructor(new Type[] { typeof(string), typeof(string) })
.Invoke(new object[] { "Reference", "SqlSchema" });
MethodInfo setMetadata = customSchemaDataType
.GetMethod("SetMetadata", BindingFlags.Public | BindingFlags.Instance);
setMetadata.Invoke(newCustomSchemaData, new[] { "FileName", filename });
setMetadata.Invoke(newCustomSchemaData, new[] { "LogicalName", logicalName });
setMetadata.Invoke(newCustomSchemaData, new[] { "SuppressMissingDependenciesErrors", suppressMissingDependenciesErrors.ToString() });
if (!string.IsNullOrEmpty(externalParts))
{
setMetadata.Invoke(newCustomSchemaData, new[] { "ExternalParts", externalParts });
}
addCustomData.Invoke(dataSchemaModel, new[] { newCustomSchemaData, true });
}
private IEnumerable <TSqlObject> GetTablesViewsAndSchemas(TSqlModel model)
{
return(model.GetObjects(DacQueryScopes.UserDefined,
Table.TypeClass,
View.TypeClass,
Schema.TypeClass));
}
public void Gets_Column_Definition()
{
var path = Path.Combine(Environment.CurrentDirectory, "dacServicesTest.dac");
if (File.Exists(path))
{
File.Delete(path);
}
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions {
}))
{
model.AddObjects("CREATE TABLE t1 (c1 NVARCHAR(30) NOT NULL, id int primary key clustered)", new TSqlObjectOptions());
DacPackageExtensions.BuildPackage(path, model,
new PackageMetadata()
{
Description = "Test Package", Name = "ssss", Version = "1"
});
}
var dac = new DacServices.DacParser(path);
Assert.AreEqual(1, dac.GetTableDefinitions().Count);
var table = dac.GetTableDefinitions().FirstOrDefault();
Assert.AreEqual("c1", table.Columns.FirstOrDefault().Name.Value);
Assert.AreEqual(LiteralType.String, table.Columns.FirstOrDefault().LiteralType);
}
private static object GetDataSchemaModel(TSqlModel model)
{
var service = model.GetType().GetField("_service", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(model);
var dataSchemaModel = service.GetType().GetProperty("DataSchemaModel", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(service);
return(dataSchemaModel);
}
public static void GeneratePackage()
{
try
{
//TSqlModel sqlModel = TSqlModel.LoadFromDacpac(@"D:\testdb.dacpac", new ModelLoadOptions());
TSqlModel sqlModel1 = new TSqlModel(SqlServerVersion.Sql150, new TSqlModelOptions());
var files = Directory.GetFiles(@"D:\Visual Studio Projects\DacPacSample\Oracle", "regions.sql", SearchOption.AllDirectories);
foreach (var file in files)
{
var model = new TSqlModel(file, DacSchemaModelStorageType.File);
model.Validate();
}
var messages = sqlModel1.Validate();
foreach (var item in messages)
{
Console.WriteLine(item.Message);
}
DacPackageExtensions.BuildPackage(@"D:\test.dacpac", sqlModel1, new PackageMetadata {
Name = "TestDacPac"
}, new PackageOptions {
TreatWarningsAsErrors = false
});
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
public static void Run()
{
string dacpacPath = "relativePath.dacpac";
// Note that you could read scripts from a file or use TSqlScript objects that are taken from other models.
// Hand-crafting TSqlScript is quite awkard but could be done programmatically (we do it internally).
// If you need examples for this let us know and we can look into that too.
string[] scripts = new[]
{
"CREATE TABLE t1 (c1 NVARCHAR(30) NOT NULL)",
"CREATE TABLE t2 (c2 INT NOT NULL)",
"CREATE TABLE t3 (c3 INT NOT NULL)",
"CREATE TABLE t4 (c4 INT NOT NULL)",
};
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions {
}))
{
// Adding objects to the model.
foreach (string script in scripts)
{
model.AddObjects(script);
}
ReadTheModel(model);
CopyFromTheModel(model);
// save the model to a new .dacpac
// Note that the PackageOptions can be used to specify RefactorLog and contributors to include
DacPackageExtensions.BuildPackage(
dacpacPath,
model,
new PackageMetadata {
Name = "MyPackageName", Description = "This is usually ignored", Version = "1.0"
},
new PackageOptions()
);
}
// Load from a dacpac
using (TSqlModel modelFromDacpac = new TSqlModel(dacpacPath))
{
// Show that all the elements were saved successfully
ReadTheModel(modelFromDacpac);
// You can update the model in the dacpac. Other parts of a dacpac can't be updated yet (pre/post deployment scripts)
modelFromDacpac.AddObjects("CREATE VIEW V1 AS SELECT * FROM T1");
using (DacPackage dacPackage = DacPackage.Load(dacpacPath,
DacSchemaModelStorageType.Memory,
FileAccess.ReadWrite))
{
DacPackageExtensions.UpdateModel(dacPackage, modelFromDacpac, null);
}
}
Console.WriteLine("Press any key to finish");
Console.ReadKey();
}
/// <summary>
/// When running analysis agaisnt a Dacpac, be sure to specific loadAsScriptBackedModel=true when loading the
/// model
/// </summary>
private static void RunDacpacAnalysis(string packagePath, string resultsFilePath)
{
using (TSqlModel model = TSqlModel.LoadFromDacpac(packagePath,
new ModelLoadOptions(DacSchemaModelStorageType.Memory, loadAsScriptBackedModel: true)))
{
RunAnalysis(model, resultsFilePath);
}
}
protected void AddScriptsToModel(TSqlModel model)
{
foreach (Tuple <string, string> tuple in this.TestScripts)
{
// Item1 = script, Item2 = (logicl) source file name
model.AddOrUpdateObjects(tuple.Item1, tuple.Item2, new TSqlObjectOptions());
}
}
public void Dispose()
{
if (this.model != null)
{
model.Dispose();
model = null;
}
}
private TSqlModel CreateTestModel()
{
var scripts = _testScripts;
TSqlModel model = _trash.Add(new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions()));
AddScriptsToModel(model, scripts);
return(model);
}
public static void Run()
{
string dacpacPath = "relativePath.dacpac";
// Note that you could read scripts from a file or use TSqlScript objects that are taken from other models.
// Hand-crafting TSqlScript is quite awkard but could be done programmatically (we do it internally).
// If you need examples for this let us know and we can look into that too.
string[] scripts = new[]
{
"CREATE TABLE t1 (c1 NVARCHAR(30) NOT NULL)",
"CREATE TABLE t2 (c2 INT NOT NULL)",
"CREATE TABLE t3 (c3 INT NOT NULL)",
"CREATE TABLE t4 (c4 INT NOT NULL)",
};
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions { }))
{
// Adding objects to the model.
foreach (string script in scripts)
{
model.AddObjects(script);
}
ReadTheModel(model);
CopyFromTheModel(model);
// save the model to a new .dacpac
// Note that the PackageOptions can be used to specify RefactorLog and contributors to include
DacPackageExtensions.BuildPackage(
dacpacPath,
model,
new PackageMetadata { Name = "MyPackageName", Description = "This is usually ignored", Version = "1.0" },
new PackageOptions()
);
}
// Load from a dacpac
using (TSqlModel modelFromDacpac = new TSqlModel(dacpacPath))
{
// Show that all the elements were saved successfully
ReadTheModel(modelFromDacpac);
// You can update the model in the dacpac. Other parts of a dacpac can't be updated yet (pre/post deployment scripts)
modelFromDacpac.AddObjects("CREATE VIEW V1 AS SELECT * FROM T1");
using (DacPackage dacPackage = DacPackage.Load(dacpacPath,
DacSchemaModelStorageType.Memory,
FileAccess.ReadWrite))
{
DacPackageExtensions.UpdateModel(dacPackage, modelFromDacpac, null);
}
}
Console.WriteLine("Press any key to finish");
Console.ReadKey();
}
public void BasicInstantiation()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
string createTable = @"
CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY
)
";
string createCheck = @"
ALTER TABLE [dbo].[Table1]
ADD CONSTRAINT [check1]
CHECK (1 > 0)
";
model.AddObjects(createTable);
model.AddObjects(createCheck);
IEnumerable<TSqlObject> tables = model.GetObjects(DacQueryScopes.Default, Table.TypeClass);
tables.Single().GetReferencing(View.BodyDependencies);
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var genericTables = typedModel.GetObjects<TSqlTable>(DacQueryScopes.Default);
var sql90Tables = typedModel.GetObjects<ISql90TSqlTable>(DacQueryScopes.Default);
TSqlTable genericTable = genericTables.First();
ISql90TSqlTable sql90Table = sql90Tables.First();
IList<TSqlColumn> genericColumns = genericTable.Columns.ToList();
IList<ISql90TSqlColumn> sql90Columns = sql90Table.Columns.ToList();
Assert.AreEqual(genericColumns.Count, sql90Columns.Count, "Column counts should not be different between implementations");
// iterate of generic columns
for (int i = 0; i < genericColumns.Count; i++)
{
TSqlColumn col = genericColumns[i];
ISql90TSqlColumn sql90Col = sql90Columns[i];
Assert.AreEqual(ColumnType.Column, col.ColumnType, "Invalid metadata ColumnType");
Assert.AreEqual(col.Collation, sql90Col.Collation, "Collation is not the same");
Assert.AreEqual(col.Expression, sql90Col.Expression, "Expression is not equal");
}
Assert.AreEqual(2, genericTable.AllConstraints.Count(), "Incorrect number of constraints");
Assert.AreEqual(1, genericTable.CheckConstraints.Count(), "Incorrect number of check constraints");
Assert.AreEqual(1, genericTable.PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key Constraints");
//TODO: Code gen the Reverse relationships for all explicitly implemented interfaces
Assert.AreEqual(2, ((TSqlTable)sql90Table).AllConstraints.Count(), "Incorrect number of constraints");
Assert.AreEqual(1, ((TSqlTable)sql90Table).CheckConstraints.Count(), "Incorrect number of check constraints");
Assert.AreEqual(1, ((TSqlTable)sql90Table).PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key Constraints");
}
}
public void InitializeTest()
{
Directory.CreateDirectory(GetTestDir());
_trash = new DisposableList();
_dacpacPath = GetTestFilePath("myDatabase.dacpac");
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, null))
{
model.AddObjects(CreateOneTable);
DacPackageExtensions.BuildPackage(_dacpacPath, model, new PackageMetadata());
}
}
/// <summary>
/// Initializes host with database objects from the specified database scripts.
/// </summary>
/// <param name="sqlScripts">Collection of database script names to parse.</param>
public void Initialize(IList<string> sqlScripts)
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()))
{
foreach (string script in sqlScripts)
{
model.AddObjects(File.ReadAllText(script));
}
_schema = DatabaseSchema.FromModel(model);
}
}
public void InitializeTest()
{
Directory.CreateDirectory(GetTestDir());
_trash = new DisposableList();
_dacpacPath = GetTestFilePath("myDatabase.dacpac");
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, null))
{
model.AddObjects("CREATE TABLE [dbo].[t1] (c1 INT NOT NULL PRIMARY KEY)");
DacPackageExtensions.BuildPackage(_dacpacPath, model, new PackageMetadata());
}
}
public void PrimaryKeysTest()
{
TSqlModel Model = new TSqlModel(@"DacToolsDb.dacpac");
foreach (var table in Model.Tables())
{
Assert.IsNotNull(table.PrimaryKeyColumns());
Assert.IsTrue(table.PrimaryKeyColumns().Count > 0);
foreach (var column in table.PrimaryKeyColumns())
{
Assert.IsFalse(string.IsNullOrEmpty(column.Name.ToString()));
}
}
}
private static IEnumerable<CodeUnit> GetScriptsInModel(string fileName)
{
var scripts = new List<CodeUnit>();
var model = new TSqlModel(fileName, DacSchemaModelStorageType.File);
scripts.AddRange(GetCodeUnits(model.GetObjects(DacQueryScopes.Default, Procedure.TypeClass)));
scripts.AddRange(GetCodeUnits(model.GetObjects(DacQueryScopes.Default, ScalarFunction.TypeClass)));
scripts.AddRange(GetCodeUnits(model.GetObjects(DacQueryScopes.Default, TableValuedFunction.TypeClass)));
scripts.AddRange(GetCodeUnits(model.GetObjects(DacQueryScopes.Default, DmlTrigger.TypeClass)));
return scripts;
}
public void Execute()
{
var compiler = new SqlPublicationCompiler();
var sourceSerializer = new XmlSerializer(typeof (Source.Publication));
var source = (Source.Publication) sourceSerializer.Deserialize(new FileStream(@"Samples\Source File.xml", FileMode.Open));
var model = new TSqlModel(@"");
var output = compiler.Compile(new List<Source.Publication> { source }, model);
var outputSerializer = new XmlSerializer(typeof(DataSchemaModel));
outputSerializer.Serialize(new FileStream(@"Samples\Compiled Model.xml", FileMode.Create), output);
}
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
List<SqlRuleProblem> problems = new List<SqlRuleProblem>();
_model = context.SchemaModel;
// Query all top level user-defined objects in the model. This restricts scope to objects actually defined
// in this model, rather than same database references, built in types, or system references
foreach (TSqlObject tSqlObject in _model.GetObjects(DacQueryScopes.UserDefined))
{
AnalyzeObject(tSqlObject, problems);
}
return problems;
}
public void Display()
{
TreeView.Items.Clear();
var repository = ModelRepository.GetRepository();
_model = repository.GetModel();
var propertiesPageBuilder = new PropertiesPageBuilder();
var dacpacNode = AddRootTreeItem("Dacpac Properties");
dacpacNode.Tag = new CachedObjectDisplay
{
Properties = propertiesPageBuilder.GetPropertiesDisplayForDacpac(_model),
Script = ""
};
if (repository.ValidateModel())
{
var messages = _model.Validate();
var messagesNode = AddTreeItem("Validation Result", dacpacNode, _defaultForeground);
messagesNode.Tag = new CachedObjectDisplay
{
Properties = propertiesPageBuilder.GetPropertiesDisplayForValidationMessages(messages),
Script = ""
};
}
DisplayTopLevelNode("Tables", ModelSchema.Table);
DisplayTopLevelNode("Views", ModelSchema.View);
var programabililtyNode = AddRootTreeItem("Programmability");
DisplayTopLevelNode(programabililtyNode, "Procedures", ModelSchema.Procedure);
DisplayTopLevelNode(programabililtyNode, "Scalar Functions", ModelSchema.ScalarFunction);
DisplayTopLevelNode(programabililtyNode, "Table-Valued Functions", ModelSchema.TableValuedFunction);
DisplayTopLevelNode(programabililtyNode, "Database Triggers", ModelSchema.DatabaseDdlTrigger);
DisplayTopLevelNode(programabililtyNode, "Server Triggers", ModelSchema.ServerDdlTrigger);
DisplayTopLevelNode(programabililtyNode, "Assemblies", ModelSchema.Assembly);
var securityNode = AddRootTreeItem("Security");
DisplayTopLevelNode(securityNode, "Server Logins", ModelSchema.Login);
DisplayTopLevelNode(securityNode, "Database Users", ModelSchema.User);
DisplayTopLevelNode(securityNode, "Schemas", ModelSchema.Schema);
DisplayTopLevelNode(securityNode, "Roles", ModelSchema.Role);
Cursor = Cursors.Arrow;
}
public void Build(string dacpacPath, string packageName, IEnumerable<string> scripts) {
using (var model = new TSqlModel(SqlServerVersion.Sql130, new TSqlModelOptions { })) {
// Adding objects to the model.
foreach (string script in scripts) {
model.AddObjects(script);
}
try {
// save the model to a new .dacpac
// Note that the PackageOptions can be used to specify RefactorLog and contributors to include
DacPackageExtensions.BuildPackage(dacpacPath, model,
new PackageMetadata { Name = packageName, Description = string.Empty, Version = "1.0" },
new PackageOptions());
} catch(DacServicesException ex) {
throw new SqlPublishException(ex.Message);
}
}
}
public void TestColumnUserDefinedTypes()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(@"
CREATE ASSEMBLY [SampleUDT]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300004BFF550000000000000000E00002210B010B00000800000006000000000000BE270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000682700005300000000400000A802000000000000000000000000000000000000006000000C000000302600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C4070000002000000008000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000A02700000000000048000000020005000C210000240500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133001000B00000001000011007E0700000A0A2B00062A00133001000C0000000200001100027B020000040A2B00062A133002001700000003000011001200FE15020000021200177D02000004060B2B00072A00133002002500000004000011000F00280800000A16FE010C082D0828030000060B2B0C1200FE1502000002060B2B00072A000000133001000B00000001000011007E0700000A0A2B00062A00133001001000000005000011007201000070730900000A0A2B00062A42534A4201000100000000000C00000076342E302E33303331390000000005006C000000E8010000237E0000540200001002000023537472696E6773000000006404000004000000235553006804000010000000234755494400000078040000AC00000023426C6F6200000000000000020000015717A2010900000000FA253300160000010000000D0000000200000002000000060000000100000001000000090000000400000005000000010000000200000002000000010000000200000000000A0001000000000006003C0035000A00670052000A008E0052000600DB00C8001300EF00000006002401040106004401040106006C0135000A009D0182010A00B90182010600DF01C0010600F501C001060000023500000000000100000000000100010009211000180000000500010001000600AE0023000100B4002600502000000000C60071000A000100682000000000E6097A000E0001008020000000009608850012000100A420000000009600980017000100D8200000000086009E000A000200F020000000009600A6001E00020000000100C600020009002100FE0032003100FE0038003900FE003D004100FE003D004900FE0041005900FE00500069000702560019007A000E001900FE0070002E000B007A002E00130083002E001B008C0043002B00470059005D00610068007500020001000000BA0029000000C1002D000200020003000200030005000480000000000000000000000000000000006201000004000000000000000000000001002C0000000000040000000000000000000000010046000000000000000000003C4D6F64756C653E0053616D706C655544542E646C6C0053716C55736572446566696E65645479706531006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E446174610053797374656D2E446174612E53716C547970657300494E756C6C61626C6500546F537472696E67006765745F49734E756C6C006765745F4E756C6C0053716C537472696E67005061727365004D6574686F6431004D6574686F6432005F76617231005F6E756C6C0049734E756C6C004E756C6C00730053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F646573002E63746F720053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053616D706C655544540053657269616C697A61626C65417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C55736572446566696E65645479706541747472696275746500466F726D61740053797374656D2E52756E74696D652E496E7465726F705365727669636573005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E6700456D7074790000000000010000F29E39299A1EBD40A56A399A5D1E10610008B77A5C561934E0890320000E0320000204000011080600011108110D040000110D02060802060203280002040800110805200101111504200101080320000105200101112908010001000000000005200101113102060E0307010E03070102060702110811080707031108110802042001010E040701110D0801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000004BFF5500000000020000001C0100004C2600004C08000052534453DFC29DC30B515C4ABFC3FD3DBB7A048801000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C6F626A5C44656275675C53616D706C655544542E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000902700000000000000000000AE270000002000000000000000000000000000000000000000000000A027000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530061006D0070006C0065005500440054002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530061006D0070006C0065005500440054002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
GO
ALTER ASSEMBLY [SampleUDT]
DROP FILE ALL
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000001B000000840000000000000018000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF380000FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF0BCA3101380000000010000000100000000000000E00FFFF04000000FFFF03000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA3101380000000010000000100000000000000F00FFFF04000000FFFF0300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101004BFF5501000000DFC29DC30B515C4ABFC3FD3DBB7A048800000000000000000100000001000000000000000000000000000000DC51330100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA310138000000001000000010000000000000FFFFFFFF04000000FFFF030000000000FFFFFFFF00000000FFFFFFFF00000000FFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA310138000000001000000010000000000000FFFFFFFF04000000FFFF030000000000FFFFFFFF00000000FFFFFFFF00000000FFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F862513FC607D311905300C04FA302A1C4454B99E9E6D211903F00C04FA302A10B9D865A1166D311BD2A0000F80849BD60A66E40CF64824CB6F042D48172A7991000000000000000A2EEC7934A3D37151FD88F759E2C495E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006E000000000000006E000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FEEFFEEF01000000C400000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C53716C55736572446566696E656454797065312E63730000643A5C75736572735C6C6F6E6E79625C646F63756D656E74735C76697375616C2073747564696F20323031335C70726F6A656374735C73616D706C657564745C73616D706C657564745C73716C75736572646566696E656474797065312E637300040000006200000001000000630000000000000003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001BE2300180000000D74A34F301F4D001010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000200000001000000020000000000000063000000280000001BE23001B5D25C0C580000000100000062000000630000006500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000400000032002A11000000001C010000000000000B000000000000000000000001000006000000000100000000546F537472696E670000001600031104000000E80000000B00000000000000010000000A0024115553797374656D00120024115553797374656D2E44617461000000001A0024115553797374656D2E446174612E53716C436C69656E7400001A0024115553797374656D2E446174612E53716C54797065730000001E002411554D6963726F736F66742E53716C5365727665722E536572766572001E00201100000000010000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040000000C000000010005000200060032002A1100000000C0010000000000000C0000000000000000000000020000060B00000001000000006765745F49734E756C6C0016000311200100008C0100000C0000000B000000010000001E00201100000000020000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040172000C000000010000060200060032002A11000000007C02000000000000170000000000000000000000030000061700000001000000006765745F4E756C6C00000016000311C4010000480200001700000017000000010000001E00201101000000030000110000000000000400435324312430303030000000160020110000000003000011000000000000000068000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040186000C00000001000006020006002E002A11000000005403000000000000250000000000000000000000040000062E000000010000000050617273650000160003118002000020030000250000002E000000010000001E002011010000000400001100000000000004004353243124303030300000001E00201102000000040000110000000000000400435324342430303031000000160020110000000004000011000000000000000075000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040186000C000000010000060200060032002A1100000000F8030000000000000B0000000000000000000000050000065300000001000000004D6574686F6431000000001600031158030000C40300000B00000053000000010000001E00201100000000010000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040195760C000000010000060200060032002A11000000009C04000000000000100000000000000000000000060000065E00000001000000004D6574686F64320000000016000311FC03000068040000100000005E000000010000001E00201100000000050000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040195760C0000000100000602000600F20000003C00000000000000010001000B000000000000000300000030000000000000000D000080010000000F00008009000000100000800500060009001D0005000600F20000003C0000000B000000010001000C000000000000000300000030000000000000001500008001000000170000800A0000001800008009000A000D001A0009000A00F200000054000000170000000100010017000000000000000500000048000000000000001E000080010000001F00008009000000200000801100000021000080150000002200008009000A000D003F000D001C000D00160009000A00F20000006C0000002E0000000100010025000000000000000700000060000000000000002600008001000000270000800C000000EEEFFE800F0000002800008017000000290000801F0000002B000080230000002C0000800500060009001600000000000D00190009003B000900120005000600F20000003C00000053000000010001000B0000000000000003000000300000000000000030000080010000003200008009000000330000800500060009001D0005000600F20000003C0000005E0000000100010010000000000000000300000030000000000000003700008001000000390000800E0000003A000080050006000900220005000600F4000000080000000100000000000000300000000000000018000000300000004C000000640000007C00000094000000A8000000C0000000D8000000F00000000801000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF160000000340200004D000000010000007D000000010000000100000001000000190000000100000009010000010000006500000001000000A900000001000000D9000000010000003100000001000000F100000001000000C1000000010000009500000001000000010000000000000000000000000000000000000000000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000000000000010000000000000000000000000000000000000000000000000000000000000001000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000000000001000400000000000000000000000000000000000000000000600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0000001800000024000000300000003C0000004800000054000000600000006C00000078000000840000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001600251100000000040000000100546F537472696E6700001600291100000000040000000100303630303030303100001A002511000000002001000001006765745F49734E756C6C000000001600291100000000200100000100303630303030303200001600251100000000C401000001006765745F4E756C6C00001600291100000000C4010000010030363030303030330000120025110000000080020000010050617273650016002911000000008002000001003036303030303034000016002511000000005803000001004D6574686F64310000001600291100000000580300000100303630303030303500001600251100000000FC03000001004D6574686F64320000001600291100000000FC030000010030363030303030360000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF77093101010000000B00008E0C004F000D00000060000000AC0000002C00000070000000000000000000000016000000190000000000EEC00000000000000000FFFF000000000000FFFFFFFF00000000FFFF0000000000000000000000000A00A004000000000000F001000001000000C02E8600000000000000000053716C55736572446566696E65645479706531003241393744463435000000002DBA2EF101000000000000000B00000000000000000000000000000000000000010000000B0000000C0000000000000000000000000000000000000001000000170000001700000000000000000000000000000000000000010000002E000000250000000000000000000000000000000000000001000000530000000B00000000000000000000000000000000000000010000005E0000001000000000000000000000000000000000000000020002000D01000000000100FFFFFFFF000000006E0000000802000000000000FFFFFFFF00000000FFFFFFFF010001000000010000000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C53716C55736572446566696E656454797065312E637300000000FEEFFEEF010000000100000000010000000000000000000000FFFFFFFFFFFFFFFFFFFF0900FFFFFFFFFFFFFFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101004BFF5501000000DFC29DC30B515C4ABFC3FD3DBB7A04888E0000002F4C696E6B496E666F002F6E616D6573002F7372632F686561646572626C6F636B002F7372632F66696C65732F643A5C75736572735C6C6F6E6E79625C646F63756D656E74735C76697375616C2073747564696F20323031335C70726F6A656374735C73616D706C657564745C73616D706C657564745C73716C75736572646566696E656474797065312E6373000400000006000000010000001B00000000000000220000000800000011000000070000000A00000006000000000000000500000000000000DC51330100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E00000020000000EA00000038000000170200003800000000000000E8000000800000005800000028000000C4060000A40200002C000000200100000300000016000000060000001400000015000000070000000A0000000B00000008000000090000000C0000000D0000000E0000000F000000100000001100000013000000120000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000170000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 AS N'SampleUDT.pdb';
GO
CREATE TYPE [dbo].[SqlUserDefinedType1]
EXTERNAL NAME [SampleUDT].[SqlUserDefinedType1];
GO
CREATE TYPE SSN
FROM varchar(11) NOT NULL;
GO
CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
SSN SSN,
ClrUDT [dbo].[SqlUserDefinedType1] NULL
);");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var table = typedModel.GetObject<TSqlTable>(new ObjectIdentifier("dbo", "Table1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(table, "table1 should not be null");
Assert.AreEqual(3, table.Columns.Count(), "Incorrect number of columns");
// validate columns to UserDefinedDataType and built-in types
ValidateColumnDataType(table, "Id", SqlDataType.Int, null);
ValidateColumnDataType(table, "SSN", SqlDataType.Unknown, SqlDataType.VarChar);
// Validate column with UserDefined types
TSqlColumn ssnColomn = table.Columns.SingleOrDefault(c => c.Name.Parts[2] == "ClrUDT");
Assert.IsNotNull(ssnColomn, "Missing ClrUDT Column");
var columnDataType = ssnColomn.DataType.SingleOrDefault() as TSqlUserDefinedTypeReference;
Assert.IsNotNull(columnDataType, "ClrUDT column data type should not be null");
Assert.AreEqual("UserDefinedType", columnDataType.ObjectType.Name);
}
}
internal static DatabaseSchema FromModel(TSqlModel model)
{
DatabaseSchema retVal = new DatabaseSchema();
var tableModels = model.GetObjects(DacQueryScopes.All, ModelSchema.Table).ToArray();
retVal.Tables = new TableInfo[tableModels.Length];
for (int i = 0; i < tableModels.Length; i++)
retVal.Tables[i] = GetSchemaForTable(tableModels[i]);
var viewModels = model.GetObjects(DacQueryScopes.All, ModelSchema.View).ToArray();
retVal.Views = new ViewInfo[viewModels.Length];
for (int i = 0; i < viewModels.Length; i++)
retVal.Views[i] = GetSchemaForView(viewModels[i]);
var keys = model.GetObjects(DacQueryScopes.All, ModelSchema.PrimaryKeyConstraint).ToArray();
UpdatePrimaryKeysInformation(retVal.Tables, keys);
var indexes = model.GetObjects(DacQueryScopes.All, ModelSchema.Index).ToArray();
retVal.Indexes = new IndexInfo[indexes.Length];
for (int i = 0; i < indexes.Length; i++)
{
var index = indexes[i];
string tableName = index.Name.Parts[1];
TableInfo targetTable = retVal.Tables.FirstOrDefault(t => t.ShortName == tableName);
if (null == targetTable)
{
throw new InvalidOperationException(
"Could not find target table for index " + index.Name);
}
IndexInfo info = new IndexInfo();
info.Table = targetTable;
info.Unique = index.GetProperty<bool>(Index.Unique);
var columns = index.GetReferenced(Index.Columns).ToArray();
info.Columns = new ColumnInfo[columns.Length];
for (int j = 0; j < info.Columns.Length; j++)
{
info.Columns[j] = GetSchemaForColumn(columns[j]);
}
retVal.Indexes[i] = info;
}
return retVal;
}
internal static void Merge()
{
var samplePackagagePath = @"..\..\Temp\Sample.dacpac";
TSqlModel sampleModel = new TSqlModel(samplePackagagePath, DacSchemaModelStorageType.Memory);
var targetPackagePath = @"merged.dacpac";
DisposableList disposables = new DisposableList();
DacPackageExtensions.BuildPackage(
targetPackagePath,
sampleModel,
new PackageMetadata(), // Describes the dacpac.
new PackageOptions()); // Use this to specify the deployment contributors, refactor log to include in package
//DacPackage package = disposables.Add(DacPackage.Load(targetPackagePath, DacSchemaModelStorageType.Memory, FileAccess.ReadWrite));
//package.UpdateModel(cilModel, new PackageMetadata());
}
public DataSchemaModel Compile(string projectFilePath, string dacpacPath)
{
Debug.Assert(projectFilePath != null, "projectFilePath != null");
var projectReplicationsDirectory = Path.Combine(Path.GetDirectoryName(projectFilePath) ?? string.Empty, "Replications");
if (!Directory.Exists(projectReplicationsDirectory))
return new DataSchemaModel { Model = new Model.Compiled.Model() };
var sourceSerializer = new XmlSerializer(typeof(Source.Publication));
var publications = Directory.EnumerateFiles(projectReplicationsDirectory, "*.xml")
.Where(x => Path.GetFileName(x) != "Replication.DefaultSettings.xml")
.Select(x => (Source.Publication) sourceSerializer.Deserialize(new FileStream(x, FileMode.Open)))
.ToList();
var sqlModel = new TSqlModel(dacpacPath);
return Compile(publications, sqlModel);
}
public UIElement GetPropertiesDisplayForDacpac(TSqlModel model)
{
var panel = GetPropertiesDisplayPanel("Target Sql Version: " + model.Version);
var options = model.CopyModelOptions();
panel.Children.Add(GetSimplePropertyLabel("Database Properties:"));
foreach (var prop in options.GetType().GetProperties().OrderBy(p => p.Name))
{
var val = prop.GetValue(options) as object;
if (val == null)
{
val = "NULL";
}
panel.Children.Add(GetPropertyLabel(prop.Name, val.ToString()));
}
return panel;
}
public void Sets_KeyColumns_To_Empty_List_When_No_Primary_Key()
{
var path = Path.Combine(Environment.CurrentDirectory, "dacServicesTest.dac");
if (File.Exists(path))
File.Delete(path);
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions { }))
{
model.AddObjects("CREATE TABLE t1 (c1 NVARCHAR(30) NOT NULL, id int)", new TSqlObjectOptions());
DacPackageExtensions.BuildPackage(path, model,
new PackageMetadata() { Description = "Test Package", Name = "ssss", Version = "1" });
}
var dac = new DacServices.DacParser(path);
Assert.AreEqual(1, dac.GetTableDefinitions().Count);
var table = dac.GetTableDefinitions().FirstOrDefault();
Assert.AreEqual(0, table.KeyColumns.Count);
}
static void Main(string[] args)
{
var tmp = new TSqlModel(@"c:\users\ed\desktop\AdventureWorks2012.dacpac");
var tables = tmp.GetObjects(DacQueryScopes.All, ModelSchema.Table);
foreach (var table in tables)
{
Console.WriteLine(table.Name);
Console.WriteLine(table.ObjectType.Name);
DumpScript(table);
foreach ( var child in table.GetChildren())
{
Console.WriteLine(child.Name);
Console.WriteLine(child.ObjectType.Name);
DumpChildren(child, 1);
}
}
}
public void Gets_Column_Definition()
{
var path = Path.Combine(Environment.CurrentDirectory, "dacServicesTest.dac");
if(File.Exists(path))
File.Delete(path);
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions {}))
{
model.AddObjects( "CREATE TABLE t1 (c1 NVARCHAR(30) NOT NULL, id int primary key clustered)", new TSqlObjectOptions());
DacPackageExtensions.BuildPackage(path, model,
new PackageMetadata() {Description = "Test Package", Name = "ssss", Version = "1"});
}
var dac = new DacServices.DacParser(path);
Assert.AreEqual(1, dac.GetTableDefinitions().Count);
var table = dac.GetTableDefinitions().FirstOrDefault();
Assert.AreEqual("c1", table.Columns.FirstOrDefault().Name.Value);
Assert.AreEqual(LiteralType.String, table.Columns.FirstOrDefault().LiteralType);
}
public DataSchemaModel Compile(List<Source.Publication> publications, TSqlModel sqlModel)
{
return new DataSchemaModel
{
Model = new Model.Compiled.Model
{
Elements = publications.Select(x => new Element
{
Type = "SqlPublication",
Name = x.Name,
Properties = new List<Property>
{
new Property { Name = "AllowAnonymous", Value = "True" },
new Property { Name = "AllowPull", Value = "True" },
new Property { Name = "AllowPush", Value = "True" },
new Property { Name = "AllowSynchronousTransactions", Value = "False" },
new Property { Name = "AutoGenerateSynchronizationStoredProcedures", Value = string.Empty },
new Property { Name = "CompressSnapshot", Value = "False" },
new Property { Name = "Description", Value = x.Name },
new Property { Name = "EnabledForInternet", Value = "False" },
new Property { Name = "ImmediateSync", Value = "True" },
new Property { Name = "IndependentAgent", Value = "False" },
new Property { Name = "PostSnapshotScript", Value = string.Empty },
new Property { Name = "PreSnapshotScript", Value = string.Empty },
new Property { Name = "ReplicateDDL", Value = "True" },
new Property { Name = "ReplicationFrequency", Value = "Continuous" },
new Property { Name = "Rentention", Value = "0" },
new Property { Name = "StatusActive", Value = "True" },
new Property { Name = "SnapshotInDefaultFolder", Value = "True" },
new Property { Name = "SynchronizationMethod", Value = "ConcurrentCharacter" }
},
Relationships = new List<Relationship>
{
new Relationship { Name = "Articles", Entries = CompileArticles(x.Tables, sqlModel) }
}
}).ToList()
}
};
}
/// <summary>
/// Runs the model filtering example. This shows how to filter a model and save a new
/// dacpac with the updated model. You can also update the model in the existing dacpac;
/// the unit tests in TestFiltering.cs show how this is performed.
/// </summary>
public static void RunFilteringExample()
{
// Given a model with objects that use "dev", "test" and "prod" schemas
string devPackagePath = GetFilePathInCurrentDirectory("dev.dacpac");
var scripts = SampleScripts;
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions()))
{
AddScriptsToModel(model, scripts);
Console.WriteLine("Saving test scripts to package '"+devPackagePath+"'");
DacPackageExtensions.BuildPackage(devPackagePath, model, new PackageMetadata());
Console.WriteLine("Objects found in original package: '" + devPackagePath + "'");
PrintTablesViewsAndSchemas(model);
}
string productionPackagePath = GetFilePathInCurrentDirectory("production.dacpac");
// When saving a dacpac for deployment to production (filtering to exclude "dev" and "test" schemas)
var schemaFilter = new SchemaBasedFilter("dev", "test");
ModelFilterer modelFilterer = new ModelFilterer(schemaFilter);
Console.WriteLine("Creating filtered 'production' package: '"+productionPackagePath+"'");
modelFilterer.CreateFilteredDacpac(devPackagePath, productionPackagePath);
// Then expect only the "prod" schema objects to remain in the new package
using (TSqlModel filteredModel = new TSqlModel(productionPackagePath))
{
Console.WriteLine("Objects found in filtered package: '" + productionPackagePath + "'");
PrintTablesViewsAndSchemas(filteredModel);
}
// If we publish the dacpac to a database, we can see that only the production schema is
// present (debug into this method or view the console output listing only production elements)
PublishProductionDacpacAndVerifyContents(productionPackagePath);
}
private static void Main(string[] args)
{
if (args.Length < 1 || CheckArg(args[0], "?"))
{
PrintArgs();
return;
}
if (!File.Exists(args[0]))
{
Console.WriteLine("File: \"{0}\" does not exist :(", args[0]);
PrintArgs();
return;
}
var model = new TSqlModel(args[0], DacSchemaModelStorageType.File);
var table = model.GetObjects(DacQueryScopes.All, ModelSchema.Table);
var sqlObjects = table as TSqlObject[] ?? table.ToArray();
if (!sqlObjects.Any())
{
Console.WriteLine("Model does not contain any tables :(");
return;
}
ShowColumnsDataType(sqlObjects.First());
//to do roughly the same thing in linq but specifying a column name:
var dataType = table.First()
.GetReferencedRelationshipInstances(Table.Columns)
.First(p => p.ObjectName.ToString() == "[dbo].[AWBuildVersion].[SystemInformationID]")
.Object.GetReferenced(Column.DataType)
.First()
.Name;
}
private IEnumerable<TSqlObject> GetTablesViewsAndSchemas(TSqlModel model)
{
return model.GetObjects(DacQueryScopes.UserDefined,
Table.TypeClass,
View.TypeClass,
Schema.TypeClass);
}
private TSqlModel CreateFilteredModel(SchemaBasedFilter schemaFilter, TSqlModel model)
{
ModelFilterer modelFilterer = new ModelFilterer(schemaFilter);
TSqlModel filteredModel = modelFilterer.CreateFilteredModel(model);
_trash.Add(filteredModel);
return filteredModel;
}
private int CountTablesViewsAndSchemas(TSqlModel model)
{
return GetTablesViewsAndSchemas(model).Count();
}
private void BuildPackage(TSqlModel model, string path)
{
DacPackageExtensions.BuildPackage(path, model, new PackageMetadata());
}
private void AssertAllObjectsHaveSchemaName(TSqlModel model, string schema)
{
// Looks for any named object that doesn't have the specified schema name
foreach (TSqlObject tsqlObject in GetTablesViewsAndSchemas(model))
{
if (tsqlObject.Name.HasName && tsqlObject.Name.Parts.Count > 0)
{
Assert.AreEqual(schema, tsqlObject.Name.Parts[0], "Expected all objects to have schema {0} after filter", schema);
}
}
}
