Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
 
Add your own
alternative version

Version control for SQL databases with SQL -> XML

, 26 Jun 2008
Demonstrates SQL Management Objects, LINQ to XML, and more.
databaseexport.zip
DatabaseExport
Properties
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.IO;

namespace DatabaseExport
{
    /// <summary>
    /// Exports a script definition of the specified database to an XML file for use in version management
    /// or for making comparisons between databases in different environments.
    /// 
    /// NOTE: Does not attempt to persist ALL database settings yet ... just does fields, indexes and foreign key constraints.
    /// Feel free to add other settings if you need them.
    /// 
    /// </summary>
    /// <remarks>
    /// This can be used as a crude form of version control for your database files.
    /// 
    /// Each time you make a change to your development databases, run this script
    /// then check-in the XML file to your version control system.  Voila, you have 
    /// a track record of when changes were made to database tables, indexes, 
    /// foreign key constraints etc.
    /// 
    /// To compare your dev environment to your staging environment, simply run this script in each
    /// and then diff the two XML files that you get.
    /// 
    /// Demonstrates these technologies:-
    /// 
    ///    SQL Management Objects
    ///    Generics
    ///    Linq
    ///    Linq to XML
    /// 
    /// </remarks>
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length < 2 || string.IsNullOrEmpty(args[0]) || string.IsNullOrEmpty(args[1]))
            {
                Console.WriteLine();
                Console.WriteLine(@"DatabaseExport <databasename> ""<connection string>"" [<output file>]");
                Console.WriteLine(@"e.g. DatabaseExport Exceptions "".;Integrated Security=True;Pooling=False;""");
                Console.WriteLine(@"The output file defaults to dump.xml in the current directory");
                return;
            }

            string databaseName = args[0];
            string connectionString1 = args[1];
            string xmlpath = (args.Length < 3 || string.IsNullOrEmpty(args[2])) ? Path.Combine(Environment.CurrentDirectory, "dump.xml") : args[2];

            SqlConnection connection1 = new SqlConnection(connectionString1);
            Server server1 = new Server(new ServerConnection(connection1));

            if (!server1.Databases.Contains(databaseName))
            {
                string databases = "[" + string.Join(",", server1.Databases.Cast<Database>().Select(d => d.Name).ToArray()) + "]";
                Console.WriteLine("Could not find database '" + databaseName + "' in " + databases);
                return;
            }

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

            Console.WriteLine("Exporting database " + databaseName + " which has " + tc1.Count + " tables to " + xmlpath);

            XElement databaseElement;
            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)
                                                ));

            foreach (Table table in tc1)
            {
                Console.WriteLine("Exporting table " + table.Name);

                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)
                ));
            }

            root.Save(xmlpath);
            Console.WriteLine("Done");
        }

        /// <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;
        }

        /// <summary>
        /// Emits the script for a given IScriptable SQL Management Object into an XCData section
        /// </summary>
        public static XCData EmitScript(IScriptable dbObject)
        {
            StringBuilder sb = new StringBuilder();
            StringCollection script = dbObject.Script();
            foreach (string s in script)
            {
                sb.AppendLine(s).Replace("\r", "");     // We want just \n's in there - you'll see why in part II
            }
            return new XCData(sb.ToString());
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

Share

About the Author

HightechRider

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 26 Jun 2008
Article Copyright 2008 by HightechRider
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid