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.
