[C#]AccessUtils_.NET_编程开发_程序员俱乐部

关键代码：

class="csharpcode">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操作的简单封装，希望有所帮助！

本人才疏学浅，难免错误，敬请指出，谢谢！