In my previous post, I discussed the difficulties of saving reference data in a Visual Studio Database Project. In this post, I’ll go over the architecture of one way this problem can be solved. While doing some research on this topic I ran across this StackOverflow question “How do you store static data in your SQL Server Database Project in VS 2012“. The accepted answer was a good one.
It suggests that you put your reference data in an XML file then write a SQL MERGE statement to import the XML data into SQL Server. The interesting thing about this solution is that it uses the OPENXML syntax in the MERGE statement. This tells SQL Server to use the XML like it would a table. So when the post-deploy script runs the MERGE statement there is no need to transform the XML; SQL Server will just use the XML as is.
This solution was intriguing to me but it still doesn’t fit all the criteria that a solution for reference data needed.
The criteria for the desired reference data solution is:
- Keeps the developers from writing SQL.
- Has the data separated from the SQL implementation.
- Updates the data in source control automatically from the database.
- Is automatically deployed via the Database Project “Publish”.
The solution from StackOverflow has the data separated from the SQL and is automatically deployed via the Database Project “Publish” but it still has the developers writing SQL and doesn’t update the data in source control automatically. So what if we added some scripting magic to add some automation to this solution? Thankfully, Visual Studio has some tools that can assist. Visual Studio has a text generator called T4 (Text Template Transformation Toolkit) that can generate scripts and code. So the idea is this, what if we use T4 to generate SQL that will create the XML file. We could also use T4 to generate the MERGE statement. So here’s a flow of the what my proposed solution:
The process starts with an XML file that’s called the manifest. This manifest will have the list of table names that contain reference data. Then, a T4 template will read the table in the database and generate an XML file per table name in the manifest. Once the XML file is created it can be put into the VS project and in source control. Now that there is an XML file of the reference data for each table we can use that XML data for our MERGE statement.
To create our MERGE statement, there is another T4 template that reads the manifest and generates a MERGE statement for each table. We then reference the SQL file that the T4 template generates into the post-deployment script. Now, whenever the database is published the MERGE statements will update the reference data in the database to the data that’s in the XML files.
In this process, the developer would have options on how to create the prime data. If the data is already in the database, a simple run of the T4 XML template would create the XML reference data file. If the data doesn’t exist in the database, the developer could create the XML file manually (hey, it’s an option) then run the T4 SQL template and publish the database project. The developer would have more than “just write a SQL statement” as an option to load the data into the database. If a developer is missing the latest reference data all they would need to do is get the latest updates for the VS database project and publish. If they need to update the reference data they can update the XML file then regenerate the SQL file and publish. No messing with UPDATE, MERGE, or INSERT statements just update and publish.
In the next post, I’ll be going over the T4 XML template in detail. But if you just can’t wait to see the code head over to my Reference Data Templates repo on GitHub. I’m using an MIT license which means you can use these templates freely just as long as you give me attribution (is that really so hard?). These templates are functional but they don’t support all SQL Server data types yet. But if you’re using “standard” data types (strings, numerical, or dates) they’ll work with no changes required.