基于 .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 onset quoted_identifier ongo-- =============================================-- author: -- create date: -- description: -- =============================================create procedure [dbo].[pr_insertadmin] ( @name nvarchar(20), @password nvarchar(50))asinsert into tadmin( [name], [password])values( @name, @password)复制代码删除管理员记录
1set ansi_nulls on2set quoted_identifier on3go4-- =============================================5-- author: 6-- create date: 7-- description: 8-- =============================================9create procedure [dbo].[pr_deleteadmin] 10(11 @id int12)13as14delete from tadmin15where [id]=@id复制代码修改管理员信息
1set ansi_nulls on2set quoted_identifier on3go4-- =============================================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)15as16update tadmin17set18[name]=@name,19[password]=@password20where [id]=@id复制代码按id取得管理员
1set ansi_nulls on2set quoted_identifier on3go4-- =============================================5-- author: 6-- create date: 7-- description: 8-- =============================================9create procedure [dbo].[pr_getadminbyid] 10(11 @id int12)13as14select * from tadmin15where [id]=@id复制代码按用户名和密码取得管理员
1set ansi_nulls on2set quoted_identifier on3go4-- =============================================5-- author: 6-- create date: 7-- description: 8-- =============================================9create procedure [dbo].[pr_getadminbynameandpassword] 10(11 @name nvarchar(20),12 @password nvarchar(50)13)14as15select * from tadmin16where [name]=@name17and [password]=@password复制代码按用户名取得管理员
1set ansi_nulls on2set quoted_identifier on3go4-- =============================================5-- author: 6-- create date: 7-- description: 8-- =============================================9create procedure [dbo].[pr_getadminbyname] 10(11 @name nvarchar(20)12)13as14select * from tadmin15where [name]=@name复制代码取得全部管理员信息
1set ansi_nulls on2set quoted_identifier on3go4-- =============================================5-- author: 6-- create date: 7-- description: 8-- =============================================9create procedure [dbo].[pr_getalladmin] 10as11select * from tadmin复制代码编写数据库辅助类
由于访问数据库的代码很相似，这里我们仍需要编写一个数据库辅助类，来将常用代码封装起来，方便复用。虽然在这里只使用到了存储过程，但是为了扩展性考虑，这个数据库辅助类仍然包含了通过sql访问数据库的方法。具体实现如下：
sqlserverdalhelper.cs:
sqlserverdalhelper
1using system;2using system.collections.generic;3using system.configuration;4using system.data;5using system.data.sqlclient;67namespace nguestbook.sqlserverdal8{9 /**//// 10 /// sqlserver数据库操作助手11 /// 12 public sealed class sqlserverdalhelper13 {14 /**//// 15 /// 用于连接sqlserver数据库的连接字符串，存于web.config中16 /// 17 private static readonly string _sqlconnectionstring = configurationmanager.appsettings[sqlserverconnectionstring];1819 /**//// 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 }3132 /**//// 33 /// 执行sql命令，并返回sqldatareader34 /// 35 /// sql命令36 /// 包含查询结果的sqldatareader37 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();4445 return sqlreader;46 }4748 /**//// 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 }6970 /**//// 71 /// 执行存储，并返回sqldatareader72 /// 73 /// 存储过程名74 /// 参数75 /// 包含查询结果的sqldatareader76 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();9192 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.sqlserverdal10{11 public class admindal : iadmindal12 {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 try28 {29 sqlserverdalhelper.executeprocedurenonqurey(pr_insertadmin, parameters);30 return true;31 }32 catch33 {34 return false;35 }36 }3738 /**//// 39 /// 删除管理员40 /// 41 /// 欲删除的管理员的id42 /// 是否成功43 public bool delete(int id)44 {45 sqlparameter[] parameters =46 {47 new sqlparameter(@id,sqldbtype.int)48 };49 parameters[0].value = id;50 try51 {52 sqlserverdalhelper.executeprocedurenonqurey(pr_deleteadmin, parameters);53 return true;54 }55 catch56 {57 return false;58 }59 }6061 /**//// 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 try78 {79 sqlserverdalhelper.executeprocedurenonqurey(pr_updateadmin, parameters);80 return true;81 }82 catch83 {84 return false;85 }86 }8788 /**//// 89 /// 按id取得管理员信息90 /// 91 /// 管理员id92 /// 管理员实体类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 try102 {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];109110 return admin;111 }112 catch113 {114 return null;115 }116 finally117 {118 datareader.close();119 }120 }121122 /**//// 123 /// 按用户名及密码取得管理员信息124 /// 125 /// 用户名126 /// 密码127 /// 管理员实体类，不存在时返回null128 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 try139 {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];146147 return admin;148 }149 catch150 {151 return null;152 }153 finally154 {155 datareader.close();156 }157 }158159 /**//// 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 try173 {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];180181 return admin;182 }183 catch184 {185 return null;186 }187 finally188 {189 datareader.close();190 }191 }192193 /**//// 194 /// 取得全部管理员信息195 /// 196 /// 管理员实体类集合197 public ilist getall()198 {199 sqldatareader datareader = null;200 try201 {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 }212213 return admincollection;214 }215 catch216 {217 return null;218 }219 finally220 {221 datareader.close();222 }223 }224 }225}复制代码