基于.NET平台的分层架构实战（八）数据访问层的第二种实现：

基于 .NET平台的 分层 架构 实战 （七-外一篇）对 数据 访问 层第一种 实现 （Access+SQL）的重构 ）中，讨论了使用SQL构建 数据 访问 层的方法，并且针对的是Access 数据 库。而这一篇中，将要创建一个针对SQLServer 数据 库的 数据 访问 层，并且配合 存储

基于.NET平台的分层架构实战（七-外一篇）——对数据访问层第一种实现（Access+SQL）的重构）中，讨论了使用SQL构建数据访问层的方法，并且针对的是Access数据库。而这一篇中，将要创建一个针对SQLServer数据库的数据访问层，并且配合存储过程实现。

曾经有朋友问我使用SQL和存储过程在效率上的差别，惭愧的是我对这方面没有研究，也没有实际做过测试。通过查阅资料，发现在一般情况下，存储过程的效率由于使用SQL，但是也不绝对，也发现有的朋友测试时发现在特定情况下SQL的效率优于存储过程，所以这个问题不能一概而论。

好，废话不多说，这里先列出使用存储过程构建数据访问层的一般步骤：
1.创建新工程
2.创建数据库
3.编写相应存储过程
4.编写数据库辅助类
5.实现数据访问层

创建新工程
在开始所有开发工作前，我们需要在解决方案下新建一个工程，叫SQLServerDAL，用于存放所有SQLServer数据访问层的代码。

创建数据库
首先，我们要根据前文设计的数据库，在SQLServer中创建相应的数据库及数据表。我使用的是SQLServer2005，使用企业管理器创建，创建方法不再赘述。

编写存储过程
数据库创建完成后，我们就要编写存储过程了。由于数据访问层接口已经确定，所以需要哪些存储过程也很好确定。例如数据访问层接口中有一个添加管理员方法，那么就一定有一个存储过程实现这个功能。
还是以管理员模块为例，经过简单分析，需要一下存储过程：

插入管理员记录
删除管理员记录
更新管理员信息
按ID取得管理员记录
按用户名及密码取得管理员记录
按用户名取得管理员记录
取得全部管理员记录

创建这些存储过程的SQL代码如下：

插入管理员记录

1. set ANSI_NULLS ON
2. set QUOTED_IDENTIFIER ON
3. GO
4. -- =============================================
5. -- Author:
6. -- Create date: <2008-07-04>
7. -- Description: <插入管理员记录>
8. -- =============================================
9. CREATE PROCEDURE [dbo].[Pr_InsertAdmin]
10. (
11. @Name Nvarchar(20),
12. @Password Nvarchar(50)
13. )
14. AS
15. INSERT INTO TAdmin
16. (
17. [Name],
18. [Password]
19. )
20. VALUES
21. (
22. @Name,
23. @Password
24. )

删除管理员记录

1. 1set ANSI_NULLS ON
2. 2set QUOTED_IDENTIFIER ON
3. 3GO
4. 4-- =============================================
5. 5-- Author:
6. 6-- Create date: <2008-07-04>
7. 7-- Description: <删除管理员记录>
8. 8-- =============================================
9. 9CREATE PROCEDURE [dbo].[Pr_DeleteAdmin]
10. 10(
11. 11 @ID Int
12. 12)
13. 13AS
14. 14DELETE FROM TAdmin
15. 15WHERE [ID]=@ID

修改管理员信息

1. 1set ANSI_NULLS ON
2. 2set QUOTED_IDENTIFIER ON
3. 3GO
4. 4-- =============================================
5. 5-- Author:
6. 6-- Create date: <2008-07-04>
7. 7-- Description: <修改管理员记录>
8. 8-- =============================================
9. 9CREATE PROCEDURE [dbo].[Pr_UpdateAdmin]
10. 10(
11. 11 @ID Int,
12. 12 @Name Nvarchar(20),
13. 13 @Password Nvarchar(50)
14. 14)
15. 15AS
16. 16UPDATE TAdmin
17. 17SET
18. 18[Name]=@Name,
19. 19[Password]=@Password
20. 20WHERE [ID]=@ID

按ID取得管理员

1. 1set ANSI_NULLS ON
2. 2set QUOTED_IDENTIFIER ON
3. 3GO
4. 4-- =============================================
5. 5-- Author:
6. 6-- Create date: <2008-07-04>
7. 7-- Description: <按ID取得管理员信息>
8. 8-- =============================================
9. 9CREATE PROCEDURE [dbo].[Pr_GetAdminByID]
10. 10(
11. 11 @ID Int
12. 12)
13. 13AS
14. 14SELECT * FROM TAdmin
15. 15WHERE [ID]=@ID

按用户名和密码取得管理员

1. 1set ANSI_NULLS ON
2. 2set QUOTED_IDENTIFIER ON
3. 3GO
4. 4-- =============================================
5. 5-- Author:
6. 6-- Create date: <2008-07-04>
7. 7-- Description: <按用户名及密码取得管理员信息>
8. 8-- =============================================
9. 9CREATE PROCEDURE [dbo].[Pr_GetAdminByNameAndPassword]
10. 10(
11. 11 @Name Nvarchar(20),
12. 12 @Password Nvarchar(50)
13. 13)
14. 14AS
15. 15SELECT * FROM TAdmin
16. 16WHERE [Name]=@Name
17. 17AND [Password]=@Password

按用户名取得管理员

1. 1set ANSI_NULLS ON
2. 2set QUOTED_IDENTIFIER ON
3. 3GO
4. 4-- =============================================
5. 5-- Author:
6. 6-- Create date: <2008-07-04>
7. 7-- Description: <按用户名取得管理员信息>
8. 8-- =============================================
9. 9CREATE PROCEDURE [dbo].[Pr_GetAdminByName]
10. 10(
11. 11 @Name Nvarchar(20)
12. 12)
13. 13AS
14. 14SELECT * FROM TAdmin
15. 15WHERE [Name]=@Name

取得全部管理员信息

1. 1set ANSI_NULLS ON
2. 2set QUOTED_IDENTIFIER ON
3. 3GO
4. 4-- =============================================
5. 5-- Author:
6. 6-- Create date: <2008-07-04>
7. 7-- Description: <取得全部管理员信息>
8. 8-- =============================================
9. 9CREATE PROCEDURE [dbo].[Pr_GetAllAdmin]
10. 10AS
11. 11SELECT * FROM TAdmin

编写数据库辅助类
由于访问数据库的代码很相似，这里我们仍需要编写一个数据库辅助类，来将常用代码封装起来，方便复用。虽然在这里只使用到了存储过程，但是为了扩展性考虑，这个数据库辅助类仍然包含了通过SQL访问数据库的方法。具体实现如下：

SQLServerDALHelper.cs:

SQLServerDALHelper

1. 1using System;
2. 2using System.Collections.Generic;
3. 3using System.Configuration;
4. 4using System.Data;
5. 5using System.Data.SqlClient;
6. 6
7. 7namespace NGuestBook.SQLServerDAL
8. 8{
9. 9 /**////
10. 10 /// SQLServer数据库操作助手
11. 11 ///
12. 12 public sealed class SQLServerDALHelper
13. 13 {
14. 14 /**////
15. 15 /// 用于连接SQLServer数据库的连接字符串，存于Web.config中
16. 16 ///
17. 17 private static readonly string _sqlConnectionString = ConfigurationManager.AppSettings["SQLServerConnectionString"];
18. 18
19. 19 /**////
20. 20 /// 执行SQL命令，不返回任何值
21. 21 ///
22. 22 /// SQL命令
23. 23 public static void ExecuteSQLNonQurey(string sql)
24. 24 {
25. 25 SqlConnection connection = new SqlConnection(_sqlConnectionString);
26. 26 SqlCommand command = new SqlCommand(sql,connection);
27. 27 connection.Open();
28. 28 command.ExecuteNonQuery();
29. 29 connection.Close();
30. 30 }
31. 31
32. 32 /**////
33. 33 /// 执行SQL命令，并返回SqlDataReader
34. 34 ///
35. 35 /// SQL命令
36. 36 /// 包含查询结果的SqlDataReader
37. 37 public static SqlDataReader ExecuteSQLReader(string sql)
38. 38 {
39. 39 SqlConnection connection = new SqlConnection(_sqlConnectionString);
40. 40 SqlCommand command = new SqlCommand(sql, connection);
41. 41 connection.Open();
42. 42 SqlDataReader sqlReader = command.ExecuteReader();
43. 43 //connection.Close();
44. 44
45. 45 return sqlReader;
46. 46 }
47. 47
48. 48 /**////
49. 49 /// 执行存储过程，不返回任何值
50. 50 ///
51. 51 /// 存储过程名
52. 52 /// 参数
53. 53 public static void ExecuteProcedureNonQurey(string storedProcedureName,IDataParameter[] parameters)
54. 54 {
55. 55 SqlConnection connection = new SqlConnection(_sqlConnectionString);
56. 56 SqlCommand command = new SqlCommand(storedProcedureName,connection);
57. 57 command.CommandType = CommandType.StoredProcedure;
58. 58 if (parameters != null)
59. 59 {
60. 60 foreach (SqlParameter parameter in parameters)
61. 61 {
62. 62 command.Parameters.Add(parameter);
63. 63 }
64. 64 }
65. 65 connection.Open();
66. 66 command.ExecuteNonQuery();
67. 67 connection.Close();
68. 68 }
69. 69
70. 70 /**////
71. 71 /// 执行存储，并返回SqlDataReader
72. 72 ///
73. 73 /// 存储过程名
74. 74 /// 参数
75. 75 /// 包含查询结果的SqlDataReader
76. 76 public static SqlDataReader ExecuteProcedureReader(string storedProcedureName,IDataParameter[] parameters)
77. 77 {
78. 78 SqlConnection connection = new SqlConnection(_sqlConnectionString);
79. 79 SqlCommand command = new SqlCommand(storedProcedureName,connection);
80. 80 command.CommandType = CommandType.StoredProcedure;
81. 81 if (parameters != null)
82. 82 {
83. 83 foreach (SqlParameter parameter in parameters)
84. 84 {
85. 85 command.Parameters.Add(parameter);
86. 86 }
87. 87 }
88. 88 connection.Open();
89. 89 SqlDataReader sqlReader = command.ExecuteReader();
90. 90 //connection.Close();
91. 91
92. 92 return sqlReader;
93. 93 }
94. 94 }
95. 95}

实现数据访问层
最后仍以管理员模块为例，看一下具体数据访问层的实现。

AdminDAL.cs:

AdminDAL

1. 1using System;
2. 2using System.Collections.Generic;
3. 3using System.Text;
4. 4using System.Data;
5. 5using System.Data.SqlClient;
6. 6using NGuestBook.IDAL;
7. 7using NGuestBook.Entity;
8. 8
9. 9namespace NGuestBook.SQLServerDAL
10. 10{
11. 11 public class AdminDAL : IAdminDAL
12. 12 {
13. 13 /**////
14. 14 /// 插入管理员
15. 15 ///
16. 16 /// 管理员实体类
17. 17 /// 是否成功
18. 18 public bool Insert(AdminInfo admin)
19. 19 {
20. 20 SqlParameter[] parameters =
21. 21 {
22. 22 new SqlParameter("@Name",SqlDbType.NVarChar),
23. 23 new SqlParameter("@Password",SqlDbType.NVarChar)
24. 24 };
25. 25 parameters[0].Value = admin.Name;
26. 26 parameters[1].Value = admin.Password;
27. 27 try
28. 28 {
29. 29 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_InsertAdmin", parameters);
30. 30 return true;
31. 31 }
32. 32 catch
33. 33 {
34. 34 return false;
35. 35 }
36. 36 }
37. 37
38. 38 /**////
39. 39 /// 删除管理员
40. 40 ///
41. 41 /// 欲删除的管理员的ID
42. 42 /// 是否成功
43. 43 public bool Delete(int id)
44. 44 {
45. 45 SqlParameter[] parameters =
46. 46 {
47. 47 new SqlParameter("@ID",SqlDbType.Int)
48. 48 };
49. 49 parameters[0].Value = id;
50. 50 try
51. 51 {
52. 52 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_DeleteAdmin", parameters);
53. 53 return true;
54. 54 }
55. 55 catch
56. 56 {
57. 57 return false;
58. 58 }
59. 59 }
60. 60
61. 61 /**////
62. 62 /// 更新管理员信息
63. 63 ///
64. 64 /// 管理员实体类
65. 65 /// 是否成功
66. 66 public bool Update(AdminInfo admin)
67. 67 {
68. 68 SqlParameter[] parameters =
69. 69 {
70. 70 new SqlParameter("@ID",SqlDbType.Int),
71. 71 new SqlParameter("@Name",SqlDbType.NVarChar),
72. 72 new SqlParameter("@Password",SqlDbType.NVarChar)
73. 73 };
74. 74 parameters[0].Value = admin.ID;
75. 75 parameters[1].Value = admin.Name;
76. 76 parameters[2].Value = admin.Password;
77. 77 try
78. 78 {
79. 79 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_UpdateAdmin", parameters);
80. 80 return true;
81. 81 }
82. 82 catch
83. 83 {
84. 84 return false;
85. 85 }
86. 86 }
87. 87
88. 88 /**////
89. 89 /// 按ID取得管理员信息
90. 90 ///
91. 91 /// 管理员ID
92. 92 /// 管理员实体类
93. 93 public AdminInfo GetByID(int id)
94. 94 {
95. 95 SqlParameter[] parameters =
96. 96 {
97. 97 new SqlParameter("@ID",SqlDbType.Int)
98. 98 };
99. 99 parameters[0].Value = id;
100. 100 SqlDataReader dataReader = null;
101. 101 try
102. 102 {
103. 103 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByID", parameters);
104. 104 dataReader.Read();
105. 105 AdminInfo admin = new AdminInfo();
106. 106 admin.ID = (int)dataReader["ID"];
107. 107 admin.Name = (string)dataReader["Name"];
108. 108 admin.Password = (string)dataReader["Password"];
109. 109
110. 110 return admin;
111. 111 }
112. 112 catch
113. 113 {
114. 114 return null;
115. 115 }
116. 116 finally
117. 117 {
118. 118 dataReader.Close();
119. 119 }
120. 120 }
121. 121
122. 122 /**////
123. 123 /// 按用户名及密码取得管理员信息
124. 124 ///
125. 125 /// 用户名
126. 126 /// 密码
127. 127 /// 管理员实体类，不存在时返回null
128. 128 public AdminInfo GetByNameAndPassword(string name, string password)
129. 129 {
130. 130 SqlParameter[] parameters =
131. 131 {
132. 132 new SqlParameter("@Name",SqlDbType.NVarChar),
133. 133 new SqlParameter("@Password",SqlDbType.NVarChar)
134. 134 };
135. 135 parameters[0].Value = name;
136. 136 parameters[1].Value = password;
137. 137 SqlDataReader dataReader = null;
138. 138 try
139. 139 {
140. 140 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByNameAndPassword", parameters);
141. 141 dataReader.Read();
142. 142 AdminInfo admin = new AdminInfo();
143. 143 admin.ID = (int)dataReader["ID"];
144. 144 admin.Name = (string)dataReader["Name"];
145. 145 admin.Password = (string)dataReader["Password"];
146. 146
147. 147 return admin;
148. 148 }
149. 149 catch
150. 150 {
151. 151 return null;
152. 152 }
153. 153 finally
154. 154 {
155. 155 dataReader.Close();
156. 156 }
157. 157 }
158. 158
159. 159 /**////
160. 160 /// 按管理员名取得管理员信息
161. 161 ///
162. 162 /// 管理员名
163. 163 /// 管理员实体类
164. 164 public AdminInfo GetByName(string name)
165. 165 {
166. 166 SqlParameter[] parameters =
167. 167 {
168. 168 new SqlParameter("@Name",SqlDbType.NVarChar)
169. 169 };
170. 170 parameters[0].Value = name;
171. 171 SqlDataReader dataReader = null;
172. 172 try
173. 173 {
174. 174 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByName", parameters);
175. 175 dataReader.Read();
176. 176 AdminInfo admin = new AdminInfo();
177. 177 admin.ID = (int)dataReader["ID"];
178. 178 admin.Name = (string)dataReader["Name"];
179. 179 admin.Password = (string)dataReader["Password"];
180. 180
181. 181 return admin;
182. 182 }
183. 183 catch
184. 184 {
185. 185 return null;
186. 186 }
187. 187 finally
188. 188 {
189. 189 dataReader.Close();
190. 190 }
191. 191 }
192. 192
193. 193 /**////
194. 194 /// 取得全部管理员信息
195. 195 ///
196. 196 /// 管理员实体类集合
197. 197 public IList GetAll()
198. 198 {
199. 199 SqlDataReader dataReader = null;
200. 200 try
201. 201 {
202. 202 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAllAdmin", null);
203. 203 IList adminCollection=new List();
204. 204 while (dataReader.Read())
205. 205 {
206. 206 AdminInfo admin = new AdminInfo();
207. 207 admin.ID = (int)dataReader["ID"];
208. 208 admin.Name = (string)dataReader["Name"];
209. 209 admin.Password = (string)dataReader["Password"];
210. 210 adminCollection.Add(admin);
211. 211 }
212. 212
213. 213 return adminCollection;
214. 214 }
215. 215 catch
216. 216 {
217. 217 return null;
218. 218 }
219. 219 finally
220. 220 {
221. 221 dataReader.Close();
222. 222 }
223. 223 }
224. 224 }
225. 225}