Database Development with Visual Studio 2010: Working with Database Projects

Posted by: Gouri Sohoni , on 7/30/2010, in Category Visual Studio, VSTS & TFS
Views: 140004
Abstract: In this article, we will see how to create database project in Visual Studio 2010, add database objects schema to it and how to build and later deploy database to the server.
In last two articles Database Development with Visual Studio 2010: Overview of SQL Server Data-Tier Application (DACPAC)  and Database Development with Visual Studio 2010: Database Projects and Database Server Projects we discussed how to work with DAC applications and an overview of Database Projects/ Server Projects with Visual Studio 2010. In this article, we will see how to create database project, add database objects schema to it and how to build and later deploy database to the server. We will create a database unit test for stored procedure and finally add the project to source control.
With Visual Studio 2010 you can manage the complete life cycle of the databases (since it is a part of application development), by using various project types like Database/ Server projects or DAC applications. Various team members can work on the schema objects at the same time without deploying them on production server. Visual Studio 2010 helps to do the changes to the database, provides permissions for SQL server.
In order to avail all the features provided for working with a Database in Visual Studio, you need to install Visual Studio 2010 Premium or Ultimate. Some of the limitations with Visual Studio Professional 2010 are:
1.    You can run database unit tests but cannot create new tests or modify.
2.    You can generate data only as part of existing database unit tests.
3.    Schema and data comparison are not available.
4.    Database refactoring and Code Analysis cannot be used.
In this article, we will discuss how to create an empty database project, add table, index, stored procedure to it, provide build and deployment features, deploy it, add to source control and also how to write database unit test.
Step 1 : Start Microsoft Visual Studio 2010 and select New Project from File menu. Expand the node for the Database (in the Installed Templates) and select the project type of ‘SQL Server 2008 Database Project’
Step 2 : Enter name DemoDB, specify location, clear check box for ‘Add to Source Control’.
Capture
Step 3 : Let us create a schema and then add database objects to the new schema. Select Database schema view from View menu and add a schema named EDU. The default schema dbo is visible as shown below

Capture1
Step 4 : Create a table named `Trainers’ in this newly created schema, by right clicking on schema and selecting Add Table option. The table will have following structure
Capture2
 
Step 5 : Let us add a primary key to the table. From Schema View, right click on EDU.Trainers, point to Add and select Primary Key, select the template of Primary key and enter name PK_TrainerID
Capture5

The file name will be Trainers.PK_TrainerID.pkey.sql
Step 6 : In order to add an index to this table use following script to .sql file

CREATE INDEX [IX_TrainerName]
    ON [EDU].[Trainer]
   ([TrainerName])
Step 7 : To add Trainings Table, add a new item template for the table in the same schema EDU and provide the structure as follows
Capture4
Step 8: Add primary key to Trainings table with following script
Capture3
Step 9 : Add a foreign key constraint by using following script
Capture6
Step 10 : Let us add a stored procedure inserting a new trainer. You can see you get offline intellisense for it as shown below:
Capture7
Step 11 : Add another stored procedure for adding new trainings
Capture8
Step 12 : Now that various database objects are added, we will build and deploy the application. Right click on the project name in Solution Explorer and select Properties. Select Deploy tab and change Deployment action to `Create a deployment script (.sql) and deploy to database’. Provide the target connection by clicking on Edit.
Capture9
Step 13 : After successful deployment you can view all the objects deployed to the database named `DemoDB'
Step 14 : Let us write database unit tests now. We will have to provide the connection from which test data can be taken. I have used 2 tables namely TestTrainers and TestTrainings for this purpose. Let us create test data for these tables by using test generation plan. I created these 2 new tables by using SQL Server 2008 R2 SQL Server Management Studio and kept the same structure and in the same schema.
Step 15 : Now I have two extra tables in database but they are not in the current database project. I used Schema Compare – New Schema Comparison, source as database and target as the current project name. The schema comparison pointed out the 2 new tables and provided me way to add it to the database by Write updates. Now you can use data generation plan to generate data for test tables. For data generation you can apply some conditions like TrainerName must be with alphabets, Days can be between 1 and 5 etc. To apply these rules select the field and use properties.
Capture10
Step 16 : Let us create database unit tests for the 2 stored procedures we have created. From Schema View, right click on stored procedure and click on `Create unit test’. You can decide the name of the project for test as well the language for it as can be seen
Capture11
Step 17 : You can use the data generation plan when creating database unit test.
Capture12
Step 18 : You can now do modifications to the database unit test code in order to take the data from test tables and insert into the Trainers and Trainings tables
The stored procedure for inserting data into Trainers table looks as follows:
-- database unit test for EDU.uspNewTrainer
DECLARE @RC AS INT, @TrainerName AS NVARCHAR (35);
SELECT @RC = 0,
       @TrainerName = NULL;
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
DECLARE @row int
-- Loop through the rows of a table TestTrainers
WHILE (@I <= 50)
BEGIN
        -- Declare variables to hold the data which we get after looping each record
        DECLARE @iUserName VARCHAR(35)
        -- Get the data from table and set to variables
        SELECT @iUserName = TrainerName FROM [EDU].TestTrainers
            WHERE @I=[EDU].[TestTrainers].[TrainerID]
        -- Increment the iterator
        SET @I = @I + 1
        EXECUTE @RC = [EDU].[uspNewTrainer]@iUserName;
END
SELECT @I

Step 19 : After the execution of the test, we can see 50 records added to the table Trainers. Similarly you can add database unit test for the other table and do the modifications to the code provided.
Step 20 : Now the last step is to add this solution to source control. From Solution Explorer, right click on the project name and select `Add solution to Source Control’. Specify the Team Project under which you need to add this database project.
The different versions of the database projects can be kept in source control and deploy any one of them when required.
Conclusion

In this article, we discussed various components of Database Projects when we started with an empty database project. We added different database objects like tables, indexes, keys, stored procedures. We deployed the database to SQL Server 2008 R2. We saw how schema comparison can be used to get the changes in the current schema and apply it to the database project. We discussed how database unit test can be added and also execute it. Finally we added the database project to the source control.
Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Gouri Sohoni is a Microsoft MVP, Trainer and consultant for over two decades. She is a Microsoft Certified Trainer (MCT), MCITP and has conducted several corporate trainings on Microsoft technologies that include Visual Studio 2010 (ALM), SQL Server 2008 BI, SQL Server 2008 developer track, SQL Server 2005 BI, SQL Server 2005 developer track etc.


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by wasim on Friday, July 30, 2010 5:25 AM
I could not create DataBase project in vs.net 2010 premium edition. I have error message says Microsoft.SqlServer.Management.SqlParser.dll is not found.  I have installed sql server 2008 as well.
Comment posted by Gouri Sohoni on Monday, August 2, 2010 1:07 AM
Hi Wasim,
Please follow this link http://msdn.microsoft.com/en-us/library/aa833292.aspx regarding the prerequisites for Database Projects

Gouri
Comment posted by James on Friday, April 8, 2011 9:42 PM
Hi,

How do you create a new schema and have all the folders underneath it show up?

Thanks,

James
Comment posted by gopal kharwat on Wednesday, August 10, 2011 5:44 AM
v good...
Comment posted by Fazal Vahora on Friday, August 19, 2011 4:38 AM
Hi Gouri,
I have created database project with using VS 2010. i have created two database test1 and test2 with same schema. when i change test1 schema and deploy project. all are running successfull. when i checked test2 database, test 1 schema updates are not populated to test2. what is problem? Please help to me, to resolve this issue.

Thanks in advance,
Fazal
Comment posted by Fazal Vahora on Friday, August 19, 2011 6:37 AM
Hi Gouri,
I have created database project with using VS 2010. i have created two database test1 and test2 with same schema. when i change test1 schema and deploy project. all are running successfull. when i checked test2 database, test 1 schema updates are not populated to test2. what is problem? Please help to me, to resolve this issue.

Thanks in advance,
Fazal
Comment posted by Hans Jákup Danielsen on Wednesday, November 16, 2011 3:32 AM
could you please review this tutorial, there way to many typos
Comment posted by Justice on Monday, February 27, 2012 7:09 AM
Please, Help Me. how can you use database you created with visual studio on different machine. Please help me. Thank you
Comment posted by Justice on Monday, February 27, 2012 7:10 AM
Please, how can you use database you created with visual studio on different machine. Please help me. Thank you
Comment posted by Guy Harwood on Sunday, March 18, 2012 3:15 AM
This tutorial is terribly rushed with far too much copy and paste and very little how and why.

You do not even explain how the db project manages the schema changes
Comment posted by arun on Wednesday, March 21, 2012 1:44 PM
Hi,
I am getting the following error when I complile
             "Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   

Thanks,
Arun
Comment posted by arun on Thursday, March 22, 2012 8:36 AM
Hi,
I am getting the following error when I complile
             "Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   

Thanks,
Arun
Comment posted by Amila jayasanka on Tuesday, April 3, 2012 11:45 PM
Hi
I am using VB.net 2010.but when i used database, I can't creat setup file.
please help me
Thank you
Amila Jayasanka
Comment posted by Gouri Sohoni on Friday, April 13, 2012 12:07 AM
Hi Amila,
Can you elaborate on your problem?
Comment posted by MsKhan on Friday, July 26, 2013 11:04 AM
Thanks Sohoni

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel