In this article we will develop our own generator and understand the basic concepts and ideas behind developing generators for SQL Data Generator. Specifically we will write an ISBN number generator for SQL Data Generator 1.0 Beta, that can generate unique ISBN-10 style strings. You will see that writing and developing your own Generators is simple and easy.
It is assumed that you are proficient at C# 2.0, have a good understanding of.NET and have access to SQL Data Generator on your machine.
To test your code you will also need access to either Microsoft SQL Server 2000 or Microsoft SQL Server 2005.
(Please note that there may be some changes between Beta and final release, as a result any generators developed may need to be modified and rebuilt for the final release.)
All the code is available in the attached C# project.
Architecture
Here is a simple diagram of the architecture of the product.
(insert image)
This discussion is about the plug-in architecture for the generators.
The engine defines a series of interfaces and a generator must implement a series of interfaces in order for the engine to consider it to be a Generator. At start-up a specified directory is scanned for DLLs. Each of these DLLs in turn is loaded up and reflection is used to see if any public classes implement these interfaces. If this is the case then they are considered to be generators and accessible to the rest of the system.
There are a series of interfaces defined by the engine. Each interface is very light-weight. As the developer of the generator, it's up to you to decide how many of these interfaces you wish to implement.
Basic Interface "IGenerator"
This interface must be implemented in order to consider that your class is a generator. Here is the definition of the interface:
namespace RedGate.SQLDataGenerator.Engine.Generators
{
public interface IGenerator : IEnumerable
{
}
}
You must also implement a special constructor that takes a single parameter of type GeneratorParameters. This parameter describes the SQL field in the Table that is being assigned. You are at liberty to throw exceptions etc within your constructor if your code decides for some reason that it is not happy.
In order for you generator to appear within the UI you must also add a simple Generator attribute to your class.
You will also need to add the SupportSqlType for non string based generators. (This will probably not be required for the final version.). This attribute can be added multiple times and is used to define the targeted SQL Type. Currently the enumeration is based on the SqlTypes defined in the SQL Compare Engine.
Here is the code for producing random values for the 8 times table, between 0 and 1024.
namespace MyNameSpace
{
[SupportSqlType(SqlType.Integer64)]
[Generator(typeof(int), "Generic", "8 times table", "8, 0, 16, 256, ...")]
public class SimpleGenerator1 : IGenerator
{
public SimpleGenerator1(GeneratorParameters parameters)
{
}
public System.Collections.IEnumerator GetEnumerator()
{
Random r = new Random(0);
while (true)
{
yield return r.Next(0, 1024) * 8;
}
}
}
}
The Generator attribute should only be defined once per class and defines the the type of the .NET result, the Category that the generator is placed in, the name and the description of the generator in the UI.
Constructor
The GeneratorParameters gives access to the actual field. This allows the code to verify that lengths and types are consistent. This is likely to change for the final release.
GetEnumerator
The easiest way to implement this code is via the yield statement. As a result the code is very straightforward. The example here never runs out of values. However in reality this is not always possible. The engine is designed to cater for a limited number of values from the GetEnumerator. In addition it is fine for the GetEnumerator to throw exceptions in order communicate errors.
Interface "ISeedableGenerator"
This interface allows the generator to specify a seed. This allows the generator to generate different random data each time.
Here is the definition of the interface:
public interface ISeedableGenerator
{
int Seed { get; set; }
}
A typical implementation would be:
public int Seed
{
get { return m_Seed; }
set { m_Seed = value; }
}
This seed is then used to initialize the Random class, in the GetEnumerator.
The engine will automatically define a seed to the generator at initialization.
(Each column will have its own seed, hence allowing the same generator being
assigned multiple times within a table, but each column producing differing results.)
Please see SimpleGenerator2.cs for the full code implementation.
Interface "IUniqueableGenerator"
This interface allows the generator to specify if the data generated is unique. This allows the
generator to generate a unique value.
Here is the definition of the interface:
public interface IUniqueableGenerator
{
bool Unique { get; set; }
}
A typical implementation would be:
public bool Unique
{
get { return m_Unique; }
set { m_Unique = value; }
}
The GetEnumerator code now becomes more complex as there are two versions.
The non-unique version, as before, and the new Unique version. For the unique version
we just iterate in increments of one over the whole range. Please see SimpleGenerator3.cs
for the full code implementation.
This generator can now be assigned to unique fields. The engine automatically configures
the Unique flag on when the generator is assigned.
A grid Control is used for the UI . This can be controlled via the standard Microsoft Attributes.
There are some examples in this code.
Putting this altogther - ISBN Generator
The ISBN number generator is very simple and takes a similar approach to the
previous examples. However there is one point of interest which is how to
define unique values.
The engine has a particularly useful set of classes in the namespace
RedGate.SQLDataGenerator.Engine.Generators.Support, called UniqueValues
and RandomValues. These classes generate either a unique or non-unique sequence
from a specified seed from zero up to 1 billion.
This number is then used as the basis of the ISBN. Extra random numbers are added
the end of the ISBN to make up the required length.
This tack-tick allows a better distribution of random unique values as compared
to SimpleGenerator3 example.
Please check out ISBNGenerator.cs for a full copy of the source code.
This example is not complete and for example could be extended to support
the newer style ISBN-13.
Other Bits And Bobs
The INullableGenerator interface allows the generator to generate a certain
percentage of SQL Null values. There is a base abstract class called BaseGenerator
which does a lot of work for the developer, however it may or may not make it into
the final release. (See SimpleGenerator4.cs for an example, this code is a fully fledged
Times Table Generator that allows Nulls etc to be generated.)
Summary to write your own Generator
- Start up Visual Studio
- Create a Class Library .NET Project
- Add references to RedGate.SQLDataGenerator.Engine and RedGate.SQLCompare.Engine
- Point the output DLL to %Program Files%\Red Gate\SQL Data Generator 1\Generators\ .
(Assuming a standard installation)
- Create a public class that implements IGenerator
- Add the class attributes
- Implement the constructor
- Implement the method GetEnumerator
- Link to download .NET Project