It’s been a bit quiet lately, and that’s because I’ve been very busy with creating a workshop and new talk for TestBash Manchester! Now that it’s done, I can finally blog about some of the stuff I did to create the workshop, and release an app for folks to practice with!

Understanding The Problem

I have previously done a workshop on testing REST services, where I wrote an ASP.NET Core 2.1 API with a simple web front-end (using the Todo API example as a base to start from). For that workshop, participants would load up the code in Visual Studio and run the solution locally. This was a no-brainer, as part of the workshop was to write some automated tests in Visual Studio.

However, for the workshop at TestBash, I needed to account for an audience with a wider range of skills – I had done the above workshop at developer conferences and at work. Discussions with the folks that run the conference suggested a workshop without needing to code, to accommodate the most attendees.

So I realized I could use what I had written, and develop a workshop testing both the API and the UI that went along with it. We could test using Postman, Swagger, and dev tools in the browser. That could easily go 4 hours! Perfect!

But then I had a problem – how was I going to provide this application to the participants? I could host it somewhere – I tried putting it up on Azure, and it worked! But what about when one of the participants did something catastrophic to the data in the application? That could cause a whole host of problems, having shared data when asking participants to really try to break an application individually…

So what I really needed was to again have each participant running the application on their own machines – their own instances of the application.

Docker To The Rescue!

The obvious choice from there, for me, was Docker. I hadn’t worked with it too much on the “creating images” side, but had worked with it as a consumer of images.

I found some documentation on how to Dockerize my application but at one point, when right-clicking on the project in Visual Studio and selecting Add, I saw “Add Docker Support” so I clicked it! What could go wrong?

It asks you to target an OS – Windows or Linux. I chose Windows, because I’m on a Windows machine. And once you click OK it creates all of the Docker files for you! It’s pretty awesome! I compared the documentation to what Visual Studio automagically created for me, but it looked all good.

I followed the steps to build and run the image, and it was up and running! Awesome!

Then I had to get it to the participants somehow. I found this documentation on sharing your images, followed the steps and I was able to pull down my new image on another machine!

Oops!

While I was prepping the prerequisite documentation for my workshop attendees, where they set up Docker and Postman and Chrome, I realized I needed to have people set their Docker configuration locally to run Windows images. After looking through the documentation, I found that folks on a Mac would not be able to run my image! Ack! Now what?!

Well thankfully, ASP.NET Core 2.1 applications can run on Linux equally as well as they can run on Windows! Yes! So I just had to set my local Docker config to run Linux machines, make a few modifications to the Dockerfile, build and publish it again. And now my application was accessible on Windows, Linux, and Mac machines without issue!

Now For The New App!

Ok, so now that we’ve got it all figured out, I can share the details for how you can use this Docker image to practice testing!

You can run the image locally, and have your own instance of a REST API and web UI to mess around with. This application has a Swagger for the API, and it can be reached via Postman as well as other tools.

It has a few known bugs (I’m not documenting them because that’s part of the fun of the workshop!) and quirks. If it ever gets in a bad state for you, simply stop the image, and start it up again! Remember to stop the image when you’re all done with it for the day – it does take up some resources on your computer.

Source code for the application can be found at https://github.com/g33klady/TodoApiSample.

The information to get it running is in the Readme of the repo, here: https://github.com/g33klady/TodoApiSample#to-use-this-application-in-docker.

Once Docker is running, the application can be accessed at http://localhost:8080.

Swagger specification can be found at http://localhost:8080/swagger.

I hope you enjoy! Watch my website or twitter for where I’ll be giving this workshop next, as well!

EDIT: One mindful reader noted that I should be using good practices to protect my code from SQL Injection. So there are some updates below, and the code is also updated! I used this as my guide: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

One part of my talk and resources on Testing RESTful Web Services that I hinted at but didn’t quite get to was using SQL databases. A lot of us will be testing a REST service along with it’s backing SQL database, so it’s useful to know how to link them together in our C# automation!

I’ve added to my repository to include some checks against the AdventureWorks2012 SQL database, continuing to use C# and NUnit. Gitter’s database isn’t publicly available (nor should it be!) and I can’t find an AdventureWorks API that I could use for my checks – maybe that will be a later, more complete example. But for now, we’re doing some pretending!

If you’d like to follow along, grab the AdventureWorks2012 database backup from Microsoft by grabbing this zip AdventureWorks2012-Full Database Backup.zip. Unzip and restore the database (google it if you need help :D).

Look through the tables, run some queries, and see what we’re working with. For the coding examples in the repo, I’m mainly looking at the Production.ProductInventory table, which depends on the Production.Product table for ProductIds. From the Product table, I’m going to pick a product to work with – I like #875: Racing Socks, L. Querying the ProductInventory table, I see there’s 288 (presumably pairs) of Large Racing Socks.

Let’s pretend that we have an inventory system that uses a REST service, which in turn grabs data from and updates our database. If we pull something off the shelf to send to a customer, we scan it into the system. The system identifies it, and sends a call like:

PUT http://ourwarehouse/api/products/875/inventory/-1

then the API goes into the database and essentially does:

UPDATE Production.ProductInventory 
SET Quantity = quantity - 1 where ProductID = 875

So with our checks, we would:

1. Do a SELECT query on the database for the product and see what the quantity is
2. Perform the PUT operation with the REST service
3. Do the SELECT query again to see what the quantity is now
4. Verify the new quantity is what we’re expecting

Utility Methods

First we can write some utility methods to execute our queries against the database. We start with a Utility class and add references to System.Data and System.Data.SqlClient.

Our first utility method is to execute a SQL query to get the quantity of a product. We’ll pass the product ID and connection string, and it will return a DataTable for us.

public static DataTable GetQuantityOfProduct(string productId, 
  string connectionString)
{
	string commandText = "SELECT Quantity FROM " +
                 "AdventureWorks2012.Production.ProductInventory WHERE " +
                 "ProductID = @ID;";
	using (SqlConnection connection = new SqlConnection(connectionString))
	{
		SqlCommand command = new SqlCommand(commandText, connection);
		command.Parameters.Add("@ID", SqlDbType.NVarChar);
		command.Parameters["@ID"].Value = productId;
		using (var da = new SqlDataAdapter(command))
		{
			var dt = new DataTable();
			da.Fill(dt);
			return dt;
		}
	}
}

Another utility method we’ll want is to execute an Update SQL command, to update the quantity (since we don’t have an API).

Again this method takes in the product ID, as well as quantity and connection string, but this method returns a code, not a DataTable. If our command is successful, we’re expecting the return code to be the number of rows affected. If it’s -1, then something went wrong.

public static int UpdateQuantityOfProduct(string productId, int quantity,
 string connectionString)
{
	int code = 0;
	string commandText = 
            "UPDATE AdventureWorks2012.Production.ProductInventory " +
            "SET Quantity = @Quantity WHERE ProductID = @ID;";
	using (SqlConnection connection = new SqlConnection(connectionString))
	{
	   using (SqlCommand command = new SqlCommand(commandText, connection))
	   {
		command.Parameters.Add("@Quantity", SqlDbType.Int);
		command.Parameters["@Quantity"].Value = quantity;
		command.Parameters.Add("@ID", SqlDbType.NVarChar);
		command.Parameters["@ID"].Value = productId;
		connection.Open();
		code = command.ExecuteNonQuery();
		command.Dispose();
	   }
	}
	return code;
}

Connection Strings

We’ll put our connection string for the database in our App.config file. We need to specify the server, which database, and the security we’re using. Mine is set up locally, with Integrated Security (Windows Authentication).

<appSettings>
    <add key="dbConnectionString" value="Data Source=MSSQLSERVER12;
            Initial Catalog=AdventureWorks2012;Integrated Security=True"/> 
</appSettings>

We’ll grab the connection string in the SetUp method in our test class:

private static string _connectionString;
[OneTimeSetUp]
public void SetUp()
{
     _connectionString = ConfigurationManager.AppSettings["dbConnectionString"];
}

The First Check

Let’s get started with our first check! Remember we’re going to:

1. Do a SELECT query on the database for the product and see what the quantity is
2. Perform the PUT operation with the REST service
3. Do the SELECT query again to see what the quantity is now
4. Verify the new quantity is what we’re expecting

0. Setup

First we need to specify a few things, including which product we’re going to use, how we’re going to modify the quantity, and create our query string.

int quantityModifier = -1;
string productId = "875";

So we’re going to take 1 away from our inventory, of product 875 or the amazing racing socks in large.

1. Do a SELECT query on the database to get initial quantity

First we execute the query, and get a DataTable in return. We could return just that individual field, but then our utility method wouldn’t be as useful or we’d have too many doing the same thing -sometimes we’ll want an entire row, sometimes we’ll want many rows. You can do it either way. Here, we’ll do the work in our test to get the field we want, which is the first row and first column.

DataTable initialInventoryDt = 
    Utilities.GetQuantityOfProduct(productId, _connectionString);
int initialQuantity = Int32.Parse(initialInventoryDt.Rows[0][0].ToString());

Now we can figure out what the expected quantity will be when we’re done with our check.

int expectedQuantity = initialQuantity + quantityModifier;

2. Peform PUT operation with the REST service

If we had this pretend REST service set up, our code now would look something like this:

string url = String.Format("http://ourwarehouse/api/products/{0}/inventory/{1}, 
    productId, quantityModifier");
HttpResponseMessage response = Utilities.SendHttpWebRequest(url, "PUT");
Assert.IsTrue(response.IsSuccessStatusCode, 
    "Response code to PUT was not successful");

However, we don’t. So we have to fake it by doing an UPDATE command to the database directly.

And we add this code:

int code = UpdateQuantityOfProduct(productId,expectedQuantity,_connectionString);
Assert.IsTrue(code == 1, "more than 1 row was affected, something went wrong");

REMEMBER this is ONLY because we don’t have that REST service and we’re faking what the service would do!

3. Do the SELECT query again to get the new quantity

Again we perform our select query, and grab the returned value

DataTable updatedInventoryDt = 
    Utilities.GetQuantityOfProduct(productId, _connectionString);
int updatedQuantity = Int32.Parse(updatedInventoryDt.Rows[0][0].ToString());

4. Verify the new quantity that we’re expecting

Now we just do our Assert!

Assert.AreEqual(expectedQuantity, updatedQuantity, 
    "Updated Quantity is not as expected; it is " + updatedQuantity + 
    " but should be " + expectedQuantity);

Other Checks

We could also do a check of the GET method, to make sure our service is pulling information from the right table. Our steps would be:

1. Do a SELECT query on the database for the product to get the quantity
2. Perform the GET operation with the REST service to get the quantity
3. Verify the quantities returned from both match

This example is in the repo, so you can check it out there!

There are many other variations that we could perform, as well, but these are some basic building blocks.

Wrap Up

I hope that this walkthrough and the code help you to be able to automate checks against SQL databases as well as REST services! Let me know if you think something is missing, or if you need some clarity, or if you find a bug in my code!

Also apologies about the formatting here – I need to find a good code snippet plugin to use in WordPress! Let me know if you have any suggestions!

I recently presented my talk Testing RESTful Web Services for the first time at the QL Technology Conference in Detroit. In this talk, I cover why we should test web services, and how to test RESTful web services both with tools like Postman, and C# and NUnit.

I was not prepared for, mid-demo, resounding applause from the audience. I was demoing a cool trick I learned from a teammate, and apparently it was new magic to the audience. Awesome! So I decided to blog about it, to spread the word of this fancy magic and help everyone save time when testing RESTful web services within Visual Studio.

What was that magic? It’s the ability to take a JSON response and quickly turn it into a class – a super fast way to model JSON responses!

Huzzah!

If you already know how to do it, awesome! Spread the word!

What you need:

• a JSON response to copy/paste
• Visual Studio, with a solution loaded (we’ll use C#, not sure how it works with other languages)

I’ll use the same examples from my talk – it uses the gitter API (http://gitter.im).

First, create a new class file in your project:

• Right-click in Solution Explorer where you want the file to go
• Select Add -> Class
• Type a name and click Add

Then, paste the JSON as classes!

• Copy the JSON you want to model as a class
• Inside the class file in Visual Studio, put your cursor inside the namespace
• Choose Edit -> Paste Special -> Paste JSON As Classes
• Much rejoicing!

You’ll need to clean up the class a bit, but it will definitely save you time!

I hope you’re able to use this neat trick to help you in your testing of web services!

You will want to choose your JSON responses for modeling carefully – make sure it has all of the potential properties! Otherwise, down the road when you’re deserializing, you’ll hit a failure because the JSON will have an extra property that your class is missing.

Any other neat tricks you can share?