AccessHelper - .net

在做数据库应用时，有时觉得Oracle太大，只是开发小程序的话，没有太大必要，那就用Access好了。在一些前辈的总结下，我也对搜集到的资料进行了整理，现在贴出来，以备不时之需。

using System; using System.Collections.Generic; using System.Text; using System.Data.OleDb; using System.Configuration; using System.Data; using System.IO; namespace Test.数据操作层 { class AccessHelper { //数据库连接对像 private OleDbConnection conn = null; //数据库命令对像 private OleDbCommand cmd = new OleDbCommand(); //DataAdapter对像 private OleDbDataAdapter adapter = new OleDbDataAdapter(); /// <summary> /// 该实例使用的数据库连接字符串 /// </summary> private string connectionString = ""; private OleDbDataReader reader = null; private OleDbTransaction trans = null; public DbType DatabaseType { get { return new System.Data.DbType(); } } public IDataReader DataReader { get { return this.reader; } set { reader = (OleDbDataReader)value; } } public IDbConnection DbConnection { get { if (conn == null) { conn = new OleDbConnection(connectionString); } return conn; } } public IDbCommand DataCommand { get { return this.cmd; } set { cmd = (OleDbCommand)value; } } public IDbDataAdapter DataAdapter { get { if (adapter != null) { return adapter; } else { adapter = new OleDbDataAdapter(); return adapter; } } } /// <summary> /// 打开数据库连接 /// </summary> public void Open() { if (connectionString == "") { string dataSource = @"E:/数据库管理系统/Test.mdb"; connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + dataSource; } if (conn == null) { conn = new OleDbConnection(connectionString); conn.Open(); } else { if (conn.State == ConnectionState.Closed) conn.Open(); } } /// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); //conn.Dispose(); //cmd.Dispose(); } } /// <summary> /// 开始执行数据库事务 /// </summary> /// <returns></returns> public IDbTransaction BeginTransaction() { Open(); trans = conn.BeginTransaction(); return trans; } /// <summary> /// 开始数据库连接 /// </summary> /// <param name="isolationLevel"></param> /// <returns></returns> public IDbTransaction BeginTransaction(IsolationLevel isolationLevel) { Open(); trans = conn.BeginTransaction(isolationLevel); return trans; } /// <summary> /// 执行无返回值的操作 /// </summary> /// <param name="commandText">数据操作字符串</param> /// <returns>返回影响的行数</returns> public int ExecuteNonQuery(string commandText) { try { Open(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = commandText; int val = cmd.ExecuteNonQuery(); return val; } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 执行无返回值的数据操作命令 /// </summary> /// <returns>返回影响的行数</returns> public int ExecuteNonQuery() { try { Open(); cmd.Connection = conn; int val = cmd.ExecuteNonQuery(); return val; } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 执行数据库操作命令 /// </summary> /// <param name="commandText">数数据库接操命令</param> /// <param name="trans">事务对像</param> /// <returns>受影响的行数</returns> public int ExecuteNonQuery(string commandText, IDbTransaction trans) { try { Open(); cmd.Connection = conn; cmd.Transaction = (OleDbTransaction)trans; cmd.CommandType = CommandType.Text; cmd.CommandText = commandText; int val = cmd.ExecuteNonQuery(); return val; } catch (OleDbException e) { trans.Rollback(); this.Close(); throw e; } } /// <summary> /// 更新整个DataTable /// </summary> /// <param name="table">要更新的DataTable</param> /// <param name="tableName">更新的表名</param> /// <returns></returns> public int UpdateDataTable(DataTable table, string tableName) { try { Open(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from " + tableName; OleDbDataAdapter da = new OleDbDataAdapter(cmd); OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da); da.UpdateCommand = cmdBuilder.GetUpdateCommand(); int val = da.Update(table); da.Dispose(); return val; } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 插入整个DataTable /// </summary> /// <param name="table">要插入的DataTable</param> /// <param name="tableName">插入的表名</param> /// <returns></returns> public int InsertDataTable(DataTable table, string tableName) { try { Open(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from " + tableName; OleDbDataAdapter da = new OleDbDataAdapter(cmd); OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da); da.InsertCommand = cmdBuilder.GetInsertCommand(); int val = da.Update(table); da.Dispose(); return val; } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 执行行数据库操作命令 /// </summary> /// <returns>返回单个操作结果</returns> public object ExecuteScalar() { try { Open(); cmd.Connection = conn; return cmd.ExecuteScalar(); } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 执行行数据库操作命令 /// </summary> /// <param name="commandText">数据库操作命令字符串</param> /// <returns>返回单个操作结果</returns> public object ExecuteScalar(string commandText) { try { Open(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = commandText; object obj = null; obj = cmd.ExecuteScalar(); return obj; } catch (OleDbException e) { throw e; } finally { Close(); } } /// <summary> /// 执行数据库操作命令 /// </summary> /// <param name="commandText">数据库操作命令字符串</param> /// <param name="aTableName">填充数据集中的表名</param> /// <returns>执行结果数据集</returns> public DataSet ExecuteDataSet(string commandText, string aTableName) { try { Open(); DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = commandText; adapter.SelectCommand = cmd; adapter.SelectCommand.Connection = conn; adapter.Fill(ds, aTableName); return ds; } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 根据DataCommand执行命令 /// 调用之前需将DataCommand赋值 /// </summary> /// <param name="aTableName"></param> /// <returns>成功时返回DataSet</returns> public DataSet ExecuteDataSet(string aTableName) { try { Open(); DataSet ds = new DataSet(); adapter.SelectCommand = cmd; adapter.SelectCommand.Connection = conn; adapter.Fill(ds, aTableName); return ds; } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 执行数据库操作命令 /// </summary> /// <param name="commandText">数据库操作命令字符串</param> /// <returns>返回数据读取器DataReader</returns> public IDataReader ExecuteReader(string commandText) { try { Open(); cmd.Connection = this.conn; cmd.CommandText = commandText; reader = cmd.ExecuteReader(); return reader; } catch (OleDbException e) { this.Close(); throw e; } } /// <summary> /// 返回指定sql语句的DataTable /// </summary> /// <param name="commandText"></param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText) { DataTable dt = new DataTable(); try { Open(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = commandText; OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.SelectCommand = cmd; da.Fill(dt); } catch (OleDbException e) { throw e; } finally { Close(); } return dt; } /// <summary> /// 关闭数据读取器 /// </summary> public void ReaderClose() { reader.Close(); } /// <summary> /// 回滚事务 /// </summary> public void RollBack() { this.trans.Rollback(); } /// <summary> /// 获取数据库中所有表的表名，以字符串数组的形式返回。 /// </summary> public string[] GetAllDBTables() { Queue<string> TableNameQueue = new Queue<string>(); if (conn != null) { Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow dr in dt.Rows) { TableNameQueue.Enqueue((String)dr["TABLE_NAME"]); } return TableNameQueue.ToArray(); } else return null; } } }

 

对AccessHelper的一个简单的应用，如下：

/// <summary> /// 根据表名、ID，返回字段Name的值 /// </summary> public string GetName(string TableName, long ID) { string sql = "SELECT NAME FROM " + TableName + " WHERE ID = " + ID ; return dao.ExecuteScalar(sql).ToString(); }