Walkthrough: Creating a Custom Data Generator for a Check Constraint

Article
06/26/2007

You can use standard data generators to fill columns with data in Microsoft Visual Studio Team Edition for Database Professionals. 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 the 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 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.

Note

You can take another approach to creating a custom data generator and accomplish the same goal by using aggregation extensibility. For more information, see Walkthrough: Creating a Custom Data Generator that Aggregates Standard Generators.

In this walkthrough, you 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

To complete this walkthrough, you need the following:

Visual Studio Team Edition for Database Professionals

Creating the Custom Data Generator Class

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.

The Add Reference dialog box appears.
Click the .NET tab. In the Component Name list, click Microsoft.VisualStudio.TeamSystem.Data, and then click OK.
(Optional, Visual Basic only) In Solution Explorer, click Show All Files, and expand the References node to verify the new reference.

At the top of the Code window, before the class declaration, add the following line of code:

Imports Microsoft.VisualStudio.TeamSystem.Data.DataGenerator
Imports System.Data.SqlTypes

using Microsoft.VisualStudio.TeamSystem.Data.DataGenerator;
using System.Data.SqlTypes;

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 Class
```
```
public class GeneratorDateRanges: Generator
{
}
```
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 SqlDateTime

SqlDateTime 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.

<Input(TypeConverter:= GetType(SqlDateTimeConverter))> _
Public Property Range1Min() As SqlDateTime
    Set(ByVal value As SqlDateTime)
        range1MinValue = value
    End Set
    Get
        Return range1MinValue
    End Get
End Property

<Input(TypeConverter:= GetType(SqlDateTimeConverter))> _
Public Property Range1Max() As SqlDateTime
    Set(ByVal value As SqlDateTime)
        range1MaxValue = value
    End Set
    Get
        Return range1MaxValue
    End Get
End Property

<Input(TypeConverter:= GetType(SqlDateTimeConverter))> _
Public Property Range2Min() As SqlDateTime
    Set(ByVal value As SqlDateTime)
        range2MinValue = value
    End Set
    Get
        Return range2MinValue
    End Get
End Property

<Input(TypeConverter:= GetType(SqlDateTimeConverter))> _
Public Property Range2Max() As SqlDateTime
    Set(ByVal value As SqlDateTime)
        range2MaxValue = value
    End Set
    Get
        Return range2MaxValue
    End Get
End Property

[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 SqlDateTime
```
```
SqlDateTime 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 Random
```
```
Random 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 Sub

protected 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 Team Edition for Database Professionals 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 Sub

protected 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 property browser, 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.Globalization

using System.ComponentModel;
using System.Data.SqlTypes;
using System.Globalization;

Rename the class from Class1 to GeneratorDateRanges, and specify that your class inherits from TypeConverter.

 [Visual Basic]
Public Class SqlDateTimeConverter
    Inherits TypeConverter

End Class

public class SqlDateTimeConverter: TypeConverter
{
}

Within the class declaration, add the class constructor. If you are writing the type converter class in Visual Basic, skip 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 Function

public 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
        dateTime.ToString(culture)
    End If
    Return MyBase.ConvertTo(context, culture, value, destinationType)
End Function

        public 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;
                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.

Security

For more information, see Security of Data Generators.

Next Steps

Now that you have built your data generator, you must register it on your computer. For more information, see one of the following: