Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Version control for SQL databases with SQL -> XML

Rate me:
Please Sign up or sign in to vote.
4.87/5 (11 votes)
26 Jun 2008CPOL4 min read 102.1K   240   43   7
Demonstrates SQL Management Objects, LINQ to XML, and more.

Introduction

This small console application exports information about a database from SQL Server to an XML file. This can come in handy when you have a version control system like SVN or GIT that manages all of your source code but lacks any way to track the changes you make to your tables in SQL Server.

Whenever you make a database change, simply run this code, and check-in the XML file that it produces, or just make that part of your MS Build task so it happens automatically with every build you make on your continuous integration server.

This utility can also come in handy to ensure that your development environment, staging environment, and production environment all have the same SQL tables in place. Just run it once for each environment, and diff the files to see what's missing. Since the XML file contains all the T-SQL CREATE scripts you'll need, it's easy to cut and paste into the SQL Management Studio Express to add whatever is missing in your other environments.

The example is also intended as a demonstration of SQL Management Objects and LINQ to XML.

Background

There are plenty of [expensive] tools to manage your SQL databases and to synchronize them between environments, but if you need a simple tool for a smaller project, something like this might be just what you need, and since it's all just plain C# code, you can easily build this into your build or deployment projects. With this in place, you'll now have a record at every step along the way as to the structure of your database and when changes were made to fields, indexes, and to foreign key constraints. Your SVN logs will show both table changes AND code changes, making it easier to blame whoever messed things up see what changed when.

The code does not attempt to copy every aspect of the database to the XML file; you can do that if you want by extending it, but for now, I just needed a quick tool to keep database fields, indexes, and constraints in sync between my various environments, and SQL MO was the easy way to achieve that.

Using the code

Compile the code and run the executable from a command line, passing it three arguments:

DatabaseExport <databasename> "<connection string>" [<output file>] 

For example, if Exceptions is the name of a database on the local SQL instance, you could export it to XML using:

DatabaseExport Exceptions ".;Integrated Security=True;Pooling=False;"

The result might look something like this ...

XML
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<!--Dump of database for .\SQLEXPRESS, database exceptions-->
<database version="1.0" name="exceptions">
  <table name="ExceptionRecord">
    <body name="ExceptionRecord"><![CDATA[SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ExceptionRecord](
    [ExceptionUID] [bigint] IDENTITY(1,1) NOT NULL,
    [Application] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DateOccurred] [datetime] NOT NULL,
    [ExceptionText] [nvarchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StackTrace] [nvarchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Module] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Severity] [int] NOT NULL
) ON [PRIMARY]

]]></body>
    <index name="PK_Exception"><![CDATA[ALTER TABLE [dbo].[ExceptionRecord] 
                                  ADD CONSTRAINT [PK_Exception] PRIMARY KEY CLUSTERED 
(
    [ExceptionUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
       GNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
       ON [PRIMARY]
]]></index>
  </table>
  <table name="ExceptionRecordData">
    <body name="ExceptionRecordData"><![CDATA[SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ExceptionRecordData](
    [ExceptionDataUID] [bigint] IDENTITY(1,1) NOT NULL,
    [ExceptionUID] [bigint] NOT NULL,
    [Name] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Value] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

]]></body>
    <index name="PK_ExceptionData"><![CDATA[ALTER TABLE [dbo].[ExceptionRecordData] 
                              ADD  CONSTRAINT [PK_ExceptionData] PRIMARY KEY CLUSTERED 
(
    [ExceptionDataUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
      ON [PRIMARY]
]]></index>
  </table>
</database>

That's actually my Exceptions database into which I log every exception thrown by any of my solutions, more about that some other time maybe ...

As you can see, each table becomes its own element in the XML file, and the script to create that table is there as a CDATA section. Each index and foreign key constraint also gets a section, with the script to create it.

The code to do all this is remarkably simple. SQL Management Objects gives us a way to discover all the Tables, Indexes, and other objects under a database. For example, here, we ask for a database with a specific name, and then ask for the collection of tables under it:

C#
Database db1 = server1.Databases[databaseName];
TableCollection tc1 = db1.Tables;

Once we have a collection of database objects, we can use the magic of LINQ to query them and construct the XML elements from them. This simple method takes a SQL MO collection and a generic parameter T, specifying the type we expect to find in that collection. Once we've cast the elements of the collection to type T, we can construct a query over them, and from that, it's easy to generate the IEnumerable<XElement> sequence we want.

Note how we put generic constraints on the type T to say that it must be a NamedSmoObject and it must support the IScriptable interface which is the one used to generate the T-SQL script for that object.

C#
/// <summary>
/// Convert a collection of SQL Management Objects into XElements
/// </summary>
private static IEnumerable<XElement> XElementsFromCollection<T>(SmoCollectionBase collection)
    where T : NamedSmoObject, IScriptable
{
    var query =
       from namedObject in collection.Cast<T>()
       orderby namedObject.Name
       select new XElement(typeof(T).Name.ToLower(), 
                     new XAttribute("name", namedObject.Name), EmitScript(namedObject));

    return query;
}

With that method in place, the following single line of code is able to do 99% of the work. It constructs an XML 'table' element with a name attribute equal to the name of the SQL table, and then within that, a body element containing a CDATA section for the script to create that table, and then a list of indexes and foreign keys each with CDATA sections for their own creation scripts.

C#
databaseElement.Add(new XElement("table",
    new XAttribute("name", table.Name),
    new XElement("body", new XAttribute("name", table.Name), EmitScript(table)),
    XElementsFromCollection<Index>(table.Indexes),
    XElementsFromCollection<ForeignKey>(table.ForeignKeys)

All that remains is to wrap that up in an XML document and to write it out to a file.

C#
XDocument root = new XDocument(new XDeclaration("1.0", "utf-8", "yes"),
    new XComment("Dump of database for " + server1.Name + ", database " + databaseName),
        databaseElement = new XElement("database",
        new XAttribute("version", "1.0"),
    new XAttribute("name", databaseName)
    ));

Points of interest

With LINQ to XML, what would have taken some convoluted foreach statement spanning 10 lines of code is now accomplished in a single line of code. For example, generating a string with a list of all the database names separated by commas is achieved thus:

C#
string databases = "[" + string.Join(",", 
               server1.Databases.Cast<Database>().Select(d => d.Name).ToArray()) + "]";

Follow on article anyone?

If anyone is interested, I may do a follow on article showing how to take this XML file and compare it back against another instance of the database using a simple GUI where you can click to add any missing tables, indexes, or foreign key constraints. That's what I'm working on now to complete this 'diversion' and then, once I no longer need to worry about keeping all my database tables in sync across two development machines and one continuous integration server, I can get back to writing some 'useful' code instead.

History

First version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
I have been writing code and managing teams of developers for more years than I care to remember.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:21
professionalKanasz Robert24-Sep-12 6:21 
QuestionWhy XML? Pin
MR_SAM_PIPER1-Jul-08 14:45
MR_SAM_PIPER1-Jul-08 14:45 
AnswerRe: Why XML? Pin
HightechRider1-Jul-08 14:55
HightechRider1-Jul-08 14:55 
GeneralInteresting Pin
BDisp1-Jul-08 2:47
BDisp1-Jul-08 2:47 
General中国人看看 Pin
zhongguosou30-Jun-08 2:59
zhongguosou30-Jun-08 2:59 
GeneralExcellent Pin
merlin98127-Jun-08 3:51
professionalmerlin98127-Jun-08 3:51 
GeneralNice Job! Pin
thomasswilliams26-Jun-08 19:59
thomasswilliams26-Jun-08 19:59 
Nice job HightechRider. This has the potential to come in very handy - can't wait to see what you have in store for part 2!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.