[C#]AccessUtils

標籤：des   style   blog   http   color   io   os   ar   for   

關鍵代碼：

using System;using System.Data;using System.Data.OleDb;namespace CSharpUtilHelpV2{    /// <summary>    /// 基於.NET 2.0的ACCESS資料簡單操作類    /// </summary>    public class AccessUtilsV2    {        string ConnectString = string.Empty;        /// <summary>        /// 建構函式        /// </summary>        /// <param name="path">access路徑</param>        public AccessUtilsV2(string path)        {            ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path;        }        /// <summary>        /// 建構函式        /// </summary>        /// <param name="path">access路徑</param>        /// <param name="password">access密碼</param>        public AccessUtilsV2(string path, string password)        {            ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Database Password= " + password;        }        /// <summary>        /// ExecuteNonQuery        /// </summary>        /// <param name="sql">查詢，修改，刪除sql語句</param>        /// <param name="parameters">OleDbParameter參數；eg: new OleDbParameter("@categoryName","Test2")</param>        /// <returns>操作影響行數</returns>        public int ExecuteNonQuery(string sql, OleDbParameter[] parameters)        {            int _affectedRows = -1;            using (OleDbConnection sqlcon = new OleDbConnection(ConnectString))            {                sqlcon.Open();                using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))                {                    if (parameters != null)                        sqlcmd.Parameters.AddRange(parameters);                    _affectedRows = sqlcmd.ExecuteNonQuery();                }            }            return _affectedRows;        }        /// <summary>        /// ExecuteReader        /// </summary>        /// <param name="sql">讀取sql語句</param>        /// <param name="parameters">OleDbParameter參數；eg: new OleDbParameter("@categoryName","Test2")</param>        /// <returns>IDataReader</returns>        public IDataReader ExecuteReader(string sql, OleDbParameter[] parameters)        {            OleDbConnection _sqlcon = new OleDbConnection(ConnectString);            using (OleDbCommand _sqlcmd = new OleDbCommand(sql, _sqlcon))            {                if (parameters != null)                    _sqlcmd.Parameters.AddRange(parameters);                _sqlcon.Open();                return _sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);            }        }        /// <summary>        /// ExecuteDataTable        /// </summary>        /// <param name="sql">讀取sql語句</param>        /// <param name="parameters">OleDbParameter參數；eg: new OleDbParameter("@categoryName","Test2")</param>        /// <returns>DataTable</returns>        public DataTable ExecuteDataTable(string sql, OleDbParameter[] parameters)        {            using (OleDbConnection _sqlcon = new OleDbConnection(ConnectString))            {                using (OleDbCommand _sqlcmd = new OleDbCommand(sql, _sqlcon))                {                    if (parameters != null)                        _sqlcmd.Parameters.AddRange(parameters);                    using (OleDbDataAdapter _sqldap = new OleDbDataAdapter(_sqlcmd))                    {                        DataTable _dt = new DataTable();                        _sqldap.Fill(_dt);                        return _dt;                    }                }            }        }        /// <summary>        /// ExecuteScalar        /// </summary>        /// <param name="sql">查詢第一行第一列資料值</param>        /// <param name="parameters">OleDbParameter參數；eg: new OleDbParameter("@categoryName","Test2")</param>        /// <returns>Object</returns>        public Object ExecuteScalar(string sql, OleDbParameter[] parameters)        {            using (OleDbConnection _sqlcon = new OleDbConnection(ConnectString))            {                using (OleDbCommand _sqlcmd = new OleDbCommand(sql, _sqlcon))                {                    if (parameters != null)                        _sqlcmd.Parameters.AddRange(parameters);                    _sqlcon.Open();                    return _sqlcmd.ExecuteScalar();                }            }        }    }}

測試代碼：

using CSharpUtilHelpV2;using System;using System.Data;using System.Data.OleDb;using System.IO;namespace CSharpUtilHelpV2Test{    public class AccessUtilsV2Test    {        static AccessUtilsV2 AccessHelper = new AccessUtilsV2(string.Format(@"{0}\DB\Northwind.MDB", AppDomain.CurrentDomain.BaseDirectory.ToString()));        public static void ShowAccessPath()        {            string _fullPath = string.Format(@"{0}\DB\Northwind.MDB", AppDomain.CurrentDomain.BaseDirectory.ToString());            Console.WriteLine(_fullPath);            Console.WriteLine("File.Exist:" + File.Exists(_fullPath));        }        public static void ExecuteNonQueryTest()        {            //-------------------------方式一---------------------------------------            //string _sql = "INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES   (22, ‘Test‘, ‘測試‘)";            //Console.WriteLine("ExecuteNonQueryTest：" + AccessHelper.ExecuteNonQuery(_sql, null));            //-------------------------方式二---------------------------------------            string _sql = "INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES   (@id, @categoryName, @description)";            OleDbParameter[] _paramter = new OleDbParameter[] {                new OleDbParameter("@id",23),               new OleDbParameter("@categoryName","Test2"),               new OleDbParameter("@description","測試2")            };            Console.WriteLine("ExecuteNonQueryTest：" + AccessHelper.ExecuteNonQuery(_sql, _paramter));        }        public static void ExecuteReaderTest()        {            string _sql = "select * from Categories where CategoryID<=5";            using (IDataReader reader = AccessHelper.ExecuteReader(_sql, null))            {                while (reader.Read())                {                    Console.WriteLine("CategoryID：" + reader["CategoryID"] + " CategoryName:" + reader["CategoryName"]);                }            }        }        public static void ExecuteDataTableTest()        {            string _sql = "select * from Categories where CategoryID<=5";            Console.WriteLine("ExecuteDataTableTest:" + AccessHelper.ExecuteDataTable(_sql, null).Rows.Count);        }        public static void ExecuteScalarTest()        {            string _sql = "select * from Categories where CategoryID<=5";            Console.WriteLine("ExecuteScalarTest:" + AccessHelper.ExecuteScalar(_sql, null) ?? "Null");        }    }}

測試結果：

這裡是對Access操作的簡單封裝，希望有所協助！

本人才疏學淺，難免錯誤，敬請指出，謝謝！

[C#]AccessUtils