关于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);