关于c#.Net操作Access数据库的类，支持事务处理

为提高程序效率计，类中所有方法均为静态方法。

oledb驱动，支持事务处理。引类抛砖引玉，不足之处大师们多多指教。

OleDbAccess.cs原码如下:

 

/**/ /* 
 * @Sban 2006-12
 * Access数据库Oldb操作类
 */
using  System;
using  System.Data;
using  System.Data.OleDb;

namespace  Sban
... ... {
    public static class OleDbAccess
    ......{
        /**//**//**//// <summary>
        /// 取得数据库连接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static OleDbConnection GetConnection(string connectionString)
        ......{
            OleDbConnection conn = new OleDbConnection(connectionString);
            try
            ......{
                conn.Open();
            }
            catch (Exception e)
            ......{
                throw new Exception("数据库连接字符串可能有问题！", e);
            }

            return conn;
        }
        /**//**//**//// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static string FiltArg(ref string param)
        ......{
            param = System.Web.HttpUtility.HtmlEncode(param);
            param = param.Replace("'","‘").Replace(""","“");

            return param;
        }
        /**//**//**//// <summary>
        /// 获得oledb连接字符串
        /// </summary>
        /// <param name="dataBasePath"></param>
        /// <returns></returns>
        public static string GetConnectionString(string dataBasePath)
        ......{
            return "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" + dataBasePath;
        }

        /**//**//**//// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static OleDbCommand GetCommand(ref OleDbConnection conn)
        ......{
            return conn.CreateCommand();
        }

        /**//**//**//// <summary>
        /// start transaction and return
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static OleDbTransaction StartTrans(ref OleDbConnection conn, ref OleDbCommand cmd)
        ......{
            return cmd.Transaction = conn.BeginTransaction();
        }

        public static void RollTrans(ref OleDbTransaction trans)
        ......{
            trans.Rollback();
        }

        public static void CommitTrans(ref OleDbTransaction trans)
        ......{
            trans.Commit();
        }

        /**//**//**//// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="query"></param>
        /// <param name="param"></param>
        public static void ExecuteNonQuery(ref OleDbCommand cmd, string query, params OleDbParameter[] param)
        ......{
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;
            cmd.Parameters.Clear();
            if (null != param)
                cmd.Parameters.AddRange(param);

            try
            ......{
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            ......{
                throw new Exception("ExecuteNonQuery:执行数据库操作时出现问题！", e);
            }
        }

        /**//**//**//// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="cmd"></param>
        public static void Dispose(ref OleDbConnection conn, ref OleDbCommand cmd)
        ......{
            try
            ......{
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch
            ......{
                //throw new Exception("执行数据库对象清除可能有问题！", e);
            }
        }

        /**//**//**//// <summary>
        /// GetParameters array
        /// </summary>
        /// <param name="arr"></param>
        /// <returns></returns>
        public static OleDbParameter[] GetParameters(params object[] arr)
        ......{
            OleDbParameter[] paramArray = new OleDbParameter[arr.Length];
            object[] o;

            for (int j = 0; j < arr.Length; j++)
            ......{
                o = (object[])arr[j];
                paramArray[j] = GetParameter(o);
            }

            return paramArray;
        }

        /**//**//**//// <summary>
        /// GetParameter
        /// </summary>
        /// <param name="arr"></param>
        /// <returns></returns>
        public static OleDbParameter GetParameter(params object[] arr)
        ......{
            OleDbParameter param = new OleDbParameter(arr[0].ToString(), arr[1]);
            if (arr.Length > 2)
                param.OleDbType = (OleDbType)arr[2];
            if (arr.Length > 3)
                param.Size = Convert.ToInt32(arr[3]);

            return param;
        }

        /**//**//**//// <summary>
        /// execute query and return dataset.
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static OleDbDataReader ExecuteReader(ref OleDbCommand cmd, string query, params OleDbParameter[] param)
        ......{
            OleDbDataReader dr = null;

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;
            cmd.Parameters.Clear();
            if (null != param)
                cmd.Parameters.AddRange(param);

            try
            ......{
                dr = cmd.ExecuteReader();
            }
            catch (Exception e)
            ......{
                throw new Exception("ExecuteReader:执行数据库操作时出现问题！", e);
            }

            return dr;
        }

        /**//**//**//// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object ExecuteScalar(ref OleDbCommand cmd, string query, params OleDbParameter[] param)
        ......{
            object obj = null;

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;
            cmd.Parameters.Clear();
            if (null != param)
                cmd.Parameters.AddRange(param);

            try
            ......{
                obj = cmd.ExecuteScalar();
            }
            catch (Exception e)
            ......{
                throw new Exception("ExecuteScalar:执行数据库操作时出现问题！", e);
            }

            return obj;
        }

        /**//**//**//// <summary>
        /// return a dataset
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(ref OleDbCommand cmd, string query, params OleDbParameter[] param)
        ......{
            OleDbDataAdapter dad = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;
            cmd.Parameters.Clear();
            if (null != param)
                cmd.Parameters.AddRange(param);

            try
            ......{
                dad.Fill(ds);
            }
            catch (Exception e)
            ......{
                throw new Exception("ExecuteDataSet:执行数据库操作时出现问题！", e);
            }
            finally
            ......{
                dad.Dispose();
            }

            return ds;
        }
    }
}

 

附一个使用例子：

 

int  id  =   int .Parse(GridView1.Rows[e.RowIndex].Cells[ 0 ].Text);
string  fileClass  =  ((TextBox)GridView1.Rows[e.RowIndex].Cells[ 1 ].Controls[ 0 ]).Text;
string  fullName  =  ((TextBox)GridView1.Rows[e.RowIndex].Cells[ 2 ].Controls[ 0 ]).Text;
string  imageUrl  =  ((TextBox)GridView1.Rows[e.RowIndex].Cells[ 3 ].Controls[ 0 ]).Text;

OleDbConnection conn  =  OleDbAccess.GetConnection(MasterDBConnectionString);
OleDbCommand cmd  =  OleDbAccess.GetCommand( ref  conn);
string  query  =   " Update [FileClass] Set [FileClass]=@FileClass,[FullName]=@FullName,[ImageUrl]=@ImageUrl Where [Id] = @Id " ;
OleDbAccess.ExecuteNonQuery( ref  cmd, query, OleDbAccess.GetParameters( new   object [] ... ... { new object[] ......{ "@FileClass", fileClass, OleDbType.VarChar },
                                  new object[] ......{ "@FullName", fullName, OleDbType.VarChar },
                                  new object[] ......{ "@ImageUrl", imageUrl, OleDbType.VarChar },
                                  new object[] ......{ "@Id", id, OleDbType.Integer } } ));
OleDbAccess.Dispose( ref  conn,  ref  cmd);