Selenium C# Tutorial: Working with Database

Databases are among the most essential and intrinsic parts of applications, particularly web applications. For that reason, we must know how to test is as well, including:

• Reading data from a database (Application DB).

• Writing data into the database.

• Running queries or stored procedures.

In this tutorial, we will use C# libraries (System.Data.SqlClient namespace) for connecting with the SQL database. To use it, make sure to install it from NuGet as an install-package System.Data.SqlClient.

Let's start developing it.

Let's start with the creation of the class we will use to interact with the SQL server:

using System;
using System.Collections.Generic;
using System.Text;
using OpenQA.Selenium;
using System.Data;
using System.Data.SqlClient;

namespace SeleniumExcel
{
public static class WorkingWithDatabase
{

//Open Connection to DB
public static SqlConnection DBConnect(this SqlConnection SqlConnection,string connectionString)
{
try
{
SqlConnection = new SqlConnection(connectionString);
SqlConnection.Open();
return SqlConnection;
}
catch(Exception e)
{
Console.WriteLine("ERROR ::" + e.Message);
}
return null;
}

//Close Connection to DB
public static void CloseDBConnection(this SqlConnection sqlConnection)
{
try
{
sqlConnection.Close();
}
catch (Exception e)
{
Console.WriteLine("ERROR ::" + e.Message);
}            
}

//Execute a query in DB
public static DataTable ExecuteQuery(this SqlConnection sqlConnection,string queryString)
{
DataSet dataset;
try
{
//Checking the connection status to DB
if (sqlConnection == null || ((sqlConnection != null && (sqlConnection.State == ConnectionState.Closed || sqlConnection.State == ConnectionState.Broken))))
{
sqlConnection.Open();
}
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand(queryString, sqlConnection);
dataAdapter.SelectCommand.CommandType = CommandType.Text;
dataset = new DataSet();
dataAdapter.Fill(dataset, "table");
sqlConnection.Close();
return dataset.Tables["table"];
}
catch (Exception e)
{
return null;
Console.WriteLine("ERROR ::" + e.Message);
}
finally
{
sqlConnection.Close();
dataset = null;
}
}
}
}