ASP.NET 2.0 基础系列 -- 1.数据库设计

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

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语句。
对存储过程的使用有一定的争论，一些开发者使用存储过程不仅封装了数据访问逻辑，而且封装了相当多的业务逻辑；而一些开发者则坚持业务和数据逻辑分别属于应用程序中的业务逻辑类和数据访问类。