Making a DB Connection Using Service Test 11’s Custom Code

I've been getting this question a lot lately: “How can I connect to a database from Service Test 11?” Actually, it would be nice if Service Test had a database activity. It doesn't, unfortunately, but the good news is that it’s fairly easy to create one.

There are a few ways to do this in C#, but for this example, I'll be using ODBC.

DSN Setup:

First, make sure you have an ODBC DSN setup under Windows Admin Tools\ODBC. I'm using mySQL for this example:

Database Info:

I also have mySQL running on my local machine with a database named ‘servicetest' that has a table named ‘customers':

Custom Code:

Drag a custom code activity onto your main canvas area in Service Test, then go to its events and select ‘create a default handler' from the ExecuteEvent. In the ‘SharedUserCode' area, we need to first import the ODBC and Forms libraries:

using System.Windows.Forms;

using System.Data.Odbc;

Next, type the following code to create a connection to the DB and return a record:

//Create a string variable that holds your database connection info.
string strConnect = “DSN=yourDSNName;UID=yourDBUserName;PWD=yourDbPassword;DATABASE=yourDbName”;
//Create a connection object
OdbcConnection dbMySQL = new OdbcConnection(strConnect);
//Open a connection
dbMySQL.Open();
//Create a OdbcCommand that will hold the sql statement to execute
OdbcCommand sqlCommand = dbMySQL.CreateCommand();

sqlCommand.CommandText = “select * from customers”;
//Create a sqlReader object which provides a way to read the data rows returned from the data source
OdbcDataReader sqlReader = sqlCommand.ExecuteReader();
//Loop through all the returned recordsets
while (sqlReader.Read())
{
//Show the second column value in the current row.  In this example it would return the Name field.
MessageBox.Show(sqlReader.GetString(1));
}

The final code in Service Test should look like this:

To learn how to pass data between activities in Service Test 11, check out my blog post on how to use the custom code functionality.

For my fellow bibliomaniacs who may be new to C#, I would also recommend these two books:

1. Beginning C# 3.0: An Introduction to Object Oriented Programming (Wrox Beginning Guides)
2. A Tester's Guide to .NET Programming (Expert's Voice)