基于.NET平台的分层架构实战（八）——数据访问层的第二种实现：SQLServer+存储过程

最新推荐文章于 2024-09-21 19:05:25 发布

DaLuanXiangYu

最新推荐文章于 2024-09-21 19:05:25 发布

阅读量407

点赞数

分类专栏：分层架构文章标签： sqlserver 存储 .net 平台 parameters 数据库

分层架构专栏收录该内容

15 篇文章 1 订阅

订阅专栏

在上一篇中，讨论了使用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
)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <T2噬菌体>
-- Create date: <2008-07-04>
-- Description:    <删除管理员记录>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ Pr_DeleteAdmin ]
(
      @ID Int
)
AS
DELETE FROM TAdmin
WHERE [ ID ] = @ID

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <T2噬菌体>
-- Create date: <2008-07-04>
-- Description:    <修改管理员记录>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ Pr_UpdateAdmin ]
(
      @ID Int ,
      @Name Nvarchar ( 20 ),
      @Password Nvarchar ( 50 )
)
AS
UPDATE TAdmin
SET
[ Name ] = @Name ,
[ Password ] = @Password
WHERE [ ID ] = @ID

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <T2噬菌体>
-- Create date: <2008-07-04>
-- Description:    <按ID取得管理员信息>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ Pr_GetAdminByID ]
(
      @ID Int
)
AS
SELECT * FROM TAdmin
WHERE [ ID ] = @ID

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <T2噬菌体>
-- Create date: <2008-07-04>
-- Description:    <按用户名及密码取得管理员信息>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ Pr_GetAdminByNameAndPassword ]
(
      @Name Nvarchar ( 20 ),
      @Password Nvarchar ( 50 )
)
AS
SELECT * FROM TAdmin
WHERE [ Name ] = @Name
AND [ Password ] = @Password

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <T2噬菌体>
-- Create date: <2008-07-04>
-- Description:    <按用户名取得管理员信息>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ Pr_GetAdminByName ]
(
      @Name Nvarchar ( 20 )
)
AS
SELECT * FROM TAdmin
WHERE [ Name ] = @Name

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <T2噬菌体>
-- Create date: <2008-07-04>
-- Description: <取得全部管理员信息>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ Pr_GetAllAdmin ]
AS
SELECT * FROM TAdmin

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

SQLServerDALHelper.cs:

1 using System;
2 using System.Collections.Generic;
3 using System.Configuration;
4 using System.Data;
5 using System.Data.SqlClient;
6
7 namespace 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:

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 using NGuestBook.IDAL;
  7 using NGuestBook.Entity;
   8
  9 namespace 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 }