Click here to Skip to main content
15,867,307 members
Articles / Programming Languages / C# 4.0
Tip/Trick

Building Integration Test Queries

Rate me:
Please Sign up or sign in to vote.
3.67/5 (2 votes)
14 Jan 2013CPOL2 min read 12.4K   27   2   7
Reduces line of code by writing builder methods to construct integration test queries.

Introduction

A common way of writing integration tests is, you will probably have,
1) SQL folder with classes in which you will store your SQL queries in the form of string constants.[Usually multiple classes based on functionality or table]
2) Test base class which will interact with database to fetch the result.

Recently while writing integration tests for few scenarios in my project, I saw redundant queries were written as constant strings with few changes in WHERE or/and AND clause.

Eg: Database: NORTHWND. Table- Employees

For example, I saw two queries similar as given below:
1) const string GetEmployeesFromCountryUk = @"Select * from Employees where Country='UK'";
2) const string GetEmployeesFromCityLondon = @"Select * from Employees where Country='UK' AND City='London'";

Let's assume for now that "Country" is my basic filteration criteria and developer can also fetch records based on "City" or "TitleOfCourtesy" or any other column values.

In such case, developers write SQL queries to fetch records based on their criteria, so if a developer wish to search by city, he adds constant string GetEmployeesByCity.
To search by City and Salary, he adds constant string GetEmployeesByCityAndSalary
and so on.

This results in adding many constant strings, so feasible solution to solve this problem is to add a builder method which will construct a query based on developer inputs.

Using the code

Here, I've a class say EmployeesSql which contains queries in region EmployeeQueries in the form of constant strings like conventional approach. Second region has a Builder method which will build and return query based on parameters passed by developer.

C#
//
namespace BuildingQueriesDemo.SQL
{
    internal class EmployeesSql
    {
        #region EmployeeQueries
        internal const string GetEmployeesFromCountryUk = @"Select * from Employees where Country='UK'";
        internal const string GetEmployeesFromCityLondon = @"Select * from Employees where Country='UK' AND City='London'";
        #endregion

        #region Query Builder Method
        internal static string GetEmployees(string country, string optionalCity=null,
                       string optionalTitleOfCourtesy=null)
        {
            var query = string.Format("Select * from Employees Where Country='{0}'",country);
            if (!string.IsNullOrEmpty(optionalCity))
                query = string.Format("{0} AND City= {1}", query, optionalCity);
            if (!string.IsNullOrEmpty((optionalTitleOfCourtesy)))
                query = string.Format("{0} AND titleOfCourtesy={1}", query, optionalTitleOfCourtesy);
            return query;
        }
        #endregion
    }
}
//
using System;

namespace BuildingQueriesDemo
{
    class Program
    {
        static void Main()
        {
            #region Using constant strings
            Console.WriteLine(SQL.EmployeesSql.GetEmployeesFromCountryUk); 
            Console.WriteLine(SQL.EmployeesSql.GetEmployeesFromCityLondon);
            #endregion

            #region Using Query Builder
            Console.WriteLine(SQL.EmployeesSql.GetEmployees(country: "UK"));
            Console.WriteLine(SQL.EmployeesSql.GetEmployees(country: "UK", optionalCity: "London"));
            Console.WriteLine(SQL.EmployeesSql.GetEmployees(country: "UK", optionalTitleOfCourtesy: "Mr."));
            Console.WriteLine(SQL.EmployeesSql.GetEmployees(country: "UK", optionalCity: "London", optionalTitleOfCourtesy: "Mr."));
            #endregion

            Console.Read();
        }
    }
}

If we observe the way we have called constant string queries to print on Console and in other region we have passed few parameters to a method which returns me same query but reduces declared string constants in my class file as well as named & optional parameters makes the method call more meaningful i.e. by looking at call itself, developer can know what all filteration criteria we have applied in each call.

Points of Interest

With this approach,

1) String constants declared for queries are reduced.
2) If developer wish to add new criteria in query, little modification is required, that too only in Builder Method.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead iGATE Global Solutions
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 1 Pin
Oleksandr Kulchytskyi14-Jan-13 23:29
professionalOleksandr Kulchytskyi14-Jan-13 23:29 
GeneralRe: My vote of 1 Pin
Kishor Deshpande15-Jan-13 0:01
professionalKishor Deshpande15-Jan-13 0:01 
GeneralRe: My vote of 1 Pin
Oleksandr Kulchytskyi15-Jan-13 2:38
professionalOleksandr Kulchytskyi15-Jan-13 2:38 
QuestionRe: My vote of 1 Pin
Kishor Deshpande15-Jan-13 2:44
professionalKishor Deshpande15-Jan-13 2:44 
Shall I add filter method at the time of invoking my Query builder method in above sample so as to make this tip more effective?
AnswerRe: My vote of 1 Pin
Oleksandr Kulchytskyi15-Jan-13 3:28
professionalOleksandr Kulchytskyi15-Jan-13 3:28 
GeneralRe: My vote of 1 Pin
Kishor Deshpande15-Jan-13 16:06
professionalKishor Deshpande15-Jan-13 16:06 
GeneralRe: My vote of 1 Pin
Oleksandr Kulchytskyi15-Jan-13 22:42
professionalOleksandr Kulchytskyi15-Jan-13 22:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.