使用VS2010管理数据库项目

From：

http://www.visualstudiotutor.com/2010/08/manage-database-projects-with-visual-studio-2010/

 

 

Manage Database Projects With Visual Studio 2010

19. Aug, 2010 0 Comments by admin

Introduction

Prior to Microsoft Visual Studio 2005, managing database projects had been a real challenge. Microsoft Visual Studio 2005 Database Professional Edition (also known as Data dude) simplified this process and Microsoft Visual Studio 2008 Database Edition GDR 2 further, along with several bug fixes, enhanced its capability to support newest feature of SQL Server 2008. New release i.e. Microsoft Visual Studio 2010 brings a whole lot of new features and enhancements compared to its earlier versions. For example, you can now connect to SQL Server from Visual Studio IDE (Integrated Development Environment) itself with new in-built T-SQL Editor without actually going to SQL Server Management Studio (SSMS), you will get T-SQL Intellisence support while writing or modifying your T-SQL code etc.

Microsoft Visual Studio 2010 (not all editions, see note below for more details) allows to create and configure a database or server project as offline representation of your user databases or master database, which you can add to VSTF (Visual Studio Team Foundation) for version control and collaborative development. This reduces the risk of overwriting the offline representation, improves the communication among developers and helps your database development process to be become a part of regular software development life cycle.

Getting started…

Microsoft Visual Studio 2010 provides several templates to work with database development. For example Database Project, Database Project Wizard, Server Project, Data tier application etc. In this article, I am going to discuss about creating database project using Database Project and Database Project Wizard templates for user databases. Rest I will be talking of in the next couple of articles.

Note:
I am using Microsoft Visual Studio 2010 Ultimate edition RTM for all these examples and demonstration though database project development is supported in couple of other editions as well. To learn more about edition wise supported features click here.

Launch Microsoft Visual Studio 2010 IDE (Integrated Development Environment) by going to START -> Programs -> Microsoft Visual Studio 2010 and then go to File -> New and you will see a screen like this. You can choose here to create a blank project and add/import database schema objects later on after project creation or choose to initiate a wizard which will walk you through importing database objects as part of project creation.
Once a project is created, if required, you can right click on the project and import scripts from one or more files into the project.

In the New Project dialog box, select SQL Server under Database node in the left side tree view and the select SQL Server 2008 Wizard (or SQL Server 2005 Wizard depending on your database version). Specify the name of the project, location and solution name for this database project in their respective text boxes as shown above and click on OK button which will bring up a New Database Project Wizard welcome screen as shown below.

Simply click on the Next button to move on to the next screen as shown below:

In the above screen you can specify some of the properties of the project you are creating, for example you can specify whether you want to a create database project for user database or SQL Server project for master database (choose database project as we are creating a project for user database development), how do you want you script files to be created, it should be created by schema name or by the type of database objects. Next you can specify to include schema name in the script files being created. Click on Next button to move on to the next screen which is shown below:

In the above screen you can specify different user database level options (for example ANSI paddings, ANSI nulls, Arithmetic abort etc. to be either ON or OFF by default), user database default collation and default schema. Click on Next button to move on to the next screen which is shown below:

In the above screen you specify the source database connection from where you want to import user database schema objects (if a connection already available you can select it from the provided combo-box, if you want to modify an existing connection click on Edit Connection button or click on New Connection if you want to create an altogether new connection), along with that you can also specify the different import options which will be used during importing database objects and number of maximum files (the script for each database object is saved in a single file) in a one directory. Click on Next button to move on to the next screen which is shown below:

In the above screen you specify different options which will be used when you build and deploy your database project to a SQL Server instance. For example you specify the target server connection string and target database name, whether the collation of the project will be used or default collation of the server will be used instead for the user database etc. I will be talking more about deployment options later when I will demonstrate how to deploy your database project in the next section. Click on Finish button to start creating the project, next screen will display the progress as shown below:

If there is any problem while creating database project and importing database schema objects, the above screen will display the error message or else click on Finish button to close the wizard and return to the IDE. Here you can see your created database project along with all the schema objects in it.

You can switch between Solution Explorer view and Schema view using the small icons in the top row of explorer as shown below:

Above you saw objects in Solution explorer view, below is the what you will see when you switch to Schema view where each schema contains all the objects owned by it/organized under it.

You can double click on the any of the object to open it in editor where you can modify it as per your need, for example as shown below for a table. If you want to add a new object, simply right click on the folder and click on Add (in Solution Explorer right click on the folder, click on the type of object you want to create or simply click on New Item to specify the type of object in another dialog box).

You can notice that the above script only contains table creation part (for [HumanResources].[Department] table) and does not have script to create primary key as shown in Schema View on the right side for the same table. The reason is, primary key in itself is an object and hence it has been placed in separate file (double click on it to open it) as you can see below:

Let me show you how you can create a new database object. Right click on Project and click on –> New -> New Item, you will see a screen like this.

In the above screen I am creating a view and selected the View template out of all the available templates and provided its name in the Name textbox. This will bring up an editor window as shown below where you can write code for your view.

In the above screen you can see the Intellisence feature in action. Good part about Intellisense feature is, it works both in project mode (offline representation of the database) when not connected to the server and in connected mode when connected to the server.

Microsoft visual Studio 2010 also includes T-SQL editor in the IDE itself as you can see below, so you don’t need to go to SSMS (SQL Server Management Studio) to connect to server and work, simply connect to the server from IDE itself and you are ready to browse through databases and database objects.

You can open up a new query window using “New Query” and write and execute your query as shown below:

Deploying your projects…

Before deployment you need to configure different deployment properties and build the project. Right click on the project node in Solution Explorer (or press ALT+Enter) and select Properties to configure these properties. Click on Deploy page on the left side and you will see a screen like this:

In the above screen you can specify different properties, for example whether you want just to create a deployment script or want to create a deployment script and also deploy it, what would be name of the deployment script file which will be created, target server connection string and the target database name in the respective textbox.

Once you are done with these configurations, you can build your project by using Build option in Menu bar (or by simply hitting F6 key) or simply build and deploy by using Deploy option in Menu bar as shown below:

Once deployed you can connect to the server and verify it as shown below:

Conclusion

In this article, I showed you how you can use Microsoft Visual Studio 2010 ultimate edition to create offline representation of user database by creating a database project in it. Then I showed you how can make changes to this offline representation and deploy these changes to the SQL Server instance.