Using Visual Studio 2010 to Generate Sample Data

By David Welford-Costelloe (http://www.welford-costelloe.com)

This tutorial will take you step by step in generating test data using Visual Studio 2010. What you will need is Visual Studio 2010 you can download the express or demo addition from:  http://www.microsoft.com/visualstudio/en-us/products/2010-editions/express

To begin I will assume you have installed Visual Studio 2010 and Service packs, you can use the web installer at: http://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=VS2010SP1Pack

to install the service pack for Visual Studio 2010, this update takes a couple of hours depending on your PC.

To begin we will create a new Database project, but first we need to ensure we have created the tables required in the database, if you followed the tutorial ‘Using Merge with SQL Server 2008 R2’ you will already have the required database/tables.

If you have the database created skip this section:

To begin you will need SQL server 2008 database there is a free version you can download from: http://www.microsoft.com/express/Database/

I will be using

Open Management Studio:

Login ensure you have admin access

Create New Database TestMerge:

Click OK

You now have the database required for this tutorial, next we need to add a couple of tables

Script:

Create New Query

Execute Query

The new table will be created do the same for the next two scripts

All three tables are generated:

Next I am going to add a Ontario Cities Regex for the Address Table to do this I will go to the internet grab a list of cities for Ontario and put them into an Excel Sheet to help design the Regex:

Using Excel go to:  http://www.trailcanada.com/ontario/cities/

Copy the list of cities to the clip board:

Next  Open Excel  and paste the City List into a spreadsheet:

Next we save the list as CSV to get rid of the links in the sheet:

Click Save

Close and re-open the csv file in Excel:

No we see the list as Text just the way we want it. Next we are going to add the | separator this is the required format for Regex:

Save the file as csv and exit Excel. Open the file in Notepad or your favorite editor I use Notepad++ (http://notepad-plus-plus.org/)

As we now need to clean up the list we will remove the ‘,’ and add beginning ( and Ending ) to be the correct format for the DataExecutionPlan needed later on

On the file menu select Search and Replace or CTRL-H for NotePad++

Enter Find What as a comma and Replace with leave blank, Click replace All

Click OK and Close

Add the opening and closing braces as per above

Save the file and leave notepad++ open and now we begin to build the database project.

Create Visual Studio Database Project

File ==>New ==>Project

Select SQL Server 2008 Database Project and Name this project ‘DataCollection’ and Click Ok

Wait for the wizard to finish setting up the project

As we have already created the tables required for population we will now import the database into our project.

Right click the Project and select Import Database Objects and Settings

Select New Connection

If you followed the above you should have a TestMerge Database use the blow settings

Click Test Connection

If all is well you will see:

Click ok and then Start

Wait for wizard to complete and Click Finish

In the solution Explorer we can see the TestMerge scripts have been imported into the project, we will use this schema to begin our new Data Generation Plan

Right Click the Folder Data Generation Plans:

Select Add ==>Data Generation Plan

Name this TestDataGenerationPlan.dgen and Click Add

The Project will load the schema information check only the Employee and Address tables

Click on the Employee to highlight change the Rows to Insert from 50 to 1000

Next we will add the format specific data to make out test more realistic, in the middle pane is where we make this changes make sure you have the Properties window open on the right (use F4 to Open)

Some of the entries have been filled in for you by the wizard, we will change some of these as the data produced by default has no meaning, you can see the default by right Clicking the Middle Pane:

Click Preview Data Generation:

The default Data is all mixed up so lets change this to more realistic data:

Before we go any further you should go to the internet a google common names and generate or as a registered user you can download all the files used for this tutorial

Select Regular Expression  and copy the list generated as there are 2000 first names I have created only 10 here as a sample:

Once copied hit CTRL-Enter  and you will see the preview window change with the new data

Cool one down more to go for the Middle Name we will also use a regex statement but we also only need about 2% or the records to have a middle name while the rest are blank, to do this in the middle pane highlight the MiddleName

Set as Regular Expression and add the following to the properties

Hit Ctrl-Enter and see the below:

Do the same for LastName ,InsertedBy, Updated By

Set the Dates to the above and you will see the data looks realistic

As our Middle name is populated we will change this to only populate 2% with values. Highlight and change the property as per below:

Set Percentage Null to 98 this will populate only 2%

Click Save All

Now we run this to populate the data, check off the table Address for now as we will create a separate plan later.

On the toolbar click the  icon

In this case we will delete so select Yes

As we can see the data went in as expected to double check pop over to the management console for SQL server and run a query to see what is in Employee Table:

Results:

Nice Employee is completed!

Now I am going to show you how to use SQL to populate the Address ID with valid ID’s from the Employee data generation, right click the dgen and select copy and paste a copy into the Data Generation Plans folder and rename to AddressDataGenerationPlan.dgen:

Check off Employee and leave Address

Click on EmployeeId a set this as Sequential bound Generator set the Connection property select the TestMerge Connection

Now we will add the following to the properties for Address Table:

Double Click on the Generation Plan AddressDataGenerationPlan.dgen

As per the grid below set the properties

In the Select Query put the below query:

The above query will ensure we don’t get any duplicates of EmployeeId into the Address table

Ok let us run the Address Plan:

Looks good now run a couple of queries

Results:

Nice now let us join the employee to the Address and see the full results

Results:

There you go you are now familiar with Visual Studio 2010 Database Projects and adding Test Data using Generation Plans. Registered Users can download the files used here in the downloads section as complete project and files.

Enjoy

David