Continuous Integration

Up to now, we have seen how we can develop applications using C# and SQL Server using ADO.NET and the Entity Framework. But developing applications is only one aspect of the typical developer’s job. At the start of the day we check out the newest code, at the end of the day we commit our own changes, and we probably do that a few times in between as well. Source control has been an invaluable tool in our day-to-day lives. Next to that, we have to make sure that our changes don’t break any existing code, and if they do, we want to know as soon as possible. Writing unit tests for our code—whether upfront, using test-driven development, or after we’ve written the code—has become a big part of our jobs. Last, but not least, we want to deploy our application to any environment, and the fewer manual actions we have to take, the better.

Yet, when we do all this, the database is usually just a bunch of scripts that we have to save, test, and deploy manually. I have to admit that’s how I do it, too. Unfortunately, databases aren’t just a bunch of code files that can easily be compiled and copied for deployment. Even though I have little experience with the topics in this chapter, that doesn’t mean I can’t point you in the right direction.

Source control

When you’re using Entity Framework Code First or Database Projects to develop your database, you already get some sort of database source control. After all, it’s pretty easy to update a database or go back to a specific version. However, when you’re not using either of those, you’re probably stuck saving SQL scripts, and they probably go one way only, so going back to a previous version isn’t really an option. None of those are examples of true source control, though. Unfortunately, all SQL source control systems I could find were paid ones, and I’m not discussing paid products in this book. So although SSMS has an options page for source control, this is only to select a third-party plugin, and the choices are limited.

It is possible to put your database into source control systems such as SVN or Git. Probably the best one out there is SQL Source Control by Redgate. I have used Redgate products in the past, and they’re very good tools. Unfortunately, they aren’t cheap.

Other tools I’ve found are GitSQL, ApexSQL (I’ve used their Diff tool), AgentSvn, and Liquibase. All have free trials or (very) limited free versions, so you can try them at your own risk and leisure.

For now, it seems database projects are the easiest and, most of all, cheapest method of maintaining some sort of source control for your database. And that’s only your schema—getting (some) data into source control seems, for now, impossible.

Testing

When it comes to testing our code, we usually mock our data access layer. After all, our tests should run independent of the database. We test if our logic is correct, if 1 + 1 really equals 2. But what we often don’t test is whether the database really returns what we expect it to return.

Once again, Redgate has a rather pricey testing tool for SQL Server unit testing. I’m not discussing it here, but if you really want a good testing tool, that’s probably the way to go. For testing, however, we don’t need a third-party tool. The Visual Studio Database Project includes testing tools.

Start Visual Studio and create a new SQL Server Database Project. I’ve called mine DbTest, but name it whatever you like. Now add a table, and call it Person. Make the default Id field an auto-incrementing field by setting Identity Specification to True in the options pane. Then add a non-nullable field, FirstName varchar(256), and a nullable field, LastName varchar(256). Next, add a stored procedure to the project, call it GetFullName, and change the contents of the created file to the following.

Code Listing 88: GetPeople stored procedure

Now publish the database. If it fails, you may need to set the Target Platform to SQL Server 2014 in the project properties. If everything went well, you should now have a database with a single table and a single stored procedure.

To create a unit test, right-click on the stored procedure, and in the context menu, choose Create Unit Tests… You now get a window where you can pick stored procedures to test, select a type and name for your test project, and pick a name for your test class. Be sure to change the default VB project to a C# project. Call the project whatever you like; I’ve called it DbTest.Tests. I’ve called my unit test GetFullNameTests. In the next part, you can pick a connection, a secondary connection, and whether or not your changes should automatically be deployed before a test run. Set the connection to your new DbTest and select Automatically deploy… Pick your DbTest project as Database project and debug as your Deployment configuration.

You now get a Test Project with a SQL Server unit test and a regular unit test class. You can delete the regular unit test class. The SQL Server unit test contains a SQL script that says “-- database unit test for dbo.GetFullName” at the top. It should be shown by default after you create the project. Underneath the script are the test conditions. You will want to delete the default Inconclusive condition, as it will always make your test fail. Now change the script to the following.

Code Listing 89: SQL unit test

Add a test condition of the type Scalar Value. In the options of the condition, set Null expected to False and Expected value to Sander Rossel (or your own name). You can rename your test in the top right above your query; I’ve renamed it FirstAndLastName.

Now, in SSMS, add some data to your database. If you didn’t test for Sander Rossel in the condition, make sure you change the value in the following script as well.

Code Listing 90: Insert test data

Now you are ready to run your test. In Visual Studio, go to the menu and select Test > Windows > Test Explorer. In the Test Explorer, simply click Run All. Your test should now run and pass, if you did everything correctly.

Now let’s add a failing test. Go to your GetFullNameTests again, and add a test by clicking the green plus sign somewhere in the bar above the query. Name your test OnlyFirstName. Again, remove the Inconclusive condition add a Scalar Value, and test for Satya. Change your script to the following.

Code Listing 91: Failing unit test

Now run your tests again, and OnlyFirstName should fail. It will tell you that value Satya was expected, but the returned value was null. That’s right, because the procedure simply concatenates FirstName and LastName, but LastName is NULL, and by default, anything + NULL = NULL. We should use the CONCAT function instead. So in Visual Studio, go to the GetFullName file and change the SELECT line with the following.

Code Listing 92: Fixed procedure

Run your tests again, and this time they should both pass. The change is automatically published because that’s what we specified when we created the project. If someone changed this procedure back to what it was, because it has less verbose syntax, and he doesn’t quite get CONCAT, our test will fail again. More likely, if someone adds MiddleName, we’ll know pretty soon if their change broke our cases where MiddleName is NULL. Likewise, they can add unit tests for MiddleName.

Deployment

Deploying a database is often a manual task. DBAs want to keep total control of their database. The IT department doesn’t trust automated deployment, or is afraid it’s triggered at a wrong time. They’re not confident that the development team has their scripts in order so that an automatic database update goes exactly as planned, always. When the database goes awry, it’s often a costly exercise to get everything running again. In short, automated database updates make a lot of people very nervous. Manually running scripts is the way to go. We have seen that (semi-) automatic deployment is pretty easy using Entity Framework Code First or Database Projects. When using a Database Project, you could even go for (manual) deployment using a Data-tier application package.

Be that as it may, it is possible to just automate database deployment. Perhaps you’d like this for noncritical environments, such as a test environment. Again, Redgate has a good, but expensive, solution. We’ve talked about Database Projects, so I’m skipping that here. One last thing I’d like to discuss is the SQL Server command line tool, or the sqlcmd. Chances are you need to download it, especially when you’re using a separate server that handles updates. You can download the Command Line Utilities for SQL Server in the Microsoft Download Center. With this tool, you can execute scripts from the command line, batch scripts, and your favorite CI tools. The sqlcmd tool was introduced with SQL Server 2005, when it replaced the functionally similar isql and osql tools.

Once you have the sqlcmd installed, open up a command window. Now, in the command window type the following (hit enter for a new line).

Code Listing 93: SELECT using sqlcmd

On the first line, we connect to our database. Each line after that makes up your query, which is executed after a GO command. The -S parameter is the name of your server, -E indicates we’re logging in with a trusted connection, and -d specifies the database. To exit the sqlcmd, simply type exit. Note that all parameters are case sensitive. If you’re not on a trusted connection and you would like to log in using a username and password, you can use the following command instead.

Code Listing 94: sqlcmd using SQL Server login

When you run the sqlcmd from the command prompt, you can stretch your query over multiple lines and execute using GO. When you’re not in the command prompt, you need to use -q (query) or -Q (query and exit) and a single line query.

Code Listing 95: sqlcmd using a direct query

Of course, you can also specify a script file to run using -i. Create a file somewhere (I put it in C:\Temp), name it something (like SelectPerson1.sql), and then you can run the following command.

Code Listing 96: sqlcmd using an input file

Now the results are a little hard to read in the command prompt. You can also output them to a file using -o.

Code Listing 97: sqlcmd using an output file

Another option you may like to use is -b, which terminates the job if an error occurs. You can find all options in the sqlcmd documentation.

Using the sqlcmd, you can automate certain SQL tasks by running them from the command line. I should mention that this in no way replaces the SQL Agent, which also runs automated SQL jobs. The thing is, you can’t run the SQL Agent from your CI tool, while you can run the sqlcmd utility. The sqlcmd tool still does not automate your deployments, but it may be useful in running scripts automatically before your tests start off, or after you commit some new scripts to your source control system.