Armadillo: Unit Testing of inline SQL with a little help from Attributes and Reflection

• Download Armadillo source code- 16.8 Kb
• Download Armadillo demo project- 29.2 Kb

Index

1. Introduction
2. Background
3. Target: Typical DAL code
4. Using the code & demo
5. The strategy behind the scenes: Attributes
   • DALClass
   • SqlExecute
   • SqlQuery
   • SqlTextExecute
   • SqlTextQuery
6. A simple test project
7. How it works: the testing engine explained
   • Checking SQL Query methods
   • Checking SQL Execute methods
   • Checking SQL Text methods
   • But, what about params?
   • The IDBInfo interface
8. Usage
   • When to use it
   • When NOT to use it
9. To Do list
10. References
11. Points of interest
12. History

If you have developed or maintained business applications, you will probably have found the following typical scenario several times:

• A well structured application with at least two or three layers: UI, Business Layer and Data Access Layer (DAL).
• A Relational Database Management System (RDBMS) behind the scenes doing all the hard work of persistence and data integrity.

When many people are involved in a system, with specific responsibilities (analyst, developers, DBA administrators, etc.) and sometimes, with contradictory requirements, you will find that databases are not frozen.

Normally, databases evolve (new tables, relations, changes in name's and data type’s fields) throughout the development process much more than we could have initially expected. Yes! Databases are alive!

On one hand, that’s a good thing: software is built to be changed. On the other hand, however, the problem arrives on the scene when we try to maintain our application robust enough surrounded by a rapid changing environment and functionality.

This common scenario is error-prone and is derived from the de-synchronization of applications and underlying databases. The tool and method provided in this article shows a way to automatically check the syntactical correctness of your SQL code in your Data Access Layer every time you need it.

In this way, you will have an automated way of detecting DB–Application inconsistencies inside your SQL code before releasing a new version of your software. It is useful to have a second chance to detect DAL bugs before compromising the robustness of your software.

Now, you have just realized where the name ‘Armadillo’ comes from: consider this technique as a defensive programming weapon for your daily programming toolbox.

Before going deeply in the topic, I will assume you have the following basic knowledge:

• SQL and database programming,
• C# .NET (especially a bit about Attributes and Reflection),
• Unit Testing (NUnit will be used), and
• (optionally) a bit of knowledge of (GoF) Design Patterns.

The examples provided assumes a MS SQL Server database. However, the technique does not depend on the RDBMS. It can be applied to Oracle or MySQL with minor changes (in fact, the Armadillo framework is prepared for such extensibility: only an interface must be implemented to support a new DB technology).

The example assumes the following DB schema (scripts provided for MS SQL Server):

You know, the classical, minimalist and educational DB for gathering Customers, Invoices, and Invoice Lines.

Before starting with testing, first let's have a look at a typical DAL, for example, the Customer code in the DAL assembly. This code is designed to encapsulate the Data Access Layer and contains all the SQL of the application, it manages connections and database APIs.

using System;
using System.Data;
using OKOKO.Armadillo.Attributes;
using System.Data.SqlClient;

namespace OKOKO.MyBiz.Dal
{
    /// <summary>
    /// Customer DAL class. Implements a classical CRUD example
    /// </summary>
    public class Customer
    {
        public static Customer Create(string id, string name, 
                               string surname, string phone)
        {
            //INSERT
            SqlCommand cmd = new SqlCommand(
                "INSERT INTO [Customer] ([id], [name], [surname], [phone])" +
                "VALUES(@id, @name,@surname,@phone)" , Dal.Cnx);

            cmd.Parameters.Add("@id", id);
            cmd.Parameters.Add("@name", name);
            cmd.Parameters.Add("@surname", surname);
            cmd.Parameters.Add("@phone", phone);

            cmd.ExecuteNonQuery();
            //TODO: Build result...
            return new Customer();
        }

The previous code shows a typical DAL code for the class Customer. The first method Create() implements the creation of a new customer in the database using the INSERT SQL command.

I have intentionally omitted part of the code (TODOs): the one that builds the response for the business layer to maintain us focused on the SQL, our main objective.

public static Customer Read(string id)
{
    //SELECT ID
    SqlCommand cmd = new SqlCommand(
        "Select * from customer where id=@id", //<-Sql code to be tested.
        Dal.Cnx);

    cmd.Parameters.Add("@id", id);

    SqlDataReader dr = cmd.ExecuteReader();
    //... read data and returns a Customers
    dr.Close();
    //TODO: Build result...
    return new Customer();
}

The second example (Read() method) implements a method to retrieve a customer from the database using a SQL SELECT clause.

The following methods: Update(), Delete(), GetAll(), and GetBySurname() in the Customer class (not shown here in the article) do what is expected of them: they update, delete, retrieve all, and retrieve by surname the customers from the database, respectively.

Finally, in some cases, the DAL code can contain code similar to this:

public static string GetSQLCustomersOrdered()
{
    return "select name, surname from" + 
           " customer order by surname, name";
}
public static string GetSQLCustomersWithPhone()
{
    return "select name, surname from Xcustomer" + 
           " where phone is not null" + 
           " order by surname, name";
} 
public static string GetSqlDeleteAll()
{
    return "delete customer";
}

These methods do not execute any SQL code. Instead, they return SQL statements in string form for further execution. OK, do not worry, we will also try to test this SQL code with Armadillo.

If you want to have a deeper look, you have more examples of the DAL code for Invoice and InvoiceLine classes (see the attached code).

Now, we will describe the steps for running the demo, and afterwards the implementation details will be provided.

Unzip the attached Zipped file. You will find the following material:

1. A DAL project example.
2. A project containing the attributes designed for this technique.
3. The Armadillo library (the test engine and attributes).
4. An SQL DB script to create a small DB example for MS SQL Server.

Follow these steps:

1. Unzip the attached file.
2. Execute the SQL script to create the database (you will need MS SQL Server installed).
3. Create a user, grant and check DB access permissions.
4. Change the configuration of the DB connection string (file: Dal.cs) to use such a created database and user.
5. Open and compile the solution.
6. Open the NUnit-GUI.
7. Select the DalMyByz.Test.dll assembly for unit testing.
8. Run the tests.

NUnit will execute the engine that tests all the attribute-labeled DAL code. NUnit shows success tests in green, and fails in red plus debug information (see the Console.Out tab for details).

If everything goes as expected, all tests will pass except two of them. The test log follows:

Testing Class: [OKOKO.MyBiz.Dal.Customer]
Testing SqlExecute Method: +OK Create()
Testing SqlQuery Method: +OK Read()
Testing SqlExecute Method: +OK Update()
Testing SqlExecute Method: +OK Delete()
Testing SqlQuery Method: +OK GetAll()
Testing SqlQuery Method: -FAIL GetBySurname()
>> Invalid column name 'surnameBUG'.
Testing SqlTextQuery Method: +OK GetSQLCustomersOrdered()
Testing SqlTextQuery Method: -FAIL GetSQLCustomersWithPhone()
>> Invalid object name 'Xcustomer'.
Testing SqlTextExecute Method: +OK GetSqlDeleteAll()
Class [OKOKO.MyBiz.Dal.Customer] tested. Results: OK: 7/9 Failed:2
Testing Class: [OKOKO.MyBiz.Dal.Invoice]
Testing SqlExecute Method: +OK Create()
Testing SqlQuery Method: +OK Read()
Testing SqlExecute Method: +OK Update()
Testing SqlExecute Method: +OK Delete()
Testing SqlQuery Method: +OK GetAll()
Testing SqlQuery Method: +OK GetInvoicesByCustomer()
Testing SqlTextQuery Method: +OK GetSQLInvoicesOrdered()
Class [OKOKO.MyBiz.Dal.Invoice] tested. Results: OK: 7/7 Failed:0
Testing Class: [OKOKO.MyBiz.Dal.InvoiceLine]
Testing SqlExecute Method: +OK Create()
Testing SqlQuery Method: +OK Read()
Testing SqlExecute Method: +OK Update()
Testing SqlExecute Method: +OK Delete()
Testing SqlQuery Method: +OK GetAll()
Testing SqlQuery Method: +OK GetByInvoice()
Class [OKOKO.MyBiz.Dal.InvoiceLine] tested. Results: OK: 6/6 Failed:0
-----------------------------------------------------------------
Final results: 3 classes & 22 methods tested.
Results: OK: 20/22 Failed: 2

A closer inspection in the log shows:

• The method GetBySurname() in the class Customer is using a wrong column name (surnameBUG). Remove the 'bug', replacing it by surname.
• The method GetSQLCustomersWithPhone() in the class Customer is using a wrong table name (XCustomer). Fix it by replacing it with Customer.

If you rebuild and execute the tests again, everything should be OK. The output will be something similar to:

Testing Class: [OKOKO.MyBiz.Dal.Customer]

...
Class [OKOKO.MyBiz.Dal.InvoiceLine] tested. Results: OK: 6/6 Failed:0
-----------------------------------------------------------------
Final results: 3 classes & 22 methods tested.
Results: OK: 22/22 Failed: 0

The main strategy was to label our DAL code with special attributes provided by the Armadillo framework. Later on, our testing engine will find by reflection such DAL methods and test them accordingly during the unit testing phase.

Let's review the attributes introduced by Armadillo:

DALClass is an attribute to label classes that implement our DAL code. The Customer class can be labeled with this attribute. This warns Armadillo to deal with this class when testing.

Usage example:

using System;
using System.Data;
using OKOKO.Armadillo.Attributes;
using System.Data.SqlClient;

namespace OKOKO.MyBiz.Dal
{
    /// <summary>
    /// Customer DAL class. Implements a classical CRUD example
    /// </summary>
    [DalClass()]
    public class Customer
    {
        ...

SQLExecute is an attribute to label methods that execute non query SQL code. Use this attribute whenever you find a piece of SQL that changes the state of your database, i.e., INSERT, UPDATE, DELETE, etc. (non SELECTs).

Usage example:

[SqlExecute()]
public static Customer Create(string id, string name, 
                       string surname, string phone)
{
    //INSERT
    ...

SQLQuery is ideal to label methods that execute query SQL code. Use this attribute for query methods that do not change the state of your database, i.e., SELECTs only.

Usage example:

[SqlQuery()]
public static Customer Read(string id)
{
    //SELECT .. WHERE ID ...
    ....

SQLTextExecute is used to label methods that return an SQL statement as text and such statements can alter the database if executed.

Usage example:

[SqlTextExecute()]
public static string GetSqlDeleteAll()
{
    return "delete customer";
}

Very similar to the previous one, SQLTextQuery is used to label methods that return SQL statements in text form and such statements are pure queries: they cannot alter the database when executed (it has no collateral effects).

Usage example:

[SqlTextQuery()]
public static string GetSQLCustomersOrdered()
{
    return "select name, surname from customer order by surname, name";
}

Using the quoted attributes provided by the Armadillo framework, we have labeled all the classes and methods of our DAL code.

The testing assembly is very straightforward and easy to build. It consists of a unique class with three methods: Init(), Down(), and TestDB() labeled with NUnit attributes to initialize, tear down and execute the test, respectively.

The Armadillo test engine can be executed providing:

• a DB implementation (MS SQL Server in this case)
• a DB connection and
• the assembly to be tested (i.e., DalMyBiz. Note: this is done in an indirect way: selecting the assembly that declares the class Customer).

using System;
using OKOKO.Armadillo.Framework;
using OKOKO.MyBiz;
using NUnit.Framework;

namespace OKOKO.MyBiz.Dal.Test
{
    /// <summary>
    /// Launch the tests
    /// </summary>
    [TestFixture()]
    public class DalTest
    {
        [SetUp()]
        public void Init()
        {
            Dal.Cnx.Open();
        }

        [TearDown()]
        public void Down()
        {
            Dal.Cnx.Close();
        }

        [Test()]
        public void TestDB()
        {
            IDbInfo dbInfo = new SqlServerDbInfo(Dal.Cnx);
            //Selection of Sql Server as target DB

            TestEngine te = new TestEngine(
                System.Reflection.Assembly.GetAssembly(typeof(Customer)),
                dbInfo);
        }
    }
}

The core of the test engine is located in the TestSql() method of the class TestEngine. This method receives a reference to the DAL assembly to be tested. Using reflection, the following steps are performed:

1. Locate all public classes labeled with the DALClass attribute.
2. For each class found, search for public and static methods labeled with one of our special attributes: SqlQuery, SqlExecute, SqlTextQuery, or SqlTextExecute.
3. Depending on the label, each method has a special routine to test it: CheckSqlQuery, CheckSqlExecute, CheckSqlTextQuery and CheckSqlTextExecute, respectively. Each of them receives a reference to the method to be tested using reflection (System.MethodInfo).

SQL query methods are executed against the database. We are assuming no collateral effects on queries. I.e., be sure you do not have a trigger after a SELECT changing your DB. Therefore, the implementation calls the DAL method directly using reflection. If it fails, we will get a nice exception, if not, everything is OK.

In this case, we cannot allow a real execution of the SQL because it changes the database. The test here will be:

1. Set the database in parse-only mode (parse SQL but do not execute it).
2. Call the DAL method.
3. Resume the normal mode of the database.

Similar to the previous case, if everything goes well, nothing will happen, but if the SQL statement is not correct, an exception will be provided.

As we explained before, DAL methods labeled with SQLTextQuery or SQLTextExecute attributes are supposed to return a string containing a SQL statement rather than executing nothing. Therefore, the test to perform is the following one:

1. Call the DAL method and retrieve the SQL statement in a variable of type string.
2. If dealing with a SQLTextExecute, set the database in a safe mode: parse-only to prevent execution.
3. Create a DB command and send the statement to the database.
4. Restore the DB mode (if needed).

Again, exceptions are the hint to know that SQL statements are corrupted.

Yes, you will probably have noticed it: DAL methods can have parameters, and we should provide valid values if we want to invoke them by reflection.

To overcome this, Armadillo creates default values for each parameter based on the parameter's type. See the method: CreateDefaultParams(MethodInfo mi). Therefore, it should not be a problem for the majority of the cases.

Anyway, if you have cases where you want to control the parameters to be used during a test, you have an alternative: Armadillo provides another attribute for specifying the specific values for a test: ParamValue.

Let's see a small example:

[SqlExecute()]
public static bool Update(string id, string number, 
                   string idCustomer, DateTime date)
{
    ...
}

In this first example, the test engine does not know a value to pass to idCustomer when calling this method. So, it will try to invent one using the parameter type's information. For this case, CreateDefaultParams(MethodInfo mi) will return "Hi!" for string parameters and "2000.01.02 03:04:05.006" for the DateTime one. Good enough, isn't it? :-)

However, if you need to change this behavior and force a value for a parameter, use the ParamValue attribute. See the following example:

[SqlExecute()]
[ParamValue("idCustomer", "customer123")]
[ParamValue("number", 13)]
public static bool Update(string id, string number, 
                   string idCustomer, DateTime date)
{
    ...

}

Now, Armadillo has information to provide a specific value for the param idCustomer = "customer123" and the param date = 13. The rest of the params will be inferred (just invented) as before.

The IDbInfo interface

The access to the database for testing is encapsulated through the interface IDbInfo. The functionality needed for testing SQL is the following:

• a connection to the DB,
• a CommandFactory (returns a System.Data.IDbCommand),
• a method to set the database in parse-only & non execution mode,
• a method to resume the execution of SQL statements, and
• an exception to be captured to check for SQL syntax errors.

A default implementation for SQL Server is also provided in the Armadillo framework (SqlServerDbInfo). This implementation takes advantage of the MS SQL command SET NOEXEC ON/OFF to enable or disable SQL code without disabling the parsing and precompilation of SQL.

If you decide to use this technique, run the unit tests whenever:

• you change the DAL code,
• the DB changes,
• or before a version of your software is released.

Any detected failure at the time, allows you to fix it before distributing a new version of your code. Preventing, in this way, runtime fails.

• When you want to assure the syntactic correctness of your SQL with respect to the target database.
• When you have your SQL code well encapsulated in methods, preferably in specific DAL classes.
• Such methods only deal with SQL code (i.e. they do not have any other side effects).
• Your DAL methods are marked as static, therefore no state is needed/preserved from the DAL class.
• As the main requirement for testing is to inject the attributes in your DAL code, you can use it with any .NET compatible language able to support attributes.

• When you do not have encapsulated, at least, your SQL code in methods.
• When you are using Stored Procedures.
• When your SQL code has other (non DAL) collateral side effects (a very bad thing, my friend) or your methods are non static.

• Armadillo can be extended to measure the time spent during the execution of an SQL code. This can be helpful to detect potential bottle-necks for SQL or database optimizations.
• It can also be easily extended to support additional RDBMSs such Oracle or MySQL. So, if you need to adapt it to your needs, please do not hesitate to do it. Try to derive a new class from IDbInfo for your own needs.
• The new Visual Studio 2005 incorporates a namespace for Unit Testing (Microsoft.VisualStudio.TestTools.UnitTesting). And, oh my dear! the attributes in this framework seem to be very, very similar to the ones in NUnit. Therefore, porting Armadillo from NUnit to Microsoft solutions for Unit Testing should be easy to achieve.

1. The NUnit Framework: NUnit.
2. Gentle tutorial to start with NUnit: Advanced Unit Testing, Part I – Overview by Marc Clifton.

Dealing with SQL errors could be a nightmare, especially if the code is under-documented and the database is changed very often.

The provided method & tool allows you to test the DAL SQL code before releasing a new version. The objective is to armor your code against any SQL syntactic errors derived from DB changes.

If the unit tests are executed in this way, SQL code will be tested against the DB (consider it like a form of assuring SQL precompilation). Therefore, this can avoid ugly surprises to customers at runtime.

I developed and apply this technique because I really needed to assure the correctness of a running system with more than four thousand SQL statements. If you cannot automate this, imagine testing them manually one by one in each release. So, I will be happy if this could be helpful to somebody else.

Please post your comments & share your opinion. Improvements and usage experiences are especially welcomed.

• November 27, 2005 – Armadillo ver. 1.00 released.