[DBPro] Generating sample data for your database using Visual Studio 2010 !December 13th, 2011 | Posted by 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:
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:
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:
The screen that appear display all the tables in your database and allow you to specify how many row you want to insert:
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:
As you can see, the “Id” column will not be generated because its value is auto-generated by the database during each insertion:
Now, if you press F5, you’ll see that the data are generated for your database:
A look in the database values (using SQL Server Management Studio) can confirm that all is OK:
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:
If you try to run your data generation plan, you’ll encounter the following error:
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:
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:
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:
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:
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:
Now, press the F5 key (choose to don’t delete the data from the database) to insert the data:
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:
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 !