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.

CreateDBProj-01-AddProjectDialog

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.

CreateDBProj-02-ChangeOutputType

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.

ImportDBProj-01-ImportDialog

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.

CreateDBProj-03-AddSchema

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.

CreateDBProj-04-AddTable

The New Item dialog with table selected.

CreateDBProj-04B-SolutionExplorer

Solution Explorer after adding a schema file and tables

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.

CreateDBProj-05-TableDesign

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.

CreateDBProj-07-DeployDBCon

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.

CreateDBProj-08-DeployPublish

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.

CreateDBProj-09-SchemaCompare

This comparison shows everything as a difference since the schema has not been deployed yet.

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.

CreateDBProj-06-InlineBuildError

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.

CreateDBProj-07-BuildErrorView

This error shows that the column name referenced by the foreign key is incorrect. It has a missing ‘s’ in the name.

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
About these ads

23 comments

  1. [...] Creating a SQL Server Database Project in Visual Studio 2012 (M. Lang) [...]

  2. bga rework stations · · Reply

    hi That’s a good post

  3. [...] Creating a SQL Server Database Project in Visual Studio 2012 – Very clear post about how to use the SSDT in different scenarios.  [...]

  4. Arnold Gultom · · Reply

    why I cannot connect SQL Server 2012 to VS 2012 Express for Web?? The Server Name is not visible. I have installed the SQL Server first then installed VS 2012.

    1. Arnold,
      I don’t have VS Express, so I can’t be definitive in an answer. But here is a Microsoft thread that may have the answers you need.

      http://forums.asp.net/t/1892250.aspx/1?VS+2012+Web+Express+and+feature+support

      It seems that you may need to use SQL Server 2012 Express if you are using Visual Studio 2012 Express.

  5. Marcos Lapa · · Reply

    Hello, great post!
    But I need your help…
    I’m trying to put a web reference to my SQL Server Database Project, but I don’t see how can I do this in Visual Studio 2012…
    I’m using the Visual Studio 2012 Ultimate version and the web reference option doesn’t appear in the Solution Explorer.
    Any idea?
    Thanks

    1. I am unclear as to what you are trying to do? Either
      1) you are trying to reference the database project from a webservice project, and then query the database from the webservice.
      2) you are trying to reference the webservice project from the database project, and then call a webservice endpoint from within the database.

      Answers:
      1) If you want to query a database from within a webservice, you do not do that by referencing the database project. You don’t even need to have a database project to do this, which is a good thing. To call a sql database from within a webservice project, I recommend you just use a SqlConnection and SqlCommand to do so. Or use an ORM that abstracts away all the database calls for you. You can see an example of how to do this in Candor.Security.SqlProvider.UserProvider.

      https://github.com/michael-lang/candor-common/blob/master/Candor.Security.SqlProvider/UserProvider.cs

      2) A database stored procedure cannot call a webservice. You technically may be able to do so via a .Net CLR class inside a database, but that is not the kind of thing the Sql .Net CLR was meant for.

      Let me know if you meant something else in your question, or if you have any clarifying thoughts to help me answer your question better.

  6. I not understood how to create new data base, because you guide us from your existing data base, so please let me know How can i create data base from new connection

    1. This is a guide for a new database project. I think you meant to ask how do you make the publish work if you have not yet created the database. Simple, you can’t. But don’t let that discourage you. I suppose the only step I left out is open SQL Server management studio and use the menus to create a new database with any default options that you need. Then connect to that from the publish step in this article.

      There are numerous guides on how to create a new empty database, so I won’t go into that here.
      via Sql Server Management Studio:

      http://technet.microsoft.com/en-us/library/ms186312%28v=sql.100%29.aspx

      via Server Explorer in Visual Studio:

      http://msdn.microsoft.com/en-us/library/z6sa01t4%28v=vs.71%29.aspx

      other considerations:

      http://stackoverflow.com/questions/7132639/how-to-create-new-database-in-sql-server-express-2008-and-allow-for-connecting

  7. Ben Corpus · · Reply

    Hi, Michael.

    I read your post “Creating a SQL Server Database Project in Visual Studio 2012″ and I wanted to know how you maintain the database as it changes in its lifetime. For example, you show how to generate it from scratch (bootstrap), which is fine, but how to maintain it moving forward.

    Is there anything in VisualStudio 2012 that a developer could run to generate idempotent SQL scripts?

    1. Yes, you can generate a script based on the state of an existing database. The section “Deploying the Database – Schema Compare” works not only on a fresh empty database, but also on a database at any prior version of your schema. When you press the “generate script” button per that section above, you will get a sql script ready to give your DBA or you can run it yourself, depending on how your process works.

      My process – as an example:
      Typically during development I will have a local database on my machine that I regularly publish changes to during database schema development and I test that until the functionality I am working on is complete. Then I have another test environment that integrates with other developers changes. I point the schema compare at that database and generate a script and then deploy from visual studio. Once all my changes are tested and a regression test is complete, I then schema compare against the staging database which duplicates the production schema. To ensure staging matches production, occasionally a production backup is restored into staging and then permissions are altered in staging as needed to allow for validation testing. I generate the script against the staging database and hand that script over for deployment by another team member. If that staging deployment goes smoothly and regression tests succeed, then I get ready for production deployment.

      production deployment: Run the schema compare now against production. Then check that the script matches the generated script for staging. Resolve that any differences are related to permissions, which should be the only difference in my process. Then at a scheduled time, someone backs up production and then runs the newly generated production script against production concurrently with corresponding application deployments.

      I hope this helps.

  8. Great post!
    In the “Deploying the Database – Schema Compare” section, you wrote “If you leave all changes selected, then this generates the same script as if you followed the ‘Publish’ option in the previous section.”, I think this is not true if you have pre or post-deployment scritpts because they run in case of the ‘Publish’ but don’t run using ‘Schema Compare’.

    1. You are correct. I wasn’t considering pre and post since in my case I had nothing in those files.

  9. hello! thank you very much for your crystal clear explanation……just one query ….I am trying to create service based database….but when I try to select table from dataset configuration wizard I cant select ….though table radio button is present…I am using sql server 2012 and vs 2012….Please help…

    1. Creating your database is beyond the scope of this article. to creata a new database I usually just open sql server management studio and use the simple commands available there.

  10. Namrata Jain · · Reply

    Great post but i am not able to have different schema as prefix to table name like we have namespace for class name in c#. In my database project i need to divide my table module wise and want to add schema name to table name for example in commercial module name of table can [Commercial].[Sales] but here even after adding schema i am getting name as [dbo].[sales].

    please help. i think i am left out with some setting.

    1. If you followed the instructions above and the schema for a new table is still dbo, then just edit the sql file for the table and update dbo to the intended schema. Its a one time operation for the table.

  11. Sasha · · Reply

    Thanks for the great post. Is it possible somehow to run scripts generated in Visual Studio directly in SQL Server Management Studio? Thanks.

    1. Yes. You have the option to either publish the database change or just to generate the script. Use the ‘generate script’ button to generate a script file. Then run it using your tool of choice.

  12. shankar · · Reply

    Very Informative

  13. Vandergood · · Reply

    great post thank’s lot perfect

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: