SQL SERVER DATABASE PROJECT的简单应用

SQL SERVER DATABASE PROJECT能够从Visual Studio中创建的脚本来创建本地的数据库。比如说，别人给了你一个已经搭建好的Solution, 里边用到了SQL Server数据库。你现在想在本地跑这个程序，就需要在本地创建这个数据库实例。如果使用SQL语句脚本的话，也可以。但是还是需要从别人的已经建好了数据库中往外生成SQL语句，生成后你再在你的本地机器上执行这个SQL文件，而且有时候还会有一些版本上的细微的区别。如果使用Database Project的话，只需要在solution中添加database项目，以后无论谁想在本地搭建数据库环境，只需要把这些脚本发布到本地数据库中就可以。

具体步骤如下：

Creating a Database Project in Visual Studio 2012

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.

The New Item dialog with table selected.

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.

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.

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.

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.

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.