C#编写的AccessHelper

C#编写的AccessHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace CZGH.Class
{
    /// <summary>
    /// access 数据访问抽象基础类
    /// </summary>
    public abstract class AccessHelper
    {
        private static string MdbPath = null;//数据库路径

        public static string path
        {
            get
            {
                return MdbPath;
            }
            set
            {
                MdbPath = value;
            }
        }

        public AccessHelper() { }

        #region 创建数据库连接
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        private static OleDbConnection CreateConnection()
        {
            return new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MdbPath + ";Persist Security Info=False");
        }
        /// <summary>
        /// 判断是否能打开数据库连接
        /// </summary>
        /// <returns></returns>
        public static bool IsOpenConnection()
        {
            bool bl = false;
            try
            {
                OleDbConnection odc = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MdbPath + ";Persist Security Info=False");
                if (odc == null)
                {
                    bl = false;
                }
                else
                {
                    bl = true;
                }
            }
            catch
            {
                bl = false;
            }
            return bl;
        }

        /// <summary>
        /// 判断数据库是否存在指定的表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static bool TableExists(string table)
        {
            OleDbConnection Connection = CreateConnection();
            Connection.Open();
            var exists = Connection.GetSchema("Tables", new string[4] { null, null, table, "TABLE" }).Rows.Count > 0;
            Connection.Close();
            return exists;
        }

        #endregion

        #region 关闭到数据库的连接并释放所有资源。
        /// <summary>
        /// 关闭到数据库的连接并释放所有资源。
        /// </summary>
        /// <param name="conn"></param>
        public static void CloseConnection(OleDbConnection Connection)
        {
            while (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
                Connection.Dispose();
            }
        }
        #endregion

        #region 执行简单的SQL语句

        #region 执行用户输入的SQL语句并返回受影响的行数。
        /// <summary>
        /// 执行用户输入的SQL语句并返回受影响的行数。
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string Sql)
        {
            using (OleDbConnection Connection = CreateConnection())
            {
                using (OleDbCommand Cmd = new OleDbCommand(Sql, Connection))
                {
                    try
                    {
                        Connection.Open();
                        int rows = Cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch
                    {
                        return 0;
                    }
                    finally
                    {
                        CloseConnection(Connection);
                    }
                }
            }
        }
        #endregion

        #region 根据用户输入的查询语句获取数据集DataTable
        /// <summary>
        /// 根据用户输入的查询语句获取数据集DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql)
        {
            using (OleDbConnection Connection = CreateConnection())
            {
                try
                {
                    DataSet ds = new DataSet();
                    Connection.Open();
                    OleDbDataAdapter Da = new OleDbDataAdapter(sql, Connection);
                    Da.Fill(ds);
                    if (ds != null)
                    {
                        return ds.Tables[0];
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (System.Data.OleDb.OleDbException ex)
                {
                    return null;
                }
                finally
                {
                    CloseConnection(Connection);
                }
            }
        }
        #endregion

        #endregion

        #region 批量处理
        public static void ExecuteSqlTran(List<string> sqlist)
        {
            using (OleDbConnection conn = CreateConnection())
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                OleDbTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < sqlist.Count; n++)
                    {
                        string strsql = sqlist[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OleDb.OleDbException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        #endregion

        #region 自增更新
        public static void AddFieldValue(List<string> listsql)
        {
            int flg = 0;
            for (int i = 0; i < listsql.Count; i++)
            {
                List<string> list_sqls = new List<string>();
                string sql = "SELECT * FROM " + listsql[i] + "";
                DataTable dt = AccessHelper.ExecuteDataTable(sql);
                if (dt.Rows.Count > 0)
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        string update = "UPDATE " + sql[i] + " SET BSM=" + flg + i + 1 + " WHERE OBJECTID =" + Convert.ToInt32(dt.Rows[j]["OBJECTID"]) + "";
                        list_sqls.Add(update);
                    }
                    ExecuteSqlTran(list_sqls);
                }
                flg = dt.Rows.Count;
            }
        }
        #endregion
    }
}