数据访问层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)。
创建存储过程的脚本参考如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE PROCEDURE dbo.ArchivePoll
2 @PollID int
3 AS
4 SET NOCOUNT ON
5 Update Polls set IsArchived=1,IsCurrent=0,ArchivedDate=getdate() where PollID=@PollID
6 GO
7
8 CREATE PROCEDURE dbo.DeletePoll
9 @PollID int
10 AS
11 SET NOCOUNT ON
12 Delete from Polls where PollID=@PollID
13 GO
14
15 CREATE PROCEDURE dbo.DeletePollOption
16 @OptionID int
17 AS
18 SET NOCOUNT ON
19 delete from Polloptions where optionId=@OptionID
20 GO
21
22 CREATE PROCEDURE dbo.GetCurrentPollID
23 @PollID int output
24 AS
25 SET NOCOUNT ON
26 select @PollID=PollID from Polls where iscurrent=1 and IsArchived=0
27 if @PollID is null
28 set @PollID=-1
29 GO
30
31 CREATE PROCEDURE dbo.GetPoll
32 @PollID int
33 AS
34 SET NOCOUNT ON
35 SELECT PollID,AddedBy,AddedDate,QuestionText,IsCurrent,IsArchived,
36 (SELECT SUM(Votes) FROM PollOptions WHERE PollID=@PollID) as Votes --增加的一列Votes返回
37 FROM Polls
38 WHERE PollID=@PollID
39 GO
40
41 CREATE PROCEDURE dbo.GetPollOption
42 @OptionID INT
43 AS
44 SET NOCOUNT ON
45 DECLARE @PollID INT --找到关联的问卷的主键
46 SELECT @PollID=PollID FROM PollOptions WHERE OptionID=@OptionID
47
48 DECLARE @tolVotes INT --获得该问卷所有选项的总投票数
49 SELECT @tolVotes=SUM(votes) FROM PollOptions WHERE PollID=@PollID
50
51 IF @tolVotes=0 SET @tolVotes=1 --避免后续计算百分比时除零异常
52
53 SELECT OptionId,OptionText,Votes,PollId,Addedby,AddedDate,
54 (CAST(votes AS DECIMAL)*100/@tolVotes) AS Percentage --增加的一列Percentage返回
55 FROM pollOptions WHERE optionId=@OptionID
56 GO
57
58 CREATE PROCEDURE dbo.GetPollOptions
59 @PollID int
60 AS
61 SET NOCOUNT ON
62 declare @totalVotes int
63
64 select @totalVotes =sum(Votes) from PollOptions where PollID=@PollID
65 if @totalVotes=0 set @totalVotes=1
66
67 select OptionID,OptionText,Votes,PollID,AddedBy,AddedDate,
68 (cast(votes as decimal)*100/@totalVotes) as percentage
69 From pollOptions where PollID=@PollID
70 order by addedDate asc --记得排序
71 GO
72
73 CREATE PROCEDURE dbo.GetPolls
74 (@IncludeActive bit,--包括 激活
75 @IncludeArchived bit)--包括 归档
76 AS
77 --IsArchived=1表示归档 IsArchived=0表示激活状态(可编辑状态)<非归档状态>
78 DECLARE @isArchived1 bit
79 DECLARE @isArchived2 bit
80 SET @isArchived1=1
81 SET @isArchived2=0
82 --默认显示所有文档(包括激活和归档)
83 SET NOCOUNT ON
84 if @IncludeActive =1 and @IncludeArchived=0 --只显示激活状态的文档
85 BEGIN
86 SET @isArchived1=0
87 SET @isArchived2=0
88 END
89
90 IF @IncludeActive =0 and @IncludeArchived=1 --只显示归档状态的文档
91 BEGIN
92 SET @isArchived1=1
93 SET @isArchived2=1
94 END
95
96 SELECT PollID,AddedBy,AddedDate,QuestionText,Iscurrent,IsArchived,
97 (SELECT SUM(Votes) FROM PollOptions WHERE Polls.PollID=PollOptions.PollID) AS Votes
98 FROM Polls
99 WHERE IsArchived=@isArchived1 or IsArchived=@isArchived2
100 ORDER BY IsArchived,ArchivedDate DESC, AddedDate DESC
101 --选择多条记录的时候,要注意两个问题:1是排序指定,2是否分页
102
103 GO
104
105 CREATE PROCEDURE dbo.InsertPoll
106 (
107 @AddedBy nvarchar(50),
108 @AddedDate datetime,
109 @QuestionText nvarchar(256),
110 @IsCurrent bit,
111 @PollID int output
112 )
113 AS
114 SET NOCOUNT ON
115 BEGIN TRAN InsertPoll --开始事务处理
116 IF @IsCurrent=1 UPDATe Polls SET IsCurrent=0 --原有记录IsCurrent=0
117
118 INSERT INTO Polls(AddedBy,AddedDate,QuestionText,IsCurrent,IsArchived,ArchivedDate)
119 VALUES(@AddedBy,@AddedDate,@QuestionText,@IsCurrent,0,null)
120 --新插入的记录当然不能归档,而要通过归档操作(ArchivePoll)来实现
121
122 SET @PollID= SCOPE_IDENTITY() --获得新增记录的ID值
123
124 IF @@ERROR>0 --全局变量@@ERROR
125 BEGIN
126 RAISERROR('增加民意记录异常!',16,1) --引发错误
127 ROLLBACK TRAN InsertPoll --事务回滚
128 RETURN 99 --可以不要该行
129 END
130 COMMIT TRAN InsertPoll --事务提交
131
132 GO
133
134 CREATE PROCEDURE dbo.InsertPollOption
135
136 (
137 @OptionText nvarchar(256),
138 @PollID int,
139 @AddedBy nvarchar(50),
140 @AddedDate datetime,
141 @OptionID int output
142 )
143
144 AS
145 SET NOCOUNT ON
146 insert into PollOptions(OptionText,PollID,AddedBy,AddedDate,votes) values(@OptionText,@PollID,@AddedBy,@AddedDate,0)
147 set @OptionID=scope_identity()
148 GO
149
150 CREATE PROCEDURE dbo.InsertVote
151 @OptionID int
152 AS
153 SET NOCOUNT ON
154 update PollOptions set votes=votes+1 where optionID=@OptionID
155
156 GO
157
158 CREATE PROCEDURE dbo.UpdatePoll
159 (
160 @PollID int,
161 @QuestionText nvarchar(256),
162 @IsCurrent bit
163 )
164 AS
165 SET NOCOUNT ON
166 BEGIN TRAN UpdatePoll
167 if @IsCurrent=1
168 Update Polls Set IsCurrent=0
169
170
171 Update Polls Set QuestionText=@QuestionText,IsCurrent=@IsCurrent,IsArchived=0 WHERE PollID=@PollID
172
173 if @@ERROR>0
174 begin
175 RAISERROR('更新民意记录异常!',16,1)
176 ROLLBACK TRAN UpdatePoll
177 --return 99
178 end
179
180 COMMIT TRAN UpdatePoll
181
182 --不能对已经归档的民意调查进行更改了!
183
184 GO
185
186 CREATE PROCEDURE dbo.UpdatePollOption
187
188 (
189 @OptionID int,
190 @OptionText nvarchar(256)
191 )
192
193 AS
194 SET NOCOUNT ON
195 update PollOptions set OptionText=@OptionText where OptionID=@OptionID
当来之世,经道灭尽,我以慈悲哀愍,特留此经止住百岁。其有众生,值斯经者,随意所愿,皆可得度。(南无阿弥陀佛)
道可道,非常道;名可名,非常名。
我常说,一个国家,一个民族,亡国都不怕,最可怕的是一个国 家和民族自己的根本文化亡掉了,这就会沦为万劫不复,永远不会翻身。---南怀瑾<国学大师>