Regardless of what business problem your application addresses, one thing is certain: The application interacts with data. Data is central to just about every application, regardless of whether the application is a web, Windows Presentation Foundation (WPF), Windows Forms, or mobile app. The irony is that although data is central to our applications, it's often treated differently from other aspects of our applications. Treatment of items such as class libraries, GUIs, and views is straightforward in source code control and deployment. But what about database artifacts such as tables, stored procedures, functions, and constraints? How are they handled in source control and deployment?
This is where Visual Studio 2010 SQL Server Database Projects come into play. Database Projects are project templates for database, server, and data-tier application (DAC) projects. With Database Projects, we can easily place our database under a source code control. Additionally, we can write unit tests to ensure that artifacts such as a stored procedure are consistent with a given specification. To support unit tests, Database Projects facilitate the task of hydrating the database with a known good set of data. Database Projects also have a deployment feature that supports both testing and build processes. In this introductory article, I provide a glimpse of what Database Projects can accomplish.
First Step: Creating a Database Project
Creating a Database Project is no different from creating any other Visual Studio project type. Figure 1 shows the New Project dialog box for our new
Figure 1: New Project dialog box for a SQL Server 2008 Database Project
In this example, we will use the SQL Server 2008 Database Project Template. If your area of concern is a single database, this is the project template you'll want to use. However, if you need to factor in server-related resources such as the Master Database and server logins, then the SQL Server 2008 Server Project is the template you will want to use. There are options for Oracle as well. You can find templates and information explaining how to integrate Oracle into your Visual Studio application development efforts.
With the Database Project in place, we have two basic options. The first is to simply begin the process of writing database scripts. The second is to
import an existing database. The latter option is the one we will choose and is the one you will most likely want to use as well. To import an existing
database definition, in Solution Explorer right-click the dbproject node and choose Import Database Objects and Settings, as Figure 2 shows.
Figure 2: Importing an existing database into a Database Project
After you select the option, the Import Data Wizard launches. In this example, the Northwind Traders sample database is used. In the wizard, you have
the opportunity to set options. In most cases, the defaults in the wizard will suffice. Once the wizard is finished, the database artifacts are added
to the project. Figure 3 shows how Solution Explorer appears after the Northwind Traders database has been imported into the project.
Figure 3: Imported database objects visible in Solution Explorer
In Figure 4 you can see how one of the artifacts shown in Figure 3, the Ten Most Expensive Products stored procedure, appears in the Visual Studio IDE.
Once the database objects are in Visual Studio, it's a simple task to get them under source code control. Remember, a Database Project is like any
other Visual Studio Project in that regard.
Figure 4: Stored procedure as viewed in Visual Studio
Unit Tests Are for Databases, Too!
Perhaps the biggest contribution that Database Projects provide is the ability to easily create unit tests. This has several implications. First, chances are you're working in a continuous integration (CI) environment. Wouldn't it be nice if you could incorporate automated database unit tests into those efforts? Manually, it's easy to run this stored procedure, whether from within SQL Server Management Studio or within the Visual Studio IDE via Server Explorer. This brings up another positive implication of the Database Project: the ability to automate unit tests.
The quickest way to wrap your database in unit tests is to navigate to the Schema View. From the Schema View, navigate to the object you want to place
under test, and choose Create Unit Tests from the shortcut menu, as Figure 5 shows.
Figure 5: Creating database unit tests via the Schema View
You can set a number of options for unit tests in the Create Unit Tests dialog box, shown in Figure 6.
Figure 6: Setting database unit test options in the Create Unit Tests dialog box
After you click OK in the Create Unit Tests dialog box, you will immediately be prompted with a Project Configuration dialog box, shown in Figure 7. In this
dialog box, you set options regarding where your test database will be deployed and whether you will generate test data before the unit tests are executed.
Figure 7: Specifying a test configuration in the Project Configuration dialog box
After setting the configuration options for our test project, we can now see in Solution Explorer a new dbprojectTests project with one new test
fixture to test the Ten Most Expensive Products procedure, as shown in Figure 8.
Figure 8: Multiple assertions assigned to a single database unit test fixture
By default, the test fixture that's created doesn't do much. For our example, I modified the test fixture by deleting the default condition and creating a new condition to test the row count. In this case, the stored procedure should return 10 rows. Accordingly, the test condition asserts that 10 rows are returned. Figure 8 also shows this test condition passed. Other test conditions that can be asserted are whether or not a result set was returned and scalar values. For a given row/column, you can assert a specific value.
Because the tests are configurable, you can modify the server location where the test database will be deployed. From within that configuration, you can also control how that test database will be hydrated. And because all this functionality is wrapped inside a test project, your build process can use these same artifacts.
Give Database Projects a Try
Database Projects are probably one of the most important yet under-utilized project templates in Visual Studio. Even as the bulk of your application undergoes the rigors of automated testing, the database aspects of your application may be neglected in your testing process. By using Database Projects, you can rectify this omission by bringing in all your database artifacts (e.g., table definitions, stored procedures, functions) into Visual Studio so that they can be treated like any other item in Visual Studio. Once it's in Visual Studio, your database can be placed under source code control.
Perhaps the most important implication of Database Projects is the ability to place a database under test. With database unit tests, you can verify whether your database procedures, functions, and ad hoc SQL conform to application requirements. With database unit tests, the database aspects of your application can be included in your automated build environment. Database Projects are one of the hidden gems in Visual Studio and are worth your consideration for inclusion in your Visual Studio development processes.
John V. Petersen is the Philadelphia Microsoft Practice Director for CEI America and a Microsoft ASP.NET MVP. He has authored or co-authored several books on software development and also was in private practice as an attorney for several years. John blogs at codebetter.com/johnpetersen, and you can follow him on Twitter: @johnvpetersen.