C#操作ACCESS的一些常用類

 

using System;
using System.Configuration;
using System.Collections;
using System.Data;
using System.Data.OleDb;
namespace MyData
{
    public static class Class_oledb_conn
    {
        //public static string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DataBase/*.mdb;";
        public static string ConnStr = MyData.Properties.Settings.Default.my_soft_oledbConn;
        //開啟資料庫連結
        public static OleDbConnection Open_Conn(string ConnStr)
        {
            OleDbConnection Conn = new OleDbConnection(ConnStr);
            Conn.Open();
            return Conn;
        }
        //關閉資料庫連結
        public static void Close_Conn(OleDbConnection Conn)
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }
        //運行OleDb語句
        public static int Run_SQL(string SQL, string ConnStr)
        {
            OleDbConnection Conn = Open_Conn(ConnStr);
            OleDbCommand Cmd = Create_Cmd(SQL, Conn);
            try
            {
                int result_count = Cmd.ExecuteNonQuery();
                Close_Conn(Conn);
                return result_count;
            }
            catch
        {
            Close_Conn(Conn);
                return 0;
            }
        }
        // 產生Command對象
        public static OleDbCommand Create_Cmd(string SQL, OleDbConnection Conn)
        {
            OleDbCommand Cmd = new OleDbCommand(SQL, Conn);
            return Cmd;
        }
        // 運行OleDb語句返回 DataTable
        public static DataTable Get_DataTable(string SQL, string ConnStr, string Table_name)
        {
            OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
            DataTable dt = new DataTable(Table_name);
            Da.Fill(dt);
            return dt;
        }
        // 運行OleDb語句返回 OleDbDataReader對象
        public static OleDbDataReader Get_Reader(string SQL, string ConnStr)
        {
            OleDbConnection Conn = Open_Conn(ConnStr);
            OleDbCommand Cmd = Create_Cmd(SQL, Conn);
            OleDbDataReader Dr;
            try
            {
                Dr = Cmd.ExecuteReader(CommandBehavior.Default);
            }
            catch
        {
            throw new Exception(SQL);
            }
            Close_Conn(Conn);
            return Dr;
        }
        // 運行OleDb語句返回 OleDbDataAdapter對象
        public static OleDbDataAdapter Get_Adapter(string SQL, string ConnStr)
        {
            OleDbConnection Conn = Open_Conn(ConnStr);
            OleDbDataAdapter Da = new OleDbDataAdapter(SQL, Conn);
            return Da;
        }
        // 運行OleDb語句,返回DataSet對象
        public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds)
        {
            OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
            try
            {
                Da.Fill(Ds);
            }
            catch (Exception Err)
            {
                throw Err;
            }
            return Ds;
        }
        // 運行OleDb語句,返回DataSet對象
        public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, string tablename)
        {
            OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
            try
            {
                Da.Fill(Ds, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            return Ds;
        }
        // 運行OleDb語句,返回DataSet對象，將資料進行了分頁
        public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, int StartIndex, int PageSize, string tablename)
        {
            OleDbConnection Conn = Open_Conn(ConnStr);
            OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
            try
            {
                Da.Fill(Ds, StartIndex, PageSize, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            Close_Conn(Conn);
            return Ds;
        }
        // 返回OleDb語句執行結果的第一行第一列
        public static string Get_Row1_Col1_Value(string SQL, string ConnStr)
        {
            OleDbConnection Conn = Open_Conn(ConnStr);
            string result;
            OleDbDataReader Dr;
            try
            {
                Dr = Create_Cmd(SQL, Conn).ExecuteReader();
                if (Dr.Read())
                {
                    result = Dr[0].ToString();
                    Dr.Close();
                }
                else
                {
                    result = "";
                    Dr.Close();
                }
            }
            catch
        {
            throw new Exception(SQL);
            }
            Close_Conn(Conn);
            return result;
        }
    }
}

 

------------------------------------------------------------------
app.config 裡的資料庫連結字串
------------------------------------------------------------------

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="MyData.Properties.Settings.my_soft_oledbConn" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=資料庫路徑\*.mdb"
providerName="System.Data.OleDb" />
</connectionStrings>
</configuration>