Making a DB Connection using Service Test 11’s Custom Code Activity Using Just 8 Lines of Code.

PerfectoTopAd

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:

Service Test 11 DNS








Database Info:

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

PHP mySql table





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:

<p>using System.Windows.Forms;</p>
<p>using System.Data.Odbc;</p>
<p>

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

<p><span style="color: #008000;">//Create a string variable that holds your database connection info.</span><br />
 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 />
 dbMySQL.Open();<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 />
 {<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 />
 MessageBox.Show(sqlReader.GetString(1));<br />
 }</p>
<p>

The final code in Service Test should look like this:

Custom Code for ST DB connection









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)

6 comments
Ofer - January 25, 2011

Hi,
I would like to update that we have a complete support for DB in Service Test.
you can connect, extract information and change information. you can use extracted information within your flow for data driven testing.
all that without one line of code.

Reply
    Joe Colantonio - January 25, 2011

    @Ofer: Hi Ofer – this is great news! Can you please tell me where this functionality is found? I know you can data drive info using Excel or XML but I don’t seen any options to point to a database. I might be missing something but I can’t find this info in the HP ST User guide either. Thanks — Look forward to hearing back from you!

    Reply
Ofer - January 25, 2011

@Joe:
Hi Joe, the support for DB is part of our next release, coming out quite soon.
We are using the same concepts as for Excel – you can bring the data in DT, see it like Excel and data drive.
Also in RT, you can connect to the DB, extract/update data and link it like any other data in the flow.
The user guide will include all the required information.
If you need additional help/information on ST 11 and above, feel free to address me.
Ofer

Reply
    Joe Colantonio - January 25, 2011

    @Ofer: Awesome – you made my day! I can’t wait to get my hands on the new version. I really like ST11 — you guys did a great job developing it.

    Reply
HP Service Test 11.10 New Database Functionality – VIDEO — Joe Colantonio -- QuickTest Pro, Tech Geek, Bibliomanic, The Software Test Automation Imagineer - March 23, 2011

[…] function to develop your own C# code that connected to a DB, as well as to perform an operation. (If you’re interested in how to do this, check out my custom code post.) However, in ST11, it is no longer necessary to write code to achieve this functionality. Now […]

Reply
Govindh - January 21, 2013

Hi joe,

Currently i have just started to explore ST 11.20. I have a requirement to connect to AS400 database, fetch data from it through multiple SQL Queries and compare the result table that is generated through all queries with the SOA Response nodes and also update the result table to an excel file for reference.

I have kept all the sql queries in excel file and i am able to parameterize it succssfully using custom code.
Then i am sending this customcode output property(SQL String) as a input to select data component.

Here i need to write “After Execute step event” to append the result table items to an Dynamic array or list.But i am not able to access the Reslut Table of Select Data Check Point.

Could you help me to get this done. Also suggest me other solutions that may be helpful for me?

Reply
Click here to add a comment

Leave a comment: