dllee

自行安裝了 .Net 後，就一直想要用 .Net 內建的資料庫功能開啟 MS Access 資料庫，不過，試了好幾次都試不出來... 今天找了一堆資料，總算是試出來了 ^_^

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace testDB
{
  public partial class Form1 : Form
  {
    /// The connection to the database
    private OleDbConnection oleDbConnection;
    private OleDbDataAdapter oleDbDataAdapter = null;
    private string AppTitle = "簡易 MS Access 資料庫查閱器";
    public Form1()
    {
      InitializeComponent();
      oleDbConnection = new OleDbConnection();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      // 參考 VB.NET 取得 MS Access 資料庫表格名稱的方式
      // http://www.vbdotnetheaven.com/UploadFile/mahesh/GettingDBSchema04232005053116AM/GettingDBSchema.aspx?ArticleID=66412256-6bcf-4113-afbc-d3dd8b3cff00
      if (openFileDialog.ShowDialog() == DialogResult.OK)
      {
        this.label1.Text = openFileDialog.FileName;
        string conect = "Provider=Microsoft.jet.oledb.4.0;Data Source=" + openFileDialog.FileName;
        oleDbConnection.ConnectionString = conect;
        oleDbConnection.Open();
        DataTable schemaTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
          new object[] { null, null, null, "TABLE" });
        // this.dataGridView1.DataSource = schemaTable; // 顯示全部的資料

        DataTable TableList = new DataTable("Table");
        DataColumn newdc = new DataColumn("Table");     // Column 名稱為 "Table"
        TableList.Columns.Add(newdc);
        DataRowCollection rowcoll = TableList.Rows;
        for (int i = 0; i < schemaTable.Rows.Count; i++)
        {
          DataRow rd = schemaTable.Rows[i];
          if (rd["TABLE_TYPE"].ToString() == "TABLE")
          {
            rowcoll.Add(new object[] { rd["TABLE_NAME"] });
          }
        }
        this.dataGridView1.DataSource = TableList;        // 只顯示表格名稱
        oleDbConnection.Close();
        this.Text = AppTitle + " Double Click 表格名稱以快速檢視";
      }
    }

    private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
    {
      DataTable dt = (DataTable)this.dataGridView1.DataSource;
      // MessageBox.Show(dt.Rows[e.RowIndex][0].ToString());
      string sel = "SELECT * FROM " + dt.Rows[e.RowIndex][0].ToString();
      if (oleDbDataAdapter != null)
      {
        oleDbDataAdapter.Dispose();
        oleDbDataAdapter = null;
      }
      this.Text = AppTitle + " 選擇資料表 " + dt.Rows[e.RowIndex][0].ToString();

      try
      {
        // 參考 http://www.mcse.ms/archive107-2005-6-1706157.html
        // 把資料表放入 DataGridView (.Net2.0) 中
        oleDbDataAdapter = new OleDbDataAdapter(sel, oleDbConnection);
        OleDbCommandBuilder oleDbCommandBuilder = new OleDbCommandBuilder(oleDbDataAdapter);
        oleDbDataAdapter.UpdateCommand = oleDbCommandBuilder.GetUpdateCommand();
        DataSet ds = new DataSet();
        this.Text = AppTitle + " 讀取資料表 " + dt.Rows[e.RowIndex][0].ToString();
        oleDbDataAdapter.Fill(ds, dt.Rows[e.RowIndex][0].ToString());
        this.dataGridView2.DataSource = ds.Tables[0];
        this.Text = AppTitle + " 讀完資料表 " + dt.Rows[e.RowIndex][0].ToString();
      }
      catch
      {
        this.Text = AppTitle + " 空的資料表 " + dt.Rows[e.RowIndex][0].ToString();
      }
      oleDbConnection.Close();
    }
  }
}