C#動態建立及串連ACCESS資料庫的詳細步驟__資料庫

串連ACCESS資料庫的詳細步驟

一、建立FORM表單，加一個按鈕控制項，加一個DATAGRIDVIEW控制項。

二、雙擊FORM，加入命名空間using System.Data.OleDb;

       雙擊按鈕，進入按鈕代碼，寫如下代碼

            OleDbConnection strConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "員工資訊.mdb" + ";Persist Security Info=False");

            //建立資料庫引擎串連，注意資料表（尾碼為.db）應放在DEBUG檔案下
            OleDbDataAdapter myda = new OleDbDataAdapter("select * from 僱員 ,strConnection);

           //建立適配器，通過SQL語句去搜尋資料庫
            DataSet myds = new DataSet();

           //建立資料集
            myda.Fill(myds, "僱員");

           //用FILL的方式將適配器已經串連好的資料表填充到資料集MYDS這張表

           dataGridView1.DataSource = myds.Tables["連絡人ID"];

          //用顯示控制項來顯示表

三、按F5運行後，點擊BUTTON按鈕，便會顯示相應的SQL語句下的資料庫裡的表。

     

下面利用Command和reader對象在控制台應用程式下輸出資料。

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.OleDb;namespace ConsoleApplication19{    class Program    {        static void Main(string[] args)        {            OleDbConnection mycon =null;            OleDbDataReader myReader=null;            try            {                string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db4.mdb;";                mycon = new OleDbConnection(strcon);                mycon.Open();                string sql = "select * from 僱員 ";                OleDbCommand mycom = new OleDbCommand(sql, mycon);                myReader = mycom.ExecuteReader();                while (myReader.Read())                {                    Console.WriteLine(myReader.GetString(0)+" "+myReader.GetDouble(1)+" "+myReader.GetString(2)+" "+myReader.GetString(3)+" "+myReader.GetString(4));                }            }            finally             {                myReader.Close();                mycon.Close();                            }        }    }}

<span style="font-size:24px;color:#ff0000;"><strong>動態建立代碼：</strong></span>

//添加兩個com組件引用//Microsoft ADO Ext. 2.8 for DDL and Security//Microsoft ActiveX Data Objects 2.8 Library  <p></p><p>using System;using System.Collections.Generic;using System.Linq;using System.Text;using ADOX;using System.IO;</p><p>namespace WebRequestTest.Common{    public static class AccessDbHelper    {        /// <summary>        /// 建立access資料庫        /// </summary>        /// <param name="filePath">資料庫檔案的全路徑，如 D:\\NewDb.mdb</param>        public static bool CreateAccessDb(string filePath)        {            ADOX.Catalog catalog = new Catalog();            if (!File.Exists(filePath))            {                try                {                    catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;DData Source=" + filePath + ";Jet OLEDB:Engine Type=5");                }                catch (System.Exception ex)                {                    return false;                }            }            return true;        }</p><p>        /// <summary>        /// 在access資料庫中建立表        /// </summary>        /// <param name="filePath">資料庫表檔案全路徑如D:\\NewDb.mdb 沒有則建立 </param>         /// <param name="tableName">表名</param>        /// <param name="colums">ADOX.Column對象數組</param>        public static void CreateAccessTable(string filePath, string tableName, params ADOX.Column[] colums)        {            ADOX.Catalog catalog = new Catalog();            //資料庫檔案不存在則建立            if (!File.Exists(filePath))            {                try                {                    catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");                }                catch (System.Exception ex)                {</p><p>                }            }            ADODB.Connection cn = new ADODB.Connection();            cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);            catalog.ActiveConnection = cn;            ADOX.Table table = new ADOX.Table();            table.Name = tableName;            foreach (var column in colums)            {                table.Columns.Append(column);            }           // column.ParentCatalog = catalog;             //column.Properties["AutoIncrement"].Value = true; //設定自動成長            //table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null); //定義主鍵            catalog.Tables.Append(table);            cn.Close();        }            //========================================================================================調用           //ADOX.Column[] columns = {           //                     new ADOX.Column(){Name="id",Type=DataTypeEnum.adInteger,DefinedSize=9},           //                     new ADOX.Column(){Name="col1",Type=DataTypeEnum.adWChar,DefinedSize=50},           //                     new ADOX.Column(){Name="col2",Type=DataTypeEnum.adLongVarChar,DefinedSize=50}           //                 };           // AccessDbHelper.CreateAccessTable("d:\\111.mdb", "testTable", columns);    }}</p>