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.
