C#数据库操作类AccessHelper实例
本文实例讲述了C#数据库操作类AccessHelper。分享给大家供大家参考。
具体实现方法如下：
usingSystem; usingSystem.Data; usingSystem.Configuration; usingSystem.Data.OleDb; usingahwildlife.Utils;
///<summary> ///AccessHelper的摘要说明 ///</summary> publicclassAccessHelper { #region变量 protectedstaticOleDbConnectionconn=newOleDbConnection(); protectedstaticOleDbCommandcomm=newOleDbCommand(); protectedstaticstringconnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=ahwildlife.mdb;PersistSecurityInfo=False;JetOLEDB:DatabasePassword=sa;"; #endregion
#region构造函数 ///<summary> ///构造函数 ///</summary> publicAccessHelper() {
} #endregion
#region打开数据库 ///<summary> ///打开数据库 ///</summary> privatestaticvoidopenConnection() { if(conn.State==ConnectionState.Closed) { conn.ConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=ahwildlife.mdb;PersistSecurityInfo=False;JetOLEDB:DatabasePassword=sa;"; comm.Connection=conn; try { conn.Open(); } catch(Exceptionex) { thrownewException(ex.Message); } } } #endregion
#region关闭数据库 ///<summary> ///关闭数据库 ///</summary> privatestaticvoidcloseConnection() { if(conn.State==ConnectionState.Open) { conn.Close(); conn.Dispose(); comm.Dispose(); } } #endregion
#region执行sql语句 ///<summary> ///执行sql语句 ///</summary> publicstaticvoidExecuteSql(stringsqlstr) { try { openConnection(); comm.CommandType=CommandType.Text; comm.CommandText=sqlstr; comm.ExecuteNonQuery(); } catch(Exceptionex) { thrownewException(ex.Message); } finally { closeConnection(); } } #endregion
#region返回指定sql语句的OleDbDataReader对象,使用时请注意关闭这个对象。 ///<summary> ///返回指定sql语句的OleDbDataReader对象,使用时请注意关闭这个对象。 ///</summary> publicstaticOleDbDataReaderDataReader(stringsqlstr) { OleDbDataReaderdr=null; try { openConnection(); comm.CommandText=sqlstr; comm.CommandType=CommandType.Text;
dr=comm.ExecuteReader(CommandBehavior.CloseConnection); } catch { try { dr.Close(); closeConnection(); } catch{} } returndr; } #endregion
#region返回指定sql语句的OleDbDataReader对象,使用时请注意关闭 ///<summary> ///返回指定sql语句的OleDbDataReader对象,使用时请注意关闭 ///</summary> publicstaticvoidDataReader(stringsqlstr,refOleDbDataReaderdr) { try { openConnection(); comm.CommandText=sqlstr; comm.CommandType=CommandType.Text; dr=comm.ExecuteReader(CommandBehavior.CloseConnection); } catch { try { if(dr!=null&&!dr.IsClosed) dr.Close(); } catch { } finally { closeConnection(); } } } #endregion
#region返回指定sql语句的DataSet ///<summary> ///返回指定sql语句的DataSet ///</summary> ///<paramname="sqlstr"></param> ///<returns></returns> publicstaticDataSetDataSet(stringsqlstr) { DataSetds=newDataSet(); OleDbDataAdapterda=newOleDbDataAdapter(); try { openConnection(); comm.CommandType=CommandType.Text; comm.CommandText=sqlstr; da.SelectCommand=comm; da.Fill(ds);
} catch(Exceptione) { thrownewException(e.Message); } finally { closeConnection(); } returnds; } #endregion
#region返回指定sql语句的DataSet ///<summary> ///返回指定sql语句的DataSet ///</summary> ///<paramname="sqlstr"></param> ///<paramname="ds"></param> publicstaticvoidDataSet(stringsqlstr,refDataSetds) { OleDbDataAdapterda=newOleDbDataAdapter(); try { openConnection(); comm.CommandType=CommandType.Text; comm.CommandText=sqlstr; da.SelectCommand=comm; da.Fill(ds); } catch(Exceptione) { thrownewException(e.Message); } finally { closeConnection(); } } #endregion
#region返回指定sql语句的DataTable ///<summary> ///返回指定sql语句的DataTable ///</summary> ///<paramname="sqlstr"></param> ///<returns></returns> publicstaticDataTableDataTable(stringsqlstr) { DataTabledt=Common.GetDataTableCache(sqlstr);//读缓存 if(dt!=null) { returndt.Copy(); } else { dt=newDataTable(); OleDbDataAdapterda=newOleDbDataAdapter(); try { using(OleDbConnectionconn=newOleDbConnection()) { conn.ConnectionString=connectionString; conn.Open(); using(OleDbCommandcomm=newOleDbCommand()) { comm.Connection=conn; comm.CommandType=CommandType.Text; comm.CommandText=sqlstr; da.SelectCommand=comm; da.Fill(dt); } } } catch(Exceptione) { thrownewException(e.Message); } finally { closeConnection(); } Common.InsertDataTableCache(sqlstr,dt);//添加缓存 returndt.Copy(); } } #endregion
#region返回指定sql语句的DataTable ///<summary> ///返回指定sql语句的DataTable ///</summary> publicstaticvoidDataTable(stringsqlstr,refDataTabledt) { OleDbDataAdapterda=newOleDbDataAdapter(); try { openConnection(); comm.CommandType=CommandType.Text; comm.CommandText=sqlstr; da.SelectCommand=comm; da.Fill(dt); } catch(Exceptione) { thrownewException(e.Message); } finally { closeConnection(); } } #endregion
#region返回指定sql语句的DataView ///<summary> ///返回指定sql语句的DataView ///</summary> ///<paramname="sqlstr"></param> ///<returns></returns> publicstaticDataViewDataView(stringsqlstr) { OleDbDataAdapterda=newOleDbDataAdapter(); DataViewdv=newDataView(); DataSetds=newDataSet(); try { openConnection(); comm.CommandType=CommandType.Text; comm.CommandText=sqlstr; da.SelectCommand=comm; da.Fill(ds); dv=ds.Tables[0].DefaultView; } catch(Exceptione) { thrownewException(e.Message); } finally { closeConnection(); } returndv; } #endregion }