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:
We need to get information out such as the column type, howif it's nullable, and the scale and precision of a column
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.