Integrating Database Development in Visual Studio

Integrating database development into visual studio to collaborate application development team and the database designers.

• Download DatabaseIntegrationInVS - 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 traditional way, it has some draw back. 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 time killing task. Why I need to eat my readers valuable time though we have simple solution for this headache. So I decided to apology from readers and integrated my sample database along with my sample app itself. It was good experience and decided to share my knowledge with my folks.

What is Integrating Database in Visual Studio?

Yes. 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 the 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 the 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.

CREATE 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

CREATE 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

)

OrderDetial Table

CREATE 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

CREATE 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 given below image.

(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 gets 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 technologies J . Now we have 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 sample with database also. Note that, to run the demo samples in 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 Figure3 clearly. And also you can downlload sample database application only from this article.

Although as 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 !!!.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)