The Code Slinger

December 6, 2007

Unit Testing: Static Entity Method Parameter Counts

Filed under: .NET,C#,NUnit — Pete @ 7:30 pm

A common issue in our development cycle is that sometimes the DBA’s need to modify the signature of a stored procedure, however somehow the .NET developer(s) don’t get notified and so the corresponding calling wrapper to that stored procedure doesn’t get updated to reflect this new parameter (or removal of an obsolete old one).

So being as there is no compile time checking of parameter counts matching up the method call to the proc, everything compiles fine until someone at runtime actually tries to call the code for that procedure…and if conditions are wrong, it will blow up.

So here is my first attempt at writing a unit test harness that will at least notify me at the automated build level that I have a stored procedure who’s parameter count doesn’t match up with my code.

NOTE: The following was written for an Oracle database back-end, but could be easily modified for SQL server or any other DB.

So here’s a class that encapsulates the concept of an Address record.  I’ve omitted all the properties and actual Address data since it’s not germane to this topic.  However what is germane is the use of a static method called GetAddresses that takes optional parameters by which to look up particular addresses.  This is a wrapper method to call the stored procedure “TLB_ADDRESS_PKG.AddressGet”.

    public class Address

    {

        public static string m_SPGet = “TLB_ADDRESS_PKG.AddressGet”;

        public static List<Address> GetAddresses(int? a_ID, int? a_PersonID, int? a_ContactID, int? a_CompanyID)

        {

            return Singleton<DataProxy<AddressData, Address>>.Instance.ExecuteGetMultiple(

                m_SPGet,

                a_ID,

                a_PersonID,

                a_ContactID,

                a_CompanyID);

        }

    }

Now, our input to this procedure call is the four parameters listed.  So we would expect that our AddressGet SP takes exactly four input parameters.  This is what we want to unit test, in case somewhere down the line during further development, a developer or DBA decides to add or remove one of these parameters from the SP. 

So in my corresponding project for unit tests project I have the following class:

    [TestFixture()]

    public class AddressTests

    {

        [Test()]

        public void AddressesGet_Check_Procedure_Parameter_Count()

        {

            int method_pc = DBTestHelper<Address>.GetProcMethodWrapperParamCount(“GetAddresses”);

            int proc_pc = DBTestHelper<Address>.GetProcParamCount(Address.m_SPGet);

            Assert.AreEqual(method_pc, proc_pc);

 

        }

    }

Fairly straight forward, get the parameter count for the procedure from the DB.  Get the parameter count from the method in code, and compare the two.  Now let’s look at how we get each.

Here’s the helper class I created to do the legwork.

    public static class DBTestHelper<T> where T: class

    {

        private static string connectionString = “(your_DB_conn_string);”;

        private static OracleConnection conn = new OracleConnection(connectionString);

 

        public static int GetProcMethodWrapperParamCount(string methodname)

        {

            Type t = typeof(T);

            MethodInfo mi = t.GetMethod(methodname, System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.Public);

            ParameterInfo[] parms = mi.GetParameters();

            int cparamcount = parms.Length;

            return cparamcount;

        }

 

        private static string[] ProcedurePattern(string proc)

        {

            string[] spArray = proc.ToUpper().Split(new char[] { ‘.’ });

            return spArray;

        }

        public static int GetProcParamCount(string proc)

        {

            string[] spPattern = ProcedurePattern(proc);

 

            if(conn.State == System.Data.ConnectionState.Closed)

                conn.Open();

 

            string qry = “SELECT COUNT(*)” +

            ” FROM user_objects o, user_arguments a” +

            ” WHERE UPPER(o.object_name) = ‘” + spPattern[0] + “‘” +

            ” AND UPPER(a.object_name) = ‘” + spPattern[1] + “‘” +

            ” AND o.object_id = a.object_id” +

            ” AND a.in_out = ‘IN'” +

            ” AND argument_name IS NOT NULL ORDER BY position”;

 

            OracleCommand cmd = new OracleCommand(qry, conn);

 

            return int.Parse(cmd.ExecuteScalar().ToString());

        }

    }

The GetProcMethodWrapperParamCount takes the method name and looks it up on the generic type we used in the call (Address in this case).  The GetProcParamCount method simply calls a standard inline SQL query against the user_objects and user_arguments views built into Oracle which provide us information on an actual DB object, in this case the sproc AddressGet in the package TLB_ADDRESS_PKG.  Notice that I force the in_out property type to be “IN” since you have to provide an OUT param of type sys_refcursor in order to get data back into .NET.  This allows the count to be accurate with respect to input params only, which is what we’re concerned with.

Anyway, nothing too earth shattering here, but it is a nice clean way to unit test that your retrieval procedure parameter count matches the calling code in .NET that you’ve written to encapsulate it.

In a follow up to this (when I get time), I will show how an additional unit test can tell us further whether or not each parameter in the calling .NET method matches the expected Oracle parameter type, giving us an even clearer way of knowing if our procedure calls will blow up or not.

Til next time….keep on slangin!

Blog at WordPress.com.