How to Create Database Using Table Designer in Visual Studio 2012

Introduction

 

In today's article, you will learn how to create a database using Table Designer in Visual Studio 2012.

 

In Visual Studio 2012 a new feature is introduced named Table Designer that is used in Data Application Development. Table Designer can be used to define a table graphically. In Table Designer, the Script Pane is used to update the Table Script definition. The Script Pane can also be used as an alternative to update or modify the Script directly. But there is one important thing to notice that Table Designer only appears for the data in SQL Server 2012. You can either create a New Database or you can edit the table or their related programming object using Table Designer and then switch to the Script Pane that will allow you to directly edit the script that defines this table.

 

Step 1

 

Start your Visual Studio 2012 and create a new project using the following:

• Now go to the Server Explorer and right-click to add a new database.
• Name this database as "Trade" or anything else you like.

Step 2

 

Now we will use Table Designer to add a table using the following:

1. First of all, expand the Trade Node, this will show you the Tables Node, right-click on Tables, and select "Add New Table".
2. Now a new window will be opened. This Window is the Table Designer that will consist of a Columns Grid, Script Pane, and Context Pane.
3. In the Script Pane rename the new table to "Suppliers".

 

Step 3

1. Click the empty row in the Columns Grid to add a new column to the table. Enter "CompanyName" for the "Name" field, "nvarchar (128)" for the "Data Type" and uncheck the "Allow Nulls" field. As you tab away from the fields, notice that the Script Pane is updated immediately.
2. Add another new column. Enter "Address" for the "Name" field, "nvarchar (MAX)" for "Data Type" and uncheck the "Allow Nulls" field.

Step 4

 

Now add one more table named "Customer". This time, add the following columns to the Customer table using the Columns Grid. And remember to change the script so that the table's name is [dbo].[Customer]

 

 

Step 5

 

Create one more table named "Products". Add the following columns to the Products table using the Columns Grid. And remember to change the script so that the table's name is [dbo].[Products]

 

 

Step 6

 

Now we will create a new Check Constraint using the Table Designer. The Table Designer's Context Pane gives you the Logical View of the Table Definition like Keys, Constraints, Triggers etc., and also enables you to select an object to highlight its relationship to individual columns.

1. For the Product table right-click the "Check Constraints" node in the Context Pane of the Table Designer and select "Add New Check Constaint".
2. Notice that the node count automatically increments by 1.
3. Click the Script Pane, and replace the default definition of the constraint with the following:

 

This Constraint will limit the value of ShelfLife to be under 5.

 

Step 7

 

Now we will create a Foreign Key reference using the Table Designer.

1. For the Products table, right-click the "Foreign Keys" node in the Context Pane, and select "Add New Foreign Key" .
2. Notice that the node count automatically increments by 1.
3. Click the Script Pane, and replace the default definition of the foreign key reference with the following.