ASP.NET 4.0 and the Entity Framework 4 - Part 1 - Create a Database using Model-First Development
page 2 of 3
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 64502/ 137

Creating a Database using Model-First Development

Step 1: Create a new solution

1.    Launch Visual Studio 2010 Beta 2.

2.    Click the New Project link on the Start Page.  The New Project dialog box should appear.

3.    Click on the Visual C# node from the tree view of installed templates.

4.    Select Empty ASP.NET Web Application from the list of templates.

5.    Enter OrderSystem for the project name and click the OK button.

Visual Studio will create a solution file in the folder you specified and also create a sub folder that contains the web site's project file and config files.

Step 2: Create an ADO.NET Entity Data Model

The next step is to create the ADO.NET Entity Data Model.  For this application we'll model the concept of users and addresses.  A user can have more that one address so we'll build an entity data model that models this relationship.

1.    Right click on the OrderSystem project in the Solution Explorer and select Add à New Item…

2.    Click the Data node under the Visual C# node in the Installed Templates tree.

3.    Select the ADO.NET Entity Data Model template.

4.    Change the name to OrderDB.edmx and click the Add button.

5.    Visual Studio will display the Entity Data Model Wizard.  This is where you can decide to build the model from an existing database or create the model first and then build the database. 

6.    Choose Empty model from the wizard and click the Finish button.

The OrdersDB.edmx file will be added to your project and the file will be displayed in the Entity Framework Designer.

Step 3: Create the Entities and Associations

The next step is to model the user and user's addresses relationship. Let's first create the user entity.

1.    Right click on the Entity Data Model Designer and select AddàEntity… from the pop-up menu.

2.    The Add Entity dialog box should appear.  Enter UserAccount for the Entity name.  Notice that as you type the Entity name the Entity Set name pluralizes the Entity name.  Think of the Entity name as the class that represents a record and the Entity set as the class that represents the table of those records.

3.    By default the Entity Framework will create a Primary Key called Id.  If you wish to not create a primary key you can uncheck the Create key property checkbox from the dialog box.  For this example we want a primary key so leave the box checked.

4.    Click the OK button. The UserAccount entity will be added to the entity diagram.

5.    The next step is to add properties to this entity. Properties will map to fields in a table. We'll first add the First and Last name properties.

6.    Right click on the UserAccount entity and select AddàScalar property from the pop-up menu.

7.    Change the property name to FirstName.

8.    In the properties window change the MaxLength to 50. Scalar properties are strings by default.

9.    Add another property called LastName the same way and set its MaxLength to 50.

The next step is to add and insert date and update date properties.  I like to have the inserted and updated dates on every entity just for the sake of trouble shooting.  These are two properties that will be added to every entity so it is the perfect candidate for a Complex Type.  A Complex Type allows you to define a set of properties and then associate them with multiple entities.

10. In the Model Browser window, left click on the Complex Types nodes.  Sometimes you have to click more than once for the node to be selected.  I'm using Beta 2 so hopefully this will be fixed when it goes live.  Once the node is selected right click and select Create Complex Type from the pop-up menu.

11. Change the name of the complex type to AuditFields.

12. Right click on the AuditFields complex type in the Model Browser and select AddàScalar PropertyàDateTime from the pop-up menu.

13. Change the name to InsertDate.

14. Follow the same steps to add the UpdateDate property to the complex type.

15. Now you can add the Complex property to the UserAccount entity.  Right click the UserAccount entity and select AddàComplex Property from the pop-up menu.

16. Change the name to AuditFields.  The type should have already defaulted to AuditFields.

When the database is created from this entity it will contain the two audit fields defined in the complex type.

Now let's add the address entity.  A user can have more than one address so there is a one-to-many relationship between these two entities.

17. Right click the designer and select AddàEntity from the pop-up menu.

18. Change the name to Address and then click the OK button.

19. Add scalar properties to the Address entity for Address1, Address2, City, State, and Zip.  All of these properties should be strings with lengths of 50, 50, 50, 2, and 5 respectively.

20. Change Address2 to be nullable by settting the Nullable property to True in the properties window.  All other properties are required.

21. Now add the Audit Fields to this entity by right clicking the Address entity and selecting AddàComplex Property from the pop-up menu.  Set the name to AuditFields and the type to AuditFields.

The next step is to create an association between the UserAccount and Address entities.

22. Right click on the UserAccount entity and select AddàAssociation from the pop-up menu.

23. The Add Association dialog appears.  It assumes correctly that you want to create a one-to-many relationship between these two entities.  You use the Multiplicity drop down to define the variations of relationships but for this example you can leave the defaults and click the OK button.  Notice that a UserAccountId property was added to the Address table.

Step 4: Generate the Database

Now that the entities are defined we can create the database.  Visual Studio makes this quite simple.  A script is generated with the correct DDL statements to create tables, indexes, and relationships which you can then execute against your database. 

1.    Right click on the Designer and select Generate Database from Model… from the pop-up menu.  The Generate Database Wizard will appear.

2.    Click the New Connection… button.

3.    Enter your server name.  Choose either User Windows Authentication or Use SQL Server Authentication.  Whichever you choose you need a user that has permissions to create a database on the server.  For this example I'll use Windows Authentication.

4.    Enter OrderSystem for the name of the database and click the OK button.

5.    You should get a message stating "The database 'OrderSystem' does not exist or you do not have permissions to see it.  Would you like to attempt to create it?"  Click the Yes button.

6.    The database should be created and you'll be returned to the Generate Database Wizard dialog.  Click the Next button.

7.    The wizard will now generate the DDL statements needed to create this database.

8.    Click the Finish button.

9.    A new file will be added to the project called OrderDB.edmx.sql.  The file contains the DDL statements to create the database.  The text of the file is as follows:

-- --------------------------------------------------
-- Date Created: 01/17/2010 09:39:04
-- Generated from EDMX file: C:\Documents and Settings\VinceVarallo\
-- my documents\visual studio 2010\Projects\OrderSystem\OrderSystem\OrderDB.edmx
-- --------------------------------------------------
 
SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
GO
 
USE [OrderSystem]
GO
IF SCHEMA_ID(N'dbo'IS NULL EXECUTE(N'CREATE SCHEMA [dbo]')
GO
 
-- --------------------------------------------------
-- Dropping existing FK constraints
-- --------------------------------------------------
 
 
-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------
 
 
-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------
 
-- Creating table 'UserAccounts'
CREATE TABLE [dbo].[UserAccounts] (
    [Id] int  NOT NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [AuditFields_InsertDate] datetime  NOT NULL,
    [AuditFields_UpdateDate] datetime  NOT NULL
);
GO
-- Creating table 'Addresses'
CREATE TABLE [dbo].[Addresses] (
    [Id] int  NOT NULL,
    [Address1] nvarchar(50)  NOT NULL,
    [Address2] nvarchar(50)  NULL,
    [City] nvarchar(50)  NOT NULL,
    [State] nvarchar(2)  NOT NULL,
    [Zip] nvarchar(5)  NOT NULL,
    [AuditFields_InsertDate] datetime  NOT NULL,
    [AuditFields_UpdateDate] datetime  NOT NULL,
    [UserAccountId] int  NOT NULL
);
GO
 
-- --------------------------------------------------
-- Creating all Primary Key Constraints
-- --------------------------------------------------
 
-- Creating primary key on [Id] in table 'UserAccounts'
ALTER TABLE [dbo].[UserAccounts] WITH NOCHECK 
ADD CONSTRAINT [PK_UserAccounts]
    PRIMARY KEY CLUSTERED ([Id] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [Id] in table 'Addresses'
ALTER TABLE [dbo].[Addresses] WITH NOCHECK 
ADD CONSTRAINT [PK_Addresses]
    PRIMARY KEY CLUSTERED ([Id] ASC)
    ON [PRIMARY]
GO
 
-- --------------------------------------------------
-- Creating all Foreign Key Constraints
-- --------------------------------------------------
 
-- Creating foreign key on [UserAccountId] in table 'Addresses'
ALTER TABLE [dbo].[Addresses] WITH NOCHECK 
ADD CONSTRAINT [FK_UserAccountAddress]
    FOREIGN KEY ([UserAccountId])
    REFERENCES [dbo].[UserAccounts]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO
 
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------

It is important to note that the tables weren't added to the database yet.  In order to actually create the tables you need to right click in the OrderDB.edmx.sql file and select Execute SQL from the pop-up menu.  You'll be prompted to log into the server that contains your database.  Once you are logged in the script will execute and the objects will be added to your database.


View Entire Article

User Comments

Title: Kasparov   
Name: Garry Kasparov
Date: 9/27/2012 3:15:40 PM
Comment:
This is an excellent article and tutorial to start with. Great Work !!!

I look forward to seeing the next articles.
Title: wer   
Name: werwer
Date: 9/21/2012 7:23:31 AM
Comment:
werwer
Title: Select execute sql is not getting in pop-up menu   
Name: Silver
Date: 1/27/2012 12:06:55 AM
Comment:
How to execute the OrderDB.edmx.sql file? it can't be found in the pop-up menu once I right click it
Title: Mr   
Name: Vibhor
Date: 12/19/2011 5:56:57 AM
Comment:
Easy and good
Title: Select execute sql is not getting in pop-up menu   
Name: execute sql
Date: 3/31/2011 1:45:03 AM
Comment:
It is important to note that the tables weren't added to the database yet. In order to actually create the tables you need to right click in the OrderDB.edmx.sql file and select Execute SQL from the pop-up menu. You'll be prompted to log into the server that contains your database. Once you are logged in the script will execute and the objects will be added to your database.
Title: Mr   
Name: Varun Maggo
Date: 3/16/2011 4:18:07 AM
Comment:
You Rock Maan!

I need to follow your aricles now :)

varun.maggo@hotmail.com
Title: Nice tutorial   
Name: PongGod
Date: 1/26/2011 1:08:44 AM
Comment:
Thanks, this makes a great starting point for developers wanting to dive into Entity Framework.
Title: EF4 to demonstrate   
Name: EF4 to demonstrate
Date: 11/18/2010 5:10:43 AM
Comment:
EF4 to demonstrate
Title: Great Article   
Name: mailvipuls@yahoo.com
Date: 9/10/2010 7:42:44 AM
Comment:
Could you please tell me when do u publish next article on Entity Framework.
This article is superb.
Regards
Vipul
Title: Good work   
Name: NurulH
Date: 5/23/2010 10:20:46 AM
Comment:
Good work, thanks
Title: Good Start   
Name: bdjm
Date: 4/21/2010 11:02:24 AM
Comment:
This is a good start, but Microsoft & Pluralsight do a much better job. See the hands on lab at:

http://code.msdn.microsoft.com/EntityFxHOLPDC09/Release/ProjectReleases.aspx?ReleaseId=3494
Title: Excellent WalkThrough   
Name: Sreedevi
Date: 3/31/2010 6:52:51 AM
Comment:
This is an excellent article. Got the insight of VS2010.

Thanks
Title: Eagerly Looking For Next Article   
Name: purna
Date: 3/29/2010 10:01:39 AM
Comment:
good
Title: Eagerly Looking For Next Article   
Name: Balu
Date: 3/26/2010 10:17:03 AM
Comment:
Hi
Could you please tell me when do u publish next article on Entity Framework.
This article is superb.
Regards
Balu
Title: Next Article   
Name: Jon Osmanson
Date: 3/13/2010 6:34:14 PM
Comment:
When are you going to have the next article, that continues the entity framework 4.0? On insert, update, delete, select? Please be soon!!!
Title: Publish Date   
Name: Vince Varallo
Date: 2/26/2010 8:39:12 AM
Comment:
I'll be writing a series of articles on this website to develop out a similar pattern described in the book but will be using EF 4 rather than LINQ to SQL. There isn't a date for the second version of the book.
Title: Publish date of "Vince Varallo"'s book   
Name: hasan boby
Date: 2/26/2010 2:55:15 AM
Comment:
I know "Vince Varallo, "the author of this article is going to include the “Entity Framework 4” in his writing 2nd edition of his great book, titled “ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008: Problem Design Solution”. I am just curious about when the 2nd edition of the book is coming. I mean the rough idea of publish date. I can’t wait to read it.
Title: Hire Asp.Net Developer   
Name: cis.neeraj07@gmail.com
Date: 2/15/2010 12:41:46 AM
Comment:
The article is good provides useful information & helpful as well.I like the article very much as it is very informative as it give information about ASP.NET application Entity Framework 4 and in future hope to see more of such articles.
Title: Very Good   
Name: Shashi
Date: 2/11/2010 1:43:38 AM
Comment:
Please explain more how to use it in real time project
Title: Good   
Name: Henry
Date: 2/10/2010 4:49:48 AM
Comment:
please explain more how to use this in three tier architecture
Title: Great!!!!!!   
Name: Majid Ismail
Date: 2/10/2010 12:02:29 AM
Comment:
This is really a great way to build Database from modal. I'll look forward for some more detail articles about Entity FrameWork.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 4/23/2014 5:13:14 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search