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.
First, make sure you have an ODBC DSN setup under Windows Admin Tools\ODBC. I’m using mySQL for this example:
I also have mySQL running on my local machine with a database named ‘servicetest’ that has a table named ‘customers’:
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:
Next, type the following code to create a connection to the DB and return a record:
string strConnect = "DSN=yourDSNName;UID=<em>yourDBUserName</em>;PWD=<em>yourDbPassword</em>;DATABASE=yourDbName";<br />
<span style="color: #008000;">//Create a connection object</span><br />
OdbcConnection dbMySQL = new OdbcConnection(strConnect);<br />
<span style="color: #008000;">//Open a connection</span><br />
<span style="color: #008000;">//Create a OdbcCommand that will hold the sql statement to execute</span><br />
OdbcCommand sqlCommand = dbMySQL.CreateCommand();</p>
<p>sqlCommand.CommandText = "select * from customers";<br />
<span style="color: #008000;">//Create a sqlReader object which provides a way to read the data rows returned from the data source</span><br />
OdbcDataReader sqlReader = sqlCommand.ExecuteReader();<br />
<span style="color: #008000;">//Loop through all the returned recordsets</span><br />
while (sqlReader.Read())<br />
<span style="color: #008000;">//Show the second column value in the current row</span>. <span style="color: #008000;"> In this example it would return the Name field</span>.<br />
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:
- Beginning C# 3.0: An Introduction to Object Oriented Programming (Wrox Beginning Guides)
- A Tester’s Guide to .NET Programming (Expert’s Voice)