Share via


Walkthrough: Creating a Custom Data Generator for a Check Constraint

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

  1. In Visual Studio, create a Class Library project in the language of your choice, and name it GeneratorDateRanges.

  2. On the Project menu, click Add Reference.

    The Add Reference dialog box appears.

  3. Click the .NET tab. In the Component Name list, click Microsoft.VisualStudio.TeamSystem.Data, and then click OK.

  4. (Optional, Visual Basic only) In Solution Explorer, click Show All Files, and expand the References node to verify the new reference.

  5. 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;
    
  6. 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
    {
    }
    
  7. 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

  1. 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;
    
  2. 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;}
    }
    
  3. 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

  1. Create a member variable to hold the random date that is the output.

    Dim randomDateValue As SqlDateTime
    
    SqlDateTime randomDateValue;
    
  2. 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;}
    }
    
  3. 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

  1. 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;
    
  2. 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);
    }
    
  3. 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

  1. 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;
    }
    
  2. 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

  1. On the Project menu, click Add Class.

    The Add New Item dialog box appears.

  2. In Name, type SqlDateTimeConverter.

  3. 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;
    
  4. 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
    {
    }
    
  5. 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()
    {
    }
    
  6. 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);
    }
    
  7. 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);
            }
    
  8. 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

  1. On the Project menu, click GeneratorDateRanges Properties to open the project 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...>.

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

  5. On the File menu, click Save All.

  6. 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:

See Also

Tasks

Walkthrough: Deploying a Custom Generator

Reference

Microsoft.VisualStudio.TeamSystem.Data.DataGenerator

Other Resources

Creating Custom Generators
An Overview of Data Generator Extensibility
Using Standard Generators
Data Generator Walkthroughs