C# 判断检测 access 建库建表文件是否存在等数据操作使用 ADOX

本文链接：https://blog.csdn.net/fwj380891124/article/details/7183852

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

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();