Click here to Skip to main content
Click here to Skip to main content

Building Integration Test Queries

, 14 Jan 2013
Rate this:
Please Sign up or sign in to vote.
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.

//
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)

About the Author

Kishor Deshpande
Technical Lead iGATE Global Solutions
India India
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 PinmemberOleksandr Kulchytskyi14-Jan-13 23:29 
GeneralRe: My vote of 1 [modified] PinmemberKishor Deshpande15-Jan-13 0:01 
Well, As far as I know, Integration tests checks that a functionality after integrating several modules works as expected.
So, it is developer who writes and runs integration tests.
Why should developer bother about SQL injection? Because he will always have access to database so he can at any point of time insert mailcious data into the database.
If someone tries to do so with this approach, he can be tracked with the help of TFS check-in history.
Also, this tip was used for reducing redundant line of query strings, developer who wants to go for SQL injection can also do it by directly declaring script as constant strings.
But to take care of SQL injection for developers, suggested way is to add custom StringValidator class which will remove few input characters from Query to be executed but it is not not in scope of this tip.

modified 15-Jan-13 6:18am.

GeneralRe: My vote of 1 PinmemberOleksandr Kulchytskyi15-Jan-13 2:38 
QuestionRe: My vote of 1 PinmemberKishor Deshpande15-Jan-13 2:44 
AnswerRe: My vote of 1 PinmemberOleksandr Kulchytskyi15-Jan-13 3:28 
GeneralRe: My vote of 1 PinmemberKishor Deshpande15-Jan-13 16:06 
GeneralRe: My vote of 1 PinmemberOleksandr Kulchytskyi15-Jan-13 22:42 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 14 Jan 2013
Article Copyright 2013 by Kishor Deshpande
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid