• Michael Lang
• | January 8, 2013
• | database
• | 30 Comments
• | schema, VisualStudio

Creating a SQL Server Database Project in Visual Studio 2012

I used to create my application database by managing a series of install scripts with one table or stored procedure per file.  In those files I had to manage potential changes to those schema items and also handle conditional creation of those entities if they did not exist.  SQL Server Data Tools built into Visual Studio 2012 has made this into a much simpler process.  A version of this existed in Visual Studio 2010, but it is even better in Visual Studio 2012.

Conditional Creation of Tables and Columns (The Old Way)

If your application has numerous deployment versions and you want your script to adapt and be able to either install in a fresh database or into a prior version of the schema, then you have lots of work to do.  First you create the latest version of the table, if it does not exist.  If it does already exist, then you check for the missing columns added since your initial deployment and add them as needed.  You may also need to drop columns that have been removed over time.  Here is an example of an old script where I have done this.

Please excuse the name prefixes. I used to prefix table and stored procedure names with a prefix indicating a kind of namespace. I’ve since changed over to using different schemas. This script also checks for an existing table with sysobjects since this was written for SQL Server 2000 and then updated to use SQL server 2005. It has since been replaced, however sysobjects still works for backwards compatibility. I discourage you from using this in any current or future projects.

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XQSXG_MMS_MailMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[XQSXG_MMS_MailMessage](
	[MailMessageID] [int] IDENTITY(1,1) NOT NULL,
	[MailMessageTypeID] [int] NULL,
	[ToAddress] [nvarchar](256) NOT NULL,
	[ToUser] uniqueidentifier NULL,
	[Priority] [int] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_Priority]  DEFAULT ((0)),
	[Subject] [nvarchar](512) NOT NULL,
	[Format] [int] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_Format]  DEFAULT ((0)),
	[Body] nvarchar(max) NULL, /*SQL Server 2005+, prior used NText*/
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_CreatedDate]  DEFAULT (getutcdate()),
	[Attempts] [int] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_Attempts]  DEFAULT ((0)),
	[LastAttemptDate] [datetime] NULL,
	[CompletedDate] [datetime] NULL
)
 END
GO

if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'MailMessageTypeID')
BEGIN
	ALTER TABLE dbo.XQSXG_MMS_MailMessage ADD
	MailMessageTypeID int NULL
END
GO
if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'ToUser')
BEGIN
	ALTER TABLE dbo.XQSXG_MMS_MailMessage ADD
	ToUser uniqueidentifier NULL
END
GO
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'FromAddress')
BEGIN
	ALTER TABLE dbo.XQSXG_MMS_MailMessage Drop Column FromAddress
END
GO
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'CCAddress')
BEGIN
	ALTER TABLE dbo.XQSXG_MMS_MailMessage Drop Column CCAddress
END
GO
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'BCCAddress')
BEGIN
	ALTER TABLE dbo.XQSXG_MMS_MailMessage Drop Column BCCAddress
END
GO

if not exists 
(	select * from dbo.sysobjects where id = object_id(N'[dbo].[PK_XQSXG_MMS_MailMessage]') and parent_obj = 
	(select id from dbo.sysobjects where id = object_id(N'[dbo].[XQSXG_MMS_MailMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
)
BEGIN
	ALTER TABLE [dbo].[XQSXG_MMS_MailMessage] ADD CONSTRAINT [PK_XQSXG_MMS_MailMessage] PRIMARY KEY  CLUSTERED 
	(
		[MailMessageID]
	) 
END
GO

Creating a Database Project in Visual Studio 2012 (The New Way)

You can reverse engineer a database project from an existing database, or create a new project from scratch.  This article will focus on how to create one from scratch.  Open Visual Studio and either create a new solution or open an existing one to which this new database should be a part of.

Adding the Project to a Solution

Select the Other Languages, SQL Server template group on the left.  Then choose the SQL Server Database Project type.  Enter a project name and press OK.  I usually pick a project name matching the class library that will contain the business layer or data layer that will interact with this database, and then append Database to the end of that name.  It may make more sense to also put SQL in the name; just in case you use another type of database in the future.

Update the Project Properties

You should check out the project properties and see what options are available.  On the main Project Settings tab page, there is a ‘Database Settings’ button that lets you specify any metadata to be applied to the database as a whole.  The defaults have worked for me, but if you need a specific database collation, file group, or need certain flags like ANSI_PADDING then check that out.

I tend to override the default output type, by checking the ‘Create script (.sql file)” option as shown here.  I do not change the default schema from ‘dbo’; even knowing that below I want most of my tables, functions, and procedures in a specific schema.

Import a Database (Optional)

If you already have a database to start with; you can import from the current schema.  Then you can follow the other sections below for making changes and publishing those changes.  To import a schema, just right click on the project node in solution explorer and select menu “Import” -> “Database”.  Then configure the database connection and pick the options for things you want to import.

I prefer the Folder structure of “Schema\Object Type”.  This is what I will assume for the following sections; It is also the default selection for the Import dialog.   I don’t normally change any of the import setting defaults.  If you need permissions of any specific database settings from your existing database then select those import options.  You can modify the database settings in the project properties as noted in the previous section.

Creating a Schema

Before I create any tables, I usually define a schema in which I will place all my database objects for this project.  This allows you to have simpler names for your tables, since the schema scopes them similar to a namespace in .Net code.

It may not really matter where you put the schema file, however I follow the convention used when reverse engineering a database.  Create a folder in the database project of the same name that you will name your schema.  Then add the schema file to that folder using that same name.

This seems so much better to me than the old way above where I showed a prefix on a table name to facilitate grouping of related tables.  Having different schemas for loosely coupled or unrelated sets of tables also helps me think of ways tables could be segmented into different database shards.  You can either go the route of one database project per schema, or one database project for all your schemas.  I usually make that decision based on how I want to deploy the database.  One database project equals a deployment to one database instance.

Add a Table

When reverse engineering a database into a database project it creates folders under the schema folder for Tables, Functions, and Stored Procedures.  I follow the same convention when creating these items manually.  I just create a table by right clicking on the Tables folder under the schema and selecting the ‘Add Table’ menu item.

Table Designer Overview

The table designer gives you options as to how you want to design your table.  It has a design pane which has a columns grid and keys overview with right click support for adding new keys.  It also has a raw text pane with the sql required to create the table as defined in the design pane.  As you type in the raw text pane the changes appear in the design pane; and as you change details on the design pane it updates the raw text pane.  On my 5 year old laptop, I have not experienced any performance issues either to open the file or in having updates sync between the panes.

As you use this designer all the keys and constraints are added in the table definition sql file.

Deploying the Database – Publish

Publishing the database changes is very simple.  Just right click on the database project in solution explorer, and select “Publish”.  A dialog appears for connection details.

Assuming you followed the steps above during project setup, this will just generate a script file.  I prefer script files so that I have them ready for promotion to the next environment.  If your project properties default to do an automatic publish instead of generating a script, you can override this by just pressing the ‘Generate Script’ button.

Deploying the Database – Schema Compare

You can also create a deployment script with the schema compare command.  This is also available as a right click menu item on the database project node in solution explorer.  This gives you more flexibility.  You pick the database target to compare the database project to and it tells you what is changed.  Then you can choose which items are included in the generated script.  If you leave all changes selected, then this generates the same script as if you followed the ‘Publish’ option in the previous section.

To begin a comparison to a database, pick in the ‘Select Target’ dropdown to select a database connection.  In the screenshot above that dropdown has my connection name “.\SQLExpress.CandorMail”.  Then press the Compare button (Or use shortcut Shift-Alt-C) to see the changes.  If you have changes, then press the “generate script button” (Or use shortcut Shift-Alt-G).

If you actually want to deploy these changes to the target database now, then press the ‘Update Target” button (next to generate script).  This has no shortcut, thankfully.  I personally wouldn’t want a possible accidental key press of the wrong combination to publish a database change to a production database.

Build Errors

One of the great advantages of a database project is the continuous ‘compilation’ of the database project objects.  If you have invalid definitions or reference other objects that do not exist, then you will see compilation errors.  This is a great development enhancement over parsing scripts and manually running them against a local database instance on a regular basis.

As you type you will see problem areas highlighted in the raw text pane as shown here.  If you hover over it, you’ll see the error message.

Also if you view the errors list the detail will be shown.  If you double click on the error it will navigate you to the table designer where the error is located.

Database References

You may have multiple database projects in your solution that have some level of dependency.  Maybe one of the projects is a set of customizations to a base database product defined in another project.  Or maybe you just want each schema defined in a separate project.

Without a reference the project that depends on external database objects will not compile (generate a script) if it cannot find the referenced database object.  To fix this you can create a reference to the other database project.  Just right click on the ‘references’ node of the database project and select “Add Database Reference”.  Then you can pick another database project in the solution, or a system database, or a dacpac file exported from another database.

Requirements

This works on my machine.  I didn’t research if it works with less features installed than I have, but it probably does.  I have the following Microsoft development tools installed.

1. Visual Studio 2012 Professional with update 1 (Full Install , lots of ‘features’.  Not all are listed below)
   • Now includes: SQL Server Data Tools 11.1.20627.00 (separate install for VS 2010)
   • Now includes: Microsoft SQL Server Data Tools (yes, this is different from the previous item)
2. SQL Server 2012 Express with all features including SQL Server Management Studio (ENU\x64\SQLEXPRWT_x64_ENU.exe, 669.9 MB).  Obviously pick the 32 bit version instead if your OS is 32 bit.  http://www.microsoft.com/en-us/download/details.aspx?id=29062