Data Driven Testing using Excel in c# | Excel parameterisation

In this chapter, I will provide the fundamental principles with code for running test data with Selenium from an MS Excel spreadsheet

Data-Driven Testing

Data-Driven Testing is the creation of test scripts where test data and/or output values are read from data files instead of using the same hard-coded values each time the test runs. This way, testers can test how the application handles various inputs effectively. It can be any of the below data files.

Datapools
Excel files
ADO objects
CSV files
ODBC sources

In its most fundamental form, data-driven testing is a Test Automation Framework where the data that ‘drives’ the testing is not hard-coded but taken from a table external to the source code and used by the test scripts during execution.

It is convenient to keep data for automated tests in special storages that support sequential access to a set of data, for example, Excel sheets, database tables, arrays, and so on. Often data is stored either in a text file and are separated by commas or in Excel files and are presented as a table. This lets you modify them easily. If you need to add more data, you simply modify the file either in any text editor or in Microsoft Excel (in case of hard-coded values, you should modify both data and code).

Data-driven testing lets you create automated test projects that can be infinitely extended by simply adding new lines of text to a text file or a spreadsheet. For example, data is read from an external source and fed line-by-line into the functional test until there is no more external data. In this way, new automated test cases that are added to the external data simply extend the loop for each new line of data. Not a rocket science but just 3 step process:

Retrieving input data from storage
Entering data in an application form
Verifying the results

Read and Write Excel Using OLEDB

There are various ways to perform Data Driven Testing using Excel. Mean there are various ways to read the data from the Excel. But for this Framework, I am selecting OLE DB to read the data from the Excel. It is a smart way doing the traditional thing. Automation of an Excel file allows us to doing various operations from C#. We can automate an Excel file from C# in two ways. Using Excel Object Model in one way and another way is using Microsoft Jet Engine to connect Excel from CSharp.

What is OLE DB?

OLE DB is a COM-based application programming interface (API) for accessing data. OLE DB supports accessing data stored in any format (databases, spreadsheets, text files, and so on) for which an OLE DB provider is available. Each OLE DB provider exposes data from a particular type of data source (for example SQL Server databases, Microsoft Access databases, or Microsoft Excel spreadsheets).

C# OLEDB Connection

The C# OleDbConnection instance takes Connection String as an argument and pass the value to the Constructor statement. An instance of the C# OleDbConnection class is supported the OLEDB Data Provider .

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=yourdatabasename.mdb;";
cnn = new OleDbConnection(connectionString);

When the connection is established between C# application and the specified Data Source, SQL Commands will execute with the help of the Connection Object and retrieve or manipulate data in the database. Once the Database activities is over Connection should be closed and release from the data source resources. The Close() method in the OleDbConnection class is used to close the Database Connection.

Selenium Data-Driven Testing Using OLE DB in C#

Let just use the basic Login scenario which we discussed previously in this tutorial and see how to Read and Write Excel Using OLEDB connection.

Create Test Data File

First we need a place to keep the TestData file. To achieve that create a folder in the project and refer it as TestDataAccess.
Go to the location of the TestDataAccess folder in the local drive. Create an Excel file in the folder name it as TestData.
Enter the data in the Excel like in the below image. Change the sheet name to DataSet.

Data Driven Testing_2

Note: Key is the TestCase name.

In the Visual Studio explorer, right click on the TestDataAccess folder and go to Add >> Existing Item..

Data Driven Testing_1

Browse to TestDataAccess folder and select the TestData excel file. This will bring the TestData file to the project and it will be visible in the Solution Explorer.

Create TestData Class

Create a C# class in the TestDataAccess folder. Name it as UserData.
Create three static properties, which represent the TestData excel file columns.

namespace OnlineStore.TestDataAccess
{
    public class UserData
    {
        public string Key { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
    }
}

Add Reference of Dapper

Dapper is a simple object mapper for .NET. Dapper is a single file you can drop in to your project that will extend your IDbConnection interface. It provides 3 helpers:

Execute a query and map the results to a strongly typed List
Execute a query and map it to a list of dynamic objects
Execute a Command that returns no results

Go to Tools >> NuGet Package Manager >> Manage Nuget Packages for Solution.
Search for Dapper and Install Dapper

Dapper_1

Create Data Access Class

Create a C# class in the TestDataAccess folder. Name it as ExcelDataAccess.

ExcelDataAccess Class

using System.Configuration;
using System.Data.OleDb;
using System.Linq;
using Dapper;

namespace OnlineStore.TestDataAccess
{
    class ExcelDataAccess
    {
        public static string TestDataFileConnection()
        {
            var fileName = ConfigurationManager.AppSettings["TestDataSheetPath"];          
            var con = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = {0}; Extended Properties=Excel 12.0;", fileName);
            return con;
        }

        public static UserData GetTestData(string keyName)
        {
            using (var connection = new OleDbConnection(TestDataFileConnection()))
            {
                connection.Open();
                var query = string.Format("select * from [DataSet$] where key='{0}'", keyName);
                var value = connection.Query<UserData>(query).FirstOrDefault();
                connection.Close();
                return value;
            }
        }
    }
}

Explanation:

var fileName = ConfigurationManager.AppSettings["TestDataSheetPath"]; var con = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = {0}; Extended Properties=Excel 12.0;", fileName);

The connection string is used to specify how the connection would be performed. It is a string made of different sections. Each section is created using the formula Argument=Value. The sections are separated by semi-colons. This would produce:

"Argument1=Value;Argument=Value2;Argument_n=Value_n;"

Everything in this string is case-insensitive. If you had declared an OleDbConnection variable using the default constructor, to create the connection string, you can declare a String variable, "fill it up" with the necessary information, and assign it to the OleDbConnection.ConnectionString property.

Data Provider: The first parameter or one of the parameters of a connection is the database provider. To specify the provider. Microsoft.Jet.OLEDB.4.0 Or Microsoft.ACE.OLEDB.12.0
Data Source: After specifying the provider, an important factor is the name of the database you want to connect to. This information is referred to as the data source. If you are creating a connection string, to specify the data source, assign the complete path of the database to the Data Source factor.
Extended Properties:
connection.Open(); : To open the Excel database Connection
connecttion.Close(); : To close the Excel database connection
string.Format("select * from [DataSet$] where key='{0}'", keyName); : This is query string, the way we use any SQL script. As we know that DataSet is the sheet name of the TestData file. Keyname is the TestCaseName in the DataSet sheet.
connection.Query<UserData>(query).FirstOrDefault(); : The Query() Extension Method and its overloads are used, as the name suggests, for extracting information from the database and using it to populate our business object model. As the Query() method always returns a collection of objects, we simply call the LINQ SingleOrDefault() method as we know the query should only return 1 or 0 rows.

Explanation: new OleDbConnection(TestDataFileConnection()

To access the database, the first action you take consists of establishing a connection with the database. To support this, the System.Data.OleDb namespace provides the OleDbConnection class.

Modify LoginPage Page Object Class

To access the data from Excel and to bring the data to LoginPage class, use above created ExcelDataAccess.GetTestData() function.

LoginPage Class

using OnlineStore.TestDataAccess;
using OpenQA.Selenium;
using OpenQA.Selenium.Support.PageObjects;

namespace OnlineStore.PageObjects
{
    class LoginPage
    {
        private IWebDriver driver;

        [FindsBy(How = How.Id, Using = "log")]
        [CacheLookup]
        private IWebElement UserName { get; set; }

        [FindsBy(How = How.Id, Using = "pwd")]
        [CacheLookup]
        private IWebElement Password { get; set; }

        [FindsBy(How = How.Id, Using = "login")]
        [CacheLookup]
        private IWebElement Submit { get; set; }

        public LoginPage(IWebDriver driver)
        {
            this.driver = driver;
            PageFactory.InitElements(driver, this);
        }

        public void LoginToApplication(string testName)
        {
            var userData = ExcelDataAccess.GetTestData(testName);
            UserName.SendKeys(userData.Username);
            Password.SendKeys(userData.Password);
            Submit.Submit();
        }
    }
}

Modify LogInTest Class

A small change is required in LogInTest. Just pass the test case name to loginPage.LoginToApplication().

LogInTest Class

using NUnit.Framework;
using OnlineStore.PageObjects;
using OpenQA.Selenium;
using OpenQA.Selenium.Firefox;
using System.Configuration;

namespace OnlineStore.TestCases
{
    class LogInTest
    {
        [Test]
        public void Test() {

            IWebDriver driver = new FirefoxDriver();
            driver.Url = ConfigurationManager.AppSettings["URL"];     

            var homePage = new HomePage(driver);
            homePage.ClickOnMyAccount();

            var loginPage = new LoginPage(driver);
            loginPage.LoginToApplication("LogInTest");

            driver.Close();
        }    
    }
}

HomePage Page Object Class

using OpenQA.Selenium;
using OpenQA.Selenium.Support.PageObjects;

namespace OnlineStore.PageObjects
{
    class HomePage
    {
        private IWebDriver driver;

        [FindsBy(How = How.Id, Using = "account")]
        [CacheLookup]
        private IWebElement MyAccount { get; set; }

        public HomePage(IWebDriver driver)
        {
            this.driver = driver;
            PageFactory.InitElements(driver, this);
        }

        public void ClickOnMyAccount()
        {
            MyAccount.Click();
        }
    }
}

Environment.config File

<appSettings>
	  <add key="URL" value="https://www.store.demoqa.com"/>
	  <add key="TestDataSheetPath" value="YOUR MACHINE PATH/TestData.xlsx"/>
</appSettings>

App.config file

<configuration>
    <appSettings configSource="Configurations\Environment.config" />
</configuration>

Project Solution Explorer

Data Driven Testing_3

Data Driven Testing