Build Online Product Management Website using MVC 3 + Entity Framework + SQL Server

This article shows you how to build CMS website using ASP.NET MVC3, EntityFramework and SQL Server.

Case Scenario

All the samples in this serial will focus on one virtual company that produces and sales mobile phones. Three business subjects are covered: products, area (sales areas) and sales data.

Preparation

Follow the steps below to create one sample database.

1. Create database HiSqlServer-Sample

2. Create table dbo.Areas (areas of sales) using the following script:

CREATE TABLE [dbo].[Areas]
(
    [AreaId] INT NOT NULL IDENTITY(100, 1) , 
    [AreaName] NVARCHAR(64) NOT NULL, 
    CONSTRAINT [PK_Areas] PRIMARY KEY ([AreaId])
)

3. Create table dbo.Products (products of the company) using the following script:

CREATE TABLE [dbo].[Products]
(
    [ProductId] INT NOT NULL IDENTITY(100, 1) , 
    [ProductName] NVARCHAR(64) NOT NULL, 
    [Cost] DECIMAL(10, 2) NOT NULL, 
    [Color] NCHAR(1) NOT NULL, 
    CONSTRAINT [PK_Products] PRIMARY KEY ([ProductId]) 
)

4. Create table dbo.Sales (sales data) using the following script:

CREATE TABLE [dbo].[Sales]
(
    [SaleNo] INT NOT NULL PRIMARY KEY, 
    [SaleDate] DATETIME NOT NULL, 
    [ProductId] INT NOT NULL, 
    [AreaId] INT NOT NULL, 
    [Amount] DECIMAL(10, 2) NULL
)

5. Initialize tables:

-- Three regions
INSERT INTO dbo.Areas VALUES(N'Earth');
INSERT INTO dbo.Areas VALUES(N'Mars');
INSERT INTO dbo.Areas VALUES(N'Saturn');

GO
-- R: Red; Y: Yellow
INSERT INTO dbo.Products VALUES('myPhone',2999,N'R')
INSERT INTO dbo.Products VALUES('myPhone',2999,N'Y')
INSERT INTO dbo.Products VALUES('yourPhone',1999,N'R')
INSERT INTO dbo.Products VALUES('yourPhone',1999,N'Y')
INSERT INTO dbo.Products VALUES('ourPhone',999,N'R')
INSERT INTO dbo.Products VALUES('ourPhone',999,N'Y')

6. Create SQL Server Login and Database User

USE [master]
GO
CREATE LOGIN [HiSqlServer] WITH PASSWORD=N'HiSqlServer', DEFAULT_DATABASE=[HiSqlServer-Sample], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [HiSqlServer-Sample]
GO
CREATE USER [HiSqlServer] FOR LOGIN [HiSqlServer] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [HiSqlServer-Sample]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [HiSqlServer]
GO

ALTER ROLE [db_owner] ADD MEMBER [HiSqlServer]

Create Website to Manage Product (dbo.Products)

1. Create website project “HiSqlServer-Sample”

https://api.kontext.tech/resource/31725c46-2f61-56a4-9462-b588ffc1472c

2)Create entity framework model SalesRepository.edmx

https://api.kontext.tech/resource/a6ab80df-9340-524f-95fe-83a9b586ca82

Choose “Generate from database”:

https://api.kontext.tech/resource/2322a9da-7113-56a6-8efd-35e067166c05

Create new connection to the database HiSqlServer-Sample:

https://api.kontext.tech/resource/55a185c8-30ff-5db0-b673-4847ca3269ff

Keep the connection string in the configuration file:

https://api.kontext.tech/resource/4e69f0a3-9493-5a65-9dde-ba9b407d3feb

Choose the tables, set model namespace as ‘SalesModel’ and then click Finish to save.

https://api.kontext.tech/resource/fd22f925-dfca-5e19-b750-7c3ad811ac19

The model looks like the following screenshot:

https://api.kontext.tech/resource/0da387d6-c8f5-526a-bdee-5aba7069404d

3. Create new controller ‘ProductController’ using the entity model

https://api.kontext.tech/resource/82e8d28b-ad16-5f73-b3d7-a1e4b8e86de3

Visual Studio will automatically generate the codes and views for managing the product entity, including Create, Edit. Delete and Details.

https://api.kontext.tech/resource/c6b9959a-2057-5e69-9e68-f1a4cc65bdd0

https://api.kontext.tech/resource/29bfbb31-8e9c-59bf-ab3c-f172be63bcc4

Using MVC + Entity Framework + SQL Server, we can complete the management website page just in several minutes.