Database Projects in Visual Studio (by Krishna)

Summary:

Database projects are simple file based projects that allow you to store and execute database scripts and queries. Within a Database Project you can edit, test and deploy your databases using scripts. You can either add your existing scripts to the project, or create new ones. Multiple scripts can be executed from these projects against a selected database.

As with any project in Visual Studio, your files are tightly and easily integrated into source control. All scripts, query files, database references and the solution by itself can be checked into source control from within Visual Studio.

A Database Project can be considered as a SQL language project, with a few added capabilities such as source control integration, UI designers, file templates, debugging, language support (coloring the SQL keywords, defining a SQL block/query) etc. Like other language projects, the Database Project is a directory/web-based project that contains files and folders. These files in a Database Project – SQL scripts and queries can be versioned.

 

FAQs to get started:

List some scenarios where DB projects feature can be leveraged?

App building: If an application involves developing front end and backend modules, one can create a solution and add a project that’s used in developing front end modules using any language of user’s choice (such as VC#, VB, etc) and add a DB project that would store related database objects to be created on the backend. All these can be bundled into a single solution and deployed.

Team development: Various team members working on developing various database objects for a database can add their developed db objects into a single database project which can be versioned using source control.

 

In above listed scenarios, how using a DB projects in VS can be different/better than just saving the files onto a common share.

Note that the Database Project is for managing SQL scripts in enterprise development projects. Databases per se are exposed in Data View (Server Explorer) where developers can code against them or design them using the Visual Database Tools. Scripts in the Database Project can be applied to / generated from databases in Data View. New scripts can be developed, executed (the language support provided in VS, will color code the SQL Syntax in the SQL Editors, invoke Query designer if you prefer to add SQL code using designer for choosing table names, columns name with appropriate joins etc.)

 

How do I create a new Database project?

In VS, under new projects window (File -> New - > Project), expand “Other projects” node and then choose “Database projects”. Follow the steps of entering name, location, add to existing solution or create new etc., details. Once you press OK, “add Database reference” dialog appears, and user either can choose an existing connection listed in there or click on “add new reference” button which will open a connection manager dialog to create a new connection. Using connection manager, user can create a connection to a SQL Server/Oracle/Access Database using various available providers on the box. I have pasted “New Project” dialog screen shot from Visual studio below for convenience.

 

What all I can store in a DB project and how do I add a new item?

Like all VS projects, from solution explorer, you can use Project / Add Item to add new files to a Database Project. Database Projects support adding:

- Table script

- View script

- Stored procedure script

- Trigger script

- Database query, and

- Generic SQL script

Once you click one of these template icons in the “add new item” dialog, a corresponding template will be add to SQL editor in VS. Note that Project / Add Item don’t add anything to a database (backend), just adds a script file to the project.

What kind of connections I can create from VS and use in database projects

One can create a new database (New Database Wizard) which will add a connection for this newly created database to Data View (Server explorer), which can be set as a the default connection for the Database Project; or

Pick an existing database from a list of connections in Data View (or create a new connection to the existing database) and set it as the default connection for the Database Project.

Were DB projects supported in VS 6.0 too?

In VS 6.0, DB projects were supported by Visual Interdev. Visual Studio .Net and Visual Studio 2005 (coming later this year), DB project template is included in New projects window, under “Other projects” node. Between VS 6 to Visual Studio .Net, more support for executing SQL scripts on databases and generating create scripts for databases has been included. Also the contrasting features in Visual Studio .Net against VS 6 are that the Visual Studio .Net Database Project is similar to the V6 Database Project in Visual Interdev, except that it does not include visible connections, and it supports executing SQL scripts on a connection (Run / Run On). Database Projects have a default connection property that can identify a connection from Data View.

Please let me know if this post was useful or not. Also any feedback on any other relevant information you may want to hear in regard to Database Project, please let us know and I will try to post them.

Thanks,

Krishna