也谈OleDbHelper,由SqlHelper类转OleDbHelper类，access操作

最新推荐文章于 2023-01-12 20:51:31 发布
iteye_4537
最新推荐文章于 2023-01-12 20:51:31 发布
阅读量171
点赞数
今意外看见一贴子谈OleDbHelper，突然想起自已一直用的由SqlHelper类转成的OleDbHelper，在此分享，自己用了二年了，还没发现异常，代码如下：
 
  usingSystem; 
 usingSystem.Data; 
 usingSystem.Data.OleDb; 
 usingSystem.Configuration; 
 usingSystem.Collections; 
 usingSystem.Data.Sql; 
 usingSystem.Text; 
 namespaceLihui.Common 
{
 ///<summary> 
 ///SummarydescriptionforOleDbHelper 
 ///</summary> 
 publicclassOleDbHelper 
{
 //Databaseconnectionstrings 
 publicstaticreadonlystringCONN_STRING=ConfigurationManager.AppSettings["OleDbConnectionString"]; 
 publicstaticreadonlystringCONN_STRING1=ConfigurationManager.AppSettings["OleDbConnectionString1"]; 
 //Hashtabletostorecachedparameters 
 privatestaticHashtableparmCache=Hashtable.Synchronized(newHashtable()); 
  
#region=ExecuteNonQuery= 
 publicstaticintExecuteNonQuery(stringconnString,CommandTypecmdType,stringcmdText) 
{
 returnExecuteNonQuery(connString,cmdType,cmdText,null); 
}
 publicstaticintExecuteNonQuery(OleDbConnectionconn,CommandTypecmdType,stringcmdText) 
{
 returnExecuteNonQuery(conn,cmdType,cmdText,null); 
}
 publicstaticintExecuteNonQuery(OleDbTransactiontrans,CommandTypecmdType,stringcmdText) 
{
 returnExecuteNonQuery(trans,cmdType,cmdText,null); 
}
 publicstaticintExecuteNonQuery(stringconnString,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) 
{
 OleDbCommandcmd=newOleDbCommand(); 
 using(OleDbConnectionconn=newOleDbConnection(connString)) 
{
 PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms); 
 intval=cmd.ExecuteNonQuery(); 
 //清除cmd的参数 
cmd.Parameters.Clear();
 if(conn.State==ConnectionState.Open) 
{
conn.Close();
}
 returnval; 
}
}
 publicstaticintExecuteNonQuery(OleDbConnectionconn,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) 
{
 OleDbCommandcmd=newOleDbCommand(); 
 PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms); 
 intval=cmd.ExecuteNonQuery(); 
cmd.Parameters.Clear();
 if(conn.State==ConnectionState.Open) 
{
conn.Close();
}
 returnval; 
}
 publicstaticintExecuteNonQuery(OleDbTransactiontrans,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) 
{
 OleDbCommandcmd=newOleDbCommand(); 
PrepareCommand(cmd,trans.Connection,trans,cmdType,cmdText,cmdParms);
 intval=cmd.ExecuteNonQuery(); 
cmd.Parameters.Clear();
 if(cmd.Connection.State==ConnectionState.Open) 
{
cmd.Connection.Close();
}
 returnval; 
}
  
#endregion 
  
#region=ExecuteReader= 
 publicstaticOleDbDataReaderExecuteReader(stringconnectionString,CommandTypecommandType,stringcommandText) 
{
 //passthroughthecallprovidingnullforthesetofOleDbParameters 
 returnExecuteReader(connectionString,commandType,commandText,(OleDbParameter[])null); 
}
 publicstaticOleDbDataReaderExecuteReader(stringconnString,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) 
{
 OleDbCommandcmd=newOleDbCommand(); 
 OleDbConnectionconn=newOleDbConnection(connString); 
 try 
{
 PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms); 
OleDbDataReaderrdr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
 if(conn.State==ConnectionState.Open) 
{
conn.Close();
}
 returnrdr; 
}
 catch 
{
conn.Close();
 throw; 
}
}
  
#endregion 
  
#region=ExecuteDataset= 
 publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText) 
{
 returnExecuteDataset(connectionString,commandType,commandText,(OleDbParameter[])null); 
}
 publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText,paramsOleDbParameter[]commandParameters) 
{
 using(OleDbConnectioncn=newOleDbConnection(connectionString)) 
{
cn.Open();
 //调用重载方法 
 returnExecuteDataset(cn,commandType,commandText,commandParameters); 
}
}
 publicstaticDataSetExecuteDataset(OleDbConnectionconnection,CommandTypecommandType,stringcommandText) 
{
 returnExecuteDataset(connection,commandType,commandText,(OleDbParameter[])null); 
}
 publicstaticDataSetExecuteDataset(OleDbConnectionconnection,CommandTypecommandType,stringcommandText,paramsOleDbParameter[]commandParameters) 
{
 //创建一个OleDbCommand对象，并对其进行初始化 
 OleDbCommandcmd=newOleDbCommand(); 
 PrepareCommand(cmd,connection,(OleDbTransaction)null,commandType,commandText,commandParameters); 
 //创建OleDbDataAdapter对象以及DataSet 
 OleDbDataAdapterda=newOleDbDataAdapter(cmd); 
 DataSetds=newDataSet(); 
 //填充ds 
da.Fill(ds);
 //清除cmd的参数集合 
cmd.Parameters.Clear();
 if(cmd.Connection.State==ConnectionState.Open) 
{
cmd.Connection.Close();
}
 //返回ds 
 returnds; 
}
  
#endregion 
  
#region=ExecuteDataTable= 
 publicstaticDataTableExecuteDataTable(stringconnectionString,CommandTypecommandType,stringcommandText) 
{
 returnExecuteDataTable(connectionString,commandType,commandText,(OleDbParameter[])null); 
}
 publicstaticDataTableExecuteDataTable(stringconnectionString,CommandTypecommandType,stringcommandText,paramsOleDbParameter[]commandParameters) 
{
 using(OleDbConnectioncn=newOleDbConnection(connectionString)) 
{
cn.Open();
 //调用重载方法 
 returnExecuteDataTable(cn,commandType,commandText,commandParameters); 
}
}
 publicstaticDataTableExecuteDataTable(OleDbConnectionconnection,CommandTypecommandType,stringcommandText) 
{
 returnExecuteDataTable(connection,commandType,commandText,(OleDbParameter[])null); 
}
 publicstaticDataTableExecuteDataTable(OleDbConnectionconnection,CommandTypecommandType,stringcommandText,paramsOleDbParameter[]commandParameters) 
{
 //创建一个OleDbCommand对象，并对其进行初始化 
 OleDbCommandcmd=newOleDbCommand(); 
 PrepareCommand(cmd,connection,(OleDbTransaction)null,commandType,commandText,commandParameters); 
 //创建OleDbDataAdapter对象以及DataSet 
 OleDbDataAdapterda=newOleDbDataAdapter(cmd); 
 DataSetds=newDataSet(); 
 //填充ds 
da.Fill(ds);
 //清除cmd的参数集合 
cmd.Parameters.Clear();
 if(cmd.Connection.State==ConnectionState.Open) 
{
cmd.Connection.Close();
}
 //返回ds 
 returnds.Tables[0]; 
}
  
#endregion 
  
#region=ExecuteScalar= 
 publicstaticobjectExecuteScalar(stringconnString,CommandTypecmdType,stringcmdText) 
{
 returnExecuteScalar(connString,cmdType,cmdText,null); 
}
 publicstaticobjectExecuteScalar(stringconnString,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) 
{
 OleDbCommandcmd=newOleDbCommand(); 
 using(OleDbConnectionconn=newOleDbConnection(connString)) 
{
 PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms); 
 objectval=cmd.ExecuteScalar(); 
cmd.Parameters.Clear();
 if(conn.State==ConnectionState.Open) 
{
conn.Close();
}
 returnval; 
}
}
 publicstaticobjectExecuteScalar(OleDbConnectionconn,CommandTypecmdType,stringcmdText) 
{
 returnExecuteScalar(conn,cmdType,cmdText,null); 
}
 publicstaticobjectExecuteScalar(OleDbConnectionconn,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) 
{
 OleDbCommandcmd=newOleDbCommand(); 
 PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms); 
 objectval=cmd.ExecuteScalar(); 
cmd.Parameters.Clear();
 if(conn.State==ConnectionState.Open) 
{
conn.Close();
}
 returnval; 
}
  
#endregion 
 publicstaticvoidCacheParameters(stringcacheKey,paramsOleDbParameter[]cmdParms) 
{
parmCache[cacheKey]=cmdParms;
}
 publicstaticOleDbParameter[]GetCachedParameters(stringcacheKey) 
{
OleDbParameter[]cachedParms=(OleDbParameter[])parmCache[cacheKey];
 if(cachedParms==null) 
 returnnull; 
 OleDbParameter[]clonedParms=newOleDbParameter[cachedParms.Length]; 
 for(inti=0,j=cachedParms.Length;i<j;i++) 
clonedParms[i]=(OleDbParameter)((ICloneable)cachedParms[i]).Clone();
 returnclonedParms; 
}
 publicstaticvoidPrepareCommand(OleDbCommandcmd,OleDbConnectionconn,OleDbTransactiontrans,CommandTypecmdType,stringcmdText,OleDbParameter[]cmdParms) 
{
 //判断连接的状态。如果是关闭状态，则打开 
 if(conn.State!=ConnectionState.Open) 
conn.Open();
 //cmd属性赋值 
cmd.Connection=conn;
cmd.CommandText=cmdText;
 //是否需要用到事务处理 
 if(trans!=null) 
cmd.Transaction=trans;
cmd.CommandType=cmdType;
 //添加cmd需要的存储过程参数 
 if(cmdParms!=null) 
{
 foreach(OleDbParameterparmincmdParms) 
cmd.Parameters.Add(parm);
}
}
}
}
 