THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Querying a dacpac from Linqpad

I have been playing around with the DAC Framework over the past few days, namely the DacAPI which is a .Net API that one can use to programmatically traverse the contents of a dacpac.

image

If one prefers one can open a new C# project to start playing with the API and interrogating a dacpac but I find it a lot easier (and more fun) to use one of my favourite tools, Linqpad. Linqpad allows you to write C# code without the heavyweight ceremony of a project in Visual Studio. Stated another way, it allows you to write C# scripts rather than programs. In this post I’ll explain how to use Linqpad to interrogate a dacpac.

First of all I’ll assume you’ve downloaded Linqpad (its free), open it up and switch your query to using C# Statements

image

Press F4 to bring up the query properties where you can add references to the assemblies that you need. If you have Visual Studio 2013 installed then you’ll find the DLLs at <Visual Studio Install Dir>\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120 (if you are using a previous version of Visual Studio or if you installed the DAC Framework in isolation then it may be somewhere else, check this forum thread for details), add them here:

image

You need to reference

  • Microsoft.SqlServer.Dac.dll
  • Microsoft.SqlServer.Dac.Extensions.dll

Switch to the Additional Namespace Imports tab and enter the following:

  • Microsoft.SqlServer.Dac
  • Microsoft.SqlServer.Dac.Model
  • System.Collections.Generic
  • System.Text

image

You’re now good to go to start writing some code. Here’s a starter for ten that dumps out a CREATE TABLE script for each table in the dacpac:

var model = new TSqlModel(@"C:dacpacs\Mydacpac.dacpac");
var allTables = model.GetObjects(DacQueryScopes.All, ModelSchema.Table);
var tableScripts = from t in allTables
                select t.GetScript();
tableScripts.Dump(); //Dump() is an extension function that simply displays the contents of the object in the Results window

image

Cool stuff. Have fun!

@Jamiet

Published Friday, September 26, 2014 9:45 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ed Elliott said:

Great to see blogs on the DacFx API, I really think that with this and the script dom stuff we, the SQL community can really improve the t-SQL tooling!

September 26, 2014 3:04 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement