Creating SQL Server tables automatically - from Visual Studio Datasets or XSD Schemas

This article tackles one of the more frustrating features (or lack thereof) in Microsoft Visual Studio. While Microsoft have furnished it with Server Explorer (which lets you manage database tables directly in SQL Server) and a DataSet designer (which lets you manage code objects for wrapping up tables and relationships), it’s only possible to create a DataSet based on database tables as a one-way manual process, which regenerates DataSet code.

I’ve long found it far more comfortable and better practise to define a schema in one place – usually, the DataSet designer in VS: it’s quick, its easy to define many tables and their relationships at once and the results go into source control. Historically, I would use Visual Studio to create an XSD in this manner then propagate changes to SQL Server manually.

I’m definitely not alone in this requirement – you can find lots of posts on forums and newsgroups. It’s always worth posting these solutions so that future frustratees can share the solution. That said:

The solution

There exists an ActiveX library – “Microsoft XML BulkLoad for SQL Server 3.0 Type Library”, also known as SQLXMLBULKLOADLib. The SQLXMLBULKLOAD3Class component within it can be used from a .NET environment very easily, using interop, and is designed to create (or update) database tables in SQL Server, based on an XML schema. The library is installed along with SQL Server 2005 development tools.

I will present a basic overview of how to use the tool to update database tables from a Visual Studio DataSet object itself, at run-time, without even having to use XSD files.

The catch

The XML format that the SQLXMLBULKLOAD3Class object uses for creating the tables differs slightly from the XSD format that Microsoft use in Visual Studio. Things such as relationships (foreign keys), nullable column specification, and so on, are possible with a little modification of this method but aren’t covered here!

The code

First, take a quick look over this MSDN article - it describes a few important basics, like the fact the component won’t run in a multi-threaded environment (use STAThread). The article is written for SQLXML 4.0 but this still applies.

Remember to add an ActiveX reference to the library and a ‘using’ directive for the SQLXMLBULKLOADLib namespace.

I usually create a console application for initialising a database using this technique – simply create any dataset objects your application uses and pass them to the method below. Obviously, this would remove any data already in the database.

The usual disclaimers apply: take a backup of your database first, add salt to taste, lather, rinse and repeat.

/// <summary>
/// Creates tables in SQL Server, for all of the tables within the DataSet object.
/// Does not populate the tables with data.  Optionally drops existing tables if
/// they exist.
/// </summary>
/// <param name="connectionString">Example:
/// "Provider=SQLOLEDB;Data Source=MyServer;Database=MyDatabase;Integrated Security=SSPI;"
/// </param>
/// <param name="schemaObject">Any DataSet object - for example </param>
/// <param name="dropTablesIfExist">Deletes tables if they alreday exist, before recreating
/// them</param>
public void CreateSchema(string connectionString, DataSet schemaObject, bool dropTablesIfExist)
{
    // instantiate the bulk load object
    SQLXMLBulkLoad3Class bulkLoad = new SQLXMLBulkLoad3Class();
    bulkLoad.BulkLoad = false;
    bulkLoad.SchemaGen = true;
    bulkLoad.SGDropTables = dropTablesIfExist;
    bulkLoad.SGUseID = true;
    bulkLoad.ConnectionString = connectionString;
    bulkLoad.ErrorLogFile = "error_log.xml"; // used to log problems from the component

    string temporarySchemaFilename = Path.GetTempFileName();
    try
    {
        // write out the schema to disk
        dataSet.WriteXmlSchema(temporarySchemaFilename);

        // load the schema into a memory document - for processing!
        XmlDocument doc = new XmlDocument();
        doc.Load(temporarySchemaFilename);

        // overview of the XML processing we will perform:

        // doc root -> element -> complextype -> choice -> table elements
        // remove this ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

        // loop through tables in the dataset, updating XML structure accordingly
        XmlNamespaceManager mgr = new XmlNamespaceManager(doc.NameTable);
        mgr.AddNamespace("xs", "http://www.w3.org/2001/XMLSchema");
        XmlNode goodDataParent = doc.DocumentElement.ChildNodes[0].ChildNodes[0].ChildNodes[0];

        foreach (XmlNode kid in goodDataParent.ChildNodes)
        {
            foreach (XmlNode field in kid.ChildNodes[0].ChildNodes[0].ChildNodes)
            {
                // FEATURE - correctly pick up on the MaxLength property for string columns here!
                if (field.Attributes["type"] == null)
                {
                    XmlNode max = field.SelectSingleNode("//xs:maxLength", mgr);
                    if (max != null)
                    {
                        XmlAttribute attrib =
                            doc.CreateAttribute("sql:datatype",
                            "urn:schemas-microsoft-com:mapping-schema");
                        attrib.InnerText = "nvarchar(" + max.Attributes["value"].InnerText + ")";
                        field.Attributes.Append(attrib);
                    }
                }

                // other things (nullable column specifications, etc) would need to be done here
            }

            doc.DocumentElement.AppendChild(kid.CloneNode(true));
        }

        doc.DocumentElement.RemoveChild(doc.DocumentElement.ChildNodes[0]);

        doc.Save(temporarySchemaFilename);
        bulkLoad.Execute(temporarySchemaFilename, null);
    }
    catch (Exception ex)
    {
        // rethrow the exception
        throw new DataException("Problem creating schema in SQL Server: " + ex.Message, ex);
    }
    finally
    {
        // clean up temporary file
        if (File.Exists(temporarySchemaFilename))
            File.Delete(temporarySchemaFilename);
    }
}