Experiencing SQL Server 2008 Database Projects in Visual Studio 2010

Introduction

I am surprised; I will say it is a hell of a new feature called SQL Server 2008 Database Project that is provided in Visual Studio 2010. All other older versions already have this project template, but VS 2010 has an advanced one. I also tried and created a project with VS 2008, but I have no idea about prior versions (VS 2005 and VS 2003). The templates are best suited for Database developers. But, those who have a simple knowledge of VS and DB can easily create, configure and deploy the project.

Last week our team had a discussion about a new project using SQL Server 2008 as the backend. But the huge task that came up during discussion was that the DB team will not provide the DB access to all the developers. Which means not even have access through SQL Server Management Studio; only one member has the rights to access the DB, and that only during deployment cycle? We were all confused and put this point in a priority list. We started collecting various approaches. One of my old friends told me about this new advanced feature provided with VS 2010. It looks interesting and we started studying it. The templates were a little confusing initially but later it became to seem good and simple. This template creates a database project, which means we can create databases, tables, stored procedures etc through Visual Studio.

It looks interesting. I thought of sharing this template here with you all.

Features of a SQL Server 2008 Database Project

• We will get a feel of working with SQL Server Management Studio.
• We can create Tables, Views, and Procedures... etc...
• We can execute SQL scripts.
• We have new in-built T-SQL Editor (no need to use SQL Server Management Studio)
• We have T-SQL Intellisence support while writing or modifying the SQL script.
• We can deploy the project to multiple environments (QA, Production environment etc.)...
• We can have an offline representation of our user databases or master database.
• We can have multiple versions controlling using Visual Studio Team Foundation and more...

Prerequisite

• Visual Studio 2010 Ultimate and Premium edition
• SQL Server 2008

Getting started

Ok, let's start and create a project and deploy it over a database server.

Launch Visual Studio; start creating a new project by selecting a template for a SQL Server 2008 Database Project (.NET Framework 4).



Now go to the Project menu and click on Properties (Database1 Properties) and select the Deploy tab. There you can see an important entry named Target Connection. This is where the database project will create the database during deployment. If no target entry is provided, then the default is master database.



Update the Target Connection by clicking the "Edit" button on the right side. This will open the Connection Properties window. Select Server Name, enter Windows/SQL Server Authentication, and let the database name be the default (Database1) and click OK. Now the Target Connection and Target Database name have been updated. If needed other deployment configuration also can be done.



Now go to Solution Explorer, we can see two new icons on top right end corner. Click on Database Schema View and select Schemas to see the database objects.



Let's start creating a Table, insert some values and write a procedure to retrieve it.

• Right click on the Tables node and click on Add Table.
  
  
  
• Select template "Table" from the list and enter a table name (UserMaster) and click OK.
• A query editor widow will be opened; we can modify the script and create the table and also insert some rows.
• We can validate the SQL Syntax, check Estimated Execution Plan also.
• Toolbar with new T-SQL IDE the same as SQL Server Management Studio.
• Save the following script. Do not run the project or execute the script.
  
  
  

Now we will create a stored procedure to fetch Username depending on SNO.

• Right-click on the Stored Procedure node and add a Procedure (procFetchUser) from the template list. Create a Stored Procedure and click OK.
  
  
  
• Save the following script. Do not run the project or execute the script.

Project Deployment

Remember to re-check or re-configure the deployment settings from the Deploy tab of Project Properties. Rebuild the project and click on Deploy project from Build menu and check the following Output message. The message says: The deployment script was generated, but was not deployed. This is due to the deployment settings. We need to update the settings to deploy the project also.



Go back to the Project menu and select Project properties, under the Deploy tab change the Deploy Action to "Create a deployment script (.SQL) and deploy to the database" and save the project.



Now again Deploy the project; we will get output as below.



Done; the project got deployed onto the database server. We can confirm this by viewing the Server Explorer window and also in SQL Server Management Studio.



So we learned something new today, try at your end.

Conclusion

Through this article I tried to give an introduction of Visual Studio Database project. Hope you all liked it. Please post your comments and rate it.

Sample project attached for reference. Post all doubts and questions to Forum section.

Thank You!