基于.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代码如下:
插入管理员记录
- set ANSI_NULLS ON
- set QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Author:
- -- Create date:
- -- 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:
- 6-- Create date:
- 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:
- 6-- Create date:
- 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:
- 6-- Create date:
- 7-- Description:
- 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:
- 6-- Create date:
- 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:
- 6-- Create date:
- 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:
- 6-- Create date:
- 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 /**////
- 10 /// SQLServer数据库操作助手
- 11 ///
- 12 public sealed class SQLServerDALHelper
- 13 {
- 14 /**////
- 15 /// 用于连接SQLServer数据库的连接字符串,存于Web.config中
- 16 ///
- 17 private static readonly string _sqlConnectionString = ConfigurationManager.AppSettings["SQLServerConnectionString"];
- 18
- 19 /**////
- 20 /// 执行SQL命令,不返回任何值
- 21 ///
- 22 /// SQL命令
- 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 /**////
- 33 /// 执行SQL命令,并返回SqlDataReader
- 34 ///
- 35 /// SQL命令
- 36 ///
包含查询结果的SqlDataReader - 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 /**////
- 49 /// 执行存储过程,不返回任何值
- 50 ///
- 51 /// 存储过程名
- 52 /// 参数
- 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 /**////
- 71 /// 执行存储,并返回SqlDataReader
- 72 ///
- 73 /// 存储过程名
- 74 /// 参数
- 75 ///
包含查询结果的SqlDataReader - 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 /**////
- 14 /// 插入管理员
- 15 ///
- 16 /// 管理员实体类
- 17 ///
是否成功 - 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 /**////
- 39 /// 删除管理员
- 40 ///
- 41 /// 欲删除的管理员的ID
- 42 ///
是否成功 - 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 /**////
- 62 /// 更新管理员信息
- 63 ///
- 64 /// 管理员实体类
- 65 ///
是否成功 - 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 /**////
- 89 /// 按ID取得管理员信息
- 90 ///
- 91 /// 管理员ID
- 92 ///
管理员实体类 - 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 /**////
- 123 /// 按用户名及密码取得管理员信息
- 124 ///
- 125 /// 用户名
- 126 /// 密码
- 127 ///
管理员实体类,不存在时返回null - 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 /**////
- 160 /// 按管理员名取得管理员信息
- 161 ///
- 162 /// 管理员名
- 163 ///
管理员实体类 - 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 /**////
- 194 /// 取得全部管理员信息
- 195 ///
- 196 ///
管理员实体类集合 - 197 public IList
GetAll() - 198 {
- 199 SqlDataReader dataReader = null;
- 200 try
- 201 {
- 202 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAllAdmin", null);
- 203 IList
adminCollection=new List (); - 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}