[ASP.net] 如何判斷Access中是否有某一指定資料表

2011-07-15

Access的mdb檔，我存成2003格式，以下是內容，只有一個Categories

接著看前端ASPX程式

<%@ Page Debug="true" Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
      <div>
          <asp:TextBox runat="server" ID="txt_table" />&nbsp;<asp:Button Text="查詢" 
              ID="btn_Query" runat="server" onclick="btn_Query_Click" />
      </div>
      <div>
          <asp:GridView runat="server" ID="gv_table" />
      
      </div>
    </form>
</body>
</html>

後置程式碼 .CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
public partial class _Default : System.Web.UI.Page
{

    protected string Conn_E = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test\App_Data\NorthwindChinese.mdb;Persist Security Info=True";
   
    protected void btn_Query_Click(object sender, EventArgs e)
    {
        //參考：http://stackoverflow.com/questions/201282/how-to-get-table-names-from-access
        //This method works for all versions of MS Access. ↓
        //參考：http://davidhayden.com/blog/dave/archive/2006/10/01/GetListOfTablesInMicrosoftAccessUsingGetSchema.aspx

        // Microsoft Access provider factory
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        DataTable userTables = null;
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = this.Conn_E;

            // We only want user tables, not system tables
            string[] restrictions = new string[4];
            restrictions[3] = "Table";

            connection.Open();

            // Get list of user tables
            userTables = connection.GetSchema("Tables", restrictions);
        }

        DataTable dt = new DataTable();//準備呈現用的DataTable(經過TextBox篩選過的)
        dt.Columns.Add("TABLE_NAME");//定義欄位
        foreach (DataRow dr in userTables.Rows)//走訪剛剛從Access撈出來的Tables資訊
        {
            if (dr["TABLE_NAME"].ToString().ToLower()==txt_table.Text.Trim().ToLower())//如果Access撈出來的Table名稱等於TextBox輸入的名稱
            {
                DataRow dt_r = dt.NewRow();
                dt_r["TABLE_NAME"] = dr["TABLE_NAME"].ToString();
                dt.Rows.Add(dt_r);
            }
        }

        if (txt_table.Text.Trim()=="")//沒輸入查詢條件
        {
            gv_table.DataSource = userTables;//呈現結果
            gv_table.DataBind();
        }
        else
        {
            gv_table.DataSource = dt;//呈現結果
            gv_table.DataBind();

        }
        

        // Add list of table names to listBox
        /*for (int i = 0; i < userTables.Rows.Count; i++)
            listBox1.Items.Add(userTables.Rows[i][2].ToString());*/
 


    }
}

執行結果：

沒輸入查詢字串

輸入categories：

程式碼懶人包下載(內附Access資料庫)

回首頁