基于.NET平台的分层架构实战（七）——数据访问层的第一种实现:Access+SQL

 1 using  System;
 2 using  System.Web;
 3 using  System.Web.Caching;
 4 using  System.Configuration;
 5 using  System.Data;
 6 using  System.Data.OleDb;
 7 using  NGuestBook.Utility;
  8
 9 namespace  NGuestBook.AccessDAL
10 {
11      ///  

12      ///  Access数据库操作助手
13      ///  

14      public   sealed   class  AccessDALHelper
15     {
16          ///  

17          ///  读取Access数据库的连接字符串
18          ///  首先从缓存里读取，如果不存在则到配置文件中读取，并放入缓存
19          ///  

20          ///   Access数据库的连接字符串
21          private   static   string  GetConnectionString()
22         {
23              if  (CacheAccess.GetFromCache( " AccessConnectionString " )  !=   null )
24             {
25                  return  CacheAccess.GetFromCache( " AccessConnectionString " ).ToString();
26             }
27              else
28             {
29                  string  dbPath  =  ConfigurationManager.AppSettings[ " AccessPath " ];
30                  string  dbAbsolutePath  =  HttpContext.Current.Server.MapPath(dbPath);
31                  string  connectionString  =  ConfigurationManager.AppSettings[ " AccessConnectionString " ];
32
33                 CacheDependency fileDependency  =   new  CacheDependency(HttpContext.Current.Server.MapPath( " Web.Config " ));
34                 CacheAccess.SaveToCache( " AccessConnectionString " , connectionString.Replace( " {DBPath} " lt;/span>, dbAbsolutePath), fileDependency);
35
36                return connectionString.Replace("{DBPath}", dbAbsolutePath);
37            }
38        }
39
40        /// 

41        /// 执行SQL语句并且不返回任何值
42        /// 

43        /// 所执行的SQL命令
44        /// 参数集合
45        public static void ExecuteSQLNonQuery(string SQLCommand,OleDbParameter[] parameters)
46        {
47            OleDbConnection connection = new OleDbConnection(GetConnectionString());
48            OleDbCommand command = new OleDbCommand(SQLCommand, connection);
49
50            for (int i = 0; i < parameters.Length; i++)
51            {
52                command.Parameters.Add(parameters[i]);
53            }
54
55            connection.Open();
56            command.ExecuteNonQuery();
57            connection.Close();
58        }
59
60        /// 

61        /// 执行SQL语句并返回包含查询结果的DataReader
62        /// 

63        /// 所执行的SQL命令
64        /// 参数集合
65        /// 
66        public static OleDbDataReader ExecuteSQLDataReader(string SQLCommand,OleDbParameter[] parameters)
67        {
68            OleDbConnection connection = new OleDbConnection(GetConnectionString());
69            OleDbCommand command = new OleDbCommand(SQLCommand, connection);
70
71            for (int i = 0; i < parameters.Length; i++)
72            {
73                command.Parameters.Add(parameters[i]);
74            }
75
76            connection.Open();
77            OleDbDataReader dataReader = command.ExecuteReader();
78            //connection.Close();
79
80            return dataReader;
81        }
82    }
83}

  1 using  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.AccessDAL
  10 {
  11      public   class  AdminDAL : IAdminDAL
  12     {
  13          ///  

  14          ///  插入管理员
  15          ///  

  16          ///   管理员实体类
  17          ///   是否成功
  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 " ,admin.Name),
  23                  new  OleDbParameter( " password " ,admin.Password)
  24             };
  25
  26              try
  27             {
  28                 AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
  29                  return   true ;
  30             }
  31              catch
  32             {
  33                  return   false ;
  34             }
  35         }
  36
  37          ///  

  38          ///  删除管理员
  39          ///  

  40          ///   欲删除的管理员的ID
  41          ///   是否成功
  42          public   bool  Delete( int  id)
  43         {
  44              string  SQLCommand  =   " delete from [TAdmin] where [ID]=@id " ;
  45             OleDbParameter[] parameters  = {
  46                  new  OleDbParameter( " id " ,id)
  47             };
  48
  49              try
  50             {
  51                 AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
  52                  return   true ;
  53             }
  54              catch
  55             {
  56                  return   false ;
  57             }
  58         }
  59
  60          ///  

  61          ///  更新管理员信息
  62          ///  

  63          ///   管理员实体类
  64          ///   是否成功
  65          public   bool  Update(AdminInfo admin)
  66         {
  67              string  SQLCommand  =   " update [TAdmin] set [Name]=@name,[Password]=@password where [ID]=@id " ;
  68             OleDbParameter[] parameters  = {
  69                  new  OleDbParameter( " id " ,admin.ID),
  70                  new  OleDbParameter( " name " ,admin.Name),
  71                  new  OleDbParameter( " password " ,admin.Password)
  72             };
  73
  74              try
  75             {
  76                 AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
  77                  return   true ;
  78             }
  79              catch
  80             {
  81                  return   false ;
  82             }
  83         }
  84
  85          ///  

  86          ///  按ID取得管理员信息
  87          ///  

  88          ///   管理员ID
  89          ///   管理员实体类
  90          public  AdminInfo GetByID( int  id)
  91         {
  92              string  SQLCommand  =   " select * from [TAdmin] where [ID]=@id " ;
  93             OleDbParameter[] parameters  = {
  94                  new  OleDbParameter( " id " ,id)
  95             };
  96
  97              try
  98             {
  99                 OleDbDataReader dataReader  =  AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
100                  if  ( ! dataReader.HasRows)
101                 {
102                      throw   new  Exception();
103                 }
104
105                 AdminInfo admin  =   new  AdminInfo();
106                 dataReader.Read();
107                 admin.ID = ( int )dataReader[ " ID " ];
108                 admin.Name = ( string )dataReader[ " Name " ];
109                 admin.Password = ( string )dataReader[ " Password " ];
110
111                  return  admin;
112             }
113              catch
114             {
115                  return   null ;
116             }
117         }
118
119          ///  

120          ///  按用户名及密码取得管理员信息
121          ///  

122          ///   用户名
123          ///   密码
124          ///   管理员实体类，不存在时返回null
125          public  AdminInfo GetByNameAndPassword( string  name,  string  password)
126         {
127              string  SQLCommand  =   " select * from [TAdmin] where [Name]=@name and [Password]=@password " ;
128             OleDbParameter[] parameters  = {
129                  new  OleDbParameter( " name " ,name),
130                  new  OleDbParameter( " password " ,password),
131             };
132
133              try
134             {
135                 OleDbDataReader dataReader  =  AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
136                  if  ( ! dataReader.HasRows)
137                 {
138                      throw   new  Exception();
139                 }
140
141                 AdminInfo admin  =   new  AdminInfo();
142                 dataReader.Read();
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         }
154
155          ///  

156          ///  按管理员名取得管理员信息
157          ///  

158          ///   管理员名
159          ///   管理员实体类
160          public  AdminInfo GetByName( string  name)
161         {
162              string  SQLCommand  =   " select * from [TAdmin] where [Name]=@name " ;
163             OleDbParameter[] parameters  = {
164                  new  OleDbParameter( " name " ,name),
165             };
166
167              try
168             {
169                 OleDbDataReader dataReader  =  AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
170                  if  ( ! dataReader.HasRows)
171                 {
172                      throw   new  Exception();
173                 }
174
175                 AdminInfo admin  =   new  AdminInfo();
176                 dataReader.Read();
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         }
188
189          ///  

190          ///  取得全部管理员信息
191          ///  

192          ///   管理员实体类集合
193          public  IList < AdminInfo >  GetAll()
194         {
195              string  SQLCommand  =   " select * from [TAdmin] " ;
196              try
197             {
198                 OleDbDataReader dataReader  =  AccessDALHelper.ExecuteSQLDataReader(SQLCommand,  null );
199                  if  ( ! dataReader.HasRows)
200                 {
201                      throw   new  Exception();
202                 }
203
204                 IList < AdminInfo >  adminCollection  =   new  List < AdminInfo > ();
205                  int  i  =   0 ;
206                  while  (dataReader.Read())
207                 {
208                     AdminInfo admin  =   new  AdminInfo();
209                     admin.ID  =  ( int )dataReader[ " ID " ];
210                     admin.Name  =  ( string )dataReader[ " Name " ];
211                     admin.Password  =  ( string )dataReader[ " Password " ];
212
213                     adminCollection.Add(admin);
214                     i ++ ;
215                 }
216
217                  return  adminCollection;
218             }
219              catch
220             {
221                  return   null ;
222             }
223         }
224     }
225 }