C#操作Access通用类实例
本文实例讲述了C#操作Access通用类。分享给大家供大家参考。具体如下：
usingSystem;
usingSystem.Data;
usingSystem.Configuration;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Web.UI.HtmlControls;
usingSystem.Data.OleDb;
usingSystem.Collections;
///<summary>
///AcceHelper的摘要说明
///</summary>
publicstaticclassAccessHelper
{
//数据库连接字符串
publicstaticreadonlystringconn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+HttpContext.Current.Request.PhysicalApplicationPath+System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//用于缓存参数的HASH表
privatestaticHashtableparmCache=Hashtable.Synchronized(newHashtable());
///<summary>
///给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
///</summary>
///<paramname="connectionString">一个有效的连接字符串</param>
///<paramname="commandText">存储过程名称或者sql命令语句</param>
///<paramname="commandParameters">执行命令所用参数的集合</param>
///<returns>执行命令所影响的行数</returns>
publicstaticintExecuteNonQuery(stringconnectionString,stringcmdText,paramsOleDbParameter[]commandParameters)
{
OleDbCommandcmd=newOleDbCommand();
using(OleDbConnectionconn=newOleDbConnection(connectionString))
{
PrepareCommand(cmd,conn,null,cmdText,commandParameters);
intval=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnval;
}
}
///<summary>
///用现有的数据库连接执行一个sql命令（不返回数据集）
///</summary>
///<remarks>
///举例:
///intresult=ExecuteNonQuery(connString,"PublishOrders",newOleDbParameter("@prodid",24));
///</remarks>
///<paramname="conn">一个现有的数据库连接</param>
///<paramname="commandText">存储过程名称或者sql命令语句</param>
///<paramname="commandParameters">执行命令所用参数的集合</param>
///<returns>执行命令所影响的行数</returns>
publicstaticintExecuteNonQuery(OleDbConnectionconnection,stringcmdText,paramsOleDbParameter[]commandParameters)
{
OleDbCommandcmd=newOleDbCommand();
PrepareCommand(cmd,connection,null,cmdText,commandParameters);
intval=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnval;
}
///<summary>
///使用现有的SQL事务执行一个sql命令（不返回数据集）
///</summary>
///<remarks>
///举例:
///intresult=ExecuteNonQuery(trans,"PublishOrders",newOleDbParameter("@prodid",24));
///</remarks>
///<paramname="trans">一个现有的事务</param>
///<paramname="commandText">存储过程名称或者sql命令语句</param>
///<paramname="commandParameters">执行命令所用参数的集合</param>
///<returns>执行命令所影响的行数</returns>
publicstaticintExecuteNonQuery(OleDbTransactiontrans,stringcmdText,paramsOleDbParameter[]commandParameters)
{
OleDbCommandcmd=newOleDbCommand();
PrepareCommand(cmd,trans.Connection,trans,cmdText,commandParameters);
intval=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnval;
}
///<summary>
///用执行的数据库连接执行一个返回数据集的sql命令
///</summary>
///<remarks>
///举例:
///OleDbDataReaderr=ExecuteReader(connString,"PublishOrders",newOleDbParameter("@prodid",24));
///</remarks>
///<paramname="connectionString">一个有效的连接字符串</param>
///<paramname="commandText">存储过程名称或者sql命令语句</param>
///<paramname="commandParameters">执行命令所用参数的集合</param>
///<returns>包含结果的读取器</returns>
publicstaticOleDbDataReaderExecuteReader(stringconnectionString,stringcmdText,paramsOleDbParameter[]commandParameters)
{
//创建一个SqlCommand对象
OleDbCommandcmd=newOleDbCommand();
//创建一个SqlConnection对象
OleDbConnectionconn=newOleDbConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
//因此commandBehaviour.CloseConnection就不会执行
try
{
//调用PrepareCommand方法，对SqlCommand对象设置参数
PrepareCommand(cmd,conn,null,cmdText,commandParameters);
//调用SqlCommand的ExecuteReader方法
OleDbDataReaderreader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除参数
cmd.Parameters.Clear();
returnreader;
}
catch
{
//关闭连接，抛出异常
conn.Close();
throw;
}
}
///<summary>
///返回一个DataSet数据集
///</summary>
///<paramname="connectionString">一个有效的连接字符串</param>
///<paramname="cmdText">存储过程名称或者sql命令语句</param>
///<paramname="commandParameters">执行命令所用参数的集合</param>
///<returns>包含结果的数据集</returns>
publicstaticDataSetExecuteDataSet(stringconnectionString,stringcmdText,paramsOleDbParameter[]commandParameters)
{
//创建一个SqlCommand对象，并对其进行初始化
OleDbCommandcmd=newOleDbCommand();
using(OleDbConnectionconn=newOleDbConnection(connectionString))
{
PrepareCommand(cmd,conn,null,cmdText,commandParameters);
//创建SqlDataAdapter对象以及DataSet
OleDbDataAdapterda=newOleDbDataAdapter(cmd);
DataSetds=newDataSet();
try
{
//填充ds
da.Fill(ds);
//清除cmd的参数集合
cmd.Parameters.Clear();
//返回ds
returnds;
}
catch
{
//关闭连接，抛出异常
conn.Close();
throw;
}
}
}
///<summary>
///用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
///</summary>
///<remarks>
///例如:
///Objectobj=ExecuteScalar(connString,"PublishOrders",newOleDbParameter("@prodid",24));
///</remarks>
///<paramname="connectionString">一个有效的连接字符串</param>
///<paramname="commandText">存储过程名称或者sql命令语句</param>
///<paramname="commandParameters">执行命令所用参数的集合</param>
///<returns>用Convert.To{Type}把类型转换为想要的</returns>
publicstaticobjectExecuteScalar(stringconnectionString,stringcmdText,paramsOleDbParameter[]commandParameters)
{
OleDbCommandcmd=newOleDbCommand();
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
PrepareCommand(cmd,connection,null,cmdText,commandParameters);
objectval=cmd.ExecuteScalar();
cmd.Parameters.Clear();
returnval;
}
}
///<summary>
///用指定的数据库连接执行一个命令并返回一个数据集的第一列
///</summary>
///<remarks>
///例如:
///Objectobj=ExecuteScalar(connString,"PublishOrders",newOleDbParameter("@prodid",24));
///</remarks>
///<paramname="conn">一个存在的数据库连接</param>
///<paramname="commandText">存储过程名称或者sql命令语句</param>
///<paramname="commandParameters">执行命令所用参数的集合</param>
///<returns>用Convert.To{Type}把类型转换为想要的</returns>
publicstaticobjectExecuteScalar(OleDbConnectionconnection,stringcmdText,paramsOleDbParameter[]commandParameters)
{
OleDbCommandcmd=newOleDbCommand();
PrepareCommand(cmd,connection,null,cmdText,commandParameters);
objectval=cmd.ExecuteScalar();
cmd.Parameters.Clear();
returnval;
}
///<summary>
///将参数集合添加到缓存
///</summary>
///<paramname="cacheKey">添加到缓存的变量</param>
///<paramname="cmdParms">一个将要添加到缓存的sql参数集合</param>
publicstaticvoidCacheParameters(stringcacheKey,paramsOleDbParameter[]commandParameters)
{
parmCache[cacheKey]=commandParameters;
}
///<summary>
///找回缓存参数集合
///</summary>
///<paramname="cacheKey">用于找回参数的关键字</param>
///<returns>缓存的参数集合</returns>
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=(OleDbParameter[])((ICloneable)cachedParms).Clone();
returnclonedParms;
}
///<summary>
///准备执行一个命令
///</summary>
///<paramname="cmd">sql命令</param>
///<paramname="conn">Sql连接</param>
///<paramname="trans">Sql事务</param>
///<paramname="cmdText">命令文本,例如：Select*fromProducts</param>
///<paramname="cmdParms">执行命令的参数</param>
privatestaticvoidPrepareCommand(OleDbCommandcmd,OleDbConnectionconn,OleDbTransactiontrans,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=CommandType.Text;
//添加cmd需要的存储过程参数
if(cmdParms!=null)
{
foreach(OleDbParameterparmincmdParms)
cmd.Parameters.Add(parm);
}
}
}
希望本文所述对大家的C#程序设计有所帮助。