基于.NET平台的分层架构实战（七-外一篇）——对数据访问层第一种实现（Access+SQL）的重构
昨天的文章基于.NET平台的分层架构实战（七）——数据访问层的第一种实现：Access+SQL发布后，很多朋友对我的程序提出了意见和建议，在这里先谢谢你们！！！尤其是 金色海洋（jyk），对我的程序提出了很多建设性的意见。
我大体总结了一下，昨天程序的主要缺点有：
1.Connection对象没有关闭
2.DataReader对象没有关闭
3.相似代码太多，造成代码冗余。
其中第一点问题，目前还没有太好的解决方案，主要是因为Connection一旦关闭，DataReader就无法读取了。而且，Connection对 象应该会自动在适当的时候关闭（通过观察临时文件得出），并且在实际运行时并无影响（在功能上），所以这里没有专门解决。而针对后面两个问题，我使用了如 下解决方案。
对于关闭DataReader的方法，实现起来很简单，在finally里将他关闭就行了。关键是如何去处冗余代码。
经过我的分析，数据访问层的操作可以分为三类：不返回数据，返回单个实体类，返回实体类集合。我将这三种操作的公共部分抽出，写成三个方法放在AccessDALHelper里，类型不同的问题使用泛型解决。
这样做有一个难题，就是不同实体在由DataReader转化为实体类时的代码很不一样，无法抽出。这里，我使用了Strategy模式解决。具体做法是：首先定义一个由DataReader转换为实体类的策略接口，然后为不同的实体编写不同的转换策略，示意图如下：
IDataReaderToEntityStrategy
using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.OleDb;
6
7namespace NGuestBook.AccessDAL
8
{
9
/// <summary>
10
/// 由DataReader转换成实体类的策略接口
11
/// </summary>
12 public interface IDataReaderToEntityStrategy<T>
13 {
14 /// <summary>
15 /// 将DataReader转换为实体类，采用泛型
16 /// </summary>
17 /// <param name="dataReader">包含数据的DataReader对象</param>
18 /// <returns>实体类</returns>
19 T DataReaderToEntity(OleDbDataReader dataReader);
20 }
21
}
AdminDataReaderToEntityStrategyusing System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.OleDb;
6using NGuestBook.Entity;
7
8namespace NGuestBook.AccessDAL
9{
10 /// <summary>
11 /// DataReader到实体类的转换策略-管理员
12 /// 实现上使用Singleton模式，保证全局唯一实例
13 /// </summary>
14 public class AdminDataReaderToEntityStrategy : IDataReaderToEntityStrategy<AdminInfo>
15 {
16 private static AdminDataReaderToEntityStrategy singleInstance = null;
17
18 /// <summary>
19 /// 私有化构造函数，保证无法外部实例化
20 /// </summary>
21 private AdminDataReaderToEntityStrategy() { }
22
23 /// <summary>
24 /// 静态方法，用于取得全局唯一实例
25 /// </summary>
26 /// <returns>全局唯一实例</returns>
27 public static AdminDataReaderToEntityStrategy GetInstance()
28 {
29 if (singleInstance == null)
30 {
31 singleInstance = new AdminDataReaderToEntityStrategy();
32 }
33
34 return singleInstance;
35 }
36
37 /// <summary>
38 /// 由DataReader转换到管理员实体类
39 /// </summary>
40 /// <param name="dataReader">包含数据的DataReader对象</param>
41 /// <returns>管理员实体类</returns>
42 public AdminInfo DataReaderToEntity(OleDbDataReader dataReader)
43 {
44 AdminInfo admin = new AdminInfo();
45 admin.ID = (int)dataReader["ID"];
46 admin.Name = (string)dataReader["Name"];
47 admin.Password = (string)dataReader["Password"];
48
49 return admin;
50 }
51 }
52}
AccessDALHelperusing System;
2using System.Collections.Generic;
3using System.Web;
4using System.Web.Caching;
5using System.Configuration;
6using System.Data;
7using System.Data.OleDb;
8using NGuestBook.Utility;
9
10namespace NGuestBook.AccessDAL
11{
12 /// <summary>
13 /// Access数据库操作助手
14 /// </summary>
15 public sealed class AccessDALHelper
16 {
17 /// <summary>
18 /// 读取Access数据库的连接字符串
19 /// 首先从缓存里读取，如果不存在则到配置文件中读取，并放入缓存
20 /// </summary>
21 /// <returns>Access数据库的连接字符串</returns>
22 private static string GetConnectionString()
23 {
24 if (CacheAccess.GetFromCache("AccessConnectionString") != null)
25 {
26 return CacheAccess.GetFromCache("AccessConnectionString").ToString();
27 }
28 else
29 {
30 string dbPath = ConfigurationManager.AppSettings["AccessPath"];
31 string dbAbsolutePath = HttpContext.Current.Server.MapPath(dbPath);
32 string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];
33
34 CacheDependency fileDependency = new CacheDependency(HttpContext.Current.Server.MapPath("Web.Config"));
35 CacheAccess.SaveToCache("AccessConnectionString", connectionString.Replace("{DBPath}", dbAbsolutePath), fileDependency);
36
37 return connectionString.Replace("{DBPath}", dbAbsolutePath);
38 }
39 }
40
41 /// <summary>
42 /// 执行SQL语句并且不返回任何值
43 /// </summary>
44 /// <param name="SQLCommand">所执行的SQL命令</param>
45 /// <param name="parameters">参数集合</param>
46 public static void ExecuteSQLNonQuery(string SQLCommand, OleDbParameter[] parameters)
47 {
48 OleDbConnection connection = new OleDbConnection(GetConnectionString());
49 OleDbCommand command = new OleDbCommand(SQLCommand, connection);
50
51 for (int i = 0; i < parameters.Length; i++)
52 {
53 command.Parameters.Add(parameters[i]);
54 }
55
56 connection.Open();
57 command.ExecuteNonQuery();
58 connection.Close();
59 }
60
61 /// <summary>
62 /// 执行SQL语句并返回包含查询结果的DataReader
63 /// </summary>
64 /// <param name="SQLCommand">所执行的SQL命令</param>
65 /// <param name="parameters">参数集合</param>
66 /// <returns></returns>
67 public static OleDbDataReader ExecuteSQLDataReader(string SQLCommand, OleDbParameter[] parameters)
68 {
69 OleDbConnection connection = new OleDbConnection(GetConnectionString());
70 OleDbCommand command = new OleDbCommand(SQLCommand, connection);
71
72 for (int i = 0; i < parameters.Length; i++)
73 {
74 command.Parameters.Add(parameters[i]);
75 }
76
77 connection.Open();
78 OleDbDataReader dataReader = command.ExecuteReader();
79 //connection.Close();
80
81 return dataReader;
82 }
83
84 /// <summary>
85 /// 执行不需要返回数据的操作
86 /// </summary>
87 /// <param name="SQLCommand">SQL命令</param>
88 /// <param name="parameters">参数</param>
89 /// <returns>是否成功</returns>
90 public static bool OperateNonData(string SQLCommand, OleDbParameter[] parameters)
91 {
92 try
93 {
94 ExecuteSQLNonQuery(SQLCommand, parameters);
95 return true;
96 }
97 catch
98 {
99 return false;
100 }
101 }
102
103 /// <summary>
104 /// 执行返回单个实体类的操作
105 /// </summary>
106 /// <typeparam name="T">实体类类型</typeparam>
107 /// <param name="SQLCommand">SQL命令</param>
108 /// <param name="parameters">参数</param>
109 /// <param name="strategy">DataReader到实体类的转换策略</param>
110 /// <returns>实体类</returns>
111 public static T OperateEntity<T>(string SQLCommand, OleDbParameter[] parameters, IDataReaderToEntityStrategy<T> strategy)
112 {
113 OleDbDataReader dataReader = ExecuteSQLDataReader(SQLCommand, parameters);
114 try
115 {
116 if (!dataReader.HasRows)
117 {
118 throw new Exception();
119 }
120
121 dataReader.Read();
122 return strategy.DataReaderToEntity(dataReader);
123 }
124 catch
125 {
126 return default(T);
127 }
128 finally
129 {
130 dataReader.Close();
131 }
132 }
133
134 /// <summary>
135 /// 执行返回实体类集合的操作
136 /// </summary>
137 /// <typeparam name="T">实体类类型</typeparam>
138 /// <param name="SQLCommand">SQL命令</param>
139 /// <param name="parameters">参数</param>
140 /// <param name="strategy">DataReader到实体类的转换策略</param>
141 /// <returns>实体类</returns>
142 public static IList<T> OperateEntityCollection<T>(string SQLCommand, OleDbParameter[] parameters, IDataReaderToEntityStrategy<T> strategy)
143 {
144 OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, null);
145 try
146 {
147 if (!dataReader.HasRows)
148 {
149 throw new Exception();
150 }
151
152 IList<T> entityCollection = new List<T>();
153 int i = 0;
154 while (dataReader.Read())
155 {
156 entityCollection.Add(strategy.DataReaderToEntity(dataReader));
157 i++;
158 }
159
160 return entityCollection;
161 }
162 catch
163 {
164 return default(IList<T>);
165 }
166 finally
167 {
168 dataReader.Close();
169 }
170 }
171 }
172}
AdminDALusing System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.OleDb;
6using NGuestBook.IDAL;
7using NGuestBook.Entity;
8
9namespace NGuestBook.AccessDAL
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 string SQLCommand = "insert into [TAdmin]([Name],[Password]) values(@name,@password)";
21 OleDbParameter[] parameters ={
22 new OleDbParameter("name",OleDbType.VarChar,20),
23 new OleDbParameter("password",OleDbType.VarChar,50)
24 };
25 parameters[0].Value = admin.Name;
26 parameters[1].Value = admin.Password;
27
28 return AccessDALHelper.OperateNonData(SQLCommand, parameters);
29 }
30
31 /// <summary>
32 /// 删除管理员
33 /// </summary>
34 /// <param name="id">欲删除的管理员的ID</param>
35 /// <returns>是否成功</returns>
36 public bool Delete(int id)
37 {
38 string SQLCommand = "delete from [TAdmin] where [ID]=@id";
39 OleDbParameter[] parameters ={
40 new OleDbParameter("id",OleDbType.Integer)
41 };
42 parameters[0].Value = id;
43
44 return AccessDALHelper.OperateNonData(SQLCommand, parameters);
45 }
46
47 /// <summary>
48 /// 更新管理员信息
49 /// </summary>
50 /// <param name="admin">管理员实体类</param>
51 /// <returns>是否成功</returns>
52 public bool Update(AdminInfo admin)
53 {
54 string SQLCommand = "update [TAdmin] set [Name]=@name,[Password]=@password where [ID]=@id";
55 OleDbParameter[] parameters ={
56 new OleDbParameter("id",OleDbType.Integer),
57 new OleDbParameter("name",OleDbType.VarChar,20),
58 new OleDbParameter("password",OleDbType.VarChar,50)
59 };
60 parameters[0].Value = admin.ID;
61 parameters[1].Value = admin.Name;
62 parameters[2].Value = admin.Password;
63
64 return AccessDALHelper.OperateNonData(SQLCommand, parameters);
65 }
66
67 /// <summary>
68 /// 按ID取得管理员信息
69 /// </summary>
70 /// <param name="id">管理员ID</param>
71 /// <returns>管理员实体类</returns>
72 public AdminInfo GetByID(int id)
73 {
74 string SQLCommand = "select * from [TAdmin] where [ID]=@id";
75 OleDbParameter[] parameters ={
76 new OleDbParameter("id",OleDbType.Integer)
77 };
78 parameters[0].Value = id;
79
80 return AccessDALHelper.OperateEntity<AdminInfo>(SQLCommand, parameters, AdminDataReaderToEntityStrategy.GetInstance());
81 }
82
83 /// <summary>
84 /// 按用户名及密码取得管理员信息
85 /// </summary>
86 /// <param name="name">用户名</param>
87 /// <param name="password">密码</param>
88 /// <returns>管理员实体类，不存在时返回null</returns>
89 public AdminInfo GetByNameAndPassword(string name, string password)
90 {
91 string SQLCommand = "select * from [TAdmin] where [Name]=@name and [Password]=@password";
92 OleDbParameter[] parameters ={
93 new OleDbParameter("name",OleDbType.VarChar,20),
94 new OleDbParameter("password",OleDbType.VarChar,50)
95 };
96 parameters[0].Value = name;
97 parameters[1].Value = password;
98
99 return AccessDALHelper.OperateEntity<AdminInfo>(SQLCommand, parameters, AdminDataReaderToEntityStrategy.GetInstance());
100 }
101
102 /// <summary>
103 /// 按管理员名取得管理员信息
104 /// </summary>
105 /// <param name="name">管理员名</param>
106 /// <returns>管理员实体类</returns>
107 public AdminInfo GetByName(string name)
108 {
109 string SQLCommand = "select * from [TAdmin] where [Name]=@name";
110 OleDbParameter[] parameters ={
111 new OleDbParameter("name",OleDbType.VarChar,20)
112 };
113 parameters[0].Value = name;
114
115 return AccessDALHelper.OperateEntity<AdminInfo>(SQLCommand, parameters, AdminDataReaderToEntityStrategy.GetInstance());
116 }
117
118 /// <summary>
119 /// 取得全部管理员信息
120 /// </summary>
121 /// <returns>管理员实体类集合</returns>
122 public IList<AdminInfo> GetAll()
123 {
124 string SQLCommand = "select * from [TAdmin]";
125
126 return AccessDALHelper.OperateEntityCollection<AdminInfo>(SQLCommand, null, AdminDataReaderToEntityStrategy.GetInstance());
127 }
128 }
129}
我大体总结了一下，昨天程序的主要缺点有：
1.Connection对象没有关闭
2.DataReader对象没有关闭
3.相似代码太多，造成代码冗余。
其中第一点问题，目前还没有太好的解决方案，主要是因为Connection一旦关闭，DataReader就无法读取了。而且，Connection对 象应该会自动在适当的时候关闭（通过观察临时文件得出），并且在实际运行时并无影响（在功能上），所以这里没有专门解决。而针对后面两个问题，我使用了如 下解决方案。
对于关闭DataReader的方法，实现起来很简单，在finally里将他关闭就行了。关键是如何去处冗余代码。
经过我的分析，数据访问层的操作可以分为三类：不返回数据，返回单个实体类，返回实体类集合。我将这三种操作的公共部分抽出，写成三个方法放在AccessDALHelper里，类型不同的问题使用泛型解决。
这样做有一个难题，就是不同实体在由DataReader转化为实体类时的代码很不一样，无法抽出。这里，我使用了Strategy模式解决。具体做法是：首先定义一个由DataReader转换为实体类的策略接口，然后为不同的实体编写不同的转换策略，示意图如下：
可以看出，所有转换策略都要实现IDataReaderToEntityStrategy接口，并且每个策略都有一个自组合，这是以为他们都要实现Singleton模式。而AccessDALHelper与具体策略无关，仅与接口耦合。
下面来看一下具体代码：
首先是IDataReaderToEntityStrategy接口
IDataReaderToEntityStrategy.cs:
IDataReaderToEntityStrategyusing System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
using System.Data.OleDb;6
7
namespace NGuestBook.AccessDAL8
{9
/// <summary>
10
/// 由DataReader转换成实体类的策略接口11
/// </summary>12
public interface IDataReaderToEntityStrategy<T>13
{14
/// <summary>15
/// 将DataReader转换为实体类，采用泛型16
/// </summary>17
/// <param name="dataReader">包含数据的DataReader对象</param>18
/// <returns>实体类</returns>19
T DataReaderToEntity(OleDbDataReader dataReader);20
}21
}
然后以Admin为例，看一下策略的具体实现：
AdminDataReaderToEntityStrategy.cs:
AdminDataReaderToEntityStrategyusing System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
using System.Data.OleDb;6
using NGuestBook.Entity;7
8
namespace NGuestBook.AccessDAL9
{10
/// <summary>11
/// DataReader到实体类的转换策略-管理员12
/// 实现上使用Singleton模式，保证全局唯一实例13
/// </summary>14
public class AdminDataReaderToEntityStrategy : IDataReaderToEntityStrategy<AdminInfo>15
{16
private static AdminDataReaderToEntityStrategy singleInstance = null;17
18
/// <summary>19
/// 私有化构造函数，保证无法外部实例化20
/// </summary>21
private AdminDataReaderToEntityStrategy() { }22
23
/// <summary>24
/// 静态方法，用于取得全局唯一实例25
/// </summary>26
/// <returns>全局唯一实例</returns>27
public static AdminDataReaderToEntityStrategy GetInstance()28
{29
if (singleInstance == null)30
{31
singleInstance = new AdminDataReaderToEntityStrategy();32
}33
34
return singleInstance;35
}36
37
/// <summary>38
/// 由DataReader转换到管理员实体类39
/// </summary>40
/// <param name="dataReader">包含数据的DataReader对象</param>41
/// <returns>管理员实体类</returns>42
public AdminInfo DataReaderToEntity(OleDbDataReader dataReader)43
{44
AdminInfo admin = new AdminInfo();45
admin.ID = (int)dataReader["ID"];46
admin.Name = (string)dataReader["Name"];47
admin.Password = (string)dataReader["Password"];48
49
return admin;50
}51
}52
}
可以看到，这里实现了一个单件模式。下一个，是重构后的AccessDALHelper，增加了三个方法。
AccessDALHelper.cs:
AccessDALHelperusing System;2
using System.Collections.Generic;3
using System.Web;4
using System.Web.Caching;5
using System.Configuration;6
using System.Data;7
using System.Data.OleDb;8
using NGuestBook.Utility;9
10
namespace NGuestBook.AccessDAL11
{12
/// <summary>13
/// Access数据库操作助手14
/// </summary>15
public sealed class AccessDALHelper16
{17
/// <summary>18
/// 读取Access数据库的连接字符串19
/// 首先从缓存里读取，如果不存在则到配置文件中读取，并放入缓存20
/// </summary>21
/// <returns>Access数据库的连接字符串</returns>22
private static string GetConnectionString()23
{24
if (CacheAccess.GetFromCache("AccessConnectionString") != null)25
{26
return CacheAccess.GetFromCache("AccessConnectionString").ToString();27
}28
else29
{30
string dbPath = ConfigurationManager.AppSettings["AccessPath"];31
string dbAbsolutePath = HttpContext.Current.Server.MapPath(dbPath);32
string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];33
34
CacheDependency fileDependency = new CacheDependency(HttpContext.Current.Server.MapPath("Web.Config"));35
CacheAccess.SaveToCache("AccessConnectionString", connectionString.Replace("{DBPath}", dbAbsolutePath), fileDependency);36
37
return connectionString.Replace("{DBPath}", dbAbsolutePath);38
}39
}40
41
/// <summary>42
/// 执行SQL语句并且不返回任何值43
/// </summary>44
/// <param name="SQLCommand">所执行的SQL命令</param>45
/// <param name="parameters">参数集合</param>46
public static void ExecuteSQLNonQuery(string SQLCommand, OleDbParameter[] parameters)47
{48
OleDbConnection connection = new OleDbConnection(GetConnectionString());49
OleDbCommand command = new OleDbCommand(SQLCommand, connection);50
51
for (int i = 0; i < parameters.Length; i++)52
{53
command.Parameters.Add(parameters[i]);54
}55
56
connection.Open();57
command.ExecuteNonQuery();58
connection.Close();59
}60
61
/// <summary>62
/// 执行SQL语句并返回包含查询结果的DataReader63
/// </summary>64
/// <param name="SQLCommand">所执行的SQL命令</param>65
/// <param name="parameters">参数集合</param>66
/// <returns></returns>67
public static OleDbDataReader ExecuteSQLDataReader(string SQLCommand, OleDbParameter[] parameters)68
{69
OleDbConnection connection = new OleDbConnection(GetConnectionString());70
OleDbCommand command = new OleDbCommand(SQLCommand, connection);71
72
for (int i = 0; i < parameters.Length; i++)73
{74
command.Parameters.Add(parameters[i]);75
}76
77
connection.Open();78
OleDbDataReader dataReader = command.ExecuteReader();79
//connection.Close();80
81
return dataReader;82
}83
84
/// <summary>85
/// 执行不需要返回数据的操作86
/// </summary>87
/// <param name="SQLCommand">SQL命令</param>88
/// <param name="parameters">参数</param>89
/// <returns>是否成功</returns>90
public static bool OperateNonData(string SQLCommand, OleDbParameter[] parameters)91
{92
try93
{94
ExecuteSQLNonQuery(SQLCommand, parameters);95
return true;96
}97
catch98
{99
return false;100
}101
}102
103
/// <summary>104
/// 执行返回单个实体类的操作105
/// </summary>106
/// <typeparam name="T">实体类类型</typeparam>107
/// <param name="SQLCommand">SQL命令</param>108
/// <param name="parameters">参数</param>109
/// <param name="strategy">DataReader到实体类的转换策略</param>110
/// <returns>实体类</returns>111
public static T OperateEntity<T>(string SQLCommand, OleDbParameter[] parameters, IDataReaderToEntityStrategy<T> strategy)112
{113
OleDbDataReader dataReader = ExecuteSQLDataReader(SQLCommand, parameters);114
try115
{116
if (!dataReader.HasRows)117
{118
throw new Exception();119
}120
121
dataReader.Read();122
return strategy.DataReaderToEntity(dataReader);123
}124
catch125
{126
return default(T);127
}128
finally129
{130
dataReader.Close();131
}132
}133
134
/// <summary>135
/// 执行返回实体类集合的操作136
/// </summary>137
/// <typeparam name="T">实体类类型</typeparam>138
/// <param name="SQLCommand">SQL命令</param>139
/// <param name="parameters">参数</param>140
/// <param name="strategy">DataReader到实体类的转换策略</param>141
/// <returns>实体类</returns>142
public static IList<T> OperateEntityCollection<T>(string SQLCommand, OleDbParameter[] parameters, IDataReaderToEntityStrategy<T> strategy)143
{144
OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, null);145
try146
{147
if (!dataReader.HasRows)148
{149
throw new Exception();150
}151
152
IList<T> entityCollection = new List<T>();153
int i = 0;154
while (dataReader.Read())155
{156
entityCollection.Add(strategy.DataReaderToEntity(dataReader));157
i++;158
}159
160
return entityCollection;161
}162
catch163
{164
return default(IList<T>);165
}166
finally167
{168
dataReader.Close();169
}170
}171
}172
}
最后以Admin为例，看一下简化后的数据访问层实现：
AdminDAL.cs:
AdminDALusing System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
using System.Data.OleDb;6
using NGuestBook.IDAL;7
using NGuestBook.Entity;8
9
namespace NGuestBook.AccessDAL10
{11
public class AdminDAL : IAdminDAL12
{13
/// <summary>14
/// 插入管理员15
/// </summary>16
/// <param name="admin">管理员实体类</param>17
/// <returns>是否成功</returns>18
public bool Insert(AdminInfo admin)19
{20
string SQLCommand = "insert into [TAdmin]([Name],[Password]) values(@name,@password)";21
OleDbParameter[] parameters ={22
new OleDbParameter("name",OleDbType.VarChar,20),23
new OleDbParameter("password",OleDbType.VarChar,50)24
};25
parameters[0].Value = admin.Name;26
parameters[1].Value = admin.Password;27
28
return AccessDALHelper.OperateNonData(SQLCommand, parameters);29
}30
31
/// <summary>32
/// 删除管理员33
/// </summary>34
/// <param name="id">欲删除的管理员的ID</param>35
/// <returns>是否成功</returns>36
public bool Delete(int id)37
{38
string SQLCommand = "delete from [TAdmin] where [ID]=@id";39
OleDbParameter[] parameters ={40
new OleDbParameter("id",OleDbType.Integer)41
};42
parameters[0].Value = id;43
44
return AccessDALHelper.OperateNonData(SQLCommand, parameters);45
}46
47
/// <summary>48
/// 更新管理员信息49
/// </summary>50
/// <param name="admin">管理员实体类</param>51
/// <returns>是否成功</returns>52
public bool Update(AdminInfo admin)53
{54
string SQLCommand = "update [TAdmin] set [Name]=@name,[Password]=@password where [ID]=@id";55
OleDbParameter[] parameters ={56
new OleDbParameter("id",OleDbType.Integer),57
new OleDbParameter("name",OleDbType.VarChar,20),58
new OleDbParameter("password",OleDbType.VarChar,50)59
};60
parameters[0].Value = admin.ID;61
parameters[1].Value = admin.Name;62
parameters[2].Value = admin.Password;63
64
return AccessDALHelper.OperateNonData(SQLCommand, parameters);65
}66
67
/// <summary>68
/// 按ID取得管理员信息69
/// </summary>70
/// <param name="id">管理员ID</param>71
/// <returns>管理员实体类</returns>72
public AdminInfo GetByID(int id)73
{74
string SQLCommand = "select * from [TAdmin] where [ID]=@id";75
OleDbParameter[] parameters ={76
new OleDbParameter("id",OleDbType.Integer)77
};78
parameters[0].Value = id;79
80
return AccessDALHelper.OperateEntity<AdminInfo>(SQLCommand, parameters, AdminDataReaderToEntityStrategy.GetInstance());81
}82
83
/// <summary>84
/// 按用户名及密码取得管理员信息85
/// </summary>86
/// <param name="name">用户名</param>87
/// <param name="password">密码</param>88
/// <returns>管理员实体类，不存在时返回null</returns>89
public AdminInfo GetByNameAndPassword(string name, string password)90
{91
string SQLCommand = "select * from [TAdmin] where [Name]=@name and [Password]=@password";92
OleDbParameter[] parameters ={93
new OleDbParameter("name",OleDbType.VarChar,20),94
new OleDbParameter("password",OleDbType.VarChar,50)95
};96
parameters[0].Value = name;97
parameters[1].Value = password;98
99
return AccessDALHelper.OperateEntity<AdminInfo>(SQLCommand, parameters, AdminDataReaderToEntityStrategy.GetInstance());100
}101
102
/// <summary>103
/// 按管理员名取得管理员信息104
/// </summary>105
/// <param name="name">管理员名</param>106
/// <returns>管理员实体类</returns>107
public AdminInfo GetByName(string name)108
{109
string SQLCommand = "select * from [TAdmin] where [Name]=@name";110
OleDbParameter[] parameters ={111
new OleDbParameter("name",OleDbType.VarChar,20)112
};113
parameters[0].Value = name;114
115
return AccessDALHelper.OperateEntity<AdminInfo>(SQLCommand, parameters, AdminDataReaderToEntityStrategy.GetInstance());116
}117
118
/// <summary>119
/// 取得全部管理员信息120
/// </summary>121
/// <returns>管理员实体类集合</returns>122
public IList<AdminInfo> GetAll()123
{124
string SQLCommand = "select * from [TAdmin]";125
126
return AccessDALHelper.OperateEntityCollection<AdminInfo>(SQLCommand, null, AdminDataReaderToEntityStrategy.GetInstance());127
}128
}129
}
浙公网安备 33010602011771号