Using Visual Studio 2008 Web Test Request Plug-In to Check results in a SQL DB

by garyg 10. April 2010 06:12

Some of my regular readers said they wanted to see more "technical" content, so here is one that perplexed me a while.

While assisting a client with setting up an automating testing environment using Visual Studio 2008 Web Tests (among other things) we uncovered a need to check the results of a transaction halfway through, then when its complete to verify the results of the test.

Now I know what experienced testers and SQA people are thinking, "why didn't you just use an Extraction rule from a results page and validate that?". Yes, that's the first thing I wanted to have done as well and that works quite well under most circumstances.

Unfortunately on this particular application there is no real "confirmation" screen that displays the results of the transaction, just kind of a yeah I did or no I didn't kind of page. Not good enough in our case where I wanted to have real transaction results.

Since time was very short and I'm still working with the group to adopt more "test friendly" designs we needed a sure way of verifying the results. I though that this check DB feature would be a native feature in the VSTS2008 Web Test, but the only DB connectivity included out of the box was binding to a DB for data driven testing (which is very useful as well).

So our option was a to create a request level plug-in to go out to the DB and check the transaction results, and write them back to the test results.

My goals here were:

  1. Connects to an SQL DB.
  2. Builds a query string using a Context parameter
  3. Puts the value pulled from the DB back into another Context parameter for use in follow on requests.

Here is how I did it (in a plug-in 101 type format), complete with code snippet I used to create this:

1. Create the following in a class library in your test project (that part is covered in detail in MSDN), compile, and reference it:

  1: using System.Text;
  3: using Microsoft.VisualStudio.TestTools.WebTesting;
  5: using System.Data.SqlClient;
  9: namespace Test1
 10: {
 12:     public class MyRequestPlugin : WebTestRequestPlugin
 13:     {
 15:         public override void PostRequest(object sender, PostRequestEventArgs e)
 16:         {
 18:             base.PostRequest(sender, e);
 20:             int CustomerID = 0;
 23:             // this is my connection string
 24:             String connectionString = "Persist Security Info=False;Initial Catalog=dbname;Data Source=machinename; User Id=dbuser;Password=somepassword";
 27:             // select statement getting just the field I need.  Note that if this is;
 28:             // messed up it may throw an error saying is cant open the db.;
 29:             // This is misleading, its probably your select; 
 30:             SqlConnection connection = new SqlConnection(connectionString);
 32:             string queryString = "Select CustomerID from Orders where OrderID=" + e.WebTest.Context["OrderID"];
 36:             SqlCommand command = new SqlCommand(queryString, connection);
 38:             command.Connection.Open();
 40:             SqlDataReader reader = command.ExecuteReader();
 42:             while (reader.Read())
 43:             {
 45:                 CustomerID = Convert.ToInt32(reader[0]);
 47:             }
 49:             e.WebTest.Context.Add("CustomerID", CustomerID);
 53:         }
 57:         public override void PreRequest(object sender, PreRequestEventArgs e)
 58:         {
 60:             base.PreRequest(sender, e);
 62:         }
 64:     }
 66: }

2. Insert the Request Plug-in (if you compiled and referenced it, it will be in the list.) AFTER the Context parameter you are using (in a production test you'll need error control, errors in a Plug-in are ugly and will mess up your results).

This whole exercise made think that a "data check" validation rule of sorts really should be part of this product.

Anyway hope this helps someone else a little further along some day using web tests. This same method also works in Visual Studio 2010 as well.

About the author

Gary Gauvin is a 20+ year Information Technologies industry leader, currently working as the Director of Application Lifecycle Management for CD-Adapco, a leading developer of CFD/CAE solutions. Working in both enterprise environments and small businesses, Gary enjoys bringing ROI to the organizations he works with through strategic management and getting hands-on wherever practical. Among other qualifications, Gary holds a Bachelor of Science in Information Technologies, an MBA, a PMP (Project Management Professional) certification, and PSM (Professional Scrum Master) certification.  Gary has also been recognized as a Microsoft Most Valuable Professional.

LinkedIn Profile:

(Note: Comments on this blog are moderated for content and relevancy)


Month List

Page List