操作Access數據庫的C#封裝類

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

namespace AccessOperator
{
    class AccessHelper
    {
        private string conn_str = null;
        private OleDbConnection ole_connection = null;
        private OleDbCommand ole_command = null;
        private OleDbDataReader ole_reader = null;
        private DataTable dt = null;

    /// <summary>
    /// 構造函數
    /// </summary>
    public AccessHelper()
    {
        conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\我的文檔\Visual Studio 2008\Projects\AccessOperator\AccessOperator\bin\Debug\myDb.mdb'";
        InitDB();
    }

    private void InitDB()
    {
        ole_connection = new OleDbConnection(conn_str); //創建實例
        ole_command = new OleDbCommand();
    }

    /// <summary>
    /// 構造函數
    /// </summary>
    /// <param name="db_path">數據庫路徑</param>
    public AccessHelper(string db_path)
    {
        conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + db_path + "'";
        InitDB();
    }

    /// <summary>
    /// 轉換數據格式
    /// </summary>
    /// <param name="reader">數據源</param>
    /// <returns>數據列表</returns>
    private DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader)
    {
        DataTable dt_tmp = null;
        DataRow dr = null;
        int data_column_count = 0;
        int i = 0;

        data_column_count = reader.FieldCount;
        dt_tmp = BuildAndInitDataTable(data_column_count);

        if (dt_tmp == null)
        {
            return null;
        }

        while (reader.Read())
        {
            dr = dt_tmp.NewRow();

            for (i = 0; i < data_column_count; ++i)
            {
                dr[i] = reader[i];
            }

            dt_tmp.Rows.Add(dr);
        }

        return dt_tmp;
    }

    /// <summary>
    /// 創建并初始化數據列表
    /// </summary>
    /// <param name="Field_Count">列的個數</param>
    /// <returns>數據列表</returns>
    private DataTable BuildAndInitDataTable(int Field_Count)
    {
        DataTable dt_tmp = null;
        DataColumn dc = null;
        int i = 0;

        if (Field_Count <= 0)
        {
            return null;
        }

        dt_tmp = new DataTable();

        for (i = 0; i < Field_Count; ++i)
        {
            dc = new DataColumn(i.ToString());
            dt_tmp.Columns.Add(dc);
        }

        return dt_tmp;
    }

    /// <summary>
    /// 從數據庫里面獲取數據
    /// </summary>
    /// <param name="strSql">查詢語句</param>
    /// <returns>數據列表</returns>
    public DataTable GetDataTableFromDB(string strSql)
    {
        if (conn_str == null)
        {
            return null;
        }

        try
        {
            ole_connection.Open(); //打開連接

            if (ole_connection.State == ConnectionState.Closed)
            {
                return null;
            }

            ole_command.CommandText = strSql;
            ole_command.Connection = ole_connection;

            ole_reader = ole_command.ExecuteReader(CommandBehavior.Default);

            dt = ConvertOleDbReaderToDataTable(ref ole_reader);

            ole_reader.Close();
            ole_reader.Dispose();
        }
        catch (System.Exception e)
        {
            Console.WriteLine(e.ToString());
        }
        finally
        {
            if (ole_connection.State != ConnectionState.Closed)
            {
                ole_connection.Close();
            }
        }

        return dt;
    }

    /// <summary>
    /// 執行sql語句
    /// </summary>
    /// <param name="strSql">sql語句</param>
    /// <returns>返回結果</returns>
    public int ExcuteSql(string strSql)
    {
        int nResult = 0;

        try
        {
            ole_connection.Open(); //打開數據庫連接
            if (ole_connection.State == ConnectionState.Closed)
            {
                return nResult;
            }

            ole_command.Connection = ole_connection;
            ole_command.CommandText = strSql;

            nResult = ole_command.ExecuteNonQuery();
        }
        catch (System.Exception e)
        {
            Console.WriteLine(e.ToString());
            return nResult;
        }
        finally
        {
            if (ole_connection.State != ConnectionState.Closed)
            {
                ole_connection.Close();
            }
        }

        return nResult;
    }

    static void Main(string[] args)
    {
        AccessHelper Helper = new AccessHelper();
        DataTable dt = Helper.GetDataTableFromDB("select * from test");

        foreach (DataRow dr in dt.Rows)
        {
            Console.WriteLine(dr[0].ToString()+" "+dr[1].ToString());
        }

        Console.WriteLine(Helper.ExcuteSql("insert into test(test) values ('hello')"));
    }
}

}</pre>