Querying Visual Studio project files using T-SQL and Powershell

jamiet — Mon, 17 Jan 2011 21:11:00 GMT

Earlier today I had a need to get some information out of a Visual Studio project file and in this blog post I’m going to share a couple of ways of going about that because I’m pretty sure I won’t be the only person that ever wants to do this. The specific problem I was trying to solve was finding out how many objects in my database project (i.e. in my .dbproj file) had any warnings suppressed but the techniques discussed below will work pretty well for any Visual Studio project file because every such file is simply an XML document, hence it can be queried by anything that can query XML documents.

Ever heard the phrase “when all you’ve got is hammer everything looks like a nail”? Well that’s me with querying stuff – if I can write SQL then I’m writing SQL. Here’s a little noddy database project I put together for demo purposes:

Two views and a stored procedure, nothing fancy. I suppressed warnings for [View1] & [Procedure1] and hence the pertinent part my project file looks like this:

  <ItemGroup>
    <Build Include="Schema Objects\Schemas\dbo\Views\View1.view.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151,3276</SuppressWarnings>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Views\View2.view.sql">
      <SubType>Code</SubType>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
    </Build>
  </ItemGroup>
  <ItemGroup>

Note the <SuppressWarnings> elements – those are the bits of information that I am after.

With a lot of help from folks on the SQL Server XML forum I came up with the following query that nailed what I was after. It reads the contents of the .dbproj file into a variable of type XML and then shreds it using T-SQL’s XML data type methods:

DECLARE @xml XML; 
SELECT @xml = CAST(pkgblob.BulkColumn AS XML) 
FROM   OPENROWSET(BULK 'C:\temp\QueryingProjectFileDemo\QueryingProjectFileDemo.dbproj' -- <-Change this path! 
                   ,single_blob) AS pkgblob 
                   
;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns) 
SELECT  REVERSE(SUBSTRING(REVERSE(ObjectPath),0,CHARINDEX('\',REVERSE(ObjectPath)))) AS [ObjectName] 
       ,[SuppressedWarnings] 
FROM   ( 
       SELECT  build.query('.') AS [_node] 
       ,       build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings] 
       ,       build.value('@Include','nvarchar(1000)') AS [ObjectPath] 
       FROM    @xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build) 
       )q

And here’s the output:

And that’s it – an easy way of discovering which warnings have been suppressed and for which objects in your database projects. I won’t bother going over the code as it is fairly self-explanatory – peruse it at your leisure.

Once I had the SQL above I figured I’d share it around a little in case it was ever useful to anyone else; hence I’m writing this blog post and I also posted it on the Visual Studio Database Development Tools forum at FYI: Discover which objects have had warnings suppressed. Luckily Kevin Goode saw the thread and he posted a different solution to the same problem, one that uses Powershell. The advantage of Kevin’s Powershell approach is that it is easy to analyse many .dbproj files at the same time. Below is Kevin’s code which I have tweaked ever so slightly so that it produces the same results as my SQL script (I just want any object that had had a warning suppressed whereas Kevin was querying specifically for warning 4151):

cd 'C:\Temp\QueryingProjectFileDemo\'
cls
$projects = ls -r -i *.dbproj
Foreach($project in $projects)
{
  $xml = new-object System.Xml.XmlDocument
  $xml.set_PreserveWhiteSpace( $true )
  $xml.Load($project)

  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings=4151]/@Include"}
  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[contains(e:SuppressWarnings,'4151')]/@Include"}
  $xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings]/@Include"}
  $ns = @{ e = "http://schemas.microsoft.com/developer/msbuild/2003" }

  $xml | Select-Xml -XPath $xpath.Start  -Namespace $ns |Select -Expand Node | Select -expand Value
}

and here’s the output:

Nice reusable Powershell and SQL scripts – not bad for an evening’s work. Thank you to Kevin for allowing me to share his code.

Don’t forget that these techniques can easily be adapted to query any Visual Studio project file, they’re only XML documents after all! Doubtless many people out there already have code for doing this but nonetheless here is another offering to the great script library in the sky. Have fun!

@Jamiet

This blog post was mentioned on This Week on Channel 9. Fast forward to 10m49s.

Maintaining version history in your database using Visual Studio 2010

jamiet — Tue, 19 Oct 2010 19:26:14 GMT

I have written a few blogs lately explaining how my current project is employing the use of datadude (aka the database development tools in Visual Studio 2010) in order to manage our database code, deployment of that code and also testing of it. In this blog post I’m going to share a little technique that we use in order to store a version history of our deployments. Note that this assumes a working knowledge of datadude so if you don’t know what the terms Post-Deployment script, SQLCMD variables, Continuous Integration & msbuild refer to then maybe this blog post is not for you!

Need a table

Firstly, we need a table to store our version history, in our case we have called it [BuildVersion] and it looks like this:

                     
       CREATE TABLE [dbo].[BuildVersion]         
(         
    [BuildVersion] NVARCHAR(20) NOT NULL,         
    [Deployed]     DATETIME NOT NULL         
)

Need a project variable

We have a SQLCMD variable declared as part of our project that is intended to hold a version number. In datadude SQLCMD variables are (by default) declared in a file called Database.sqlcmdvars:

In our case we have a variable called ‘BuildVersion’ that we default to the value of “Unknown”

Need to populate the table

We use the value in our ‘BuildVersion’ variable in order to populate our [BuildVersion] table and we do that within a Post-Deployment script using the following simple code:

INSERT [dbo].[BuildVersion]([BuildVersion], [Deployed]) VALUES ('$(BuildVersion)', GETDATE()) --$(BuildVersion is a SQLCMD variable declared within this project)

And of course we need to tell our mechanism what the build number is

We need to make sure that $(BuildVersion) has a value in it. As we are deploying our database as part of a continuous integration build (leveraging msbuild) we can pass in a value from the msbuild script. Here’s the code that we use to do that:

<Exec Command="..\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:"Data Source=$(Server);Integrated Security=True" /DeployToDatabase:+ /ManifestFile:"..\MyDB.deploymanifest" /p:BuildVersion="$(BuildLabel)"" />

The important bit for what we are discussing herein is that last underlined part:

/p:BuildVersion="$(BuildLabel)"

The /p directive is used to populate a named variable (in our case BuildVersion) with a value. In our case than value is taken from an msbuild property which, in our case, is referred to by $(BuildLabel). [Note that managing msbuild properties is outside the scope of this blog post.]

Wrap-up

That’s pretty much all you need. If it all hangs together correctly then [BuildVersion] will contain a nice history of all your deployments like so:

Hope this is useful! I suspect the same technique will work in earlier versions of datadude but I don’t have any to hand so can’t find out.