How to traverse a dacpac
c#, dacpac, sql-server
Solution
The way that we have found to do it is to find the property in object type using Linq, and then using the `GetProperty` method to get the value:
bool isNullable = (bool)column.GetProperty(column.ObjectType.Properties.Where(p => p.Name == "Nullable").First());
This still doesn't feel like the best option, so if someone else has a better answer please post it.
Problem
We are looking to upgrade our dbproj to a sqlproj so that we can point it to a new SQL 2012 database. We have a program at the moment that reads the .dbschema xml file to find all tables and columns and retrieve information from them. We use this data to build our own custom classes. The new sqlproj file now produces a dacpac which we want to interrigate to get out the data that we need. I have written the following to try and traverse the dacpac and get the information that I need: ``` using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.SqlServer.Dac; using Microsoft.SqlServer.Dac.Extensions; using Microsoft.SqlServer.Dac.Model; namespace DacPacReader { class Program { static void Main(string[] args) { using (System.IO.TextWriter writter = new System.IO.StreamWriter(@"c:\temp\output.txt")) { using (TSqlModel model = new TSqlModel(@"C:\temp\Data.dacpac")) { var allTables = model.GetObjects(DacQueryScopes.All, ModelSchema.Table); foreach (var table in allTables) { writter.WriteLine(table.Name); foreach (var column in table.GetChildren().Where(child => child.ObjectType.Name == "Column")) { writter.WriteLine("\t" + column.Name); writter.WriteLine("\tProperties:"); foreach (var property in column.ObjectType.Properties) { writter.WriteLine("\t\t" + property.Name + "\t\t" + property.DataType.FullName); } writter.WriteLine("\tMetadata:"); foreach (var metaData in column.ObjectType.Metadata) { writter.WriteLine("\t\t" + metaData.Name + "\t\t" + metaData.DataType.FullName); } } } } } } } } ``` I have no idea if I'm doing this the right way, or if there is a much better/easier way. I'm not sure what to search for on Google/S.E. and can't find any examples. I can see that the variable column has a non-public member called ContextObject which is a Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSimpleColumn. If I could access this object then I would be able to pull all the info I needed out of it. Table also has a similar ContextObject which would help me. Anyway, currently this opens the dacpac and retrieves all of the table and column names. An example of the data I get is: ``` [dbo].[User] [dbo].[User].[UserID] Properties: Collation System.String IsIdentityNotForReplication System.Boolean Nullable System.Boolean IsRowGuidCol System.Boolean Sparse System.Boolean Expression Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlScriptProperty Persisted System.Boolean PersistedNullable System.Nullable`1[[System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] Scale System.Int32 Precision System.Int32 Length System.Int32 IsMax System.Boolean XmlStyle Microsoft.SqlServer.Dac.Model.XmlStyle IdentityIncrement System.String IdentitySeed System.String IsFileStream System.Boolean IsIdentity System.Boolean Metadata: ColumnType Microsoft.SqlServer.Dac.Model.ColumnType ``` Basically, I'd like to do one of the following: - Access the ContextObject to get a Microsoft.Data.Tools.Schema.Sql.SchemaModel.* object OR - Get the value of the property and metadata from the ObjectType properties OR - Start from scratch with an easier way to get this information out We need to get information out such as the column type, howif it's nullable, and the scale and precision of a column