Step by step. NET three-tier Architecture Analysis 2. Database Design

To develop a user management system, we must first understand the requirements. Now we will give you a simple requirement, user table. Assume that there are two types of roles that use the departID field to determine, administrators and employees,
We need to first create a user table custom and a department table department:
View sourceprint? Create table [dbo]. [custom] (

[Id] [int] IDENTITY (1, 1) not null,

[Cname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS not null,

[DepartID] [int] not null,

[Age] [int] not null,

[Ename] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS not null,

[Password] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS not null,

CONSTRAINT [PK_custom] PRIMARY KEY CLUSTERED

(

[Id] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY];

Create table [dbo]. [department] (

[Id] [int] IDENTITY (1, 1) not null,

[Partition name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS not null,

[Description] [nchar] (10) COLLATE Chinese_PRC_CI_AS not null,

CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED

(

[Id] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

After the database table is created, write the stored procedure and insert a data entry:
View sourceprint? Create procedure [dbo]. [spInsertCustom]

@ Cname nvarchar (50 ),

@ Ename nvarchar (50 ),

@ Age int,

@ DepartID int,

@ Password nvarchar (50)

AS

BEGIN

Insert into custom (cname, departID, age, ename, password) values (@ cname, @ departID, @ age, @ ename, @ password)

END

RETURN @ Identity

View sourceprint? Create PROCEDURE [dbo]. [spInsertDepartment]

@ Brief name nvarchar (50 ),

@ Description nvarchar (50)

AS

BEGIN

Insert into department (partition name, description) values (@ partition name, @ description)

END

RETURN @ Identity

Two stored procedures for updating a data record are created:
View sourceprint? Create procedure [dbo]. [spupdatecustom]

@ Id int,

@ Cname nvarchar (50 ),

@ DepartID int,

@ Age int,

@ Ename nvarchar (50 ),

@ Password nvarchar (50)

AS

BEGIN

Update

Custom

Set

Cname = @ cname,

DepartID = @ departID,

Age = @ age,

Ename = @ ename,

Password = @ password

Where id = @ id

END

COMMIT TRAN

View sourceprint? Create procedure spupdatedepart

(

@ Brief name nvarchar (50 ),

@ Description nchar (10 ),

@ Id int

)

As

UPDATE [dbo]. [department]

SET [CommonName] = @ CommonName

, [Description] = @ brief name

WHERE id = @ id

Create two stored procedures to retrieve all users:

View sourceprint? Create procedure [dbo]. [spGetcustom]

AS

BEGIN

Select * from custom order by id desc

END

View sourceprint? Create PROCEDURE [dbo]. [spGetAlldepartment]

AS

BEGIN

Select * from department

END

Create a new stored procedure for retrieving data by ID:

View sourceprint? Create procedure [dbo]. [spGetcustomer]

@ Id int

AS

BEGIN

Select * from custom where id = @ id

END

Now we create a stored procedure for retrieving the Department ID based on the department name:

View sourceprint? Create PROCEDURE [dbo]. [spgetjavasmenter]

@ Brief name nvarchar (50)

AS

BEGIN

Select * from department where region name = @ region name

END

Create two stored procedures for deleting data by ID:

View sourceprint? Create PROCEDURE [dbo]. [spDeletecustom]

@ Id int

AS

BEGIN

Delete custom where id = @ id

END

View sourceprint? Create procedure spdeletedepart

@ Id int

AS

BEGIN

Delete department where id = @ id

END

GO

The database has been designed. This is just a simple example.

Next I will explain the SQLHelper design.