Compartilhar via

Database Projects in Visual Studio (by Krishna)

  • Artigo

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

Comments

  • Anonymous
    March 08, 2005
    One thing that bugs me about the Database Project (using Whidbey) is that you can't add entire folders to the project in one operation.

    I had been using the November Whidbey CTP, which didn't support Database Projects, so i was managing the scripts independently in source control.

    Then I recently got the Feb CTP setup, and i wanted to start using Database Projects again.

    I have folders for my SQL scripts that i created with CodeSmith. One folder per table, basically.

    When moving my SQL scripts into my Whidbey solution, i created a new db project, but had to create the folders by hand in the IDE, and then do 'add existing item' for each set of files in each folder. Would have been sooo much easier to multiselect the folders to add, and automatically add the folders and scripts to the db project.

    Probably too late for Whidbey RTM, but this would be a really helpful fix!
  • Anonymous
    March 08, 2005
    I've been using database projects for awhile: see http://weblogs.asp.net/taganov/ for details. I think they're widely under-appreciated, and I think they could be a much more powerful part of the database development process.
  • Anonymous
    March 27, 2005
    One welcome addition to database projects would be integration with Setup & Deployment projects. It would be nice if DB project output translates to a single installation/patch step.
  • Anonymous
    March 30, 2005
    In a VS 2005 DB Project referencing a SQL 2005 database. It sure would be nice to be able to export data to a .dat file and then create a command file that would use a table creation script and the exported .dat file to import it on another database.<br><br>These features were available in VS 2003 DB Projects.<br><br>Does anyone know if they will find there way back into VS 2005 DB Projects?
  • Anonymous
    March 12, 2008
    PingBack from http://bordercrossingstatsblog.info/vs-data-teams-weblog-database-projects-in-visual-studio-by-krishna/