C# 串連 Access 操作

C# 串連 Access 操作[轉，整理]
--------------------------------------------------------------------------------

1、配置web.config檔案：設定資料庫串連參數

<configuration>    <appSettings/>    <connectionStrings>        <add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\TeacherSystem\App_Data\db.mdb;Jet OLEDB:Database Password=123"        providerName="System.Data.OleDb" /></connectionStrings>

2、程式設計開始：

1、按要求建立Access資料庫及資料表

2、編寫資料庫訪問、操作的公用類，此類可以複用到以後開發的任何系統中

（1）、建立一個C# 類庫項目， 命名為“Com.LXJ”，設定項目屬性：程式集名稱、預設命名空間均為“Com.LXJ”

（2）、在此項目目錄下建立目錄Database，建立C# 類檔案ConnDbForAccess.cs 在Database目錄下。

          添加引用：System.Web.dll

（3）、編寫ConnDbForAccess.cs 的代碼

using System;using System.Data;using System.Data.OleDb;using System.Web;using System.Web.UI;using System.Configuration; namespace Com.LXJ.Database{    /// <summary>    /// conn 的摘要說明。    /// </summary>    public class ConnDbForAcccess    {        /// <summary>        /// 串連資料庫字串        /// </summary>        private string connectionString;         /// <summary>        /// 儲存資料庫連接（保護類，只有由它派生的類才能訪問）        /// </summary>        protected OleDbConnection Connection;         /// <summary>        /// 建構函式：資料庫的預設串連        /// </summary>        public ConnDbForAcccess()        {            string connStr;            connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();            // connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString(); //從web.config配置中讀取              connectionString = connStr;            //connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + connStr;            // connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();            //            Connection = new OleDbConnection(connectionString);        }         /// <summary>        /// 建構函式：帶有參數的資料庫連接        /// </summary>        /// <param name="newConnectionString"></param>        public ConnDbForAcccess(string newConnectionString)        {            //connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + newConnectionString;            connectionString = newConnectionString;            Connection = new OleDbConnection(connectionString);        }         /// <summary>        /// 獲得連接字串        /// </summary>        public string ConnectionString        {            get            {                return connectionString;            }        }         /// <summary>        /// 執行SQL語句沒有返回結果，如：執行刪除、更新、插入等操作        /// </summary>        /// <param name="strSQL"></param>        /// <returns>操作成功標誌</returns>        public bool ExeSQL(string strSQL)        {            bool resultState = false;             Connection.Open();            OleDbTransaction myTrans = Connection.BeginTransaction();            OleDbCommand command = new OleDbCommand(strSQL, Connection, myTrans);             try            {                command.ExecuteNonQuery();                myTrans.Commit();                resultState = true;            }            catch            {                myTrans.Rollback();                resultState = false;            }            finally            {                Connection.Close();            }            return resultState;        }         /// <summary>        /// 執行SQL語句返回結果到DataReader中        /// </summary>        /// <param name="strSQL"></param>        /// <returns>dataReader</returns>        private OleDbDataReader ReturnDataReader(string strSQL)        {            Connection.Open();            OleDbCommand command = new OleDbCommand(strSQL, Connection);            OleDbDataReader dataReader = command.ExecuteReader();            Connection.Close();             return dataReader;        }         /// <summary>        /// 執行SQL語句返回結果到DataSet中        /// </summary>        /// <param name="strSQL"></param>        /// <returns>DataSet</returns>        public DataSet ReturnDataSet(string strSQL)        {            Connection.Open();            DataSet dataSet = new DataSet();            OleDbDataAdapter OleDbDA = new OleDbDataAdapter(strSQL, Connection);            OleDbDA.Fill(dataSet, "objDataSet");             Connection.Close();            return dataSet;        }         /// <summary>        /// 執行一查詢語句，同時返回查詢結果數目        /// </summary>        /// <param name="strSQL"></param>        /// <returns>sqlResultCount</returns>        public int ReturnSqlResultCount(string strSQL)        {            int sqlResultCount = 0;             try            {                Connection.Open();                OleDbCommand command = new OleDbCommand(strSQL, Connection);                OleDbDataReader dataReader = command.ExecuteReader();                 while (dataReader.Read())                {                    sqlResultCount++;                }                dataReader.Close();            }            catch            {                sqlResultCount = 0;            }            finally            {                Connection.Close();            }            return sqlResultCount;        }     }//}//