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 TestTableType()
        {
            using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions()
            {
            }))
            {
                model.AddObjects(@"CREATE TYPE [dbo].[UserDefinedTableType1] AS TABLE
(
	Id INT PRIMARY KEY, 
	Name VARCHAR(128) CHECK( Id <> ' '),
	MI char(1) DEFAULT (' '),
	UniqueValue int UNIQUE,
    ComputedColumn AS ( Name + ' ' + MI + ' ' + UniqueValue),
	INDEX IX_1 (Name)
)");


                var tableType1 = model.GetObject <TSqlTableType>(
                    new ObjectIdentifier("dbo", "UserDefinedTableType1"), DacQueryScopes.UserDefined);
                Assert.IsNotNull(tableType1, "table type should not be null");
                Assert.AreEqual(4, tableType1.Constraints.Count(), "Incorrect number of constraints");
                Assert.AreEqual(1, tableType1.DefaultConstraints.Count(), "Incorrect number of Default constraints");
                Assert.AreEqual(1, tableType1.CheckConstraints.Count(), "Incorrect number of Check constraints");
                Assert.AreEqual(1, tableType1.PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key constraints");
                Assert.AreEqual(1, tableType1.UniqueConstraints.Count(), "Incorrect number of Unique Key constraints");
                Assert.AreEqual(1, tableType1.Indexes.Count(), "Incorrect number of indexes");
                Assert.AreEqual(5, tableType1.Columns.Count(), "Incorrect number of columns");
                TSqlTableTypeColumn computedColumn = tableType1.Columns
                                                     .SingleOrDefault(c => c.Name.Parts[2] == "ComputedColumn");
                Assert.IsNotNull(computedColumn, "computed column ComputedColumn missing");
                Assert.AreEqual(3, computedColumn.ExpressionDependencies.Count(), "incorrect number of dependencies");
            }
        }
Esempio n. 3
0
        private ProcedureModel GetStoredProcedures(string dacpacPath, ProcedureModelFactoryOptions options)
        {
            var result = new List <RevEng.Core.Abstractions.Metadata.Procedure>();
            var errors = new List <string>();

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

            var consolidator = new DacpacConsolidator();

            dacpacPath = consolidator.Consolidate(dacpacPath);

            var model = new TSqlTypedModel(dacpacPath);

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

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

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

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

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

                    result.Add(procedure);
                }
            }

            return(new ProcedureModel
            {
                Procedures = result,
                Errors = errors,
            });
        }
Esempio n. 4
0
        public SqlDacFxDiagram(string pathToDacPac, DiagramFormat diagramFormat)
        {
            model   = new TSqlTypedModel(pathToDacPac);
            diagram = new StringBuilder();
            format  = diagramFormat;


            if (format == DiagramFormat.GraphViz)
            {
                // Add the file header
                diagram.Append(Resources.GraphVizHeader);

                OutputDiagramSchemaDef();
                OutputDiagramRelationships();

                // Add the file footer
                diagram.Append(Resources.GraphVizFooter);
            }
            else if (format == DiagramFormat.PlantUML)
            {
                // Add the file header
                diagram.Append(Resources.PlantUMLHeader);

                // Add the file footer
                diagram.Append(Resources.PlantUMLFooter);
            }
        }
Esempio n. 5
0
        public List <Tuple <string, bool, List <string> > > GetTableDefinitions()
        {
            var consolidator = new DacpacConsolidator();

            var dacpacPath = consolidator.Consolidate(_dacpacPath);

            using (var model = new TSqlTypedModel(dacpacPath))
            {
                var result = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                             .Select(m => new Tuple <string, bool, List <string> >($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]",
                                                                                   m.PrimaryKeyConstraints.Any(),
                                                                                   m.Columns.Where(i => !i.GetProperty <bool>(Column.IsHidden) &&
                                                                                                   i.ColumnType != ColumnType.ColumnSet &&
                                                                                                   i.ColumnType != ColumnType.ComputedColumn)
                                                                                   .Select(c => c.Name.Parts[2])
                                                                                   .ToList()))
                             .OrderBy(m => m.Item1)
                             .ToList();

                var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
                            .Select(m => new Tuple <string, bool, List <string> >($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", false, null))
                            .OrderBy(m => m.Item1)
                            .ToList();

                result = result.Concat(views).ToList();

                return(result);
            }
        }
        public DatabaseModel Create(string dacpacPath, DatabaseModelFactoryOptions options)
        {
            if (string.IsNullOrEmpty(dacpacPath))
            {
                throw new ArgumentException(@"invalid path", nameof(dacpacPath));
            }
            if (!File.Exists(dacpacPath))
            {
                throw new ArgumentException("Dacpac file not found");
            }

            var schemas = options.Schemas;
            var tables  = options.Tables;

            var dbModel = new DatabaseModel
            {
                DatabaseName  = Path.GetFileNameWithoutExtension(dacpacPath),
                DefaultSchema = schemas.Count() > 0 ? schemas.First() : "dbo"
            };
            //Sequences not created - not needed for scaffolding

            var model = new TSqlTypedModel(dacpacPath);

            var typeAliases = GetTypeAliases(model, dbModel);

            var items = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                        .Where(t => !t.GetProperty <bool>(Table.IsAutoGeneratedHistoryTable))
                        .Where(t => tables == null || !tables.Any() || tables.Contains($"[{t.Name.Parts[0]}].[{t.Name.Parts[1]}]"))
                        .Where(t => $"{t.Name.Parts[1]}" != HistoryRepository.DefaultTableName)
                        .ToList();

            foreach (var item in items)
            {
                var dbTable = new DatabaseTable
                {
                    Name   = item.Name.Parts[1],
                    Schema = item.Name.Parts[0],
                };

                if (item.MemoryOptimized)
                {
                    dbTable["SqlServer:MemoryOptimized"] = true;
                }

                GetColumns(item, dbTable, typeAliases, model.GetObjects <TSqlDefaultConstraint>(DacQueryScopes.UserDefined).ToList());
                GetPrimaryKey(item, dbTable);

                dbModel.Tables.Add(dbTable);
            }

            foreach (var item in items)
            {
                GetForeignKeys(item, dbModel);
                GetUniqueConstraints(item, dbModel);
                GetIndexes(item, dbModel);
            }

            return(dbModel);
        }
        public List <TableInformation> GetTableDefinitions()
        {
            var model = new TSqlTypedModel(_dacpacPath);

            return(model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                   .Select(m => new TableInformation(m.Name.Parts[0], m.Name.Parts[1], m.PrimaryKeyConstraints.Any()))
                   .ToList());
        }
Esempio n. 8
0
        public void Init()
        {

            if (File.Exists(Path.Combine(Directories.GetSampleSolution(), @"NestedProjects\Nested\bin\Debug\Nested.dacpac")))
                _model = Model.Get(Path.Combine(Directories.GetSampleSolution(), @"NestedProjects\Nested\bin\Debug\Nested.dacpac"));
            else
                _model = Model.Get(Path.Combine(Directories.GetSampleSolution(), @"NestedProjects\Nested\bin\Release\Nested.dacpac"));
        }
        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 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");
            }
        }
Esempio n. 11
0
        public List <TableInformationModel> GetTableDefinitions()
        {
            using (var model = new TSqlTypedModel(_dacpacPath))
            {
                var result = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                             .Select(m => new TableInformationModel($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", m.PrimaryKeyConstraints.Any(), false))
                             .ToList();

                return(result);
            }
        }
Esempio n. 12
0
 public void Init()
 {
     if (File.Exists(Path.Combine(Directories.GetSampleSolution(), @"NestedProjects\Nested\bin\Debug\Nested.dacpac")))
     {
         _model = Model.Get(Path.Combine(Directories.GetSampleSolution(), @"NestedProjects\Nested\bin\Debug\Nested.dacpac"));
     }
     else
     {
         _model = Model.Get(Path.Combine(Directories.GetSampleSolution(), @"NestedProjects\Nested\bin\Release\Nested.dacpac"));
     }
 }
        public List <string> GetTableNames()
        {
            var names = new List <string>();
            var model = new TSqlTypedModel(_dacpacPath);

            var tables = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                         .Where(t => t.PrimaryKeyConstraints.Count() > 0)
                         .ToList();

            foreach (var table in tables)
            {
                names.Add($"{table.Name.Parts[0]}.{table.Name.Parts[1]}");
            }
            return(names);
        }
        private IReadOnlyDictionary <string, (string, string)> GetTypeAliases(TSqlTypedModel model, DatabaseModel dbModel)
        {
            var items = model.GetObjects <TSqlDataType>(DacQueryScopes.UserDefined)
                        .ToList();

            var typeAliasMap = new Dictionary <string, (string, string)>(StringComparer.OrdinalIgnoreCase);

            foreach (var udt in items)
            {
                int maxLength = udt.UddtIsMax ? -1 : udt.UddtLength;
                var storeType = GetStoreType(udt.Type.First().Name.Parts[0], maxLength, udt.UddtPrecision, udt.UddtScale);
                typeAliasMap.Add($"{udt.Name.Parts[0]}.{udt.Name.Parts[1]}", (storeType, udt.Type.First().Name.Parts[0]));
            }

            return(typeAliasMap);
        }
Esempio n. 15
0
        public List <TableInformationModel> GetTableDefinitions(bool includeViews = false)
        {
            var model  = new TSqlTypedModel(_dacpacPath);
            var result = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                         .Select(m => new TableInformationModel(m.Name.Parts[0] + "." + m.Name.Parts[1], m.PrimaryKeyConstraints.Any()))
                         .ToList();

            if (includeViews)
            {
                var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
                            .Select(m => new TableInformationModel(m.Name.Parts[0] + "." + m.Name.Parts[1], false))
                            .ToList();
                result = result.Concat(views).ToList();
            }

            return(result);
        }
        public void TestFullTextIndex()
        {
            using (var model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
            {

                string createIndex = @"CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID)";
                string createCatalog = "CREATE FULLTEXT CATALOG ft AS DEFAULT";
                string createFTIIndex = @"
            CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume)
               KEY INDEX ui_ukJobCand
               WITH STOPLIST = SYSTEM";
                model.AddObjects(createIndex);
                model.AddObjects(createCatalog);
                model.AddObjects(createFTIIndex);

                var indexes = model.GetObjects<TSqlFullTextIndex>(DacQueryScopes.UserDefined).ToList();
                Assert.AreEqual(1, indexes.Count, "Incorrect number of full Text Indexes");
                TSqlFullTextIndex ftIndex = indexes[0];
                var ftColumns = ftIndex.Columns.ToList();
                Assert.AreEqual(1, ftColumns.Count, "Incorrect number of 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");
            }
        }
        public List <Tuple <string, bool> > GetTableDefinitions()
        {
            var consolidator = new DacpacConsolidator();

            var dacpacPath = consolidator.Consolidate(_dacpacPath);

            using (var model = new TSqlTypedModel(dacpacPath))
            {
                var result = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                             .Select(m => new Tuple <string, bool>($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", m.PrimaryKeyConstraints.Any()))
                             .OrderBy(m => m.Item1)
                             .ToList();

                var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
                            .Select(m => new Tuple <string, bool>($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", false))
                            .OrderBy(m => m.Item1)
                            .ToList();

                result = result.Concat(views).ToList();

                return(result);
            }
        }
Esempio n. 19
0
        public List <TableInformationModel> GetTableDefinitions(bool includeViews)
        {
            var consolidator = new DacpacConsolidate.DacpacConsolidator();

            var dacpacPath = consolidator.Consolidate(_dacpacPath);

            using (var model = new TSqlTypedModel(dacpacPath))
            {
                var result = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                             .Select(m => new TableInformationModel($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", m.PrimaryKeyConstraints.Any(), false))
                             .ToList();

                if (includeViews)
                {
                    var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
                                .Select(m => new TableInformationModel($"[{m.Name.Parts[0]}].[{m.Name.Parts[1]}]", true, true))
                                .ToList();

                    result = result.Concat(views).ToList();
                }

                return(result);
            }
        }
        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");
            }
        }
Esempio n. 21
0
        private static void Main(string[] args)
        {
            if (!CheckArgs(args))
                return;

            var model = new TSqlTypedModel(args[0]);
            var outputFolder = args[1];

            var schemaDir = CreateDirectory(outputFolder, "Schema");
            var procDir = CreateDirectory(outputFolder, "Tests");

            var procedures = model.GetObjects<TSqlProcedure>(DacQueryScopes.UserDefined);
            foreach (var procedure in procedures)
            {
                var schema = GenerateSchema(procedure);

                var test = GenerateTest(procedure);

                using (var writer = new StreamWriter(Path.Combine(schemaDir, procedure.Name.Parts.Last() + ".sql")))
                {
                    writer.Write(schema);
                }

                using (
                    var writer =
                        new StreamWriter(Path.Combine(procDir,
                            procedure.Name.Parts.Last() + "." + "test To Be Implemented.sql")))
                {
                    writer.Write(test);
                }
            }
        }
        private static RoutineModel GetStoredProcedures(string dacpacPath, ModuleModelFactoryOptions options, bool mergeDacpacs)
        {
            var result = new List <RevEng.Core.Abstractions.Metadata.Procedure>();
            var errors = new List <string>();

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

            using var model = new TSqlTypedModel(dacpacPath);

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

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

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

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

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

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

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

                    result.Add(procedure);
                }
            }

            return(new RoutineModel
            {
                Routines = result.Cast <Routine>().ToList(),
                Errors = errors,
            });
        }
        public 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);
            }
        }
Esempio n. 24
0
        public DatabaseModel Create(string dacpacPath, DatabaseModelFactoryOptions options)
        {
            if (string.IsNullOrEmpty(dacpacPath))
            {
                throw new ArgumentException(@"invalid path", nameof(dacpacPath));
            }
            if (!File.Exists(dacpacPath))
            {
                throw new ArgumentException("Dacpac file not found");
            }

            var schemas = options.Schemas;
            var tables  = options.Tables;

            var dbModel = new DatabaseModel
            {
                DatabaseName  = Path.GetFileNameWithoutExtension(dacpacPath),
                DefaultSchema = schemas.Count() > 0 ? schemas.First() : "dbo"
            };

            dbModel["Scaffolding:ConnectionString"] = $"Data Source=(local);Initial Catalog={dbModel.DatabaseName};Integrated Security=true";

            //Sequences not created - not needed for scaffolding

            var consolidator = new DacpacConsolidator();

            dacpacPath = consolidator.Consolidate(dacpacPath);

            var model = new TSqlTypedModel(dacpacPath);

            var typeAliases = GetTypeAliases(model, dbModel);

            var items = model.GetObjects <TSqlTable>(DacQueryScopes.UserDefined)
                        .Where(t => !t.GetProperty <bool>(Table.IsAutoGeneratedHistoryTable))
                        .Where(t => tables == null || !tables.Any() || tables.Contains($"[{t.Name.Parts[0]}].[{t.Name.Parts[1]}]"))
                        .Where(t => $"{t.Name.Parts[1]}" != HistoryRepository.DefaultTableName)
                        .ToList();

            foreach (var item in items)
            {
                var dbTable = new DatabaseTable
                {
                    Name   = item.Name.Parts[1],
                    Schema = item.Name.Parts[0],
                };

                if (item.MemoryOptimized)
                {
                    dbTable["SqlServer:MemoryOptimized"] = true;
                }

                GetColumns(item, dbTable, typeAliases, model.GetObjects <TSqlDefaultConstraint>(DacQueryScopes.UserDefined).ToList(), model);
                GetPrimaryKey(item, dbTable);

                var description = model.GetObjects <TSqlExtendedProperty>(DacQueryScopes.UserDefined)
                                  .Where(p => p.Name.Parts.Count == 4)
                                  .Where(p => p.Name.Parts[0] == "SqlTableBase")
                                  .Where(p => p.Name.Parts[1] == dbTable.Schema)
                                  .Where(p => p.Name.Parts[2] == dbTable.Name)
                                  .Where(p => p.Name.Parts[3] == "MS_Description")
                                  .FirstOrDefault();

                dbTable.Comment = FixExtendedPropertyValue(description?.Value);

                dbModel.Tables.Add(dbTable);
            }

            foreach (var item in items)
            {
                GetForeignKeys(item, dbModel);
                GetUniqueConstraints(item, dbModel);
                GetIndexes(item, dbModel);
            }

            var views = model.GetObjects <TSqlView>(DacQueryScopes.UserDefined)
                        .Where(t => tables == null || !tables.Any() || tables.Contains($"[{t.Name.Parts[0]}].[{t.Name.Parts[1]}]"))
                        .ToList();

            foreach (var view in views)
            {
                var dbView = new DatabaseTable
                {
                    Name   = view.Name.Parts[1],
                    Schema = view.Name.Parts[0],
                };

                GetViewColumns(view, dbView, typeAliases);

                dbModel.Tables.Add(dbView);
            }

            return(dbModel);
        }
        public void TestTableType()
        {
            using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
            {
                model.AddObjects(@"CREATE TYPE [dbo].[UserDefinedTableType1] AS TABLE
            (
            Id INT PRIMARY KEY,
            Name VARCHAR(128) CHECK( Id <> ' '),
            MI char(1) DEFAULT (' '),
            UniqueValue int UNIQUE,
            ComputedColumn AS ( Name + ' ' + MI + ' ' + UniqueValue),
            INDEX IX_1 (Name)
            )");

                var tableType1 = model.GetObject<TSqlTableType>(
                    new ObjectIdentifier("dbo", "UserDefinedTableType1"), DacQueryScopes.UserDefined);
                Assert.IsNotNull(tableType1, "table type should not be null");
                Assert.AreEqual(4, tableType1.Constraints.Count(), "Incorrect number of constraints");
                Assert.AreEqual(1, tableType1.DefaultConstraints.Count(), "Incorrect number of Default constraints");
                Assert.AreEqual(1, tableType1.CheckConstraints.Count(), "Incorrect number of Check constraints");
                Assert.AreEqual(1, tableType1.PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key constraints");
                Assert.AreEqual(1, tableType1.UniqueConstraints.Count(), "Incorrect number of Unique Key constraints");
                Assert.AreEqual(1, tableType1.Indexes.Count(), "Incorrect number of indexes");
                Assert.AreEqual(5, tableType1.Columns.Count(), "Incorrect number of columns");
                TSqlTableTypeColumn computedColumn = tableType1.Columns
                    .SingleOrDefault(c => c.Name.Parts[2] == "ComputedColumn");
                Assert.IsNotNull(computedColumn, "computed column ComputedColumn missing");
                Assert.AreEqual(3, computedColumn.ExpressionDependencies.Count(), "incorrect number of dependencies");
            }
        }
        public void TestTableValuedFunction()
        {
            string assemblyScript = @"CREATE ASSEMBLY [CLR_TVF]
            AUTHORIZATION [dbo]
            FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103006D425E550000000000000000E00002210B010B000008000000060000000000002E270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000D42600005700000000400000A002000000000000000000000000000000000000006000000C0000009C2500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000034070000002000000008000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E0000000000000000000000000000400000420000000000000000000000000000000010270000000000004800000002000500C0200000DC0400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004003600000001000011000214FE0116FE010C082D0A00168D0A0000010B2B1E02178D0B0000010D0916039D096F0600000A0A06280100002B00060B2B00072A00001330020016000000020000110002740A0000010A0306280800000A730900000A512A1E02280A00000A2A000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000098010000237E000004020000E801000023537472696E677300000000EC0300000800000023555300F403000010000000234755494400000004040000D800000023426C6F620000000000000002000001471502000908000000FA253300160000010000000D0000000200000003000000040000000A000000040000000200000001000000020000000100000000000A00010000000000060037003000060051003E000A00890074000600D900BA000600F900E60017000D01000006003C011C0106005C011C010A009D0182010600B20130000600B90130000600C40130000A00CF0174000000000001000000000001000100010010001600000005000100010050200000000096005D000A0001009420000000009600920011000300B6200000000086189A001900050000000100A00000000200A60000000100B00002000200B40021009A00190029009A001D0039009A00230041009A00190049009A0019005100BE0178006100CA017F006900D901950019009A009B0009009A00190020002B0028002E001300A5002E001B00AE002E002300B7008B00A1000480000000000000000000000000000000007A010000040000000000000000000000010027000000000004000000000000000000000001006800000000000F00870000000000003C4D6F64756C653E00434C525F5456462E646C6C00546162756C61724576656E744C6F6742006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C6500496E69744D6574686F640053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730046696C6C526F77002E63746F7200696E70757400736570617261746F72006F626A0076616C75650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C525F545646004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500537472696E6700436861720053706C697400417272617900536F72740053716C537472696E67006F705F496D706C696369740000000000032000000000006A03DD2B8352F240A89BC91F5A7890600008B77A5C561934E08906000212090E03070002011C10120D0320000105200101111904200101084F01000200540E1146696C6C526F774D6574686F644E616D650D53706C6974416E644F72646572540E0F5461626C65446566696E6974696F6E165B76616C75655D206E766172636861722834303030290620011D0E1D0307100101011D1E00030A010E0907041D0E1209021D0305000111350E0520010111350307010E0801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000006D425E5500000000020000001C010000B8250000B807000052534453FC017B641174E84696DA41D68EB73EFA03000000633A5C55736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C434C52205456535C434C52205456535C6F626A5C44656275675C434C525F5456462E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FC26000000000000000000001E270000002000000000000000000000000000000000000000000000102700000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000C00010049006E007400650072006E0061006C004E0061006D006500000043004C0052005F005400560046002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C0052005F005400560046002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000303700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;";

            string functionScript = @"
            CREATE FUNCTION [dbo].[SplitAndOrder]
            (@input NVARCHAR (MAX), @separator NCHAR (1))
            RETURNS
             TABLE (
            [value] NVARCHAR (4000) NULL)
            ORDER ([value])
            AS
             EXTERNAL NAME [CLR_TVF].[TabularEventLogB].[InitMethod]";

            using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
            {
                model.AddObjects(assemblyScript);
                model.AddObjects(functionScript);
                var function = model.GetObject<TSqlTableValuedFunction>(new ObjectIdentifier("dbo", "SplitAndOrder"), DacQueryScopes.UserDefined);
                Assert.AreEqual(1, function.Columns.Count(), "Incorrect number of columns");
                Assert.AreEqual(1, function.TableOption.First().OrderColumns.Count(), "Incorrect number of Order columns");
                ClrTableOptionOrderColumnsReference orderColumn = function.TableOption.First().OrderColumns.First();
                Assert.AreEqual("value", orderColumn.Name.Parts[2], "Incorrect number of Order columns");
                Assert.AreEqual(true, orderColumn.Ascending, "Incorrect Ascending metadata value");
                //validate Function Metadata property
                Assert.AreEqual(FunctionType.MultiStatementTableValuedFunction, function.FunctionType, "Incorrect function type");
                Assert.AreEqual(FunctionType.MultiStatementTableValuedFunction, function.GetMetadata<FunctionType>(TableValuedFunction.FunctionType), "Incorrect function type");

            }
        }
Esempio n. 27
0
 public static void Close(TSqlTypedModel model)
 {
     model.Dispose();
 }
        public void TestTableColumnMetadataProperties()
        {
            using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
            {
                string createTable = @"
            CREATE TABLE [dbo].[Table1]
            (
            [Id] INT NOT NULL,
            [two] AS (ID +1),
            columnSet1 int sparse,
            columnSet2 int sparse,
            cs xml column_set for all_sparse_columns
            )
            ";
                model.AddObjects(createTable);

                List<TSqlTable> tables = model.GetObjects<TSqlTable>(DacQueryScopes.Default).ToList();
                Assert.AreEqual(1, tables.Count(), "Incorrect number of tables");
                List<TSqlColumn> columns = tables[0].Columns.ToList();

                TSqlColumn col = columns[0];
                Assert.AreEqual(ColumnType.Column, col.ColumnType, "Incorrect ColumnType Metadata for column "  + col.Name.Parts[2]);
                col = columns[1];
                Assert.AreEqual(ColumnType.ComputedColumn, col.ColumnType, "Incorrect ColumnType Metadata for column " + col.Name.Parts[2]);
                col = columns[2];
                Assert.AreEqual(ColumnType.Column, col.ColumnType, "Incorrect ColumnType Metadata for column " + col.Name.Parts[2]);
                col = columns[3];
                Assert.AreEqual(ColumnType.Column, col.ColumnType, "Incorrect ColumnType Metadata for column " + col.Name.Parts[2]);
                col = columns[4];
                Assert.AreEqual(ColumnType.ColumnSet, col.ColumnType, "Incorrect ColumnType Metadata for column " + col.Name.Parts[2]);
            }
        }
        public void TestFullTextIndexWithTypeColumn()
        {
            using (var model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
            {

                string createIndex = @"CREATE FULLTEXT INDEX ON Production.Document
              (
              Title
              Language 1033,
              DocumentSummary
              Language 1033,
              Document
              TYPE COLUMN FileExtension
              Language 1033
              )
              KEY INDEX PK_Document_DocumentID
              WITH STOPLIST = SYSTEM, SEARCH PROPERTY LIST = DocumentPropertyList, CHANGE_TRACKING OFF, NO POPULATION";
                model.AddObjects(createIndex);

                var tc = Column.TypeClass;
                var indexes = model.GetObjects<TSqlFullTextIndex>(DacQueryScopes.UserDefined).ToList();
                Assert.AreEqual(1, indexes.Count, "Incorrect number of full Text Indexes");
                TSqlFullTextIndex ftIndex = indexes[0];
                var ftColumns = ftIndex.Columns.ToList();
                Assert.AreEqual(3, ftColumns.Count, "Incorrect number of columns");
            }
        }
        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");
            }
        }
        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 TestIndexColumnMetadata()
        {
            using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
            {
                string createTable = @"
            CREATE TABLE [dbo].[Table1]
            (
            [Id] INT NOT NULL,
            [two] AS (ID +1),
            columnSet1 int sparse,
            columnSet2 int sparse,
            cs xml column_set for all_sparse_columns
            )
            ";

                model.AddObjects(createTable);
                model.AddObjects("CREATE INDEX IX ON Table1 (Id ASC, two DESC)");

                TSqlIndex index = model.GetObject<TSqlIndex>(new ObjectIdentifier("dbo", "Table1", "IX"), DacQueryScopes.UserDefined);
                var columns = index.Columns.ToList();
                Assert.AreEqual(2, columns.Count, "Incorrect number of index columns");
                Assert.AreEqual(true, columns[0].Ascending, "Incorrect Ascending value");
                Assert.AreEqual(true, columns[0].GetMetadataProperty<bool>(Index.ColumnsRelationship.Ascending), "Incorrect Ascending value from GetMetadataProperty");
                Assert.AreEqual(false, columns[1].Ascending, "Incorrect Ascending value");
                Assert.AreEqual(false, columns[1].GetMetadataProperty<bool>(Index.ColumnsRelationship.Ascending), "Incorrect Ascending value from GetMetadataProperty");

            }
        }
 public void Init()
 {
     _model =
         Model.Get(Path.Combine(Directories.GetSampleSolution(),
                                @"NestedProjects\Nested\bin\Debug\Nested.dacpac"));
 }
 public void Setup()
 {
     dacpacQuirk = TestPath("TestDb.dacpac");
     dacpac      = TestPath("Chinook.dacpac");
     model       = new TSqlTypedModel(dacpac);
 }
        public void TestSignatures()
        {
            using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
            {
                model.AddObjects(@"CREATE CERTIFICATE cert_signature_demo
            ENCRYPTION BY PASSWORD = '******'
            WITH SUBJECT = 'ADD SIGNATURE demo';");
                model.AddObjects(@"CREATE PROC [sp_signature_demo]
            AS
            PRINT 'This is the content of the procedure.' ;
            GO");
                model.AddObjects(@"ADD SIGNATURE TO [sp_signature_demo]
            BY CERTIFICATE [cert_signature_demo]
            WITH PASSWORD = '******' ;");

                var signatures = model.GetObjects<TSqlSignature>( DacQueryScopes.UserDefined);
                var signature = signatures.First();
                TSqlSignatureEncryptionMechanism encryption = signature.EncryptionMechanism.FirstOrDefault() as TSqlSignatureEncryptionMechanism;
                Assert.IsNotNull(encryption, " Encryption Mechanism is null or missing");
                Assert.AreEqual(1, encryption.Certificate.Count(), "Missing Certificate on signature");
                TSqlCertificateReference certifcate = encryption.Certificate.First() as TSqlCertificateReference;
                Assert.AreEqual("ADD SIGNATURE demo", certifcate.Subject, "Incorrect Subject");
            }
        }