数据访问层DAL的再次重构_1_建库表存储过程

    以前已经写过DAL层编码实现(仅仅为了演示)，当时介绍的表Depts和Users很简单，这次实现网站的民意调查模块功能，第一表按照真实项目运作(存储过程实际实现过程)，二来DAL的代码继续重构完善，并配合web.config自定义配置节和用户成员配置综合起来，BLL层编码实现没有什么变化，UI层Web版实现主题和模板页，民意调查的投票结果用用户控件封装。总之，该模块演示了完整的流程，综合前面博客的文章所有内容。算是一次整体大演练，希望能讲述的清楚。如果涉及的知识点在以前随笔中有了，这里会简单说明，而不再详细展开。(演练过程中的错误会截图说明，总之确保大家也能一道循序渐进的参与。)

    民意调查模块共两个表，结构如下：

Polls(问卷调查)

字段	说明	备注
PollID	主键	自增长
AddedBy	创建者	用户登录的帐号
AddedDate	创建日期	当前日期
QuestionText	问卷问题
IsCurrent	是否默认	默认的民意调查，只能有一个是默认的---bit类型
IsArchived	是否归档	归档的民意调查(表示已经接受了投票并过期了)，不能再更改，允许浏览
ArchivedDate	归档日期	被设置成归档的日期

上述字段只有ArchivedDate允许为空，其它字段必须有值。

插入的新记录是归档的，只有执行归档操作时，才能对其问卷归档，归档后的记录只能看而不能改，所有记录中只有一条记录是默认的(IsCurrent=1)，

当设置成归档记录时(IsCurrent要设置为0)

---可以理解为类似于选择题的题目

 

PollOptions（问卷的选项表）

字段	说明	备注
OptionID	主键	自增长
OptionText	选项文本
Votes	投票数	该选项被投票的次数
PollID	外键(关联Polls表)	表示所属的问卷
AddedBy	创建者
AddedDate	创建日期

该表字段都必须有值，将来显示在UI界面上，每个问卷的选项用户只能参与一个进行投票，投票后不能重复投票(或者设定一个锁定时间，该时间内只允许投一次票)

----可以理解为类似于选择题的选项。综合起来两表就类似 单项选择题。

 

后台表建立好后，接下来就是设计存储过程了。

存储过程名	功能	说明
ArchivePoll	对指定PollID的记录进行归档	该记录IsArchived=1,IsCurrent=0,ArchivedDate=getdate()
DeletePoll	对指定PollID的记录进行删除	由于前面两表关系的删除层叠，所以对应PollOptions的记录也被删除
GetCurrentPollID	查找当前被设置为默认状态的问卷PollID	问卷表只允许一条记录的IsCurrent=1，找到这条记录的PollID返回
GetPoll	返回指定PollID的记录	为将来DAL层简单实体对象(PollDetail)各属性的值提供数据
GetPolls	返回所有的问卷记录	提供一个集合，每个元素表示一份问卷。List<PollDetail>
InsertPoll	插入一份问卷记录 (@PollID是输出参数)	判断IsCurrent的设置，若为1，则插入前先将所有记录的IsCurrent设置为0。
UpdatePoll	更新问卷记录	归档状态的是不能更新的，意味着要更新的界面上只能看到非归档(激活状态)的记录
UpdatePollOption	更新调查选项	更新指定OptionID记录的选项文本
InsertPollOption	插入一份选项(@OptionID是输出参数)	@OptionID是新增的主键值
InsertVote	对指定OptionID的记录投票数自加1	让指定的记录Votes=Votes+1
GetPollOptions	根据PollID返回所有的选项记录	提供一个集合，每个元素表示该问卷的一个选项投票情况。List<PollOptionDetail>
GetPollOption	返回指定OptionID的记录	为将来DAL层简单实体对象(PollOptionDetail)各属性的值提供数据
DeletePollOption	对指定的主键OptionID进行删除

注意：

1. 返回的问卷记录时，增加一列Votes来得到所有选项的投票总数。

2. 返回的问卷选项时，增加一列Percentage来表示该选项的投票数占该问卷投票总数的百分比。

返回多条记录时，注意Order By的字段选择，比如GetPolls返回问卷记录列表，按照归档方式升序、归档日期降序、创建日期降序

(在UI显示的时候保证最新的归档问卷显示在前面)。这里的两个参数请仔细看下面的截图中的说明。

（可以返回归档的记录、可以返回非归档的记录<可编辑的记录>、或是所有问卷记录）

3. UpdatePoll与InsertPoll执行时，可能设置参数@IsCurrent为1，若是则需要先进行设置原有记录的IsCurrent为0，再执行本操作。所以多步操作用事务处理。

 

 

UpdatePoll类似，不再重复截图。

 

用VS建立空白解决方案，启动vs,新建-项目(其他项目类型-Visual Studio解决方案)

 

 

右击解决方案---添加--新建网站--选择解决方案的目录---在现有位置创建新网站

由于该民意调查模块实际需要用户注册及相应授权，所以需要使用成员配置系统。(用Framework2.0自带的用户管理配置)。

点击上图的[ASP.NET配置]对应的按钮，弹出浏览器窗口，选择[安全]选项页。

点击"选择身份验证类型"，选择单选项[通过Internet](达到表单验证Forms)

点击”启用角色”链接。

 

点击“创建或管理角色”链接，创建新角色名称：Administrators

单击”添加角色”，成功显示后回到最开始“安全”选项卡的地方，点击”创建用户”链接。并输入以下如图的信息：

注意：这里密码我们希望设置为admin，点击[创建用户]按钮，出现错误提示。

为了先确保创建admin成功，这里先将电子邮件的内容作为密码输入。

其实关于密码长度的设置以及是否出现非字母数字字符的设置，以及更多的设置，可以在web.config设置。我们先回到解决方案资源管理器窗口。

如上图所示：先[刷新]，然后双击[ASPNETDB.MDF]，在服务器资源管理器窗口，选中[ASPNETDB.MDF]，对应的属性器中得到连接字符串的内容：

Data Source=.\SQLEXPRESS;AttachDbFilename=F:\netStudy\问卷调查\App_Data\ASPNETDB.MDF;Integrated Security=True;User Instance=True

<roleManager enabled="true" /><authentication mode="Forms" />就是通过网站的配置管理工具来进行改动的。

可以看到前面注册的用户admin的密码是加密了的。

现在大家看到web.config里面的内容很简洁(VS2010版本)，因为很多的设置(比如数据库连接串的设置）都已经放到了缺省设置(machine.config.default)。

这里的connectionString的内容中的字符串”|DataDirectory|”<占位符>会自动替换为网站所在根目录下的App_Data文件夹对应的物理路径。

仔细比较一下就发现：与前面我们获得的截图中显示的ASPNETDB.MDF数据库连接串内容其实是一致的。

好，接下来建立我们所需要的数据库Polls。它包含表(Polls和PollOptions)。

创建存储过程的脚本参考如下：

CREATE PROCEDURE dbo.ArchivePoll
    @PollID int
AS
     SET NOCOUNT ON  
     Update Polls set IsArchived=1,IsCurrent=0,ArchivedDate=getdate() where PollID=@PollID
GO

CREATE PROCEDURE dbo.DeletePoll
    @PollID int
AS
      SET NOCOUNT ON  
    Delete from Polls where PollID=@PollID
GO

CREATE PROCEDURE dbo.DeletePollOption
    @OptionID int
AS
  SET NOCOUNT ON  
    delete from Polloptions where optionId=@OptionID
GO

CREATE PROCEDURE dbo.GetCurrentPollID
    @PollID int output
AS
     SET NOCOUNT ON  
    select @PollID=PollID  from Polls where iscurrent=1 and IsArchived=0
    if @PollID is null
      set @PollID=-1
GO

CREATE PROCEDURE dbo.GetPoll
 @PollID int
AS
SET NOCOUNT ON 
 SELECT PollID,AddedBy,AddedDate,QuestionText,IsCurrent,IsArchived,
    (SELECT SUM(Votes) FROM PollOptions WHERE  PollID=@PollID) as Votes --增加的一列Votes返回
     FROM Polls
    WHERE PollID=@PollID
GO

CREATE PROCEDURE dbo.GetPollOption
    @OptionID INT
AS
     SET NOCOUNT ON 
    DECLARE @PollID INT --找到关联的问卷的主键
    SELECT @PollID=PollID FROM PollOptions WHERE OptionID=@OptionID

    DECLARE @tolVotes INT --获得该问卷所有选项的总投票数
    SELECT @tolVotes=SUM(votes) FROM PollOptions WHERE PollID=@PollID

    IF @tolVotes=0 SET @tolVotes=1 --避免后续计算百分比时除零异常

    SELECT OptionId,OptionText,Votes,PollId,Addedby,AddedDate,
    (CAST(votes AS DECIMAL)*100/@tolVotes) AS Percentage --增加的一列Percentage返回
    FROM pollOptions WHERE optionId=@OptionID
GO
 
CREATE PROCEDURE dbo.GetPollOptions
 @PollID int
AS
     SET NOCOUNT ON 
     declare @totalVotes int
     
     select @totalVotes =sum(Votes) from PollOptions where PollID=@PollID
     if @totalVotes=0 set @totalVotes=1

     select OptionID,OptionText,Votes,PollID,AddedBy,AddedDate,
     (cast(votes as decimal)*100/@totalVotes) as percentage
     From pollOptions where PollID=@PollID   
     order by addedDate asc --记得排序
GO

CREATE PROCEDURE dbo.GetPolls
  (@IncludeActive bit,--包括 激活
  @IncludeArchived bit)--包括 归档
AS
 --IsArchived=1表示归档  IsArchived=0表示激活状态(可编辑状态)<非归档状态>
 DECLARE @isArchived1 bit
 DECLARE @isArchived2 bit
 SET @isArchived1=1 
 SET @isArchived2=0
 --默认显示所有文档(包括激活和归档)
    SET NOCOUNT ON 
    if @IncludeActive =1 and @IncludeArchived=0 --只显示激活状态的文档
    BEGIN
      SET @isArchived1=0
      SET @isArchived2=0
    END

    IF @IncludeActive =0 and @IncludeArchived=1 --只显示归档状态的文档
    BEGIN
      SET @isArchived1=1
      SET @isArchived2=1
    END

    SELECT PollID,AddedBy,AddedDate,QuestionText,Iscurrent,IsArchived,
    (SELECT SUM(Votes) FROM PollOptions WHERE Polls.PollID=PollOptions.PollID) AS Votes 
    FROM Polls
    WHERE IsArchived=@isArchived1 or IsArchived=@isArchived2    
    ORDER BY IsArchived,ArchivedDate DESC, AddedDate DESC
    --选择多条记录的时候，要注意两个问题：1是排序指定，2是否分页

GO

CREATE PROCEDURE dbo.InsertPoll
(
 @AddedBy nvarchar(50),
 @AddedDate datetime,
 @QuestionText nvarchar(256),
 @IsCurrent bit,
 @PollID int output
)     
AS
  SET NOCOUNT ON
      BEGIN TRAN InsertPoll --开始事务处理
       IF @IsCurrent=1 UPDATe Polls SET IsCurrent=0 --原有记录IsCurrent=0

       INSERT INTO Polls(AddedBy,AddedDate,QuestionText,IsCurrent,IsArchived,ArchivedDate)
         VALUES(@AddedBy,@AddedDate,@QuestionText,@IsCurrent,0,null)
        --新插入的记录当然不能归档，而要通过归档操作(ArchivePoll)来实现

        SET @PollID= SCOPE_IDENTITY() --获得新增记录的ID值
        
        IF @@ERROR>0 --全局变量@@ERROR
        BEGIN
          RAISERROR('增加民意记录异常！',16,1) --引发错误
          ROLLBACK TRAN InsertPoll --事务回滚
          RETURN 99 --可以不要该行 
        END
        COMMIT TRAN InsertPoll --事务提交
         
GO

CREATE PROCEDURE dbo.InsertPollOption
     
    (
    @OptionText nvarchar(256),
    @PollID int,
    @AddedBy nvarchar(50),
    @AddedDate datetime,
    @OptionID int output
    )
     
AS
      SET NOCOUNT ON  
      insert into PollOptions(OptionText,PollID,AddedBy,AddedDate,votes) values(@OptionText,@PollID,@AddedBy,@AddedDate,0)
      set @OptionID=scope_identity()
GO

CREATE PROCEDURE dbo.InsertVote
  @OptionID int
  AS
     SET NOCOUNT ON 
     update PollOptions set votes=votes+1 where optionID=@OptionID

GO

CREATE PROCEDURE dbo.UpdatePoll
 (
   @PollID int,
   @QuestionText nvarchar(256),
   @IsCurrent bit
 )
AS
     SET NOCOUNT ON
    BEGIN TRAN UpdatePoll
    if @IsCurrent=1 
   Update Polls Set IsCurrent=0      


   Update Polls Set QuestionText=@QuestionText,IsCurrent=@IsCurrent,IsArchived=0  WHERE PollID=@PollID

   if @@ERROR>0
   begin
     RAISERROR('更新民意记录异常！',16,1)
     ROLLBACK TRAN UpdatePoll
     --return 99
   end
    
     COMMIT TRAN UpdatePoll

     --不能对已经归档的民意调查进行更改了！

GO

CREATE PROCEDURE dbo.UpdatePollOption
     
    (
      @OptionID int,
      @OptionText nvarchar(256)
    )
     
AS
      SET NOCOUNT ON  
    update PollOptions set OptionText=@OptionText where OptionID=@OptionID