Selenium, data driven testing using NUnit and SQL queries.

Question

I am developing a Selenium Test Automation Framework for the purpose of testing multiple websites.

I have currently set up the Framework in Visual Studio using C# and implementing the Page Object Model.

The Framework itself is a C# project and the Test project is separate but references the Framework.

I am happy with the fundamentals of the Framework but I want to implement some Data Driven aspects in order to increase the durability of the tests. I currently have NUnit running paramaterised test using [TestCase] but the data is hardcoded by me.

The ideal scenario would be to add a DatabaseHelper class to the Framework which would allow me to define queries on the database under test to return values and Nunit would repeat the same test for each row of the SQL query.

Has anyone here implemented a similar method and how would you go about creating it?

Answer 1

This is quite regular feature for such kind of test frameworks. You need to consider the following:

• Proper connectivity library to your particular database
• The way how you're going to store credentials. Do not store passwords in to code. One of the possible solutions is to fetch the creds from environment variables
• How you manage the connections. Consider using database connection pools otherwise you can eat up all the available connections and either make your other tests fail or other people wouldn't be able to connect
• How you prepare and then reset test data.
• If it will make sense for your particular case to deploy some stored procedures to simplify your code on C#
• If it will make sense not to use direct queries but rather ORM approach.

This is the basic issues you should think of before start implementation of your database integration.

Answer 2

This scenario is faced by every qa person in automation testing services company while creating framework. If we are creating a DataDriven Framework we need to pass data from file, This can be done in two ways in C#:

1. Using libraries like "Excellibrary" in NugetPackage manager of Visual studio by which we can read and write data from Excel and csv files.

2. Using Oledb commands : We need install the corresponding driver like "Microsoft Jet OLEDB Driver 4.0", and create connection object for the Excel. Here you can query using sql commands where your excel file acts as Database.

Refer to below code:

// Connection String for Excel 2007 Format (.XLSX)

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties='Excel 8.0;HDR=Yes'"

// establish connection:

String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

"Data Source=Book1.xls;"

"Extended Properties='Excel 8.0;HDR=Yes'";

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

// Access sheets

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

connExcel.Close();

//This will give the name of the first sheet. i.e. Sheet1$

//Running a Select Query by Specifying Column Names like below:

DataSet ds = new DataSet();

string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

cmdExcel.CommandText = "SELECT ID, Name From [" + SheetName + "]";

da.SelectCommand = cmdExcel;

da.Fill(ds);