C# 判斷access建庫、建表、檔案是否存在等

標籤：ODB   schema   col   sts   param   return   false   click   str   

1.建立資料庫

2.判斷表是否存在

3.建立表

1.    #region access資料庫操作 之 建立資料庫
        private void creatMDB(string dbName)
        {
            ADOX.CatalogClass cat = new ADOX.CatalogClass();
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";");
            //ADOX.Catalog cat = new Catalog();
            //cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";Jet OLEDB:Engine Type=5");s
        }
        #endregion

   2.     #region access資料庫操作 之 判斷資料庫中是否存在某表
        public bool GetTables(OleDbConnection conn)
        {
            int result = 0;
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                             new object[] {null, null, null, "TABLE"});

            if (schemaTable != null)
            {

                for (Int32 row = 0; row < schemaTable.Rows.Count; row++)
                {
                    string col_name = schemaTable.Rows[row]["TABLE_NAME"].ToString();
                    if (col_name == "MyChooseStock")
                    {
                        result++;
                    }
                }
            }
            if (result == 0)
                return false;
            return true;
        }
        #endregion

   3.     #region access資料庫操作 之 建立表
        //建立mdb的表,C#操作Access之建立表
        //mdbHead是一個ArrayList，儲存的是table表中的具體列名。
        private void CreateMDBTable(string mdbPath, string tableName, ArrayList mdbHead)
        {
            ADOX.CatalogClass cat = new ADOX.CatalogClass();
            string sAccessConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;  Data Source=" + mdbPath;
            connection = new OleDbConnection(sAccessConnection);
            connection.Open();
             ADODB.Connection cn = new ADODB.Connection();
            cn.Open(sAccessConnection, null, null, -1);

            cat.ActiveConnection = cn;
            //建立一個表,C#操作Access之建立表
            ADOX.TableClass tbl = new ADOX.TableClass();
            tbl.ParentCatalog = cat;//目錄
            tbl.Name = tableName;

            //增加一個自動成長的欄位
            ADOX.ColumnClass col = new ADOX.ColumnClass();
            col.ParentCatalog = cat;
            col.Type = ADOX.DataTypeEnum.adInteger; // 必須先設定欄位類型
            col.Name = "id";
            col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
            col.Properties["AutoIncrement"].Value = true;
            tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0);

            // 增加一個文字欄位
            int size = mdbHead.Count;
            for (int i = 0; i < size; i++)
            {
                //增加一個文字欄位      
                ADOX.ColumnClass col2 = new ADOX.ColumnClass();
                col2.ParentCatalog = cat;
                col2.Name = mdbHead[i].ToString(); //列的名稱   
                col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
                tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500);
            }
            //設定主鍵  
            tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "id", "", "");

            cat.Tables.Append(tbl); //這句把表加入資料庫(非常重要)  ,C#操作Access之建立表
            tbl = null;
            cat = null;
            connection.Close();
        }
        #endregion

4.

using System.Data;
using System.Data.OleDb;

using ADOX; //需添加 Microsoft ADO Ext. 6.0 ***Security

 protected override void buttonOk_Click(object sender, EventArgs e)
        {      

  private OleDbConnection connection;     

   private OleDbDataAdapter dataAdapter;

                      string systemPath = Environment.GetFolderPath(Environment.SpecialFolder.System);
                        if (Directory.Exists(systemPath + "\\data") == false)//如果不存在就建立file檔案夾
                        {
                            Directory.CreateDirectory(systemPath + "\\data");
                        }
                        string favoriteStockMDB = systemPath + "\\data\\Data.mdb";
                        if (!File.Exists(favoriteStockMDB)) creatMDB(favoriteStockMDB);//判斷是否存在資料庫，不存在，則建立
                        connection =
                            new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + favoriteStockMDB);
                        connection.Open();

                        if (!GetTables(connection))
                        {
                            string table_name = "MyChooseStock";
                            ArrayList col_names = new ArrayList();
                            col_names.Add("StockCode");
                            CreateMDBTable(favoriteStockMDB, table_name, col_names);
                            connection.Open();
                        }
                        dataAdapter = new OleDbDataAdapter("select  * from MyChooseStock", connection);
                        DataSet dataSetSelect = new DataSet();
                        dataAdapter.Fill(dataSetSelect, "sel");
                        DataTable dataTable = dataSetSelect.Tables["sel"];
                        for (int i = 0; i < dataTable.Rows.Count; i++)
                        {
                            listSelect.Add(dataTable.Rows[i][1].ToString());
                        }
                        OleDbCommand insertCommand = connection.CreateCommand();
                        insertCommand.CommandText = "insert into MyChooseStock(StockCode) values (?)";
                        insertCommand.Parameters.Add("StockCode", OleDbType.VarChar, 1024);
                        insertCommand.Prepare();
                        insertCommand.Parameters[0].Value = list[0];
                        Int32 row = insertCommand.ExecuteNonQuery();
                        if (row != 0)
                        {
                            MessageBoxHelper.ShowInfo("收藏成功！");
                        }
                        else
                        {
                            MessageBoxHelper.ShowInfo("收藏失敗！");
                        }
                        connection.Close();

}

C# 判斷access建庫、建表、檔案是否存在等