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.
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:
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.
.gif) Caution |
|---|
| 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
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