C# Access2003操作辅助类(AccessHelper.cs)

众所周知,在与.Net数据交互中,SqlServer是最好的数据库选择。而在单机中,Access也占据很大份额。今天奉上自己的AccessHelper类库,用作Access2003数据库的基本操作,这里没有加入什么接口、泛型、抽象类……等等超高深概念,这是基础中的基础,给各位当踏脚石的。

请看代码:

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data.OleDb;
  4 using System.Data;
  5 using System.Collections;
  6 using System.Reflection;
  7 using System.Runtime.InteropServices;
  8 
  9     /// <summary>
 10     /// Access 数据库的操作类库。代码原创。
 11     /// </summary>
 12     public class AccessHelper
 13     {
 14         #region[字段]
 15         private string accesspath = string.Empty;
 16         private string accesspwd = string.Empty;
 17 
 18         private string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb";
 19         //"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\1.mdb;User ID='admin';Password='rr';
 20         OleDbConnection connect = null;
 21         OleDbCommand command = null;
 22         #endregion
 23         #region[属性]
 24         /// <summary>
 25         /// Access数据库连接字符串
 26         /// </summary>
 27         public string ConnectionString
 28         {
 29             get { return connectionString; }
 30             set { connectionString = value; }
 31         }
 32 
 33         /// <summary>
 34         /// Access数据库路径
 35         /// </summary>
 36         public string AccessPath
 37         {
 38             get { return accesspath; }
 39             set { accesspath = value; }
 40         }
 41         #endregion
 42         #region[构造函数]
 43         /// <summary>
 44         /// 构造函数,初始化
 45         /// </summary>
 46         /// <param name="FilePath">Access文件路径</param>
 47         public AccessHelper(string FilePath)
 48         {
 49             ToFullRootPath(FilePath);
 50             ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessPath;
 51             connect = new OleDbConnection(ConnectionString);
 52         }
 53         /// <summary>
 54         /// 构造函数,初始化
 55         /// </summary>
 56         /// <param name="FilePath">Access文件路径</param>
 57         /// <param name="PassWord">密码</param>
 58         public AccessHelper(string FilePath, string PassWord)
 59         {
 60             accesspwd = PassWord;
 61             ToFullRootPath(FilePath);
 62             ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};User ID=Admin;Jet OLEDB:Database Password={1};", AccessPath, accesspwd);
 63             connect = new OleDbConnection(ConnectionString);
 64         }
 65         #endregion
 66         #region[私有函数]
 67         private void Open()
 68         {
 69             try
 70             {
 71                 if (connect.State != System.Data.ConnectionState.Open)
 72                 {
 73                     connect.Open();
 74                 }
 75 
 76             }
 77             catch (Exception ex)
 78             {
 79                 throw (new Exception(ex.Message));
 80             }
 81         }
 82 
 83         private void Close()
 84         {
 85             try
 86             {
 87                 if (connect.State != System.Data.ConnectionState.Closed)
 88                 {
 89                     connect.Close();
 90                 }
 91 
 92             }
 93             catch (Exception ex)
 94             {
 95                 throw (new Exception(ex.Message));
 96             }
 97         }
 98 
 99         private void ToFullRootPath(string path)
100         {
101             if (System.IO.Path.IsPathRooted(path))
102             {
103                 AccessPath = path;
104             }
105             else
106             {
107                 AccessPath = AppDomain.CurrentDomain.BaseDirectory + path;
108             }
109         }
110         #endregion
111 
112         #region[数据库简单操作]
113         /// <summary>
114         /// 测试是否能够连通
115         /// </summary>
116         /// <returns>布尔值</returns>
117         public bool ConnectTest()
118         {
119             try
120             {
121                 connect.Open();
122             }
123             catch
124             {
125                 connect.Close();
126                 return false;
127             }
128             return true;
129         }
130 
131         /// <summary>
132         /// 执行无返回的Sql语句,如插入,删除,更新
133         /// </summary>
134         /// <param name="sqlstr">SQL语句</param>
135         /// <returns>受影响的条数,出错则产生异常</returns>
136         public int ExecuteNonQuery(string sqlstr)
137         {
138             try
139             {
140                 Open();
141                 command = new OleDbCommand(sqlstr, connect);
142                 int num = command.ExecuteNonQuery();
143                 command.Parameters.Clear();
144                 Close();
145                 return num;
146             }
147             catch
148             { throw; }
149         }
150 
151         /// <summary>
152         /// 执行查询语句,返回DataSet
153         /// </summary>
154         /// <param name="sqlstr">Sql</param>
155         /// <returns>DataSet数据集,出错则产生异常</returns>
156         public DataSet ReturnDataSet(string sqlstr)
157         {
158             DataSet ds = new DataSet();
159             try
160             {
161                 OleDbDataAdapter adapter = new OleDbDataAdapter(sqlstr, connect);//adapter可自动打开数据库连接,不用Open()
162                 adapter.Fill(ds, "Obj");
163                 adapter.Dispose();
164             }
165             catch (Exception)
166             {
167                 throw;
168             }
169             return ds;
170 
171         }
172 
173         /// <summary>
174         /// 执行查询语句,返回DataTable
175         /// </summary>
176         /// <param name="sqlstr">Sqk</param>
177         /// <returns>DataTable数据表,出错则产生异常</returns>
178         public DataTable ReturnDataTable(string sqlstr)
179         {
180             try
181             {
182                 return ReturnDataSet(sqlstr).Tables[0];
183             }
184             catch { throw; }
185         }
186 
187         /// <summary>
188         /// 执行查询语句,返回DataReader
189         /// </summary>
190         /// <param name="sqlstr">Sql</param>
191         /// <returns>DataReader,出错则产生异常</returns>
192         public OleDbDataReader ReturnDataReader(string sqlstr)
193         {
194             try
195             {
196                 Open();
197                 command = new OleDbCommand(sqlstr, connect);
198                 OleDbDataReader myReader = command.ExecuteReader();
199                 command.Parameters.Clear();
200                 Close();
201                 return myReader;
202             }
203             catch (System.Data.SqlClient.SqlException e)
204             {
205                 throw new Exception(e.Message,e);
206             }
207 
208         }
209 
210         /// <summary>
211         /// 执行事务,出错则产生异常
212         /// </summary>
213         /// <param name="SQLStringList">事务集合</param>
214         public void ExecuteSqlTran(ArrayList SQLStringList)
215         {
216 
217             Open();
218             command = new OleDbCommand();
219             command.Connection = connect;
220             OleDbTransaction tx = connect.BeginTransaction();
221             command.Transaction = tx;
222             try
223             {
224                 for (int n = 0; n < SQLStringList.Count; n++)
225                 {
226                     string strsql = SQLStringList[n].ToString();
227                     if (strsql.Trim().Length > 1)
228                     {
229                         command.CommandText = strsql;
230                         command.ExecuteNonQuery();
231                     }
232                 }
233                 tx.Commit();
234                 Close();
235             }
236             catch (Exception)
237             {
238                 tx.Rollback();
239                 Close();
240                 throw;
241             }
242         }
243 
244         /// <summary>
245         /// Access获取数据库中的所有表
246         /// </summary>
247         /// <returns>表集合</returns>
248         public string[] GetTables()
249         {
250             List<string> Ls = new List<string>();
251 
252             Open();
253             DataTable dt = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" }); //利用oledbconnection的getoledbschematable来获得数据库的结构
254             Close();
255             foreach (DataRow dr in dt.Rows)
256             {
257                 Ls.Add((string)dr["table_name"]);
258             }
259 
260             return Ls.ToArray();
261         }
262 
263         /// <summary>
264         /// 获取指定表的所有列
265         /// </summary>
266         /// <param name="TableName">表名</param>
267         /// <returns>列集合</returns>
268         public string[] GetColumns(string TableName)
269         {
270             Open();
271             DataTable dt = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
272             Close();
273             List<string> Ls = new List<string>();
274             for (int i = 0; i < dt.Rows.Count; i++)
275             {
276                 Ls.Add(dt.Rows[i]["COLUMN_NAME"].ToString());//["COLUMN_NAME"]);
277             }
278             return Ls.ToArray();
279 
280         }
281         #endregion
282 
283         #region[数据库整体操作]
284         /// <summary>
285         /// 创建Access2003版本的数据库,可设密码
286         /// 
287         /// 
288         /// Exception
289         /// 通常创建数据库失败时会抛出基本类型的Exception,请注意在使用该方法时提供接收异常的容器
290         /// </summary>
291         /// <param name="mdbFilePath">Access数据库地址</param>
292         /// <param name="password">密码</param>
293         /// <returns></returns>
294         public static  bool CreateMDB(string mdbFilePath, string password)
295         {
296             if (System.IO.File.Exists(mdbFilePath))
297             {
298                 throw (new Exception("当前目录下已存在该数据库,为保证安全性,请先删除后在新建!"));                
299             }
300             try
301             {
302                 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;";
303                 if (password == null || password.Trim() == "")
304                 {
305                     connStr += "Data Source=" + mdbFilePath;
306                 }
307                 else
308                 {
309                     connStr += "Jet OLEDB:Database Password=" + password + ";Data Source=" + mdbFilePath;
310                 }
311                 object objCatalog = Activator.CreateInstance(Type.GetTypeFromProgID("ADOX.Catalog"));
312                 object[] oParams = new object[] { connStr };
313                 objCatalog.GetType().InvokeMember("Create", BindingFlags.InvokeMethod, null, objCatalog, oParams);
314                 Marshal.ReleaseComObject(objCatalog);
315                 objCatalog = null;
316                 return true;
317             }
318             catch
319             {
320                 throw;
321             }
322         }
323 
324          /// <summary>
325         /// 创建Access2003版本的数据库
326         /// 
327         /// 
328         /// Exception
329         /// 通常创建数据库失败时会抛出基本类型的Exception,请注意在使用该方法时提供接收异常的容器
330         /// </summary>
331         /// <param name="mdbFilePath">Access数据库地址</param>
332         /// <returns></returns>
333         public static bool CreateMDB(string mdbFilePath)
334         {
335             try
336             {
337                 CreateMDB(mdbFilePath, null);
338                 return true;
339             }
340             catch
341             {
342                 throw; 
343             }
344         }
345 
346         /// <summary>
347         /// 设置或修改Access数据库的访问密码
348         /// 
349         /// Exception
350         /// 设置失败时会弹出异常,请注意提供容器
351         /// </summary>
352         /// <param name="mdbFilePath">数据库文件路径</param>
353         /// <param name="oldPwd">旧密码</param>
354         /// <param name="newPwd">新密码</param>
355         /// <returns></returns>
356         public static bool SetMDBPassword(string mdbFilePath, string oldPwd, string newPwd)
357         {
358             string connStr = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;","Mode=Share Deny Read|Share Deny Write;", /*独占模式*/"Jet OLEDB:Database Password=" + oldPwd + ";Data Source=" + mdbFilePath);
359             using (OleDbConnection conn = new OleDbConnection(connStr))
360             {
361                 try
362                 {
363                     conn.Open();
364                     //如果密码为空时,请不要写方括号,只写一个null即可
365                     string sqlOldPwd = (oldPwd == null || oldPwd.Trim() == "") ? "null" : "[" + oldPwd + "]";
366                     string sqlNewPwd = (newPwd == null || newPwd.Trim() == "") ? "null" : "[" + newPwd + "]";
367                     OleDbCommand cmd = new OleDbCommand(string.Concat("ALTER DATABASE PASSWORD ", sqlNewPwd, " ", sqlOldPwd),conn);
368                     cmd.ExecuteNonQuery();
369                     conn.Close();
370                     return true;
371                 }
372                 catch
373                 {
374                     throw;
375                 }
376             }
377         }
378 
379         /// <summary>
380         /// 设置或修改Access数据库的访问密码
381         /// 
382         /// Exception
383         /// 设置失败时会弹出异常,请注意提供容器
384         /// </summary>
385         /// <param name="newPwd">新密码</param>
386         /// <returns></returns>
387         public bool SetMDBPassword(string newPwd)
388         {
389             string connStr = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;", "Mode=Share Deny Read|Share Deny Write;", /*独占模式*/"Jet OLEDB:Database Password=" + accesspwd + ";Data Source=" + AccessPath);
390             using (OleDbConnection conn = new OleDbConnection(connStr))
391             {
392                 try
393                 {
394                     conn.Open();
395                     //如果密码为空时,请不要写方括号,只写一个null即可
396                     string sqlOldPwd = (accesspwd == null || accesspwd.Trim() == "") ? "null" : "[" + accesspwd + "]";
397                     string sqlNewPwd = (newPwd == null || newPwd.Trim() == "") ? "null" : "[" + newPwd + "]";
398                     OleDbCommand cmd = new OleDbCommand(string.Concat("ALTER DATABASE PASSWORD ", sqlNewPwd, " ", sqlOldPwd), conn);
399                     cmd.ExecuteNonQuery();
400                     conn.Close();
401                     return true;
402                 }
403                 catch
404                 {
405                     throw;
406                 }
407             }
408         }
409         #endregion
410 
411     }


代码不具备美观性,大家可以拍砖!!呵呵。也希望共同进步!

posted @ 2012-09-06 23:50 蜗牛往前走 阅读(...) 评论(...) 编辑 收藏