基於.NET平台的分層架構實戰（八）—數據訪問層的第二種實現：SQLServer+存儲過程
在上一篇中，討論了使用SQL構建數據訪問層的方法，並且針對的是Access數據庫。而這一篇中，將要創建一個針對SQLServer數據庫的數據訪問層，並且配合存儲過程實現。
曾經有朋友問我使用SQL和存儲過程在效率上的差別，慚愧的是我對這方面沒有研究，也沒有實際做過測試。通過查閱資料，發現在一般情況下，存儲過程的效率由於使用SQL，但是也不絕對，也發現有的朋友測試時發現在特定情況下SQL的效率優於存儲過程，所以這個問題不能一概而論。
好，廢話不多說，這裡先列出使用存儲過程構建數據訪問層的一般步驟：
1.創建新工程
2.創建數據庫
3.編寫相應存儲過程
4.編寫數據庫輔助類
5.實現數據訪問層
創建新工程
在開始所有開發工作前，我們需要在解決方案下新建一個工程，叫SQLServerDAL，用於存放所有SQLServer數據訪問層的代碼。
創建數據庫
首先，我們要根據前文設計的數據庫，在SQLServer中創建相應的數據庫及數據表。我使用的是SQLServer2005，使用企業管理器創建，創建方法不再贅述。
編寫存儲過程
數據庫創建完成後，我們就要編寫存儲過程了。由於數據訪問層接口已經確定，所以需要哪些存儲過程也很好確定。例如數據訪問層接口中有一個添加管理員方法，那麼就一定有一個存儲過程實現這個功能。
還是以管理員模塊為例，經過簡單分析，需要一下存儲過程：
插入管理員記錄
刪除管理員記錄
更新管理員信息
按ID取得管理員記錄
按用戶名及密碼取得管理員記錄
按用戶名取得管理員記錄
取得全部管理員記錄
創建這些存儲過程的SQL代碼如下：
插入管理員記錄
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <T2噬菌體>
-- Create date: <2008-07-04>
-- Description: <插入管理員記錄>
-- =============================================
CREATE PROCEDURE [dbo].[Pr_InsertAdmin]
(
@Name Nvarchar(20),
@Password Nvarchar(50)
)
AS
INSERT INTO TAdmin
(
[Name],
[Password]
)
VALUES
(
@Name,
@Password
)
刪除管理員記錄
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4-- =============================================
5-- Author: <T2噬菌體>
6-- Create date: <2008-07-04>
7-- Description: <刪除管理員記錄>
8-- =============================================
9CREATE PROCEDURE [dbo].[Pr_DeleteAdmin]
10(
11 @ID Int
12)
13AS
14DELETE FROM TAdmin
15WHERE [ID]=@ID
修改管理員信息
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4-- =============================================
5-- Author: <T2噬菌體>
6-- Create date: <2008-07-04>
7-- Description: <修改管理員記錄>
8-- =============================================
9CREATE PROCEDURE [dbo].[Pr_UpdateAdmin]
10(
11 @ID Int,
12 @Name Nvarchar(20),
13 @Password Nvarchar(50)
14)
15AS
16UPDATE TAdmin
17SET
18[Name]=@Name,
19[Password]=@Password
20WHERE [ID]=@ID
按ID取得管理員
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4-- =============================================
5-- Author: <T2噬菌體>
6-- Create date: <2008-07-04>
7-- Description: <按ID取得管理員信息>
8-- =============================================
9CREATE PROCEDURE [dbo].[Pr_GetAdminByID]
10(
11 @ID Int
12)
13AS
14SELECT * FROM TAdmin
15WHERE [ID]=@ID
按用戶名和密碼取得管理員
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4-- =============================================
5-- Author: <T2噬菌體>
6-- Create date: <2008-07-04>
7-- Description: <按用戶名及密碼取得管理員信息>
8-- =============================================
9CREATE PROCEDURE [dbo].[Pr_GetAdminByNameAndPassword]
10(
11 @Name Nvarchar(20),
12 @Password Nvarchar(50)
13)
14AS
15SELECT * FROM TAdmin
16WHERE [Name]=@Name
17AND [Password]=@Password
按用戶名取得管理員
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4-- =============================================
5-- Author: <T2噬菌體>
6-- Create date: <2008-07-04>
7-- Description: <按用戶名取得管理員信息>
8-- =============================================
9CREATE PROCEDURE [dbo].[Pr_GetAdminByName]
10(
11 @Name Nvarchar(20)
12)
13AS
14SELECT * FROM TAdmin
15WHERE [Name]=@Name
取得全部管理員信息
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4-- =============================================
5-- Author: <T2噬菌體>
6-- Create date: <2008-07-04>
7-- Description: <取得全部管理員信息>
8-- =============================================
9CREATE PROCEDURE [dbo].[Pr_GetAllAdmin]
10AS
11SELECT * FROM TAdmin
編寫數據庫輔助類
由於訪問數據庫的代碼很相似，這裡我們仍需要編寫一個數據庫輔助類，來將常用代碼封裝起來，方便復用。雖然在這裡只使用到了存儲過程，但是為了擴展性考慮，這個數據庫輔助類仍然包含了通過SQL訪問數據庫的方法。具體實現如下：
SQLServerDALHelper.cs:
SQLServerDALHelper
1using System;
2using System.Collections.Generic;
3using System.Configuration;
4using System.Data;
5using System.Data.SqlClient;
6
7namespace NGuestBook.SQLServerDAL
8{
9 /**//// <summary>
10 /// SQLServer數據庫操作助手
11 /// </summary>
12 public sealed class SQLServerDALHelper
13 {
14 /**//// <summary>
15 /// 用於連接SQLServer數據庫的連接字符串，存於Web.config中
16 /// </summary>
17 private static readonly string _sqlConnectionString = ConfigurationManager.AppSettings["SQLServerConnectionString"];
18
19 /**//// <summary>
20 /// 執行SQL命令，不返回任何值
21 /// </summary>
22 /// <param name="sql">SQL命令</param>
23 public static void ExecuteSQLNonQurey(string sql)
24 {
25 SqlConnection connection = new SqlConnection(_sqlConnectionString);
26 SqlCommand command = new SqlCommand(sql,connection);
27 connection.Open();
28 command.ExecuteNonQuery();
29 connection.Close();
30 }
31
32 /**//// <summary>
33 /// 執行SQL命令，並返回SqlDataReader
34 /// </summary>
35 /// <param name="sql">SQL命令</param>
36 /// <returns>包含查詢結果的SqlDataReader</returns>
37 public static SqlDataReader ExecuteSQLReader(string sql)
38 {
39 SqlConnection connection = new SqlConnection(_sqlConnectionString);
40 SqlCommand command = new SqlCommand(sql, connection);
41 connection.Open();
42 SqlDataReader sqlReader = command.ExecuteReader();
43 //connection.Close();
44
45 return sqlReader;
46 }
47
48 /**//// <summary>
49 /// 執行存儲過程，不返回任何值
50 /// </summary>
51 /// <param name="storedProcedureName">存儲過程名</param>
52 /// <param name="parameters">參數</param>
53 public static void ExecuteProcedureNonQurey(string storedProcedureName,IDataParameter[] parameters)
54 {
55 SqlConnection connection = new SqlConnection(_sqlConnectionString);
56 SqlCommand command = new SqlCommand(storedProcedureName,connection);
57 command.CommandType = CommandType.StoredProcedure;
58 if (parameters != null)
59 {
60 foreach (SqlParameter parameter in parameters)
61 {
62 command.Parameters.Add(parameter);
63 }
64 }
65 connection.Open();
66 command.ExecuteNonQuery();
67 connection.Close();
68 }
69
70 /**//// <summary>
71 /// 執行存儲，並返回SqlDataReader
72 /// </summary>
73 /// <param name="storedProcedureName">存儲過程名</param>
74 /// <param name="parameters">參數</param>
75 /// <returns>包含查詢結果的SqlDataReader</returns>
76 public static SqlDataReader ExecuteProcedureReader(string storedProcedureName,IDataParameter[] parameters)
77 {
78 SqlConnection connection = new SqlConnection(_sqlConnectionString);
79 SqlCommand command = new SqlCommand(storedProcedureName,connection);
80 command.CommandType = CommandType.StoredProcedure;
81 if (parameters != null)
82 {
83 foreach (SqlParameter parameter in parameters)
84 {
85 command.Parameters.Add(parameter);
86 }
87 }
88 connection.Open();
89 SqlDataReader sqlReader = command.ExecuteReader();
90 //connection.Close();
91
92 return sqlReader;
93 }
94 }
95}
實現數據訪問層
最後仍以管理員模塊為例，看一下具體數據訪問層的實現。
AdminDAL.cs:
AdminDAL
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.SqlClient;
6using NGuestBook.IDAL;
7using NGuestBook.Entity;
8
9namespace NGuestBook.SQLServerDAL
10{
11 public class AdminDAL : IAdminDAL
12 {
13 /**//// <summary>
14 /// 插入管理員
15 /// </summary>
16 /// <param name="admin">管理員實體類</param>
17 /// <returns>是否成功</returns>
18 public bool Insert(AdminInfo admin)
19 {
20 SqlParameter[] parameters =
21 {
22 new SqlParameter("@Name",SqlDbType.NVarChar),
23 new SqlParameter("@Password",SqlDbType.NVarChar)
24 };
25 parameters[0].Value = admin.Name;
26 parameters[1].Value = admin.Password;
27 try
28 {
29 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_InsertAdmin", parameters);
30 return true;
31 }
32 catch
33 {
34 return false;
35 }
36 }
37
38 /**//// <summary>
39 /// 刪除管理員
40 /// </summary>
41 /// <param name="id">欲刪除的管理員的ID</param>
42 /// <returns>是否成功</returns>
43 public bool Delete(int id)
44 {
45 SqlParameter[] parameters =
46 {
47 new SqlParameter("@ID",SqlDbType.Int)
48 };
49 parameters[0].Value = id;
50 try
51 {
52 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_DeleteAdmin", parameters);
53 return true;
54 }
55 catch
56 {
57 return false;
58 }
59 }
60
61 /**//// <summary>
62 /// 更新管理員信息
63 /// </summary>
64 /// <param name="admin">管理員實體類</param>
65 /// <returns>是否成功</returns>
66 public bool Update(AdminInfo admin)
67 {
68 SqlParameter[] parameters =
69 {
70 new SqlParameter("@ID",SqlDbType.Int),
71 new SqlParameter("@Name",SqlDbType.NVarChar),
72 new SqlParameter("@Password",SqlDbType.NVarChar)
73 };
74 parameters[0].Value = admin.ID;
75 parameters[1].Value = admin.Name;
76 parameters[2].Value = admin.Password;
77 try
78 {
79 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_UpdateAdmin", parameters);
80 return true;
81 }
82 catch
83 {
84 return false;
85 }
86 }
87
88 /**//// <summary>
89 /// 按ID取得管理員信息
90 /// </summary>
91 /// <param name="id">管理員ID</param>
92 /// <returns>管理員實體類</returns>
93 public AdminInfo GetByID(int id)
94 {
95 SqlParameter[] parameters =
96 {
97 new SqlParameter("@ID",SqlDbType.Int)
98 };
99 parameters[0].Value = id;
100 SqlDataReader dataReader = null;
101 try
102 {
103 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByID", parameters);
104 dataReader.Read();
105 AdminInfo admin = new AdminInfo();
106 admin.ID = (int)dataReader["ID"];
107 admin.Name = (string)dataReader["Name"];
108 admin.Password = (string)dataReader["Password"];
109
110 return admin;
111 }
112 catch
113 {
114 return null;
115 }
116 finally
117 {
118 dataReader.Close();
119 }
120 }
121
122 /**//// <summary>
123 /// 按用戶名及密碼取得管理員信息
124 /// </summary>
125 /// <param name="name">用戶名</param>
126 /// <param name="password">密碼</param>
127 /// <returns>管理員實體類，不存在時返回null</returns>
128 public AdminInfo GetByNameAndPassword(string name, string password)
129 {
130 SqlParameter[] parameters =
131 {
132 new SqlParameter("@Name",SqlDbType.NVarChar),
133 new SqlParameter("@Password",SqlDbType.NVarChar)
134 };
135 parameters[0].Value = name;
136 parameters[1].Value = password;
137 SqlDataReader dataReader = null;
138 try
139 {
140 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByNameAndPassword", parameters);
141 dataReader.Read();
142 AdminInfo admin = new AdminInfo();
143 admin.ID = (int)dataReader["ID"];
144 admin.Name = (string)dataReader["Name"];
145 admin.Password = (string)dataReader["Password"];
146
147 return admin;
148 }
149 catch
150 {
151 return null;
152 }
153 finally
154 {
155 dataReader.Close();
156 }
157 }
158
159 /**//// <summary>
160 /// 按管理員名取得管理員信息
161 /// </summary>
162 /// <param name="name">管理員名</param>
163 /// <returns>管理員實體類</returns>
164 public AdminInfo GetByName(string name)
165 {
166 SqlParameter[] parameters =
167 {
168 new SqlParameter("@Name",SqlDbType.NVarChar)
169 };
170 parameters[0].Value = name;
171 SqlDataReader dataReader = null;
172 try
173 {
174 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByName", parameters);
175 dataReader.Read();
176 AdminInfo admin = new AdminInfo();
177 admin.ID = (int)dataReader["ID"];
178 admin.Name = (string)dataReader["Name"];
179 admin.Password = (string)dataReader["Password"];
180
181 return admin;
182 }
183 catch
184 {
185 return null;
186 }
187 finally
188 {
189 dataReader.Close();
190 }
191 }
192
193 /**//// <summary>
194 /// 取得全部管理員信息
195 /// </summary>
196 /// <returns>管理員實體類集合</returns>
197 public IList<AdminInfo> GetAll()
198 {
199 SqlDataReader dataReader = null;
200 try
201 {
202 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAllAdmin", null);
203 IList<AdminInfo> adminCollection=new List<AdminInfo>();
204 while (dataReader.Read())
205 {
206 AdminInfo admin = new AdminInfo();
207 admin.ID = (int)dataReader["ID"];
208 admin.Name = (string)dataReader["Name"];
209 admin.Password = (string)dataReader["Password"];
210 adminCollection.Add(admin);
211 }
212
213 return adminCollection;
214 }
215 catch
216 {
217 return null;
218 }
219 finally
220 {
221 dataReader.Close();
222 }
223 }
224 }
225}
