HTTP Status Coop – 307 Temporary Redirect

Sixth in the HTTP Status Coop series is 307 Temporary Redirect!

307TempRedirect_img

In this case, the request should be repeated with another URI; however, future requests should still use the original URI. In contrast to how 302 was historically implemented, the request method is not allowed to be changed when reissuing the original request. For example, a POST request should be repeated using another POST request.

via Wikipedia

My husband, lovingly redirecting Cooper out of the snow ūüėÄ


For the entire HTTP Status Coop series that’s been released so far, head over to the HTTP Status Coop page on my blog!

Advertisements

HTTP Status Coop – 305 Use Proxy

Fifth in the HTTP Status Coop series – 305 Use Proxy!

305UseProxy_img

The requested resource is available only through a proxy, the address for which is provided in the response. Many HTTP clients (such as Mozilla[26] and Internet Explorer) do not correctly handle responses with this status code, primarily for security reasons.

via Wikipedia

This¬† is a photo of my custom Big Head (from Fathead) of Cooper, that sits at my desk! It’s my proxy for interacting with my dog while at work ūüėÄ


For the entire HTTP Status Coop series that’s been released so far, head over to the HTTP Status Coop page on my blog!

HTTP Status Coop – 303 See Other

Fourth post in the HTTP Status Coop series – 303 See Other!

303SeeOther_img

The response to the request can be found under another URI using the GET method. When received in response to a POST (or PUT/DELETE), the client should presume that the server has received the data and should issue a new GET request to the given URI.

via Wikipedia


For the entire HTTP Status Coop series that’s been released so far, head over to the HTTP Status Coop page on my blog!

HTTP Status Coop – 206 Partial Content

Third post in the HTTP Status Coop series – 206 Partial Content!

206PartialContent_img

The server is delivering only part of the resource (byte serving) due to a range header sent by the client. The range header is used by HTTP clients to enable resuming of interrupted downloads, or split a download into multiple simultaneous streams.

via Wikipedia

 

HTTP Status Coop – 202 Accepted

Second post in the HTTP Status Coops – 202 Accepted!

202Accepted_img

The request has been accepted for processing, but the processing has not been completed. The request might or might not be eventually acted upon, and may be disallowed when processing occurs.

via Wikipedia

I chose this photo because it both shows that Cooper was accepted into our family, and he’s accepted that he’ll be made to wear sweaters. His pig has also been accepted as his favorite toy…

Targeting Quality 2017 Recap

I was lucky enough to be accepted to speak at Targeting Quality in Kitchener, ON this year, and had a great time out there! This was my first time speaking outside the US, so it felt like a big accomplishment even though it’s just 3 hours from home.

I only attended 3 sessions, including the keynotes – I missed one to center myself for my talk, and then I missed a session during my talk ūüėÄ

I storified my tweets from sessions I attended here: https://storify.com/g33klady/targeting-quality-2017-recap

I attended:

 

HTTP Status… Coops?

In going back over my Testing RESTful Web Services talk prior to presenting at Targeting Quality, I decided that the HTTP Status Cats I was using on one of my slides just didn’t feel right. I was already using my dog in my talk, so why not make some up?

I’m calling them HTTP Status Coops because his name is Cooper. I’ll roll them out once a week or so here, in order. The list is incomplete as of now, but I’ll keep adding to them as long as Cooper keeps being goofy!

200 OK

200Ok_img

Standard response for successful HTTP requests. The actual response will depend on the request method used. In a GET request, the response will contain an entity corresponding to the requested resource. In a POST request, the response will contain an entity describing or containing the result of the action.

via Wikipedia

This one was difficult because there are SO MANY PICTURES of Cooper that are him being 200 OK. He’s so expressive and has a huge smile. But this photo won out.

 

Kalamazoo X 2017 Recap

Holy shit.

Another amazing Kalamazoo X. This year was even more special because I was honored to be asked to speak. To be honest, speaking at Kalamazoo X was on my bucket list, but I never thought it would happen. It’s like the pinnacle of my speaking career. Doing a technical talk is easy. Doing live demos is easy. Being this vulnerable and open with a room full of people, some of which you work with, or have known for years, but never opened up like this? It’s fucking hard.

I was a nervous wreck leading up to it – is this too much? Am I going to regret this? Are people going to look at me differently, or treat me differently?

It went really well, actually. As usual, all talks leading up to mine fit mine perfectly, and all after did the same. Somehow we always get a theme or three running through all of the talks unintentionally. Yes, it’s always by accident – Mike isn’t that good of a planner.

My live tweets are compiled here:¬†https://storify.com/g33klady/kalamazoo-x-2017. I always get a lot of encouragement from people for live-tweeting – both those that aren’t able to make it, and from those that have hearing difficulties. It’s my way of note taking, so I’m glad that it helps others!

CodeMash 2017 Recaps

Another CodeMash here and gone. I got in around 450 tweets this year – seemed like I didn’t attend as many sessions.

Here are the recaps (via Storify) for the sessions I attended!

Precompilers (Tues and Wed)

Storify: https://storify.com/g33klady/codemash-2017-days-1-and-2-precompilers

Day 3 Sessions (Thursday)

Storify: https://storify.com/g33klady/codemash-2017-day-3

Day 4 Sessions (Friday)

  • Mashing Up QA and Security with Craig Stuntz
  • (I was going to attend¬†Over-Achiever, Under-Believer: How to Match Your Confidence to Your Competence with Maureen Zappala but a few minutes in I realized it was a re-versioning of the first talk in my recap from last year:¬†https://storify.com/g33klady/codemash-2016-day-4)
  • Getting Started in Functional Programming with F# with Reid Evans
  • Leading Hackers and Hacking Leaders with Jonathan Popham

Storify: https://storify.com/g33klady/codemash-2017-day-4

Using SQL Databases and REST together in Integration Automation with C# and NUnit

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!