Database Development with Visual Studio 2010

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’.

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

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

 

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

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

Step 8: Add primary key to Trainings table with following script

Step 9 : Add a foreign key constraint by using following script

Step 10 : Let us add a stored procedure inserting a new trainer. You can see you get offline intellisense for it as shown below:

Step 11 : Add another stored procedure for adding new trainings

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.

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.

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

Step 17 : You can use the data generation plan when creating database unit test.

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.

Source: http://www.dotnetcurry.com/ShowArticle.aspx?ID=548