Custom Generator string return val limitations?

bretlowery

Hi, I'm doing a POC of Data Generator 1 against a SQL Server 2008 SP1 database. I have successfully created and used the custom class generator listed below, but it only appears as an option for NVARCHAR columns, not VARCHAR columns. How can I get this generator to appear as an option on my VARCHAR columns? Are there other type or length limitations to returns types used in custom generators?

Thanks!

[Generator(typeof(String), "Generic", "Company Emails", "In [url=a.b@nowhere.com]a.b@nowhere.com[/url] format")]
public class EmailGenerator: IGenerator
{

protected List<string> testdata = new List<string>();

public EmailGenerator(GeneratorParameters parameters)
{
testdata.Add("marge.n.overror@nowhere.com");
testdata.Add("heywood.ubuzzoff@nowhere.com");
testdata.Add("heywood.ustopitt@nowhere.com");
testdata.Add("heywood.ulevmealohn@nowhere.com");
testdata.Add("les.izmore@nowhere.com");
testdata.Add("itwerks.onanoff@nowhere.com");
}

public System.Collections.IEnumerator GetEnumerator(GenerationSession session)
{
Random rnd = new Random(DateTime.Now.Millisecond);
while (true)
{
yield return testdata[rnd.Next(0, testdata.Count)];
}
}
}

James B

Thanks for your post.

I've created a generator myself based on your class, and got it to appear for both nvarchar and varchar columns.

Could you perhaps post your config file, or, if you prefer, email it to support@red-gate.com quoting F0040000 in the subject line?

Thanks!

bretlowery

I can send config file. Can you tell me which one you need?

Interestingly, the problem doesn't appear to be related to the data type, data length, nullability, or ordinal position of the column in the tables. I see my custom generator on most tables in my database, but not all. Weird.

I tried specifying a new "category" of generators by changing this:

[Generator(typeof(String), "Generic", "Company Emails", "In [url=a.b@nowhere.com]a.b@nowhere.com[/url] format")]

to:

[Generator(typeof(String), "Interclick", "Company Emails", "In [url=a.b@nowhere.com]a.b@nowhere.com[/url] format")]

which gave me the same symptoms on the same tables. I also reinstalled Data Generator, and created a brand new project on the same database, and have the same problem on the same tables.

Really weird.

I can send the entire generator .cs file to you if you'd like.

James B

I don't think the .cs file will be the problem. Once you have your DLL in place in the Generators folder, you need a config file to then help it show up in Data Generator. This will go in the "config" subfolder. For instance, when testing your class, I created "CompanyEmail.xml" with the following contents:


<?xml version="1.0" encoding="iso-8859-1"?>
<generators>
<generator /* Specify the class. */
type="EmailGenerator"
name="Company Emails"
description="In a.b@nowhere.com format"
category="Generic">

/* Specify the columns that match, and their score. */
<matches field="*" score="50"/>


/* Define the data types for which the generator is valid. */
<type type="string"/>
</generator>

bretlowery

Tried it, and it did not fix the problem. I've also tried reinstalling Data Generator, renaming the problematic table, the column in the table, and dropping then recreating my database; nothing worked. I've also checked my rights to the Data Generator folder and subfolders and I have full R/W access.

Again, on some columns in other tables with or without the same datatype and length, it worked both with and without the xml file.

I also modified my .sqlgen file by hand, copying the custom generator from a different table and column where it worked correctly, and pasting the generator block in the problematic column's generator tag. I was able to save and load the project successfully, but it still had the same problem, and after I saved the project again it overwrote my sqlgen file change.

I'm stumped.

James B

Definitely a bit strange.

Is it possible for you to send over the database to us so we can test it here? You can mail it to support@red-gate.com quoting F0040000 in the subject line, along with details of which tables/columns are causing the problem (or include your project).

If could also include the xml config file and DLL it would be great, just in case I built mine slightly differently to yourself.

bretlowery

Stumped no longer; I figured it out. DataGenerator will not allow me to create a custom string generator on a varchar or nvarchar column if there is a unique index on the column. Once I removed the unique index, the custom generator appeared in the drop-down.

For my purposes, my custom generator will generate unique values as it's enumerated. Is there a way that this restraint can be removed?

Thanks for the support!

James B

That makes sense. You'll need to include the IUniqueable interface and implement it, then your generator will show.

Obviously if the Index is only including that column, it will probably fail due to the values not being... unique

See below for example:



using System;
using System.Collections.Generic;
using System.Text;
using RedGate.SQLDataGenerator.Engine;
using RedGate.SQLDataGenerator.Engine.Generators;
using RedGate.SQLDataGenerator.Engine.Generators.Static;

namespace CompanyEmailGenerator
{
[Generator(typeof(String), "Generic", "Company Emails", "In [url=a.b@nowhere.com]a.b@nowhere.com[/url] format")]
public class EmailGenerator : IGenerator, IUniqueableGenerator
{

protected List<string> testdata = new List<string>();
bool m_Unique;

public EmailGenerator(GeneratorParameters parameters)
{
testdata.Add("marge.n.overror@nowhere.com");
testdata.Add("heywood.ubuzzoff@nowhere.com");
testdata.Add("heywood.ustopitt@nowhere.com");
testdata.Add("heywood.ulevmealohn@nowhere.com");
testdata.Add("les.izmore@nowhere.com");
testdata.Add("itwerks.onanoff@nowhere.com");
}

public System.Collections.IEnumerator GetEnumerator(GenerationSession session)
{
Random rnd = new Random(DateTime.Now.Millisecond);
while (true)
{
yield return testdata[rnd.Next(0, testdata.Count)];
}
}

public bool Unique
{
get { return m_Unique; }
set { m_Unique = value; }
}
}
}

bretlowery

Got it, thx. Pls close.