ASP.NET 2.0 基础系列 -- 1.数据库设计_[Asp.Net教程]

本节主要创建网站后续使用到的用户表及相关存储过程

1.创建数据库

打开SQL Server Management Studio，在查询窗口执行以下T-SQL语句

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'Wish')
DROP DATABASE [Wish]
GO
CREATE DATABASE [Wish]
GO

请考虑一下为什么我们使用
SELECT 1 FROM sys.databases WHERE name = N'Wish'
而不是
SELECT * FROM sys.databases WHERE name = N'Wish'

2.创建数据库表
Users表存储论坛注册成员的信息

USE Wish
go
IF exists (SELECT 1 FROM sys.objects WHERE name = 'Users' AND type = 'U')
DROP TABLE [dbo].[Users]
go
CREATE TABLE [dbo].[Users] (
[UserID] INT IDENTITY
CONSTRAINT [PK_Users_UserID] PRIMARY KEY CLUSTERED,
[UserName] [varchar](16) NOT NULL
CONSTRAINT [UK_Users_UserName] UNIQUE ,
[Nickname] [varchar](16) ,
[Password] [varchar] (60) NOT NULL,
[Email] [varchar] (255) ,
[Question] [varchar] (40) NOT NULL,
[Answer] [varchar](40) NOT NULL,
[Sex] [varchar](1)
CONSTRAINT [DF_Users_Sex] DEFAULT 'M',
[ShowEmail] [bit] NOT NULL
CONSTRAINT [DF_Users_ShowEmail] DEFAULT (1),
[Signature] [varchar] (300) NULL ,
[ImageUrl] [varchar] (100) NULL
CONSTRAINT [DF_Users_ImageUrl] DEFAULT ('') ,
[AddedDate] [datetime] NOT NULL
CONSTRAINT [DF_Users_AddedDate] DEFAULT (getdate())
) ON [PRIMARY]
GO

3.创建存储过程

3.1 插入用户

if exists (select 1 from sys.objects where name = 'InsertUser' and type = 'P')
drop procedure [dbo].[InsertUser]
GO
CREATE PROCEDURE InsertUser
@UserName varchar(16),
@NickName varchar(16),
@Password varchar(60),
@Email varchar(255),
@Question varchar(40),
@Answer varchar(40),
@Sex varchar(1),
@ImageUrl varchar(100),
@UserID int output
AS
INSERT INTO Users
([UserName], [NickName], [Password], [Email], [Question], [Answer], [Sex], [ImageUrl], [AddedDate])
VALUES (@UserName, @NickName, @Password, @Email, @Question, @Answer, @Sex, @ImageUrl, getdate())
SET @UserID = SCOPE_IDENTITY()
GO

在SQL Server 2005中，可以使用 SCOPE_IDENTITY()、 @@IDENTITY 、 IDENT_CURRENT() 来取得最后插入记录的标识值，它们的区别在于：
SCOPE_IDENTITY() 返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值。一个作用域就是一个模块——存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。
@@IDENTITY 返回在当前会话的所有表中生成的最后一个标识值
IDENT_CURRENT() 返回为任何会话和任何作用域中的指定表最后生成的标识值

3.2 删除用户

if exists (select 1 from sys.objects where name = 'DeleteUser' and type = 'P')
drop procedure [dbo].[DeleteUser]
GO
CREATE PROCEDURE DeleteUser
@UserID int
AS
DELETE FROM Users WHERE UserID = @UserID
GO

3.3 更新用户Email

if exists (select 1 from sys.objects where name = 'UpdateUserEmail' and type = 'P')
drop procedure [dbo].[UpdateUserEmail]
GO
CREATE PROCEDURE UpdateUserEmail
@UserID int ,
@Email varchar(255)
AS
UPDATE Users
Set Email = @Email
Where UserID = @UserID
GO

3.4 验证登录

if exists (select 1 from sys.objects where name = 'ValidateLogin' and type = 'P')
drop procedure [dbo].[ValidateLogin]
GO
CREATE PROCEDURE [dbo].[ValidateLogin]
@UserName varchar(16),
@CryptPassword varchar(60)
AS
SELECT UserID
FROM Users
WHERE UserName = @UserName AND Password = @CryptPassword
GO

3.5 返回所有可用用户信息

if exists (select 1 from sys.objects where name = 'GetUsers' and type = 'P')
drop procedure [dbo].[GetUsers]
GO
CREATE PROCEDURE [dbo].[GetUsers]
AS
SELECT UserID, UserName, Nickname, Email, Question, Answer, Sex, ShowEmail, Signature, ImageUrl, AddedDate
FROM Users
GO

3.6 根据用户名得到用户id

if exists (select 1 from sys.objects where name = 'GetUserIDByUserName' and type = 'P')
drop procedure [dbo].[GetUserIDByUserName]
GO
CREATE PROCEDURE [dbo].[GetUserIDByUserName]
@UserName varchar(16)
AS
SELECT UserID
FROM Users
WHERE UserName = @UserName
GO

在我们的网站中我们使用参数化的SQL和存储过程。存储过程是预先定义的、可以复用的SQL语句的集合，它存储在一个数据库中。它可以接受输入参数、输出参数，并且返回单个或多个结果集。存储过程通过引用其名字来执行，并且它允许使用用户声明的变量、条件逻辑，以及其他强大的编程功能。
使用存储过程的优点主要有：
1.提高性能: 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.减少网络流量:存储过程位于服务器上，调用的时候只须传递存储过程的名称以及参数，不用每次访问都传递很长的SQL 语句，当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.安全性高:可设定只有特定用户才具有对指定存储过程的使用权，并可减少SQL注入攻击
并非所有的DBMS都支持存储过程，但是对于那些支持存储过程的提供者（如SQL Server和Oracle），我们可以通过把DbCommand的CommandText属性设置为存储过程的名字，并把其CommandType属性设置为CommandType.StoredProcedure，从而来使用存储过程。CommandType属性制定了如何解释CommandText，是作为存储过程的名字还是作为一条SQL语句（缺省）。某些DBMS缓存执行存储逻辑的计划，这确实提供了比直接的SQL语句更好的性能；然而，对于SQL Server，执行计划为了所有的命令而缓存，而不管它们是存储过程还是普通的SQL语句。
对存储过程的使用有一定的争论，一些开发者使用存储过程不仅封装了数据访问逻辑，而且封装了相当多的业务逻辑；而一些开发者则坚持业务和数据逻辑分别属于应用程序中的业务逻辑类和数据访问类。

来源：http://blog.csdn.net/amandag/