A SQL Based Data Driven Framework for Selenium using Excel

While Developing Selenium Test Cases,executing test cases with multiple data sets is required.There are several apis like Apache Poi,JExcel,Open CSV ,using which one need to come up with there suitable implementation for data retrieval and to integrate with their test cases.This requires some coding on top these apis,to make these apis,to meet their requirements.

There is one such api which is developed on top of apache poi,which supports SQL operations like Select,Update,Delete on your Spread Sheet Data .The Framework is called Fillo developed by Codoid.Using this framework retrieving data from data sources is easy,since one has to write SQL queries on Spread Sheet instead of some java implementation.

Codoid Treats the sheet name as a table,first row as column names,remaining columns as a table.If you 10 scenarios you need to use 10 sheets in spread sheet one for each scenario,where sheet name represents the scenario name.

In the below image UserLogin is a table name,which is a scenario name that contains data which need to be validated against login test.The first row represents the column names,mapped to fields in our UI.The User role is a filter variable which will be specified as where condition variable in our SQL Query.If we dont specify where in our query,the script will be executed with all 5 rows of data.If Filter Variable is Specified script will be executed based on where condition.If we use LoginFailed as filter variable,then the script will be executed with 3 rows of data.

Usage of Fillo in our Script.

Fillo fillo=new Fillo();
Connection connection=fillo.getConnection("C:\\Test.xlsx");
String strQuery="Select * from Sheet1 where UserRole like 'LoginFailed'
Recordset recordset=connection.executeQuery(strQuery);

while(recordset.next())
{
driver.findElement(By.Id("UserName")).sendKeys(rs.getField("UserName"));
driver.findElement(By.Id("Password")).sendKeys(rs.getField("Password"));
driver.findElement(By.Id("LogIn")).click();
if( Assert.assertTrue(driver.findElement(By.Id("Logout")).getText(),"Logout"))
{
System.out.println("Login Success");
}
else {
System.out.println("Login Failed");
}

}
recordset.close();
connection.close();

More Details on Fillo can be found at Fillo WebSite