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-IIis related to database and library design with demo application. ThoughI explained database design and management in traditional way, it hassome draw back. For instance, user wants to design demo sample databasemanually or needs to restore the given backup to run the demoapplication. This is really time killing task. Why I need to eat myreaders valuable time though we have simple solution for this headache.So I decided to apology from readers and integrated my sample databasealong with my sample app itself. It was good experience and decided toshare my knowledge with my folks.

What is Integrating Database in Visual Studio?

Yes.It means, you can manage your database such as creating tables, primarykey, foreign key and almost all the SQL features from visual studio. Bytightly integrating database development into Visual Studio theapplication development team and the database designers are responsiblefor building the database have a common platform enabling them toeasily collaborate. It’s really great right.

Thiswill be very useful when developer makes a change in one tier thataffects another, for example adding a column in the database, he or shecan make the respective changes to the middle tier without changingdevelopment tools. He can then deploy one project or the entiresolution. He may then execute the unit tests implemented by herteammates 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 ViewButton in the solution explorer toolbox. You could see now thatthe visual studio created all the required things for databasemanagement.

(Figure 2 - Schema View)

Let’s start connecting visual studio database schema with SQL server. To set the connection string, right click on Customer Databaseproject and open project property window. Find the Target Connectionfield 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)

Clickok and see the script window. By default it will have one tablewith two fields(script). Now, change the script based on yourrequirements. 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

)

Howare we going to create foreign key and primary key scripts for ourtables? 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 projectand select Deploy. Visual studio will execute all the scripts forcreating tables and constraints in SQL Server 2005. Once deploy getssuccessful, open the SQL Server Management Studio Express and make surethat database has been created properly with all the constraints. Hereis 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 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. Articleand uploaded sample with database also. Note that, to run the demosamples in your end, you have to change the server name before youdeploy or run the application. I mentioned about how to change theserver name in Figure3 clearly. And also you can downlload sampledatabase application only from this article.

Although as its core, thisarticle 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 reallyappreciate some votes and some comments if you feel this is useful foryou.

Enjoy !!!.

License

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