Managing sample data using the VS 2010 database

Question

I am using a database project in Visual Studio 2010 to manage all the code in my database.

For my application, we share the data for each client in our own copy of the database with a common database for mapping users to the corresponding database.

I would like to save several different sets of sample data in TFS along with the code and be able to selectively deploy one of them as needed.

I created scripts for two different client databases and saved them in a directory after deployment to tfs. I would like to keep another that we use for demos and unit testing.

Is there any way to do: r conditional in SQLCMD? Are there any other suggestions or recommendations on how to do this? I read the Visual Studio 2010 Database Project Guide , but found nothing.

Thanks in advance for any help you can provide.

Answer 1

I was on the right track before adding a variable to the project and checking it in a script.

There are two small catches:

• In the .sqlcmdvars file, the value of the variable must contain single quotes around it.
• This method creates a single script with all data samples included, but only the one specified by the variable is executed. As the sample data becomes more complete, it will become a rather large file, but it should be good for my purposes. I could see how this would work for really large datasets. In addition, individual scripts cannot have a GO delimiter in them, because it is contained in a BEGIN END block.

Here is my file:

/* All deployments get the reference data */ :r .\ReferenceData.PostDeployment.sql IF ('$(SampleSet)' = 'ClientA') BEGIN :r .\SampleData.ClientA.PostDeployment.sql END ELSE IF ('$(SampleSet)' = 'ClientB') BEGIN :r .\SampleData.ClientB.PostDeployment.sql END 

Credit to Jamie Thomson and this article for a quote bit of value.

Answer 2

Maybe this is not the way you want, but to simplify the creation of a database and population from a .Net overview of using a wide migration tool. My preferences (Migrator.NET), but a full review can be found there: http://flux88.com/blog/net-database-migration-tool-roundup/