Red Gate forums :: View topic - Trying to generate on the fly NHS numbers for my UAT server
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 1
SQL Data Generator 1 forum

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

Search in SQL Data Generator 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jonathan.baggaley



Joined: 20 Apr 2010
Posts: 5
Location: Berkshire

PostPosted: Tue Apr 20, 2010 5:05 pm    Post subject: Trying to generate on the fly NHS numbers for my UAT server Reply with quote

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:
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;
        }

    }

}
Question
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed Apr 28, 2010 10:41 am    Post subject: Reply with quote

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:
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;
        }

    }

}
Back to top
View user's profile Send private message
jonathan.baggaley



Joined: 20 Apr 2010
Posts: 5
Location: Berkshire

PostPosted: Wed Apr 28, 2010 11:09 am    Post subject: Brilliant! Reply with quote

Thanks very much for your help on this - I can now create loads of pseudo patients for testing with! Smile
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
Back to top
View user's profile Send private message
CraigOttley



Joined: 15 Jul 2010
Posts: 13
Location: Newport Pagnell

PostPosted: Fri Dec 17, 2010 11:42 am    Post subject: Reply with quote

Why couldn't you use a Regular Expression?

IE

99(8|9) \d{3} \d{4}
Back to top
View user's profile Send private message
jbaggaley



Joined: 29 Mar 2006
Posts: 28

PostPosted: Tue Aug 02, 2011 3:41 pm    Post subject: Re: Reply with quote

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... Smile
_________________
:-)zz[
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group