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.
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]
1) Create website project “HiSqlServer-Sample”
2)Create entity framework model SalesRepository.edmx
Choose “Generate from database”:
Create new connection to the database HiSqlServer-Sample:
Keep the connection string in the configuration file:
Choose the tables, set model namespace as ‘SalesModel’ and then click Finish to save.
The model looks like the following screenshot:
3) Create new controller ‘ProductController’ using the entity model
Visual Studio will automatically generate the codes and views for managing the product entity, including Create, Edit. Delete and Details.
Using MVC + Entity Framework + SQL Server, we can complete the management website page just in several minutes.
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.