[DBPro] Generating sample data for your database using Visual Studio 2010 !

December 13th, 2011 | Posted by Tom in .NET | Article | Visual Studio | Read: 4,170

Since Visual Studio 2008 (and maybe before), it’s possible for developers to generate sample data for the database they are using to develop applications. This feature is really useful to test some scenarios like pagination, load test, etc.

To start, take a look at the following diagram that represent the tables which we are going to use in the article:

image

It’s a pretty simple example but, as you can see, there is a foreign key between the 2 tables and that’s the good part: Visual Studio will be able to generate data and take care of the foreign keys for you !

To populate your database, you first need to add a SQL Server Database Project:

image

Import the content of your database in your project then, right click on the project that has just been created and choose to add a new data generation plan:

image

The screen that appear display all the tables in your database and allow you to specify how many row you want to insert:

image

If your right click on one of the previous line, you can even display a preview of the data that will be generated in your database:

image

As you can see, the “Id” column will not be generated because its value is auto-generated by the database during each insertion:

image

Now, if you press F5, you’ll see that the data are generated for your database:

image

A look in the database values (using SQL Server Management Studio) can confirm that all is OK:

image

We can see that the tables are correctly populated, even for the “FrequencyId”, which is the foreigh key but well filled in !

This is very performant and useful but imagine that some of your SQL tables already contains some data: you don’t want to delete them before generating the sample data:

image

If you try to run your data generation plan, you’ll encounter the following error:

image

Of course, you need to have selected “No” in the MessageBox asking you if you want to delete the data that are currently in the database:

image

After thinking a bit, it appears that the error is normal. Indeed, our data generation plan will insert some data in the “Frequency” table and so, the first index will start from 1. But, as we have already have some data in the table, we cannot insert a duplicate key.

To prevent this error, we need to specify that 0 row will be inserted:

image

But it’ not enough. Indeed, we need to ensure that the data inserted in the column ”FrequencyId” contains good data (i.e. good reference to the data from the Frequency table). To do that, select  the line “NewsPaper” and you’ll see that the “FrequencyId” is actually a foreign key:

image

Change that to select “Data bound generator” and, in the properties (F4), specify the connection information. This property is used to indicate to Visual Studio where to get the data to populate this column. Then, in the “Select Query” property, enter the SQL request that will be used to get the id corresponding to FrequencyId:

image

Automatically, the data generation plan know that to populate the FrequencyId column of the NewsPaper table, it need to get a random value from the SQL request:

image

Now, press the F5 key (choose to don’t delete the data from the database) to insert the data:

image

If you look in the database, you can see that the data has been effectively correctly inserted and, the most important, that the foreign key values (the “FrequencyId” column) contains only values taken from the “Frequency” table:

image

As you’ve seen in the article, generating sample data for your database is pretty simple and a lot of more complex scenarios can be covered….. Maybe I’ll talk about them a bit later !

Happy coding!

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

Add Comment Register



Leave a Reply