This is the third and final part of the Database Projects in Visual Studio 2010 –series. In this post I’ll show you how to copy and generate sample data for testing purposes.
Earlier parts can be found from here:
Copying sample data with VS2010 Data Compare
When you are working with a copy of an existing database, an easy way to get good data for testing is copying it from the test or production database. This is fine, if the data isn’t really confidential or contains gibberish, anyway.
There are some quite important requirements, before Data Compare can be used:
- Table and column names and types must match and be compatible
- Primary keys and unique indexes/constraints must match
- Views must have the same unique, clustered index
In practice, it works the best when the schemas are fully matching – at least for the tables that we are interested in copying.
If the source database is really big, copying all the with the Data Compare may not be the best option.
Data Compare works the best, when the source database (i.e. a test database) is small enough and needs often synchronizing. Copying the whole database can be easier and faster with a full backup or by copying the database files when the database is detached.
Data Compare is very easy tool to use: select Data | Data Compare | New Data Comparison…
You should get the following dialog:
- Source and Target Database –connections are quite obvious
- Data Compare Options affect greatly what the tool will do:
- Different Records copies rows with the same keys, but different data to the Target
- Only in Source copies missing data to the Target
- Only in Target deletes the rows from the Target that do not exist in the Source.
- Identical Records includes also those rows that are identical in both Source and Target (keys and data)
- Once the options are set (I’ll just keep the defaults), advance to the next page, where you can choose which tables you want to copy. All the tables are selected by default.
- After you click finish, Data Compare does the comparison and displays the differences. It won’t copy the data yet:
- You can still deselect the tables or rows, after you see how many will be affected.
- Finally, from the toolbar you have the option to export the script to the editor or file, which might be a good idea to save for resetting the database later in testing scenarios. You can also write the updates immediately.
- Changed rows are easy to synchronize by re-running the tool.
Generating Test Data with Data Generator
If you don’t have the luxury or need to copy real-life data for testing, you can always generate it with the Data Generator tool.
I can think of at least these scenarios:
- Filling empty databases with typical data
- Generating large amounts of data for load testing, query and index tuning
- Generating varying types of data to test edge-cases like Unicode, special characters and even badly formed data
To get started, you’ll need to create a Data Generation Plan. You can create multiple plans for different scenarios.
- You can right-click in the Solution Explorer on Data Generation Plans –folder of the Database Project (see Part 1). Select the Add | Data Generation Plan… and choose the Data Generation Plan –option.
- The tool uses automatically the current schema of the Database Project and creates a basic plan.
- The designer window appears, where you can adjust the parameters. I’ll explain it in the next section more closely.
- If you then select Write Updates from the toolbar and choose a destination database, it’ll generate the data with default parameters. It’ll look something like this:
- As you can see, it isn’t very easy on eyes, but it’s better than nothing.
This was the simplest way to use the tool, but you can tune the generator to make it generate exactly the kind of data you want.
Customizing VS2010 Data Generator parameters
- Open the designer screen, where you can adjust the parameters:
- The most interesting parts are:
- Rows to Insert column in the upper part (should be obvious)
- Generator in the lower, column-specific part
- The generator-column allows you to tell, what kind of data you want to generate. For example, you can use regular expressions to define patterns:
- With regular expressions, you can easily generate usually needed types of data like e-mails, IP-addresses, postal codes etc. You can find ready-made samples from RegExLib.com.
- With Preview –window (toolbar or menu) you can see what the outcome will be before running the tool.
- Here is an example with this regex in the CompanyName –column:
([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$
This should help you get started with the Data Generator-tool.
Conclusion
In this post I explained how to use the Data Compare and Data Generator to utilize them in real-life needs.
This post ends the current series, but there are still lot’s of things in Database Projects to write about later. If you have any questions about these tools, write a comment or give a nudge at the Twitter.
