首页 > 代码库 > C#操作Access的一些小结
C#操作Access的一些小结
C#操作Access的一些小结
好久没有写blog，感觉今年一年都没怎么真正开心过，整天有一些事围绕在身边，使心情难以平静下来，真正写点有意义的东西。博客园是天天看的，看得多，写的少，偶尔也是Copy一篇技术文章放一下，便于自己将来查询。
最近有Winfom做了一个小系统，后台数据库是用Access，主要是单机版，考虑方便性，现在总结一些值得后来人参考的地方。
一，数据库操作基类
一般都要写个基类，这样，将来换数据库，换一下这个DA基类就行了。这个类也是从网上找的。不过感觉有的挺不错，一般就要返回dataset,datatable,datarow。还有执行sql语句，这里主要好的地方是可以带params执行，另一个就是支持事务。拿出来和大家共享一下。
??
代码
?public?static?class?AccessHelper
????{
????????//数据库连接字符串
????????//WebForm
????????//public?static?readonly?string?conn_str?=?"Provider=Microsoft.Jet.OLEDB.4.0;Data?Source="?+?HttpContext.Current.Request.PhysicalApplicationPath?+?System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
????????//WinForm
????????//public?static?readonly?string?conn_str?=?"Provider=Microsoft.Jet.OLEDB.4.0;Data?Source="?+?AppDomain.CurrentDomain.BaseDirectory?+?System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString;
????????//public?static?readonly?string?conn_str?=??System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString;
????????public?static?readonly?string?conn_str?=?"Provider=Microsoft.Jet.OLEDB.4.0;Data?Source="?+?AppDomain.CurrentDomain.BaseDirectory?+?System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString?+?";Persist?Security?Info=True";
????????//?用于缓存参数的HASH表
????????private?static?Hashtable?parmCache?=?Hashtable.Synchronized(new?Hashtable());
????????///?<summary>
????????///??给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
????????///?</summary>
????????///?<param?name="connectionString">一个有效的连接字符串</param>
????????///?<param?name="commandText">存储过程名称或者sql命令语句</param>
????????///?<param?name="commandParameters">执行命令所用参数的集合</param>
????????///?<returns>执行命令所影响的行数</returns>
????????public?static?int?ExecuteNonQuery(string?connectionString,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????using?(OleDbConnection?conn?=?new?OleDbConnection(connectionString))
????????????{
????????????????PrepareCommand(cmd,?conn,?null,?cmdText,?commandParameters);
????????????????int?val?=?cmd.ExecuteNonQuery();
????????????????cmd.Parameters.Clear();
????????????????return?val;
????????????}
????????}
????????///?<summary>
????????///?用现有的数据库连接执行一个sql命令（不返回数据集）
????????///?</summary>
????????///?<remarks>
????????///举例:??
????????///??int?result?=?ExecuteNonQuery(connString,?"PublishOrders",?new?OleDbParameter("@prodid",?24));
????????///?</remarks>
????????///?<param?name="conn">一个现有的数据库连接</param>
????????///?<param?name="commandText">存储过程名称或者sql命令语句</param>
????????///?<param?name="commandParameters">执行命令所用参数的集合</param>
????????///?<returns>执行命令所影响的行数</returns>
????????public?static?int?ExecuteNonQuery(OleDbConnection?connection,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????PrepareCommand(cmd,?connection,?null,?cmdText,?commandParameters);
????????????int?val?=?cmd.ExecuteNonQuery();
????????????cmd.Parameters.Clear();
????????????return?val;
????????}
????????///?<summary>
????????///使用现有的SQL事务执行一个sql命令（不返回数据集）
????????///?</summary>
????????///?<remarks>
????????///举例:??
????????///??int?result?=?ExecuteNonQuery(trans,?"PublishOrders",?new?OleDbParameter("@prodid",?24));
????????///?</remarks>
????????///?<param?name="trans">一个现有的事务</param>
????????///?<param?name="commandText">存储过程名称或者sql命令语句</param>
????????///?<param?name="commandParameters">执行命令所用参数的集合</param>
????????///?<returns>执行命令所影响的行数</returns>
????????public?static?int?ExecuteNonQuery(OleDbTransaction?trans,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????PrepareCommand(cmd,?trans.Connection,?trans,?cmdText,?commandParameters);
????????????int?val?=?cmd.ExecuteNonQuery();
????????????cmd.Parameters.Clear();
????????????return?val;
????????}
????????///?<summary>
????????///?用执行的数据库连接执行一个返回数据集的sql命令
????????///?</summary>
????????///?<remarks>
????????///?举例:??
????????///??OleDbDataReader?r?=?ExecuteReader(connString,?"PublishOrders",?new?OleDbParameter("@prodid",?24));
????????///?</remarks>
????????///?<param?name="connectionString">一个有效的连接字符串</param>
????????///?<param?name="commandText">存储过程名称或者sql命令语句</param>
????????///?<param?name="commandParameters">执行命令所用参数的集合</param>
????????///?<returns>包含结果的读取器</returns>
????????public?static?OleDbDataReader?ExecuteReader(string?connectionString,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????//创建一个SqlCommand对象
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????//创建一个SqlConnection对象
????????????OleDbConnection?conn?=?new?OleDbConnection(connectionString);
????????????//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
????????????//因此commandBehaviour.CloseConnection?就不会执行
????????????try
????????????{
????????????????//调用?PrepareCommand?方法，对?SqlCommand?对象设置参数
????????????????PrepareCommand(cmd,?conn,?null,?cmdText,?commandParameters);
????????????????//调用?SqlCommand??的?ExecuteReader?方法
????????????????OleDbDataReader?reader?=?cmd.ExecuteReader(CommandBehavior.CloseConnection);
????????????????//清除参数
????????????????cmd.Parameters.Clear();
????????????????return?reader;
????????????}
????????????catch
????????????{
????????????????//关闭连接，抛出异常
????????????????conn.Close();
????????????????throw;
????????????}
????????}
????????///?<summary>
????????///?返回一个DataSet数据集
????????///?</summary>
????????///?<param?name="connectionString">一个有效的连接字符串</param>
????????///?<param?name="cmdText">存储过程名称或者sql命令语句</param>
????????///?<param?name="commandParameters">执行命令所用参数的集合</param>
????????///?<returns>包含结果的数据集</returns>
????????public?static?DataSet?ExecuteDataSet(string?connectionString,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????//创建一个SqlCommand对象，并对其进行初始化
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????using?(OleDbConnection?conn?=?new?OleDbConnection(connectionString))
????????????{
????????????????PrepareCommand(cmd,?conn,?null,?cmdText,?commandParameters);
????????????????//创建SqlDataAdapter对象以及DataSet
????????????????OleDbDataAdapter?da?=?new?OleDbDataAdapter(cmd);
????????????????DataSet?ds?=?new?DataSet();
????????????????try
????????????????{
????????????????????//填充ds
????????????????????da.Fill(ds);
????????????????????//?清除cmd的参数集合?
????????????????????cmd.Parameters.Clear();
????????????????????//返回ds
????????????????????return?ds;
????????????????}
????????????????catch
????????????????{
????????????????????//关闭连接，抛出异常
????????????????????conn.Close();
????????????????????throw;
????????????????}
????????????}
????????}
????????public?static?DataSet?ExecuteDataSet(OleDbConnection?conn,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????//创建一个SqlCommand对象，并对其进行初始化
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????PrepareCommand(cmd,?conn,?null,?cmdText,?commandParameters);
????????????//创建SqlDataAdapter对象以及DataSet
????????????OleDbDataAdapter?da?=?new?OleDbDataAdapter(cmd);
????????????DataSet?ds?=?new?DataSet();
????????????try
????????????{
????????????????//填充ds
????????????????da.Fill(ds);
????????????????//?清除cmd的参数集合?
????????????????cmd.Parameters.Clear();
????????????????//返回ds
????????????????return?ds;
????????????}
????????????catch
????????????{
????????????????//关闭连接，抛出异常
????????????????conn.Close();
????????????????throw;
????????????}
????????}
????????///?<summary>
????????///?用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
????????///?</summary>
????????///?<remarks>
????????///例如:??
????????///??Object?obj?=?ExecuteScalar(connString,?"PublishOrders",?new?OleDbParameter("@prodid",?24));
????????///?</remarks>
????????///<param?name="connectionString">一个有效的连接字符串</param>
????????///?<param?name="commandText">存储过程名称或者sql命令语句</param>
????????///?<param?name="commandParameters">执行命令所用参数的集合</param>
????????///?<returns>用?Convert.To{Type}把类型转换为想要的?</returns>
????????public?static?object?ExecuteScalar(string?connectionString,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????using?(OleDbConnection?connection?=?new?OleDbConnection(connectionString))
????????????{
????????????????PrepareCommand(cmd,?connection,?null,?cmdText,?commandParameters);
????????????????object?val?=?cmd.ExecuteScalar();
????????????????cmd.Parameters.Clear();
????????????????return?val;
????????????}
????????}
????????///?<summary>
????????///?用指定的数据库连接执行一个命令并返回一个数据集的第一列
????????///?</summary>
????????///?<remarks>
????????///?例如:??
????????///??Object?obj?=?ExecuteScalar(connString,?"PublishOrders",?new?OleDbParameter("@prodid",?24));
????????///?</remarks>
????????///?<param?name="conn">一个存在的数据库连接</param>
????????///?<param?name="commandText">存储过程名称或者sql命令语句</param>
????????///?<param?name="commandParameters">执行命令所用参数的集合</param>
????????///?<returns>用?Convert.To{Type}把类型转换为想要的?</returns>
????????public?static?object?ExecuteScalar(OleDbConnection?connection,?string?cmdText,?params?OleDbParameter[]?commandParameters)
????????{
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????PrepareCommand(cmd,?connection,?null,?cmdText,?commandParameters);
????????????object?val?=?cmd.ExecuteScalar();
????????????cmd.Parameters.Clear();
????????????return?val;
????????}
????????///?<summary>
????????///?将参数集合添加到缓存
????????///?</summary>
????????///?<param?name="cacheKey">添加到缓存的变量</param>
????????///?<param?name="cmdParms">一个将要添加到缓存的sql参数集合</param>
????????public?static?void?CacheParameters(string?cacheKey,?params?OleDbParameter[]?commandParameters)
????????{
????????????parmCache[cacheKey]?=?commandParameters;
????????}
????????///?<summary>
????????///?找回缓存参数集合
????????///?</summary>
????????///?<param?name="cacheKey">用于找回参数的关键字</param>
????????///?<returns>缓存的参数集合</returns>
????????public?static?OleDbParameter[]?GetCachedParameters(string?cacheKey)
????????{
????????????OleDbParameter[]?cachedParms?=?(OleDbParameter[])parmCache[cacheKey];
????????????if?(cachedParms?==?null)
????????????????return?null;
????????????OleDbParameter[]?clonedParms?=?new?OleDbParameter[cachedParms.Length];
????????????for?(int?i?=?0,?j?=?cachedParms.Length;?i?<?j;?i++)
????????????????clonedParms?=?(OleDbParameter[])((ICloneable)cachedParms).Clone();
????????????return?clonedParms;
????????}
????????///?<summary>
????????///?准备执行一个命令
????????///?</summary>
????????///?<param?name="cmd">sql命令</param>
????????///?<param?name="conn">Sql连接</param>
????????///?<param?name="trans">Sql事务</param>
????????///?<param?name="cmdText">命令文本,例如：Select?*?from?Products</param>
????????///?<param?name="cmdParms">执行命令的参数</param>
????????private?static?void?PrepareCommand(OleDbCommand?cmd,?OleDbConnection?conn,?OleDbTransaction?trans,?string?cmdText,?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?(OleDbParameter?parm?in?cmdParms)
????????????????????cmd.Parameters.Add(parm);
????????????}
????????}
????}
??
??
??
如新增：
??
代码
public?int?InsertCby(CbyEntity?objCbyEntity)
????????{
????????????int?intResult?=?0;
????????????string?strSql?=?"insert?into?cby?(dh,xm)??values?(@dh,@xm)";
????????????OleDbParameter[]?prams?=?{
?????????????????????????????????????????????new?OleDbParameter("@dh",?objCbyEntity.Dh),
?????????????????????????????????????????????new?OleDbParameter("@xm",?objCbyEntity.Xm)
?????????????????????????????????????????};
????????????intResult?=?AccessHelper.ExecuteNonQuery(AccessHelper.conn_str,?strSql,?prams);
????????????return?intResult;
????????}
??
用事务：
??
代码
public?bool?InsertDfdjzkOrder(DfdjzkEntity?etyDfdj,?DfdjhmEntity?etyDfdjhm,?out?string?errmsg)
????????{
????????????bool?result?=?true;
????????????errmsg?=?"";
????????????string?strCon?=?AccessHelper.conn_str;
????????????OleDbConnection?con?=?new?OleDbConnection(strCon);
????????????try
????????????{
????????????????con.Open();
????????????????OleDbTransaction?tra?=?con.BeginTransaction();?//创建事务，开始执行事务
????????????????if?(this.InsertDfdjzk(tra,?etyDfdj)?!=?1)
????????????????{
????????????????????tra.Rollback();
????????????????????return?false;
????????????????}
????????????????DfdjhmDA?daDfdjhm?=?new?DfdjhmDA();
????????????????if?(daDfdjhm.UpdateDfdjhm(tra,?etyDfdjhm)?!=?1)
????????????????{
????????????????????tra.Rollback();
????????????????????return?false;
????????????????}
????????????????tra.Commit();//关闭事务
????????????}
????????????catch?(Exception?ex)
????????????{
????????????????errmsg?=?ex.Message;
????????????????return?false;
????????????}
????????????finally
????????????{
????????????????con.Close();
????????????}
????????????return?result;
????????}
??
??
批量更新时，一定要用同一连接，不然效率肯定狂慢。
??
代码
?public?bool?UpdateXhRqBatch()
????????{
????????????using?(OleDbConnection?conn?=?new?OleDbConnection(AccessHelper.conn_str))
????????????{
????????????????string?strSql?=?"SELECT?*?FROM?dfdjzk?Where?1=1?";//and?djhm>‘00050235‘
????????????????DataSet?ds;
????????????????ds?=?AccessHelper.ExecuteDataSet(conn,?strSql,?null);
????????????????foreach?(DataRow?dr?in?ds.Tables[0].Rows)
????????????????{
????????????????????string?djhm?=?dr[DfdjzkSchema.DJHM].ToString();
????????????????????string?xhrq?=?dr[DfdjzkSchema.XHRQ].ToString();
????????????????????if?(!string.IsNullOrEmpty(xhrq)?&&?xhrq.Length?==?4)
????????????????????{
????????????????????????if?(xhrq.Substring(0,?2)?==?"99")
????????????????????????{
????????????????????????????xhrq?=?"19"?+?xhrq?+?"01";
????????????????????????}
????????????????????????else
????????????????????????{
????????????????????????????xhrq?=?"20"?+?xhrq?+?"01";
????????????????????????}
????????????????????????string?updatesql?=?"update?Dfdjzk?set?xhrq=‘"?+?xhrq?+?"‘?where?djhm?=‘"?+?djhm?+?"‘?";
????????????????????????AccessHelper.ExecuteNonQuery(conn,?updatesql,?null);
????????????????????}
????????????????}
????????????}
????????????return?true;
????????}
??
??
2,一些注意点
Access对日期操作时，sql语句要用#
??
代码
string?strSql?=?@"insert?into?Dfdjzk?(zhh,yhdw,bqss1,sqss1,syl1,dj1,je1,
????????????????????????????????????????????????????bqss2,sqss2,syl2,dj2,je2,
????????????????????????????????????????????????????bqss3,sqss3,syl3,dj3,je3,
????????????????????????????????????????????????????je,rzbz,zfbz,djhm,kprq,sssq,ssqh,xhrq,cby,sfy)
??????????????????????????????????????????values?(@zhh,@yhdw,@bqss1,@sqss1,@syl1,@dj1,@je1,
????????????????????????????????????????????????????@bqss2,@sqss2,@syl2,@dj2,@je2,
????????????????????????????????????????????????????@bqss3,@sqss3,@syl3,@dj3,@je3,
????????????????????????????????????????????????????@je,@rzbz,@zfbz,@djhm,#"?+?objDfdjzkEntity.Kprq.ToString("yyyy-MM-dd")?+?"#,@sssq,@ssqh,@xhrq,@cby,@sfy)";
??
??
Access支持子查询，不过效率狂低，能不用最好不用，与SQL server没法比
//??????????? string strSql = @"SELECT zhh,ssqh,yhdw from yhdw where zhh<>‘‘ and (glbz=true or
//??????????????? exists (select 0 from dfdjzk where yhdw.zhh = zhh and sssq=‘" + sssq + @"‘)
//??????????????? )";
??
用ADO.NET模糊查询时，和SQL Server一样。和直接在Access查询设计器中是不一样的概念。
??
if (kplq_end != "")
??????????? {
??????????????? strSql += " And kprq<=#" + kplq_end + "# ";
??????????? }
??????????? if (kplq_bgn != "")
??????????? {
??????????????? strSql += " And kprq>=#" + kplq_bgn + "# ";
??????????? }
??????????? if (djhm != "")
??????????? {
??????????????? strSql += " And djhm like ‘%" + djhm + "%‘ ";
??????????? }
??????????? if (zhh != "")
??????????? {
??????????????? strSql += " And zhh like ‘%" + zhh + "%‘ ";
??????????? }
??
就先写这么多吧。
C#操作Access的一些小结