Simple Way to Create a Database Unit Test Project in SSDT

Another interesting feature of SQL Server Data Tools (SSDT) is a unit testing. SSDT enables to develop and execute unit tests interactively in Visual Studio. You can write tests for stored procedures, functions and triggers.

To create a simple database unit test project you need Visual Studio + SSDT, of course, and a database project (how to create it I wrote in this post).

  • Open your database project in Visual Studio
  • Find the object to be tested in Solution Explorer
  • Right-click and select Create Unit Tests
Create unit test
  • In Create Unit Tests window check that only one database object is selected (on which you right-clicked earlier).
  • Choose the type of test project (Visual Basic or C#). This does not influence the database tests, it’s just a language of test project. As you see later all the work around the test will be made in the graphical interface of test designer.
  • Type the name of your test project and the name of a test class.
Name unit test project
  • Press OK button.
  • In SQL Server Test Configuration choose an existing connection (I’ve created it earlier during the creation of database project) or create a new connection, then press OK.
  • Visual Studio will create a database test project and unit test. This unit test will be opened automatically in Test Designer.

Unit test

Create a New Database Project in SSDT from Existing Database

One of the very useful features in SQL Server Data Tools (SSDT) is a possibility of offline database projects. If you have an existing database in SQL Server it’s quite easy to create a database project from it.

  • In Visual Studio click File -> New -> Project
  • In New Project window select SQL Server template, select SQL Server Database Project and enter the desired name for you project, then press OK
New database project window
  • In Soltion Explorer right-click you project, select Import in context menu, then Database.
  • In Import Database page press New Connection button (if you had not created any connection earlier).
Import database - step 1
  • In Connection Properties page enter the Server name and select a database name in dropdown list.
Setup connection
  • In Import Database page press Start button. Visual Studio will create the definitions of your database objects in database project.
  • After all is done just press Finish button. Now let’s go to the Solution Explorer and examine the code.

Import database - final step

Installation of SQL Server Data Tools

SQL Server 2005-2008R2 was shipped with a Business Intelligence Development Studio. This is a Visual Studio shell with special templates/add-ons/plugins, etc. that are needed for development of BI solutions in SQL Server (analysis, reporting and integration services). SQL Server 2012 has introduced two new tools that are build on Visual Studio. They are SQL Server Data Tools (SSDT) and SQL Server Data Tools – Business Intelligence (SSDT-BI). The latter is a successor of BIDS.

As stated in MSDN, Microsoft SQL Server Data Tools provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform (both on and off premise) within Visual Studio. Database developers can use the SQL Server Object Explorer in VS to easily create or edit database objects and data, or execute queries. Developers will also appreciate the familiar VS tools we bring to database development, specifically; code navigation, IntelliSense, language support that parallels what is available for C# and VB, platform-specific validation, debugging and declarative editing in the TSQL Editor, as well as a visual Table Designer for both database projects and online database instances.To install the latest and up-to-date versions of software you need:

  • Visual Studio 2013 Professional Edition or higher. You can select only SSDT and unselect the other options.
 VS installation - feature selection
  • Install the latest update via Visual Studio update channel (Tools –> Extensions and Updates –> Updates).
 VS Extensions and Updates