【Rosion L.X】ASP.NET[C#]的ACCESS資料庫操作類

   //網上很多都是操作SQL SER 的，整理了一下，不是很完善，但不影響使用，呵呵……
       //private string datapatch = ConfigurationSettings.AppSettings["acessconn"];//資料庫地址
                                   
        private string datapatch = "db/global.asa";//資料庫地址
        ///
        /// 取得dataset
        ///
        /// 查詢語句
        ///
        public  DataSet GetDataSet(string Commandtext)
        {       

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
                   "Data Source=" + Server.MapPath(datapatch);
            string strCommandText = Commandtext;

            OleDbConnection myConnection = new OleDbConnection(strConnection);
            myConnection.Open();

            OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

            OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

            DataSet myDataset = new DataSet();

            myAdpater.Fill(myDataset);
           
            myConnection.Close();
            return myDataset;
        }
        ///
        /// 取得表
        ///
        /// 查詢語句
        ///
        public  DataTable GetDataTable(string Commandtext)
        {       

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
                "Data Source=" + Server.MapPath(datapatch);
            string strCommandText =Commandtext;

            OleDbConnection myConnection = new OleDbConnection(strConnection);
            myConnection.Open();

            OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

            OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

            DataSet myDataset = new DataSet();

            myAdpater.Fill(myDataset);

            DataTable mytable = myDataset.Tables[0];

            myConnection.Close();
            return mytable;
        }
        ///
        /// 取得某行的某列的值
        ///
        /// 列的名稱
        /// 所屬表名
        /// 表的主鍵
        /// 列所屬的主鍵值
        ///
        public  string GetDataColum(string strColumnName,string strTableName,string strColumnkey,string strColumnValue)
        {       

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
                "Data Source=" + Server.MapPath(datapatch);
            string strCommandText = "select "+strColumnName+","+strColumnkey+" from "+strTableName;
           
            OleDbConnection myConnection = new OleDbConnection(strConnection);
            myConnection.Open();

            OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

            OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

            DataSet myDataset = new DataSet();

            myAdpater.MissingSchemaAction = MissingSchemaAction.AddWithKey;
           
            myAdpater.Fill(myDataset);
           
            myConnection.Close();
           
            DataTable mytable = myDataset.Tables[0];
            DataRow mydr = mytable.Rows.Find(strColumnValue);
            string mydc = mydr[strColumnName].ToString();
           
           
            return mydc;
        }
       

        ///
        /// 更新某個欄位
        ///
        /// 要更新欄位名稱
        /// 要更新的值
        /// 所屬表名稱
        /// 表中KEY
        /// 表中KEY的值
        public  void UpdateColum(string strColumnName,string strValue,string strTableName,string strColumnKey,string strColumnValue)
        {       

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
                "Data Source=" + Server.MapPath(datapatch);
            string strCommandText = "select "+strColumnKey+","+strColumnName+" from "+strTableName;
           
            OleDbConnection myConnection = new OleDbConnection(strConnection);
            myConnection.Open();

            OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

            OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

            DataSet myDataset = new DataSet();
           
           
            myAdpater.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            myAdpater.Fill(myDataset,strTableName);
           
            DataRow drFindRow = myDataset.Tables[strTableName].Rows.Find(strColumnValue);
 
            drFindRow[strColumnName] = strValue;
           
           
           
            myAdpater.Update(myDataset,strTableName);
           
            myConnection.Close();
           
        }

        ///
        /// 新增行
        ///
        /// 此行中的欄位集合
        /// 此行中的欄位集合的對應值
        /// 所屬表名稱
        /// 表中主鍵

        public  void AddRow(string[]columns,string[]columnvalue,string strTableName,string strColumnKey)
        {       
           
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
                "Data Source=" + Server.MapPath(datapatch);

            string strColumnCount = "";
            foreach(string thiscolunmname in columns)
            {
                strColumnCount = thiscolunmname+","+strColumnCount;
            }

            string strCommandText = "select "+strColumnCount+strColumnKey+" from "+strTableName;
           
            OleDbConnection myConnection = new OleDbConnection(strConnection);
            myConnection.Open();

            OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

            OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

            DataSet myDataset = new DataSet();
           
           
            myAdpater.Fill(myDataset,strTableName);
       
            DataRow newrow = myDataset.Tables[strTableName].NewRow();
           
            for(int i=0;i            {
                string strColumnName = columns[i].ToString();

                newrow[strColumnName] = columnvalue[i].ToString();
            }
               
           
            myDataset.Tables[strTableName].Rows.Add(newrow);
           
            myAdpater.Update(myDataset,strTableName);
           
            myConnection.Close();
           
        }
///
/// 更新資料行
///
/// 要更新的列集合
/// 要更新的列集合的對應值
/// 所屬表名稱
/// 表主鍵
/// 行所屬的ＩＤ
        public  void updateRow(string[]columns,string[]columnvalue,string strTableName,string strColumnKey,string strColumnValue)
        {       

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
                "Data Source=" + Server.MapPath(datapatch);

            string strColumnCount = "";
            foreach(string thiscolunmname in columns)
            {
                strColumnCount = thiscolunmname+","+strColumnCount;
            }

            string strCommandText = "select "+strColumnCount+strColumnKey+" from "+strTableName;
           
            OleDbConnection myConnection = new OleDbConnection(strConnection);
            myConnection.Open();

            OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

            OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

            DataSet myDataset = new DataSet();
           
            myAdpater.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            myAdpater.Fill(myDataset,strTableName);
       
            DataRow updaterow = myDataset.Tables[strTableName].Rows.Find(strColumnValue);
           
            for(int i=0;i            {
                string strColumnName = columns[i].ToString();

                updaterow[strColumnName] = columnvalue[i].ToString();
            }
           
           
            myAdpater.Update(myDataset,strTableName);
           
            myConnection.Close();
           
        }

　
        ///
        /// 刪除行
        ///
        /// 所屬表
        /// 主鍵欄位名
        /// 此行主鍵值
        public  void delectRow(string strTableName,string strColumnKey,string strColumnValue)
        {       

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
                "Data Source=" + Server.MapPath(datapatch);

            string strCommandText = "select "+strColumnKey+" from "+strTableName;
           
            OleDbConnection myConnection = new OleDbConnection(strConnection);
            myConnection.Open();

            OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

            OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

            DataSet myDataset = new DataSet();
           
            myAdpater.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            myAdpater.Fill(myDataset,strTableName);
       
            DataRow newrow = myDataset.Tables[strTableName].Rows.Find(strColumnValue);
           
            newrow.Delete();
           
            myAdpater.Update(myDataset,strTableName);
           
            myConnection.Close();
           
        }

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-402850/，如需轉載，請註明出處，否則將追究法律責任。