Trying to generate on the fly NHS numbers for my UAT server

The simplest way to populate your database with test data.

Moderators: David Atkinson, Anu Deshpande, Lionel

Trying to generate on the fly NHS numbers for my UAT server

Postby jonathan.baggaley » Tue Apr 20, 2010 5:05 pm

I am sure there is something simple in what am I doing wrong but I just can't see it! I am trying to generate sample nhs numbers for my UAT database which the code below does. However, when run through the data generator I only get one entry. If I stick the generate part in a loop (see commented bit) I just get one entry repeated. how do I get different entries and also how do I make sure they are unique over the whole dataset generated?
BTW RG - the code generator version in Codeplex is broken as the object model is different.

Thanks

Jon

Code: Select all
using System;
using RedGate.SQLDataGenerator.Engine.Generators;
using RedGate.SQLDataGenerator.Engine.Generators.Static;

namespace NHS
{
    [Generator(typeof(string), \"Generic\", \"NHS Number\", \"A valid check digited NHS number\")]
    public class NHSNumberGenerator : IGenerator
    {
        private int m_Seed;
        private bool m_Unique;

        public NHSNumberGenerator(GeneratorParameters parameters)
        {
        }

        public System.Collections.IEnumerator GetEnumerator(GenerationSession session)
        {
            yield return GenerateNHSNumber();

            //for (int counter = 0; 0 < 1000; counter++)
            //{
            //    yield return GenerateNHSNumber();
            //}

            //Random r = new Random(0);
            //while (true)
            //{
            //    yield return r.Next(0, 1024) * 8;
            //}
        }

        #region ISeedableGenerator Members

        public int Seed
        {
            get { return m_Seed; }
            set { m_Seed = value; }
        }



        #endregion
        #region IUniqueableGenerator Members

        public bool Unique
        {
            get { return m_Unique; }
            set { m_Unique = value; }
        }
        #endregion       
        /// <summary>
        /// Determines whether the specified NHS number to test is a valid NHS number - including the temporary derivatives.
        /// </summary>
        /// <param name=\"nhsNumberToTest\">The NHS number to test.</param>
        /// <returns>
        ///    <c>true</c> if [is valid NHS number] [the specified NHS number to test]; otherwise, <c>false</c>.
        /// </returns>
        private bool IsValidNHSNumber(string nhsNumberToTest)
        {
            bool result = true;

            if (nhsNumberToTest.Length == 10)
                result = StandardNHSTest(nhsNumberToTest);
            else if (nhsNumberToTest.Length >= 5)
                result = TemporaryNHSNumberTest(nhsNumberToTest);
            else
                result = false;

            return result;
        }

        private bool TemporaryNHSNumberTest(string nhsNumberToTest)
        {
            bool result = true;
            if (nhsNumberToTest.Length < 7)
                result = false;

            return result;

        }

        private bool StandardNHSTest(string nhsNumberToTest)
        {
            int calcResult = 0;
            bool returnValue = true;

            // Remove any spaces if number is in 3 3 4 format
            nhsNumberToTest = nhsNumberToTest.Replace(\" \", string.Empty);

            // Simple validation
            if (nhsNumberToTest.Length != 10)
                // Only 10 characters allowed
                returnValue = false;
            else if (!IsNumeric(nhsNumberToTest))
                // is numeric
                returnValue = false;
            else if (\",0000000000,1111111111,2222222222,3333333333,4444444444,5555555555,6666666666,7777777777,8888888888,9999999999\".Contains(nhsNumberToTest))
                // Make sure no consecutive numbers
                returnValue = false;

            if (returnValue)
            {
                // Step 1 - add the numbers multiplied by their weighting
                for (int digitIndex = 0; digitIndex < 9; digitIndex++)
                {
                    calcResult += Convert.ToInt32(nhsNumberToTest.Substring(digitIndex, 1)) * (10 - digitIndex);
                }

                // Step 2 Mod 11 calcuation to get remainder
                calcResult = calcResult % 11;

                // Step 3 - take remainder from 11 to give check digit
                calcResult = 11 - calcResult;
                if (calcResult == 11)
                {
                    calcResult = 0;
                }

                // Test calculated check digit against real digit
                if (Convert.ToInt32(nhsNumberToTest.Substring(9, 1)) != calcResult)
                {
                    returnValue = false;
                }
            }
            return returnValue;
        }
        /// <summary>
        /// Helper function to generate valid NHS numbers.
        /// </summary>
        /// <returns></returns>
        private string GenerateNHSNumber()
        {
            string nhsNumberToTest;
            Random random = new Random(m_Seed);
            int calcResult = 0;

            nhsNumberToTest = random.Next(100000000, 999999998).ToString();

            // Step 1 - add the numbers multiplied by their weighting
            for (int digitIndex = 0; digitIndex < 9; digitIndex++)
            {
                calcResult += Convert.ToInt32(nhsNumberToTest.Substring(digitIndex, 1)) * (10 - digitIndex);
            }

            // Step 2 Mod 11 calcuation to get remainder
            calcResult = calcResult % 11;

            // Step 3 - take remainder from 11 to give check digit
            calcResult = 11 - calcResult;
            if (calcResult == 11)
                calcResult = 0;
            else if (calcResult >= 10)  // If >10 is an invalid number so generate another
                nhsNumberToTest = GenerateNHSNumber();
            else
                nhsNumberToTest += calcResult.ToString();

            // Do a last sanity check to stop invalid checkdigit numbers coming through.
            if (StandardNHSTest(nhsNumberToTest))
                return nhsNumberToTest;
            else
                return GenerateNHSNumber();
        }

        /// <summary>
        /// Determines whether the specified expression is numeric.
        /// </summary>
        /// <param name=\"expression\">The expression.</param>
        /// <returns>
        ///    <c>true</c> if the specified expression is numeric; otherwise, <c>false</c>.
        /// </returns>
        private Boolean IsNumeric(Object expression)
        {
            if (expression == null || expression is DateTime)
                return false;

            if (expression is Int16 || expression is Int32 || expression is Int64 || expression is Decimal || expression is Single || expression is Double || expression is Boolean)
                return true;

            try
            {
                if (expression is string)
                    Double.Parse(expression as string);
                else
                    Double.Parse(expression.ToString());
                return true;
            }
            catch { } // just dismiss errors but return false
            return false;
        }

    }

}
:?:
jonathan.baggaley
 
Posts: 5
Joined: Tue Apr 20, 2010 4:56 pm
Location: Berkshire

Postby Brian Donahue » Wed Apr 28, 2010 10:41 am

Hi Jon,

I think I've got it working -- there seems to be a randomization problem to do with the seed. In the few code examples we have, there is a mention that the Random() object will get a new seed for each row from Data Generator itself, but I don't think that logic applies when you use your own function to generate random numbers. So what I have done it to add a private m_Random variable for use through the entire session, rather than a new Random() every time you execute GenerateNHSNumber(). This gives me a different number for every row.
Code: Select all
using System;
using RedGate.SQLDataGenerator.Engine.Generators;
using RedGate.SQLDataGenerator.Engine.Generators.Static;

namespace NHS
{
    [Generator(typeof(string), \"Generic\", \"NHS Number\", \"A valid check digited NHS number\")]
    public class NHSNumberGenerator : IGenerator
    {
        private int m_Seed;
        private bool m_Unique;
        private Random m_Random = null;

        public NHSNumberGenerator(GeneratorParameters parameters)
        {
        }

        public System.Collections.IEnumerator GetEnumerator(GenerationSession session)
        {
            //yield return GenerateNHSNumber();

            for (int counter = 0; 0 < 1000; counter++)
            {
               yield return GenerateNHSNumber();
            }

            //Random r = new Random(0);
            //while (true)
            //{
            //    yield return r.Next(0, 1024) * 8;
            //}
        }

        #region ISeedableGenerator Members

        public int Seed
        {
            get { return m_Seed; }
            set { m_Seed = value; }
        }



        #endregion
        #region IUniqueableGenerator Members

        public bool Unique
        {
            get { return m_Unique; }
            set { m_Unique = value; }
        }
        #endregion
        /// <summary>
        /// Determines whether the specified NHS number to test is a valid NHS number - including the temporary derivatives.
        /// </summary>
        /// <param name=\"nhsNumberToTest\">The NHS number to test.</param>
        /// <returns>
        ///    <c>true</c> if [is valid NHS number] [the specified NHS number to test]; otherwise, <c>false</c>.
        /// </returns>
        private bool IsValidNHSNumber(string nhsNumberToTest)
        {
            bool result = true;

            if (nhsNumberToTest.Length == 10)
                result = StandardNHSTest(nhsNumberToTest);
            else if (nhsNumberToTest.Length >= 5)
                result = TemporaryNHSNumberTest(nhsNumberToTest);
            else
                result = false;

            return result;
        }

        private bool TemporaryNHSNumberTest(string nhsNumberToTest)
        {
            bool result = true;
            if (nhsNumberToTest.Length < 7)
                result = false;

            return result;

        }

        private bool StandardNHSTest(string nhsNumberToTest)
        {
            int calcResult = 0;
            bool returnValue = true;

            // Remove any spaces if number is in 3 3 4 format
            nhsNumberToTest = nhsNumberToTest.Replace(\" \", string.Empty);

            // Simple validation
            if (nhsNumberToTest.Length != 10)
                // Only 10 characters allowed
                returnValue = false;
            else if (!IsNumeric(nhsNumberToTest))
                // is numeric
                returnValue = false;
            else if (\",0000000000,1111111111,2222222222,3333333333,4444444444,5555555555,6666666666,7777777777,8888888888,9999999999\".Contains(nhsNumberToTest))
                // Make sure no consecutive numbers
                returnValue = false;

            if (returnValue)
            {
                // Step 1 - add the numbers multiplied by their weighting
                for (int digitIndex = 0; digitIndex < 9; digitIndex++)
                {
                    calcResult += Convert.ToInt32(nhsNumberToTest.Substring(digitIndex, 1)) * (10 - digitIndex);
                }

                // Step 2 Mod 11 calcuation to get remainder
                calcResult = calcResult % 11;

                // Step 3 - take remainder from 11 to give check digit
                calcResult = 11 - calcResult;
                if (calcResult == 11)
                {
                    calcResult = 0;
                }

                // Test calculated check digit against real digit
                if (Convert.ToInt32(nhsNumberToTest.Substring(9, 1)) != calcResult)
                {
                    returnValue = false;
                }
            }
            return returnValue;
        }
        /// <summary>
        /// Helper function to generate valid NHS numbers.
        /// </summary>
        /// <returns></returns>
        private string GenerateNHSNumber()
        {
            string nhsNumberToTest;
            if (m_Random == null) m_Random = new Random(m_Seed);
           
            int calcResult = 0;

            nhsNumberToTest = m_Random.Next(100000000, 999999998).ToString();

            // Step 1 - add the numbers multiplied by their weighting
            for (int digitIndex = 0; digitIndex < 9; digitIndex++)
            {
                calcResult += Convert.ToInt32(nhsNumberToTest.Substring(digitIndex, 1)) * (10 - digitIndex);
            }

            // Step 2 Mod 11 calcuation to get remainder
            calcResult = calcResult % 11;

            // Step 3 - take remainder from 11 to give check digit
            calcResult = 11 - calcResult;
            if (calcResult == 11)
                calcResult = 0;
            else if (calcResult >= 10)  // If >10 is an invalid number so generate another
                nhsNumberToTest = GenerateNHSNumber();
            else
                nhsNumberToTest += calcResult.ToString();

            // Do a last sanity check to stop invalid checkdigit numbers coming through.
            if (StandardNHSTest(nhsNumberToTest))
                return nhsNumberToTest;
            else
                return GenerateNHSNumber();
        }

        /// <summary>
        /// Determines whether the specified expression is numeric.
        /// </summary>
        /// <param name=\"expression\">The expression.</param>
        /// <returns>
        ///    <c>true</c> if the specified expression is numeric; otherwise, <c>false</c>.
        /// </returns>
        private Boolean IsNumeric(Object expression)
        {
            if (expression == null || expression is DateTime)
                return false;

            if (expression is Int16 || expression is Int32 || expression is Int64 || expression is Decimal || expression is Single || expression is Double || expression is Boolean)
                return true;

            try
            {
                if (expression is string)
                    Double.Parse(expression as string);
                else
                    Double.Parse(expression.ToString());
                return true;
            }
            catch { } // just dismiss errors but return false
            return false;
        }

    }

}
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Brilliant!

Postby jonathan.baggaley » Wed Apr 28, 2010 11:09 am

Thanks very much for your help on this - I can now create loads of pseudo patients for testing with! :)
A few suggestions for the next version of the generator
1) Inbuilt javascript generator
2) Ability to mark any customised generator as available for use in other tables within the project
3) Ability to add phantom columns which can also have any generation data applied to them e.g. if I want a one field address I can't do that at the moment as I don't have any other generated address fields (street, town, postcode) to aggregate with
4) Use the approach LinqPad currently do for creating more complex c# (including Linq queries) without it crashing the data generator...
5) Document the API/More samples!

Feel free to add my NHS number generator to your list of samples!

Regards

Jon
jonathan.baggaley
 
Posts: 5
Joined: Tue Apr 20, 2010 4:56 pm
Location: Berkshire

Postby CraigOttley » Fri Dec 17, 2010 11:42 am

Why couldn't you use a Regular Expression?

IE

99(8|9) \\d{3} \\d{4}
CraigOttley
 
Posts: 13
Joined: Thu Jul 15, 2010 11:50 am
Location: Newport Pagnell

Re:

Postby jbaggaley » Tue Aug 02, 2011 3:41 pm

CraigOttley wrote:Why couldn't you use a Regular Expression?

IE

99(8|9) \\d{3} \\d{4}


-- Because I needed valid NHS numbers complete with their calculated checkdigit which a regex would not be able to give me... :-)
:-)zz[
jbaggaley
 
Posts: 28
Joined: Wed Mar 29, 2006 3:14 pm


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests