Walkthrough: Creating a Custom Data Generator for a Check Constraint
You can use standard data generators to fill columns with data in Visual Studio Premium or Visual Studio Ultimate. If the column that you want to fill has a check constraint defined on it, the data with which you fill the column must satisfy that check constraint. The standard data generators can generate data that satisfies many check constraints. For example, if you have a check constraint that requires that a date is in a certain range, you can use the standard DateTime generator and set the Min and Max properties to satisfy that check constraint.
However, the standard data generators cannot satisfy all check constraints. For example, if a check constraint requires that a date is in either of two distinct ranges, you cannot use the standard DateTime generator. In this walkthrough, you will create a custom data generator that can satisfy such a constraint. The generator accepts two ranges as the input and generates a random date that is in one of those ranges.
In this walkthrough, you will perform the following tasks:
Create a class that inherits from Generator.
Create input properties so that the user can specify the two date ranges.
Create an output property to use as the generator output.
Override the OnInitialize method to seed the Random objects and make your generator deterministic.
Override the OnGenerateNextValues method to generate the data.
Sign the generator with a strong name.
Prerequisites
You need to have Visual Studio Premium or Visual Studio Ultimate installed to complete this walkthrough.
Creating the Custom Data Generator Class
Start creating the custom data generator by creating a class library.
To create the custom data generator class
In Visual Studio, create a Class Library project in the language of your choice, and name it GeneratorDateRanges.
On the Project menu, click Add Reference.
Select the .NET tab.
In the Component Name column, locate the following components:
[Microsoft.Data.Schema]
[Microsoft.Data.Schema.Sql]
[Microsoft.Data.Schema.Tools]
Tip
Press CTRL while clicking to select multiple components.
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.
(Optional, Visual Basic only) In Solution Explorer, click Show All Files, and expand the References node to verify the new reference.
Open the Class1 file and then, at the top of the Code window, before the class declaration, add the following lines of code:
Imports System.Data.SqlTypes Imports Microsoft.Data.Schema.Tools.DataGenerator Imports Microsoft.Data.Schema.Extensibility Imports Microsoft.Data.Schema Imports Microsoft.Data.Schema.Sqlusing System.Data.SqlTypes; using Microsoft.Data.Schema.Tools.DataGenerator; using Microsoft.Data.Schema.Extensibility; using Microsoft.Data.Schema; using Microsoft.Data.Schema.Sql;Rename the class from Class1 to GeneratorDateRanges, and specify that your class inherits from Generator.
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 standard generator or of another custom generator.
Public Class GeneratorDateRanges Inherits Generator End Classpublic class GeneratorDateRanges: Generator { }Add the DatabaseSchemaProviderCompatibilityAttribute, as shown in the following example:
<DatabaseSchemaProviderCompatibility(GetType(SqlDatabaseSchemaProvider))> _ Public Class GeneratorDateRanges Inherits Generator End Class[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))] public class GeneratorDateRanges : Generator { }For more information about the extension compatibility attribute, see Extending the Database Features of Visual Studio.
On the File menu, click Save All.
Adding the Input Properties
This custom data generator accepts two date ranges as the input. To specify each range, the user specifies the minimum and the maximum date for each. Therefore, you must create four input properties in total: two minimum dates and two maximum dates.
To add the input properties
Create four member variables to hold the minimum and maximum dates for the two date ranges:
Dim range1MinValue As SqlDateTime Dim range1MaxValue As SqlDateTime Dim range2MinValue As SqlDateTime Dim range2MaxValue As SqlDateTimeSqlDateTime range1MinValue; SqlDateTime range1MaxValue; SqlDateTime range2MinValue; SqlDateTime range2MaxValue;Create four properties to set the minimum and maximum dates for the two date ranges. The properties must have the InputAttribute to identify them as input properties.
Note
In order to specify the input properties for this data generator in the Properties window, you must provide a type converter that converts the input values to and from the SqlDateTime type. To do this, you will add a supporting SqlDateTimeConverter class later in this walkthrough.
<Input(TypeConverter:=GetType(SqlDateTimeConverter))> _ Public Property Range1Min() as SqlDateTime <Input(TypeConverter:=GetType(SqlDateTimeConverter))> _ Public Property Range1Max() as SqlDateTime <Input(TypeConverter:=GetType(SqlDateTimeConverter))> _ Public Property Range2Min() as SqlDateTime <Input(TypeConverter:=GetType(SqlDateTimeConverter))> _ Public Property Range2Max() as SqlDateTime[Input(TypeConverter = typeof(SqlDateTimeConverter))] public SqlDateTime Range1Min { set {range1MinValue = value;} get {return range1MinValue;} } [Input(TypeConverter = typeof(SqlDateTimeConverter))] public SqlDateTime Range1Max { set {range1MaxValue = value;} get {return range1MaxValue;} } [Input(TypeConverter = typeof(SqlDateTimeConverter))] public SqlDateTime Range2Min { set {range2MinValue = value;} get {return range2MinValue;} } [Input(TypeConverter = typeof(SqlDateTimeConverter))] public SqlDateTime Range2Max { set {range2MaxValue = value;} get {return range2MaxValue;} }On the File menu, click Save All.
Adding the Output Property
This custom data generator returns one random date as the output. Therefore, you must create one output property.
To add the output property
Create a member variable to hold the random date that is the output:
Dim randomDateValue As SqlDateTimeSqlDateTime randomDateValue;Create a property to return the random date as the output. The property must have the OutputAttribute to identify it as an output property.
<Output()> _ Public ReadOnly Property RandomDate() As SqlDateTime Get Return randomDateValue End Get End Property[Output] public SqlDateTime RandomDate { get {return randomDateValue;} }On the File menu, click Save All.
Overriding the OnInitialize Method
When you generate random data, it can be deterministic or non-deterministic. Deterministic data repeats the same data every time that you generate it from the same seed. All data generators have a Seed property that the user can set. You can override the OnInitialize method to seed the Random objects and make your generator deterministic.
To override the OnInitialize method
Create two member variables to generate random numbers, as shown in the following example. One variable generates a random date. The other variable randomly chooses between the two possible date ranges.
Dim random As Random Dim randomRange As RandomRandom random; Random randomRange;Override the OnInitialize method:
Protected Overrides Sub OnInitialize(ByVal initInfo As GeneratorInit) random = New Random(Me.Seed) 'deterministic randomRange = New Random(Me.Seed) 'deterministic 'random = New Random() 'non-deterministic 'randomRange = New Random() 'non-deterministic MyBase.OnInitialize(initInfo) End Subprotected override void OnInitialize(GeneratorInit initInfo) { random = new Random(this.Seed); //deterministic randomRange = new Random(this.Seed); //deterministic //random = new Random(); //non-deterministic //randomRange = new Random(); //non-deterministic base.OnInitialize(initInfo); }On the File menu, click Save All.
Overriding the OnGenerateNextValues Method
Visual Studio Premium calls the OnGenerateNextValues method of the generator to create the data that it needs. You must override this method to provide the logic that generates the random date for your output property.
To override the OnGenerateNextValues method
Override the OnGenerateNextValues method, as shown in the following example:
Protected Overrides Sub OnGenerateNextValues() Dim min As SqlDateTime Dim max As SqlDateTime 'Generate a random date from either range 1 or range 2. 'Randomly select either range 1 or range 2 by randomly 'generating an odd or an even random number. '------------------------------------------------------------ If randomRange.Next() Mod 2 = 0 Then 'check for odd or even min = range1MinValue max = range1MaxValue Else min = range2MinValue max = range2MaxValue End If 'The formula for creating a random number in a specific range is: 'start of range + (size of range * random number between 0 and 1) 'size of range Dim range As TimeSpan = max.Value - min.Value '(size of range * random number between 0 and 1) Dim randomNumber As TimeSpan = New TimeSpan(CLng(range.Ticks * random.NextDouble())) 'start of range + (size of range * random number between 0 and 1) randomDateValue = min + randomNumber End Subprotected override void OnGenerateNextValues() { SqlDateTime min; SqlDateTime max; //Generate a random date from either range 1 or range 2. //Randomly select either range 1 or range 2 by randomly //generating an odd or an even random number. //------------------------------------------------------------ if (randomRange.Next() % 2 == 0) //check for odd or even { min = range1MinValue; max = range1MaxValue; } else { min = range2MinValue; max = range2MaxValue; } //The formula for creating a random number in a specific range is: //start of range + (size of range * random number between 0 and 1) //size of range TimeSpan range = max.Value - min.Value; //(size of range * random number between 0 and 1) TimeSpan randomNumber = new TimeSpan((long)(range.Ticks * random.NextDouble())); //start of range + (size of range * random number between 0 and 1) randomDateValue = min + randomNumber; }On the File menu, click Save All.
Defining the Type Converter
In order to specify the input properties for this data generator in the Properties window, you must provide a type converter that converts the input values to and from the SqlDateTime type.
To create the SqlDateTime type converter class
On the Project menu, click Add Class.
The Add New Item dialog box appears.
In Name, type SqlDateTimeConverter.
At the top of the Code window, before the class declaration, add the following lines of code:
Imports System.ComponentModel Imports System.Data.SqlTypes Imports System.Globalizationusing System.ComponentModel; using System.Data.SqlTypes; using System.Globalization;Specify that your class inherits from TypeConverter:
Public Class SqlDateTimeConverter Inherits TypeConverter End Classpublic class SqlDateTimeConverter: TypeConverter { }Within the class declaration, add the class constructor. If you are writing the type converter class in Visual Basic, go to step 6.
public SqlDateTimeConverter() { }Following the class constructor, add a method that checks to see whether a particular conversion is possible by this type converter:
Public Overrides Function CanConvertFrom(ByVal context As ITypeDescriptorContext, ByVal sourceType As Type) As Boolean Dim result As Boolean result = False If (sourceType Is GetType(System.String)) Then result = True Else result = MyBase.CanConvertFrom(context, sourceType) End If Return result End Function Public Overrides Function CanConvertTo(ByVal context As System.ComponentModel.ITypeDescriptorContext, ByVal destinationType As System.Type) As Boolean If (destinationType Is GetType(System.String)) Then Return True End If Return MyBase.CanConvertTo(context, destinationType) End Functionpublic override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType) { bool result = false; if (sourceType == typeof(string)) { result = true; } else { result = base.CanConvertFrom(context, sourceType); } return result; } public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType) { if (destinationType == typeof(string)) { return true; } return base.CanConvertTo(context, destinationType); }Finally, add the converter methods:
Public Overrides Function ConvertFrom(ByVal context As System.ComponentModel.ITypeDescriptorContext, ByVal culture As System.Globalization.CultureInfo, ByVal value As Object) As Object Dim dateTimeString As String dateTimeString = value.ToString If (dateTimeString.Length > 0) Then Dim dateTime As Date dateTime = Date.Parse(dateTimeString, culture) Return New SqlDateTime(dateTime) End If Return MyBase.ConvertFrom(context, culture, value) End Function Public Overrides Function ConvertTo(ByVal context As System.ComponentModel.ITypeDescriptorContext, ByVal culture As System.Globalization.CultureInfo, ByVal value As Object, ByVal destinationType As System.Type) As Object If (destinationType Is GetType(System.String)) Then Dim dateTime As Date dateTime = CType(value, SqlDateTime).Value Return dateTime.ToString(culture) End If Return MyBase.ConvertTo(context, culture, value, destinationType) End Functionpublic override object ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, object value) { string dateTimeString = value as string; if (dateTimeString != null) { DateTime dateTime = DateTime.Parse(dateTimeString, culture); return new SqlDateTime(dateTime); } return base.ConvertFrom(context, culture, value); } public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType) { if (destinationType == typeof(string)) { DateTime dateTime = ((SqlDateTime)value).Value; return dateTime.ToString(culture); } return base.ConvertTo(context, culture, value, destinationType); }On the File menu, click Save All.
Signing the Generator
All custom data generators must be signed with a strong name before they are registered.
To sign the generator with a strong name
On the Project menu, click GeneratorDateRanges Properties to open the project properties.
On the Signing tab, select the Sign the assembly check box.
In the Choose a strong name key file box, click <New...>.
In the Key file name box, type GeneratorDateRangesKey, type and confirm a password, and then click OK.
When you build your solution, the key file is used to sign the assembly.
On the File menu, click Save All.
On the Build menu, click Build Solution.
Your data generator has now been built. Next, you must register it on your computer so that you can use it in data generation plans.
Registering the Generator
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
On the View menu, click Other Windows, and then click Command Window to open the Command window.
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? System.Reflection.Assembly.LoadFrom(@"FilePath").FullNamePress Enter. The line should resemble the following with your specific PublicKeyToken:
" GeneratorDateRanges, 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
In Solution Explorer, select the GeneratorDateRanges project.
On the Project menu, select Add New Item.
In the Templates pane, locate and select the XML File item.
In the Name text box, type GeneratorDateRanges.Extensions.xml and click the Add button.
The GeneratorDateRanges.Extensions.xml file is added to the project in Solution Explorer.
Note
You must use the name of your dll (in this case, "GeneratorDateRanges" followed by ".Extensions.xml") for the assembly to register correctly.
Open the GeneratorDateRanges.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="GeneratorDateRanges.GeneratorDateRanges".
<?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="GeneratorDateRanges.GeneratorDateRanges" assembly=" GeneratorDateRanges, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn" enabled="true"/> </extensions>On the File menu, click Save All.
Next, you will copy the assembly and XML file to the Extensions directory. When Visual Studio Premium starts, it will identify any extensions in the %Program Files%\Microsoft Visual Studio 10.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
Create a folder that is named CustomGenerators in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\ directory.
Copy the GeneratorDateRanges.dll assembly file from the ProjectFolder\GeneratorDateRanges\GeneratorDateRanges\bin\Debug\ directory to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\CustomGenerators directory that you created.
Copy the GeneratorDateRanges.Extensions.xml file from the ProjectFolder\GeneratorDateRanges\GeneratorDateRanges\ directory to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\CustomGenerators directory that you created.
Note
A best practice is to put your extension assemblies in a folder in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions directory. This strategy will help you identify which extensions were included with the product and which ones are your custom creations. You should also consider organizing your extensions into category-specific folders.
Testing the Date Ranges Generator
Now that you have created the DateRanges data generator, you must start a new instance of Visual Studio. When Visual Studio starts, it will register the GeneratorDateRanges assembly that you added to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\CustomGenerators directory.
Next, you will create a database project in which you can verify that the DateRanges data generator works correctly.
To create the database project
Start a new instance of Visual Studio, which will recognize the GeneratorDateRanges.dll assembly and register it.
On the File menu, point to New, and click Project.
The New Project dialog box appears.
In the list of Installed Templates, expand Database, and click SQL Server.
In Templates, click SQL Server 2008 Wizard.
In Name, type SampleGeneratorDB.
Note
If you have already completed all the steps in Walkthrough: Creating a Custom Data Generator, you have already created the SampleGeneratorDB project, and you can go to the next procedure, To add a table to the database project.
Select the Create directory for solution check box.
Accept the default values for Location, Solution Name, and Add to Source Control, and then click OK.
Click Finish. When the wizard is finished, click Finish again.
The new database project, SampleGeneratorDB, appears in Solution Explorer.
On the View menu, 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 DateRange SQL type.
To add a table to the database project
In Schema View, expand the SampleGeneratorDB node, expand the Schemas node, expand the dbo node, and then click the Tables node.
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.
In Templates, click Table.
Note
In the Categories list, you can click Tables and Views to more easily find the template for a table.
In Name, type TableDates as the name that you want to give the new table.
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 editor appears and displays the definition for your new table.
In the Transact-SQL editor, modify the table definition to match the following example:
CREATE TABLE [dbo].[TableDates] ( dates DateTime )On the File menu, click Save TableDates.table.sql.
With the table in place, you will now add a check constraint to verify that the date ranges used are valid.
Note
You will enter corresponding date ranges for the custom DateRanges data generator's property values in another procedure. For more information, see Creating and Configuring the Data Generation Plan.
To add a check constraint to the table
In Schema View, expand the Tables node and click the TableDates table.
On the Project menu, click Add New Item.
In Templates, click Check Constraint.
Note
You can also right-click the Dates table in Schema View, point to Add, and click Check Constraint.
In Name, type CheckConstraintDateRanges as the name that you want to give the new check constraint.
Click Add to add the check constraint to your database project.
Solution Explorer shows the new file for the check constraint in your database project. Schema View shows the new check constraint object. The Transact-SQL editor appears and displays the definition for your new check constraint.
In the Transact-SQL editor, modify the check constraint to match the following example:
ALTER TABLE [dbo].[TableDates] ADD CONSTRAINT [CheckConstraintDateRanges] CHECK ((dates BETWEEN '1/1/1800' AND '1/1/1900')OR(dates BETWEEN '1/1/1979' AND '12/31/2008'))On the File menu, click Save Dates.CheckConstraintDateRanges.chkconst.sql.
Note
If you have already completed all the steps in Walkthrough: Creating a Custom Data Generator, you can go to the section Creating and Configuring the Data Generation Plan.
With the table and check constraint in place, you can now configure the database for deployment.
To configure project deployment settings
In Solution Explorer, click SampleGeneratorDB (the project, not the solution).
On the Project menu, click SampleGeneratorDB Properties.
The project's Properties window appears.
Note
You can also right-click SampleGeneratorDB in Solution Explorer and click Properties.
Click the Deploy tab.
In Deploy action, click Create a deployment script (sql) and deploy to database.
Click the Edit button to specify the target connection.
Specify the information to connect to the database server to which you want to deploy the SampleGeneratorDB database.
In Select or enter a database name, type SampleGeneratorDB.
Click OK.
Target Connection is populated with the connection string. Note that the Target Database Name is set to SampleGeneratorDB.
Accept the default values for the other options.
On the File menu, click Save Selected Items.
Your project build settings are saved.
Next, you will build your 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 your database project.
To deploy the database project to the database server
In Solution Explorer, click SampleGeneratorDB (the project, not the solution).
On the Build menu, click Deploy SampleGeneratorDB.
Your database project is deployed by using the connection that you specified in the build configuration. If successful, 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 fill with data. For more information, see How to: Create Data Generation Plans.
To create and configure the data generation plan
In Solution Explorer, select the Data Generation Plans node.
On the Project menu, click Add New Item.
In the Categories pane, click Data Generation Plan.
In the Templates pane, click Data Generation Plan.
In the Name text box, type SampleGenerator.dgen.
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.TableDates 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 Generator, and then clicking Preview Data Generation. 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 Dock as Tabbed Document.
In the SampleGenerator.dgen designer, verify that the dbo.TableDates table and the dates column are both checked.
In the table, change the value under Rows to Insert to 500.
In the SampleGenerator.dgen designer, select the dates column and click the Generator drop-down to select the GeneratorDateRanges.
With the dates column selected, in the Properties window, type values for the two date range inputs:
Range1Max 12/31/2008 12:00:00 AM
Range1Min 1/1/1979 12:00:00 AM
Range2Max 1/1/1900 12:00:00 AM
Range2Min 1/1/1800 12:00 AM
Your custom address generator is now correctly configured.
On the File menu, click Save All.
Running the Data Generation Plan to Generate Date Ranges Data
Finally, you will run the data generation plan and see your custom date ranges data generator in action.
To run the plan to generate data
In Solution Explorer, click SampleGenerator.dgen.
Note
The data generation plan must be open. If the plan is not open, open it first.
On the Data menu, point to Data Generator, and then click Generate Data.
The Connect to Database dialog box appears.
In the Data Generation Connection Information list, click the SampleGeneratorDB database, and then click OK.
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.
(Optional) Use a different tool to log on to the database. You can use the Transact-SQL editor that is provided in Visual Studio Premium for this step. For more information, see Editing Database Scripts and Objects with the Transact-SQL Editor on the Microsoft web site. View the new data by running the following query:
use SampleGeneratorDB select * from dbo.TableDatesThe Results tab should display the resulting 500 dates.
(Optional) In the Properties window, change the value of Range1Max to 12/31/3000 12:00:00 AM and run the data generator again by repeating steps 2 through 5.
In the Error List window, you will see an error that is generated because of the check constraint being violated, which is caused by the date range being changed outside the range allowed.
See Also
Tasks
Walkthrough: Creating a Custom Data Generator
How to: Register and Manage Feature Extensions
Troubleshooting Feature Extensions
Reference
Microsoft.Data.Schema.Tools.DataGenerator
Concepts
Generate Specialized Test Data with a Custom Data Generator