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 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(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 AddFilesToModel(string fileName)
{
string FileContent = string.Empty;
using (var reader = new StreamReader(fileName))
{
FileContent += reader.ReadToEnd();
}
_Model.AddObjects(FileContent);
}
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("CREATE TABLE [dbo].[t1] (c1 INT NOT NULL)");
model.AddObjects("CREATE UNIQUE CLUSTERED INDEX Idx1 ON [dbo].[t1] (c1)");
DacPackageExtensions.BuildPackage(_dacpacPath, model, new PackageMetadata());
}
}
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)");
model.AddObjects("CREATE UNIQUE CLUSTERED INDEX Idx1 ON [dbo].[t1] (c1)");
DacPackageExtensions.BuildPackage(_dacpacPath, model, new PackageMetadata());
}
}
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);
}
/// <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);
}
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);
}
}
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());
}
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 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 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);
}
private static void AddScriptsToModel(TSqlModel model, IEnumerable <string> scripts)
{
foreach (string script in scripts)
{
model.AddObjects(script);
}
}
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());
}
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 AddObjects(IEnumerable <TSqlObject> objects, string schemaOwnerUser)
{
var names = new List <string>(SqlModel.GetObjects(DacQueryScopes.UserDefined).Where(o => o.Name.HasName).Select(o => o.Name.ToString()));
foreach (var obj in objects)
{
if (obj.TryGetScript(out var script))
{
if (obj.IsSchema() && script.Contains("[dbo]") && (schemaOwnerUser ?? "dbo").ToLower(CultureInfo.InvariantCulture) != "dbo")
{
script = script.Replace("[dbo]", $"[{schemaOwnerUser}]");
}
if (!obj.Name.HasName || !names.Contains(obj.Name.ToString()))
{
SqlModel.AddObjects(script);
if (obj.Name.HasName)
{
names.Add(obj.Name.ToString());
}
}
}
else
{
_logger.Debug("AddObjects - Unable to get script from SQL object {Name} {Type}", obj.Name, obj.ObjectType.Name);
}
}
}
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();
}
protected TSqlModel ModelFromSql(string sql)
{
var model = new TSqlModel(SqlServerVersion.Sql130, new TSqlModelOptions());
model.AddObjects(sql);
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 Build()
{
Stopwatch stopwatch = Stopwatch.StartNew();
_log.Info("Build started.");
LoadDacProfile();
if (!Enum.TryParse(_options.SqlServerVersion, out SqlServerVersion sqlServerVersion))
{
sqlServerVersion = SqlServerVersion.Sql150;
}
TSqlModel model = new TSqlModel(sqlServerVersion, new TSqlModelOptions {
});
//model.EngineVersion = _options.EngineVersion;
PackageMetadata meta = new PackageMetadata {
Name = _options.TargetDacVersion,
Version = _options.TargetDacVersion,
};
_log.Info("Loading build files...");
var sqlFiles = _project.GetBuildFiles().ToList();
foreach (var sqlFile in sqlFiles)
{
var sql = File.ReadAllText(sqlFile.PhysicalPath);
model.AddObjects(sql);
}
_log.Info("{0} file(s) added to build queue.", sqlFiles.Count);
using (Stream buffer = GetDacpacStream())
{
_log.Info("Building dacpac...");
DacPackageExtensions.BuildPackage(buffer, model, meta);
buffer.Seek(0, SeekOrigin.Begin);
_log.Success("Dacpac generated ({0}).", _options.GenerateDacPac ? "file" : "in-memory");
if (_options.GenerateScript)
{
GenerateScript(buffer);
}
}
_log.Success("\nBuild complete. Elapsed {0:F3}s.", stopwatch.Elapsed.TotalSeconds);
if (_options.GenerateDacPac)
{
_log.Success(" Dacpac: " + _dacpacFilename ?? "N/A");
}
if (_options.GenerateScript)
{
_log.Success(" Script: " + _scriptFileName ?? "N/A");
}
Environment.ExitCode = 0;
}
public void NotBalancedTranNames()
{
var model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
model.AddObjects(SaveTranMastHavePairRollbackTranTestData.TestData.SaveTransactionSampleProcedureNotBalanced);
var result = caService.Analyze(model);
Assert.AreEqual(1, result.Problems.Count);
}
public void TableWithGrantSelectPermission()
{
var model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
model.AddObjects(TableShouldHaveReaderRoleTestData.TestData.TableWithGrantSelectPermission);
var result = caService.Analyze(model);
Assert.AreEqual(0, result.Problems.Count);
}
public string Convert(CTable table)
{
string[] parts = { table.Schema.SchemaName, table.TableName };
var createTable = new CreateTableStatement();
///set schema and table name
createTable.SchemaObjectName = new SchemaObjectName();
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.Schema.SchemaName
});
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.TableName
});
//add columns
createTable.Definition = new TableDefinition();
foreach (var col in table.Column)
{
var dataType = new SqlDataTypeReference {
SqlDataTypeOption = GetSqlDataTypeOption(col.ColumnType)
};
if (col.ColumnLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = col.ColumnLength.ToString()
});
}
var column = new ColumnDefinition
{
ColumnIdentifier = new Identifier {
Value = col.ColumnName
},
DataType = dataType
};
createTable.Definition.ColumnDefinitions.Add(column);
}
//generate DDL
var script = new TSqlScript();
var batch = new TSqlBatch();
script.Batches.Add(batch);
batch.Statements.Add(createTable);
var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
dacpacModel.AddObjects(script);
var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
return(existing.GetScript());
}
public void TestStopDeployment()
{
// Given database name
string dbName = TestContext.TestName;
// Delete any existing artifacts from a previous run
TestUtils.DropDatabase(TestUtils.ServerConnectionString, dbName);
// When deploying using the deployment stopping contributor
try
{
DacDeployOptions options = new DacDeployOptions
{
AdditionalDeploymentContributors = DeploymentStoppingContributor.ContributorId
};
// Deploy initial schema, should pass as no data motion
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
dacServices.Deploy(dacpac, dbName, false, options);
}
// Create schema that will cause data motion by adding column before existing one
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, null))
{
model.AddObjects("CREATE TABLE [dbo].[t1] (motion int NOT NULL, c1 INT NOT NULL PRIMARY KEY)");
DacPackageExtensions.BuildPackage(_dacpacPath, model, new PackageMetadata());
}
// Attempt to deploy and verify it fails as there's now data motion blocking it
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
try
{
dacServices.GenerateDeployScript(dacpac, dbName, options);
Assert.Fail("Expected Deployment to fail and exception to be thrown");
}
catch (DacServicesException expectedException)
{
Assert.IsTrue(expectedException.Message.Contains(DeploymentStoppingContributor.ErrorViaPublishMessage),
"Expected Severity.Error message passed to base.PublishMessage to block deployment");
Assert.IsTrue(expectedException.Message.Contains(DeploymentStoppingContributor.ErrorViaThrownException),
"Expected thrown exception to block deployment");
}
}
}
finally
{
TestUtils.DropDatabase(TestUtils.ServerConnectionString, dbName);
}
}
/// <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);
}
}
/// <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(CreateOneTable);
DacPackageExtensions.BuildPackage(_dacpacPath, model, new PackageMetadata());
}
}
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());
}
}
public void AddFilesToModel()
{
foreach (string FileName in _TestFiles)
{
String FileContent = "";
using (var reader = new StreamReader(FileName))
{
FileContent += reader.ReadToEnd();
}
_Model.AddObjects(FileContent);
}
}
private static void CopyFromTheModel(TSqlModel model)
{
// Copy all tables from 1 model to another - could be useful for filtering, say when you load from 1 model
// And copy to another
using (TSqlModel copiedModel = new TSqlModel(SqlServerVersion.Sql110, null))
{
foreach (var table in model.GetObjects(DacQueryScopes.Default, Table.TypeClass))
{
TSqlScript script;
if (table.TryGetAst(out script))
{
copiedModel.AddObjects(script);
}
}
}
}
public void TableWithNoGrantSelectPermission()
{
var model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
model.AddObjects(TableShouldHaveReaderRoleTestData.TestData.TableWithNoGrantSelectPermission);
var result = caService.Analyze(model);
Assert.AreEqual(1, result.Problems.Count);
var problem = result.Problems[0];
Assert.AreEqual(ruleId, problem.RuleId);
Assert.AreEqual(SqlRuleProblemSeverity.Warning, problem.Severity);
Assert.IsTrue(model.CollationComparer.Equals(new ObjectIdentifier("dbo", "T1"), problem.ModelElement.Name));
}
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 Run()
{
TSqlModel tm01 = new TSqlModel(folderPath01 + file01);
TSqlModel newModel = new TSqlModel(tm01.Version, tm01.CopyModelOptions());
List <TSqlObject> objs = tm01.GetObjects(DacQueryScopes.UserDefined).ToList();
List <TSqlObject> delt = new List <TSqlObject>();
foreach (var r in objs.Where(x => x.ObjectType.Name == "Schema"))
{
delt.Add(r);
AddChildren(r, delt);
}
objs.RemoveAll(z => delt.Contains(z));
foreach (TSqlObject o in objs.Where(z => !delt.Contains(z)))
{
if (o.TryGetScript(out string s))
{
newModel.AddObjects(s);
}
}
PackageMetadata m = new PackageMetadata()
{
Name = "Nazwa",
Version = "1.0",
Description = ""
};
DacPackageExtensions.BuildPackage
(
@"C:\Users\XTOKO\Desktop\NewDacpacSchemaFilter.dacpac"
, newModel
, m
);
Console.WriteLine("==> koniec");
Console.ReadLine();
}
private static void Build(string projectPath, string outputPath, SqlServerVersion targetVersion)
{
var outputFilePath = EnusreOutputPath(projectPath, outputPath);
IEnumerable <string> dbFiles = GetInputFiles(projectPath);
var sqlModel = new TSqlModel(targetVersion, new TSqlModelOptions()
{
});
foreach (var scriptFile in dbFiles)
{
Console.WriteLine($"Loading file into model: {scriptFile}");
var script = File.ReadAllText(scriptFile);
sqlModel.AddObjects(script);
}
Console.WriteLine($"Building Dac Package to {outputFilePath}");
DacPackageExtensions.BuildPackage(outputFilePath, sqlModel, new PackageMetadata()
{
});
}
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);
}
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 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 Remove(IEnumerable <string> objectNames)
{
var options = SqlModel.CloneModelOptions();
var newModel = new TSqlModel(SqlModel.Version, options);
foreach (var obj in SqlModel.GetObjects(DacQueryScopes.UserDefined).Where(o => !o.Name.HasName || !objectNames.Contains(o.Name.ToString())))
{
if (obj.TryGetScript(out var script))
{
newModel.AddObjects(script);
}
else
{
_logger.Debug("Remove - Unable to get script from SQL object {Name} {Type}", obj.Name, obj.ObjectType.Name);
}
}
lock (SqlModel)
{
SqlModel?.Dispose();
SqlModel = newModel;
}
}
public void TestIndexIncludedColumns()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()
{
}))
{
model.AddObjects(@"CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
col1 int not null,
[c] AS (Id + ' ')
);
GO
CREATE INDEX ix_1 on Table1 (c) INCLUDE ( col1)
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var index = typedModel.GetObject <TSqlIndex>(new ObjectIdentifier("dbo", "Table1", "ix_1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(index, "Index should not be null");
Assert.AreEqual(1, index.IncludedColumns.Count(), "Incorrect number of included columns");
}
}
public void TestIndexIncludedColumns()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(@"CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
col1 int not null,
[c] AS (Id + ' ')
);
GO
CREATE INDEX ix_1 on Table1 (c) INCLUDE ( col1)
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var index = typedModel.GetObject<TSqlIndex>(new ObjectIdentifier("dbo", "Table1", "ix_1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(index, "Index should not be null");
Assert.AreEqual(1, index.IncludedColumns.Count(), "Incorrect number of included columns");
}
}
private void AddScriptsToModel(TSqlModel model, IEnumerable<string> scripts)
{
foreach (string script in scripts)
{
model.AddObjects(script);
}
}
public void TestParamterWithUserDefinedTypes()
{
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 TYPE [dbo].[UserDefinedTableType1] AS TABLE
(
Id SSN PRIMARY KEY,
Name VARCHAR(128) ,
CustomUDT SqlUserDefinedType1 NULL,
MI char(1) DEFAULT (' '),
UniqueValue int UNIQUE,
ComputedColumn AS ( Name + ' ' + MI + ' ' + UniqueValue)
)
GO
CREATE PROC TestUDT @UDDT SSN, @UDT SqlUserDefinedType1, @UDTT UserDefinedTableType1 READONLY
AS
BEGIN
SELECT * from @UDTT
END;
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
TSqlProcedure proc = typedModel.GetObject<TSqlProcedure>(new ObjectIdentifier("dbo","TestUDT"), DacQueryScopes.UserDefined);
Assert.IsNotNull(proc, "Missing procedure TestUDT");
var parameters = proc.Parameters.ToList();
Assert.AreEqual(3, parameters.Count, "Incorrect number of parameters");
var uddtParameter = parameters[0];
var parameterName = "@UDDT";
var parameterSqlDataType = SqlDataType.Unknown;
var baseSqlType = SqlDataType.VarChar;
var parameterDataType = uddtParameter.DataType.SingleOrDefault() as TSqlDataTypeReference;
Assert.IsNotNull(parameterDataType, "parameter data type should not be null");
Assert.AreEqual(parameterSqlDataType, parameterDataType.SqlDataType, "{0} data type should be based off {1}", parameterName, parameterSqlDataType);
var baseType = parameterDataType.Type.SingleOrDefault();
Assert.IsNotNull(baseType, "parameter base type should not be null");
Assert.AreEqual(baseSqlType, baseType.SqlDataType, "Incorrect SQLDataType for base type");
var udtParameterType = parameters[1].DataType.SingleOrDefault() as TSqlUserDefinedTypeReference;
Assert.IsNotNull(udtParameterType, "UDT Parameter data type should be of type TSqlUserDefinedType" );
Assert.AreEqual("SqlUserDefinedType1", udtParameterType.Name.Parts[1]);
var udttParameterType = parameters[2].DataType.SingleOrDefault() as TSqlTableTypeReference;
Assert.IsNotNull(udttParameterType, "Parameter @UDTT' data type should not be null");
Assert.AreEqual("UserDefinedTableType1", udttParameterType.Name.Parts[1], "Incorrect table type name");
}
}
/// <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;
}
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;
}
private static void CopyFromTheModel(TSqlModel model)
{
// Copy all tables from 1 model to another - could be useful for filtering, say when you load from 1 model
// And copy to another
using (TSqlModel copiedModel = new TSqlModel(SqlServerVersion.Sql110, null))
{
foreach (var table in model.GetObjects(DacQueryScopes.Default, Table.TypeClass))
{
TSqlScript script;
if (table.TryGetAst(out script))
{
copiedModel.AddObjects(script);
}
}
}
}
public void TestPartitionFunction()
{
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 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);
}
}