Walkthrough: Creating a Custom Data Generator

As you develop your database schema, you can test proposed changes more effectively by populating a test database with data that represents production data as closely as possible. Custom data generators provide test data that meets your specifications more exactly than built-in data generators can. For example, you can create a generator to fill a table column with random strings from a list that you specify or with numbers in a range that you specify. For more information, see An Overview of Data Generator Extensibility.

In this step-by-step topic, you will create a custom data generator that generates random address strings. In this walkthrough, you will perform the following tasks:

  1. Create a class library for your custom address data generator that inherits from Generator.

  2. Add an output property to use as the generator output.

  3. Override the OnGenerateNextValues method to generate the data.

  4. Sign the generator assembly.

  5. Create an XML file associated with the assembly that is used for registration.

  6. Create a database project and a data generation plan to test your custom data generator.

Prerequisites

You must have either Visual Studio Team System Database Edition or Visual Studio Team System installed to complete this walkthrough.

Creating a Class Library for the Custom Generator

The first step is to create a class library for your custom address data generator.

To create a class library for a custom generator

  1. Create a Visual C# class library project and name it SampleGenerator.

  2. In Solution Explorer, right-click the project, and then click Add Reference.

  3. Click the .NET tab.

  4. In the Component Name column, select the following components:

    Tip

    Press CTRL while clicking to select multiple components.

  5. Click OK when you have selected all the components you need.

    The selected references will appear under the References node of the project in Solution Explorer.

  6. At the top of the Code window, before the class declaration, add the following lines of code:

    using Microsoft.Data.Schema.DataGenerator;
    using Microsoft.Data.Schema.Extensibility;
    using Microsoft.Data.Schema.Sql.SqlDsp;
    
  7. Rename the class from Class1 to AddressGenerator.

    Warning

    By default, the name that you give your class is the name that appears in the list in the Generator column in the Column Details window. You should specify a name that does not conflict with the name of a built-in generator or another custom generator.

    public class AddressGenerator
    {
    }
    
  8. Specify that your class inherits from Generator, as shown in the following example:

    public class AddressGenerator : Generator
    {
    }
    
  9. Add the DatabaseSchemaProviderCompatibilityAttribute, as shown in the following example:

    [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
    public class AddressGenerator : Generator
    {
    }
    

    For more information about the extension compatibility attribute, see Extending the Features of Database Edition.

  10. On the File menu, click Save All.

Adding an Output Property to the Generator

In the previous section, you created a class library that inherited from the Generator class. In this section, you will add an output property to your class. Output properties contain the values of the generated data. Output properties also specify which types of data this generator can produce.

To add an output property

  1. Create the member variables that will be used for generating the address, as shown in the following example.

    The _address variable will be used in a later step in an output property.

    private Random _random;
    private string _street;
    private string _city;
    private string _state;
    private int _zipCode;
    private string _address;
    
    private static readonly List<String> _states =
                new List<string>() { "AK", "AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY" };
    
  2. Create a private property for the _random member variable, as shown in the following example:

    private Random Random
            {
                get
                {
                    if (_random == null)
                        _random = new Random(base.Seed);
    
                    return _random;
                }
            }
    
  3. Create a public property for the _address member variable and add the OutputAttribute to it, as shown in the following example:

    [Output(Description = "Generates street and city values of \"2150 Newton Street, San Francisco\", with random state and zip code.", Name = "Address")]
            public string Address
            {
                get
                {
                    return this._address;
                }
            }
    
  4. On the File menu, click Save All.

Overriding the OnGenerateNextValues Method

Visual Studio Team Edition for Database Professionals calls the OnGenerateNextValues method of each generator for each set of values that it needs. When you create a data generator, you should override this method to provide logic that generates values for each of your output properties.

To override the OnGenerateNextValues method

  1. Override the OnGenerateNextValues method, as shown in the following example:

    protected override void OnGenerateNextValues()
    {
        this._street = "2150 Newton Street";
                this._city = "San Francisco";
                this._state = _states[Random.Next(0, _states.Count)];
                this._zipCode = Int32.Parse(String.Format("{0}{1}{2}{3}{4}", GetRandomDigit(1), GetRandomDigit(), GetRandomDigit(), GetRandomDigit(), GetRandomDigit()));
                this._address = String.Format("{0}, {1}, {2} {3}", _street, _city, _state, _zipCode);
    }
    
  2. Add the following methods, which are used in creating the value for the _zipcode variable:

    private int GetRandomDigit()
            {
                return GetRandomDigit(0);
            }
    
            private int GetRandomDigit(int lowNumber)
            {
                return Random.Next(lowNumber, 10);
            }
    
  3. On the File menu, click Save All.

Signing the Generator

Next, you must sign the custom data generator with a strong name.

To sign the generator with a strong name

  1. On the Project menu, click SampleGenerator Properties.

  2. On the Signing tab, select the Sign the assembly check box.

  3. In the Choose a strong name key file box, click <New...>.

    The Create Strong Name Key dialog box appears.

  4. In the Key file name box, type SampleGeneratorKey.

  5. Type and confirm a password, and then click OK.

    When you build your solution, the key file is used to sign the assembly.

  6. On the File menu, click Save All.

  7. On the Build menu, click Build Solution.

    You have created a custom data generator.

Registering the Generator Assembly

Once your assembly is signed and compiled, the next step is to gather the assembly information generated in the project, including the version, the culture, and the PublicKeyToken, to facilitate registering the generator assembly.

To gather assembly information

  1. On the View menu, click Other Windows, and then click Command Window to open the Command window.

  2. In the Command window, type the following code. For FilePath, substitute the path and file name of your compiled .dll file. Include the quotation marks around the path and file name.

    Note

    By default, the path of your compiled .dll file is SampleGenerator\bin\Debug.

    ? System.Reflection.Assembly.LoadFrom(@"<FilePath>").FullName
    
  3. Press Enter. The line should resemble the following, with your specific PublicKeyToken:

    " SampleGenerator, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"
    

    Notate or copy this assembly information; it will be used in the next procedure.

Next, you will create an XML file by using the assembly information that you gathered in the previous procedure.

To create the XML file

  1. In Solution Explorer, select the SampleGenerator project.

  2. On the Project menu, select Add New Item.

  3. In the Templates pane, locate and select the XML File item.

  4. In the Name text box, type SampleGenerator.Extensions.xml and click the Add button.

    The SampleGenerator.Extensions.xml file is added to the project in Solution Explorer.

    Note

    You must use the name of your dll (in this case, "SampleGenerator" followed by ".Extensions.xml") for the assembly to register correctly.

  5. Open the SampleGenerator.Extensions.xml file and update it to match the following XML. Replace the assembly's version, culture, and PublicKeyToken that you retrieved in the previous procedure.

    Note

    The extension type must use the fully qualified name of the class. In this case: extension type="SampleGenerator.AddressGenerator".

    <?xml version="1.0" encoding="utf-8"?>
    <extensions assembly="" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">
     
    <extension type="SampleGenerator.AddressGenerator" assembly="SampleGenerator, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn" enabled="true"/>
     
    </extensions>
     
    
  6. On the File menu, click Save All.

Next, you will copy the assembly and the XML file to the Extensions directory. When Database Edition starts, it will identify any extensions in the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions directory and subdirectories, and register them for use in the session.

To copy and register the assembly and XML file to the Extensions directory

  1. Create a new folder named CustomGenerators in the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions\ directory.

  2. Copy the SampleGenerator.dll assembly file from the My Documents\Visual Studio 2008\Projects\SampleGenerator\SampleGenerator\bin\Debug\ directory to the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions\ CustomGenerators directory that you created.

  3. Copy the SampleGenerator.Extensions.xml file from the My Documents\Visual Studio 2008\Projects\SampleGenerator\SampleGenerator\ directory to the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions\ CustomGenerators directory that you created.

    Tip

    A best practice is to put your extension assemblies in a folder in the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions directory. This will help you identify which extensions were included with the product, and which ones are your custom creations. Folders are also recommended for organizing your extensions into specific categories.

Testing the Address Generator

Now that you have created the address data generator, you must start a new instance of Database Edition. When Database Edition starts, it will register the SampleGenerator assembly that you added to the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions\ CustomGenerators directory. You will then create a database project in which you can verify that the address data generator works correctly.

To create the database project

  1. Start a new instance of Database Edition, which will recognize the SampleGenerator.dll assembly and register it.

  2. On the File menu, point to New, and click Project.

    The New Project dialog box appears.

  3. In Project Types and click Microsoft SQL Server.

  4. In Templates, click SQL Server 2005 Wizard.

  5. In Name, type SampleGeneratorDB.

    Note

    If you have already completed all the steps in Walkthrough: Creating a Custom Data Generator for a Check Constraint, you have created the SampleGeneratorDB project and you can go to the next procedure, To add a table to the database project.

  6. Select the Create directory for solution check box.

  7. Accept the default values for Location, Solution Name, and Add to Source Control, and then click OK.

  8. Click Finish. When the wizard is finished, click Finish again.

    The new database project, SampleGeneratorDB, appears in Solution Explorer.

  9. On the View menu, point to Other Windows, and click Database Schema View.

    Schema View appears if it was not already visible.

Next, you will add a simple table to the project with a single column with a varchar SQL type.

To add a table to the database project

  1. In Schema View, expand the SampleGeneratorDB node, expand the Schemas node, expand the dbo node, and then click the Tables node.

  2. On the Project menu, click Add New Item.

    Note

    You can also right-click the SampleGeneratorDB project in Schema View, point to Add, and click Table.

  3. In Templates, click Table.

    Note

    In the Categories list, you can click Tables and Views to more easily find the template for a table.

  4. In Name, type Addresses as the name that you want to give the new table.

  5. Click Add to add the table to your database project.

    Solution Explorer shows the new file for the table in your database project. Schema View shows the new table object. The Transact-SQL (T-SQL) editor appears and displays the definition for your new table.

  6. In the T-SQL editor, modify the table definition to match the following example:

    CREATE TABLE [dbo].[Addresses]
    (
    address varchar(100)
    )
    
  7. On the File menu, click Save dbo.Addresses.table.sql.

With the table in place, you can now configure the database for deployment.

To configure project deployment settings

  1. In Solution Explorer, click SampleGeneratorDB (the project, not the solution).

  2. On the Project menu, click SampleGeneratorDB Properties.

    The project's Properties window appears.

    Note

    You can also right-click SampleGeneratorDB in Solution Explorer and then click Properties.

  3. Click the Deploy tab.

  4. In Deploy action, click Create a deployment script (sql) and deploy to database.

  5. Click the Edit button to specify the target connection.

  6. Specify the information to connect to the database server to which you want to deploy the SampleGeneratorDB database.

  7. In Select or enter a database name, type SampleGeneratorDB.

  8. Click OK.

    Target Connection is populated with the connection string. Note that the Target Database Name is set to SampleGeneratorDB.

  9. Accept the default values for the other options.

  10. On the File menu, click Save Selected Items.

    Your project build settings are saved.

Next, you will build the database project.

To build the database project

  • On the Build menu, click Build Solution.

    Your database project builds. If successful, the message "Build succeeded" appears in the status bar, and the build results are displayed in the Output window.

Next, you will deploy the database project.

To deploy the database project to the database server

  1. In Solution Explorer, click SampleGeneratorDB (the project, not the solution).

  2. On the Build menu, click Deploy SampleGeneratorDB.

    Your database project is deployed using the connection that you specified in the build configuration. The message "Deployment succeeded" appears in the status bar and in the Output window.

Creating and Configuring the Data Generation Plan

Next, you will create the data generation plan. The data generation plan contains the information about which tables and columns you want to populate with data. For more information, see How to: Create Data Generation Plans.

To create and configure the data generation plan

  1. In Solution Explorer, select the Data Generation Plans node.

  2. On the Project menu, click Add New Item.

  3. In the Categories pane, click Data Generation Plans.

  4. In the Templates pane, click Data Generation Plan.

  5. In the Name text box, type SampleGenerator.dgen.

  6. Click Add.

    The data generation plan is created. The data generation plan and the Data Generation Preview window appear.

    The data generation plan window is divided horizontally into two panes. The upper pane lists the tables that are defined in the database project schema — in this case, the dbo.Addresses table. The lower pane displays column details for the table that is highlighted in the upper pane — in this case, the address column.

    Note

    If the Data Generation Preview window is not open, you can open it by opening the Data menu, pointing to Data Generation, and then clicking the window name. By default, the Data Generation Preview window is docked and tabbed at the bottom of the data generation plan window. To expand your view, click the window, and then click Tabbed Document on the Window menu. You can also right-click the title bar and then click Tabbed Document.

  7. In the SampleGenerator.dgen designer, verify that the dbo.Addresses table and the address column are both checked.

  8. In the SampleGenerator.dgen designer, select the address column and click the Generator drop-down to select the AddressGenerator.

    Your custom address generator is now correctly configured.

  9. On the File menu, click Save All.

Running the Data Generation Plan to Generate Address Data

Finally, you will run the data generation plan and see your custom address data generator in action.

To run the plan to generate data

  1. In Solution Explorer, click SampleGenerator.dgen.

    Note

    The data generation plan must also be open. If the plan is not open, open it first.

  2. On the Data menu, point to Data Generator, and then click Generate Data.

    The Connect to Database dialog box appears.

  3. In the Data Generation Connection Information list, click the SampleGeneratorDB database, and then click OK.

  4. Click Yes when you are prompted to clear the contents of the tables before inserting new rows.

    The data is generated. In the Population window, the status column is updated with the status of the data generation. The status bar summarizes the data generation for all the tables.

  5. (Optional) Use a different tool to log on to the database. You can use the Transact-SQL (T-SQL) editor that is provided in Database Edition for this step. For more information, see Editing Database Scripts and Objects with the Transact-SQL Editor.

  6. View the new data by running the following query:

    use SampleGeneratorDB
    
    select * from dbo.Addresses
    

    The Results tab should display addresses with random state and ZIP code values.

Next Steps

Visual Studio Team System Database Edition includes the DateTime generator, which can be used with columns that have a check constraint that requires that a date be within a certain range, by using the generator's Min and Max properties. In Walkthrough: Creating a Custom Data Generator for a Check Constraint, you will create a custom data generator for a column with a check constraint that requires that a date be within one of two distinct ranges.

See Also

Tasks

How to: Register and Manage Feature Extensions

How to: Add Input Properties to a Data Generator

How to: Add Output Properties to a Data Generator

How to: Create Custom Data Generators

Walkthrough: Creating and Running a Data Generation Plan

Concepts

Extending the Features of Database Edition

An Overview of Data Generator Extensibility

Reference

Microsoft.VisualStudio.TeamSystem.Data.DataGenerator

OnGenerateNextValues

Other Resources

Editing Database Scripts and Objects with the Transact-SQL Editor

Managing Assembly and Manifest Signing