Integrating Database Development in Visual Studio

venugopalm

5.00/5 (3 votes)

Sep 28, 2010

CPOL

5 min read

57926

868

Integrating database development into Visual Studio to collaborate application development team and the database designers

Download source - 186.24 KB

Introduction

Last week, I started to prepare part-II for Enterprise Application Architecture: Designing Applications and Services in .NET article. As I explained in part-I, part-II is related to database and library design with demo application. Though I explained database design and management in a traditional way, it has some drawback. For instance, user wants to design demo sample database manually or needs to restore the given backup to run the demo application. This is really a time killing task. Why I need to eat my readers' valuable time though we have a simple solution for this headache. So I decided to apologize to readers and integrated my sample database along with my sample app itself. It was a good experience and I decided to share my knowledge with my folks.

What is Integrating Database in Visual Studio?

It means you can manage your database such as creating tables, primary key, foreign key and almost all the SQL features from Visual Studio. By tightly integrating database development into Visual Studio, the application development team and the database designers are responsible for building the database have a common platform enabling them to easily collaborate. It’s really great, right.

This will be very useful when developer makes a change in one tier that affects another, for example adding a column in the database, he or she can make the respective changes to the middle tier without changing development tools. He can then deploy one project or the entire solution. He may then execute the unit tests implemented by her teammates to verify that the other tiers still function as expected.

Customer Order Management Database Design through Visual Studio

Let’s start how to design and manage my database via Visual Studio. I will explain this step by step with complete snapshots.

To create the new database project, open Visual Studio 2010, Select DatabaseàSQL Server from Installed Templates pane (Left pane) in New Project dialog window.

(Figure 1- Database Project Template)

Note that I have selected SQL Server 2005 Database Project and project name given as Customer Database. Now Visual Studio creates the entire schemas for interacting with SQL Server.

To open the schema view, click the Schema View Button in the solution explorer toolbox. You could see now that Visual Studio created all the required things for database management.

(Figure 2 - Schema View)

Let’s start connecting Visual Studio database schema with SQL server. To set the connection string, right click on Customer Database project and open project property window. Find the Target Connection field and click Edit button which belongs to this field to bring the Connection properties window.

(Figure 3 – Connection Settings)

In the Connection properties window, set your Server name, database name and click ok button. That’s it; we configured our project with database. Now we can go ahead with table and constraints script creation.

To create the tables, right click on Schemes --> dbo --> Tables and select Add-->Tables from the sub menu.

(Figure 4 - Schema View)

Now select Table from Add New Item dialog box and give name for the table as Customer.

(Figure 5 -Add New Table)

Click ok and see the script window. By default it will have one table with two fields(script). Now, change the script based on your requirements. Here is the script for Customer table for our sample.

 TABLE [dbo].[Customer] 
( 
CustomerID int NOT NULL, 
FirstName varchar(20) NULL, 
LastName varchar(20) Null, 
Photo image null, 
Address1 varchar(40) null, 
Address2 varchar(40) null, 
City varchar(20) null, 
Country varchar(20) null, 
Phone varchar(15) null, 
Email varchar(30) null 
)

Similarly create all the required tables. Here is the script for other tables that we are going to create for our demo sample.

Order Table

 TABLE [dbo].[Order] 
( 
OrderID int NOT NULL, 
CustomerID int Not NULL, 
OrderDetailsID int Not NULL, 
Quantity int Not NULL, 
OrderDate datetime NULL, 
RequiredDate datetime NULL, 
ShippedDate datetime NULL 
)

OrderDetail Table

 TABLE [dbo].[OrderDetail] 
( 
OrderDetailsID int NOT NULL, 
OrderID int NULL, 
ProductID int null, 
Quantity int null, 
UnitPrice decimal NULL, 
Discounts int NULL, 
Size varchar(15) NULL, 
Color varchar(20) NULL, 
RequiredDate datetime NULL, 
OrderDate datetime NULL, 
ShippedDate datetime NULL, 
Total decimal NULL 
)

Product Table

 TABLE [dbo].[Product] 
( 
ProductID int NOT NULL, 
UntiPrice int NULL, 
ProductName varchar(20) Not NULL, 
ProductDescription varchar(200) NULL, 
AvialableColors varchar(15) NULL, 
Size varchar(10) NULL, 
Color varchar(20) NULL, 
Discount int NULL, 
Picture image NULL, 
Ranking varchar(20) NULL 
)

How are we going to create foreign key and primary key scripts for our tables? It’s very simple. Just do the same as table script creation. Right click on table and select Primary Key or Foreign Key item and write the script for that. Here are the scripts for primary and foreign key for our tables.

Primary key script for customer Table

ALTER TABLE [dbo].[Customer] 
ADD CONSTRAINT [CustomerIDPrimaryKey] 
PRIMARY KEY (CustomerID)

Primary key script for Product Table

ALTER TABLE [dbo].[Product] 
ADD CONSTRAINT [ProductIDPrimaryKey] 
PRIMARY KEY (ProductID)

Primary key script for Order Table

ALTER TABLE [dbo].[Order] 
ADD CONSTRAINT [OrderIDPrimaryKey] 
PRIMARY KEY (OrderID)

Primary key script for OrderDetail Table

ALTER TABLE [dbo].[OrderDetail] 
ADD CONSTRAINT [OrderDetailsIDPrimaryKey] 
PRIMARY KEY (OrderDetailsID)

Foreign key script for Order Table

ALTER TABLE [dbo].[Order] 
ADD CONSTRAINT [FK_CustomerID] 
FOREIGN KEY (CustomerID) 
REFERENCES Customer (CustomerID)

Foreign key scripts for OrderDetail Table

ALTER TABLE [dbo].[OrderDetail] 
ADD CONSTRAINT [FK_OrderID] 
FOREIGN KEY (OrderID) 
REFERENCES [Order] (OrderID) 
ALTER TABLE [dbo].[OrderDetail] 
ADD CONSTRAINT [FK_ProductID] 
FOREIGN KEY (ProductID) 
REFERENCES Product (ProductID)

See the final project with all the tables and constraints in the image given below.

(Figure 6 - Tables and Constraints)

That’s all. Deploy the project now. To deploy the database project, right click on Customer Database project and select Deploy. Visual Studio will execute all the scripts for creating tables and constraints in SQL Server 2005. Once deploy is successful, open the SQL Server Management Studio Express and make sure that database has been created properly with all the constraints. Here is the snap of my SQL Server 2005.

(Figure 7 - Tables in SQL Server)

Wow, what a feature. Really VS IDE guys are great. I am really glad to work in .NET technologiesJ. Now we are done with integrating our database in Visual Studio. This is what I have done in my Enterprise Application Architecture: Designing Applications and Services in .NET. article and uploaded a sample with database also. Note that, to run the demo samples at your end, you have to change the server name before you deploy or run the application. I mentioned about how to change the server name in Figure 3 clearly. Also, you can download a sample database application only from this article.

Although at its core, this article is a very simple idea, I am really pleased with the results, and do think it's really easy to use in your own project. I really appreciate some votes and some comments if you feel this is useful for you.

Enjoy!!!

History

28^th September, 2010: Initial post