This site uses cookies to deliver our services. By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Allow Cookies and Dismiss

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

806 views 1 comments last modified about 5 years ago Raymond

In this page

Serial: An Introduction to SQL Server Features

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”

image

2)Create entity framework model SalesRepository.edmx

image

Choose “Generate from database”:

image

Create new connection to the database HiSqlServer-Sample:

image

Keep the connection string in the configuration file:

image

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

image

The model looks like the following screenshot:

image

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

image

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

image

image

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

Related pages

Querying Teradata and SQL Server - Tutorial 1: The SELECT Statement

28899 views   7 comments last modified about 4 years ago

SELECT is one of the most commonly used statements. In this tutorial, I will cover the following items: Two of the principal query clauses—FROM and SELECT Data Types Built-in functions CASE expressions and variations like ISNULL and COALESCE. * The functio...

View detail

Querying Teradata and SQL Server - Tutorial 2 Filtering and Sorting

2898 views   2 comments last modified about 4 years ago

Tutorial –1 For Teradata developers, if you have no SQL Server installed, please go to the following link to download the SQL Server 2014 Expression Edition. ...

View detail

Querying Teradata and SQL Server - Tutorial 0: Preparation

2683 views   0 comments last modified about 4 years ago

In this serial, I will compare Teradata SQL with T-SQL with samples provided. This is mainly prepared for SQL Server DBAs, Developers and other users to help them quickly master the common used SQLs in Teradata platform. Similar to T-SQL, Teradata SQL implements most part of the ANSI SQL with ext...

View detail

[SQL Server 2012] 使用自带功能简化SQL

743 views   0 comments last modified about 6 years ago

在低版本SQL Server中,对于一些常用的查询功能,我们可能需要运用复杂的逻辑,如临时表,游标等等来实现,这样可能会导致代码难维护,同时产生性能问题。本文将简述用自带的功能去简单的实现这些操作。 注意:一些功能仅在SQL Server 2012以及以上版本支持。 1.查询分页 (OFFSET, FETCH) 在很多Web应用程序中,我们需要实现分页功能,在过去,我们往往通过top语句实现或者通过ROW_NUMBER;在2012中加入了FETECH以及OFFSET标准SQL中支持的功能。下面的例子是查询第11到20行数据: ...

View detail

将远程 Sql Server 服务器上的数据库备份到本地的方法

547 views   0 comments last modified about 10 years ago

首先,必须知道数据库服务器的地址(IP),连接用户名和密码、其次,可以按照下面几种方式进行:

View detail

SQL Server 附加没有日志文件ldf的数据库mdf的方法

692 views   0 comments last modified about 9 years ago

Keywords: how to attach database without log file

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (1)

SS Customized Software Development Company | Web Development Company | ERP Software Development Company Mobile App Development Company

Smo*** about 3 years ago

Techno Softwares having several years experience working with global customers, connecting our professionals to their business needs, as their IT Development & Support Partner. Techno Softwares having a team of dedicated and experienced softwares developers that works for your all business needs. Techno Softwares deals in web design and development, Customized ERPs, CRMs, Web & Mobile Applications, eCommerce platforms etc.