基于.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:
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:
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:
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:
7. 7-- Description:   
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:
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:
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:
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}

复制代码