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 + "%' ";
            }

 

就先写这么多吧。

 

 

 

 

转载于:https://www.cnblogs.com/huang/archive/2009/12/17/1626545.html