ASP. NET2.0 website development (2) database settings

This section mainly sets the database:

The database used in the software is MS SqlServer2000. Create a database, Books, and create two data tables in the database:

User table:

Create table [dbo]. [USERTAB] (
[XH] [int] IDENTITY (1, 1) not null,
[DLMC] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS not null,
[YHMC] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL,
[YHKL] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

This table is only used as a test table and has a primary key with no indexes. The fields are sequential: serial number, Logon Name, user name, and user password. When a user logs on, the user information will be queried in this table. If the user is a valid user, log on. Otherwise, an error is returned. In actual use, you can use MD5, SHA1, and other methods to encrypt the password.

Library data table:

Create table [dbo]. [BOOKS] (
[ID] [int] IDENTITY (1, 1) not null,
[MC] [varchar] (20) COLLATE Chinese_PRC_CI_AS not null,
[CBS] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL,
[DJ] [money] not null,
[NRTY] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL,
[TP] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Create index [IX_BOOKS] ON [dbo]. [BOOKS] ([MC]) ON [PRIMARY]
GO

This table stores the saved books and materials. The fields are sequence numbers, book names, publishers, pricing, content feeds, and images.

According to my habits, I stored data operations in a database stored in a stored procedure. In this way, I can debug the problems that may occur during the development process separately, to ensure that the database is free of errors, you can develop it in the Code as easily as possible. In the case of a large amount of data, you can query it in the form of a stored procedure to get the best query speed.

Create procedure dbo. Books_AddValue
@ Mc NVARCHAR (20 ),
@ Cbs NVARCHAR (60 ),
@ Dj MONEY,
@ Nrty NVARCHAR (2000 ),
@ Tp IMAGE
AS
Insert into dbo. BOOKS (
MC,
CBS,
DJ,
NRTY,
TP
) VALUES (
@ Mc,
@ Cbs,
@ Dj,
@ Nrty,
@ Tp
)

RETURN

GO

Create procedure dbo. Books_Delete
@ Id INT
AS
Delete from dbo. BOOKS
WHERE dbo. BOOKS. ID = @ id

RETURN

GO

Create procedure dbo. Books_GetValue
@ Id INT
AS
SELECT
Dbo. BOOKS. ID,
Dbo. BOOKS. MC,
Dbo. BOOKS. CBS,
Dbo. BOOKS. DJ,
Dbo. BOOKS. NRTY,
Dbo. BOOKS. TP
FROM dbo. BOOKS
WHERE dbo. BOOKS. ID = @ id

RETURN


GO

Create procedure dbo. Books_GetValues
AS
SELECT
Dbo. BOOKS. ID,
Dbo. BOOKS. MC,
Dbo. BOOKS. CBS,
Dbo. BOOKS. DJ,
Dbo. BOOKS. NRTY
FROM dbo. BOOKS
Order by dbo. BOOKS. ID
RETURN

GO

Create procedure dbo. Books_Update
@ Id INT,
@ Mc NVARCHAR (20 ),
@ Cbs NVARCHAR (60 ),
@ Dj MONEY,
@ Nrty NVARCHAR (2000 ),
@ Tp IMAGE
AS
UPDATE dbo. BOOKS SET
Dbo. BOOKS. MC = @ mc,
Dbo. BOOKS. CBS = @ cbs,
Dbo. BOOKS. DJ = @ dj,
Dbo. BOOKS. NRTY = @ nrty,
Dbo. BOOKS. TP = @ tp
WHERE dbo. BOOKS. ID = @ id

RETURN

GO

Create procedure dbo. User_GetValue
@ Dlmc NVARCHAR (20)
AS
SELECT
Dbo. USERTAB. XH,
Dbo. USERTAB. DLMC,
Dbo. USERTAB. YHMC,
Dbo. USERTAB. YHKL
FROM dbo. USERTAB
WHERE dbo. USERTAB. DLMC = @ dlmc

RETURN

GO

In comparison with Oracle in SQL Server, it is very easy to return data. In Oracle, you need to use cursor and other operations to return data sets. If the queried data volume is large, for example, a table containing tens of millions of data records, you 'd better return only the data you need, such as a page. Of course, suitable indexes and row/column partitions are indispensable.

The code in the database is listed so much. The next section begins to develop the data classes associated with the database.