Access数据库连接封装类

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace AccessSQLHelper
{
    public static class AccessHelper
    {
        //从配置文件中获取连接字符串   readonly修饰的变量只能在初始化或构造函数中赋值；其他地方只能读取
        //private static readonly string constr = ConfigurationManager.ConnectionStrings["OperationHistory"].ConnectionString;
        private static string constr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = G:\\AA封装类\\Access数据库\\AccessTest.mdb ";

        /// <summary>
        /// 配置Access数据库连接字符串
        /// </summary>
        /// <param name="pathConnection">Access数据库路径</param>
        /// <param name="passWord">密码</param>
        public static void AccessConnection(string pathConnection, string passWord)
        {
            constr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + pathConnection;
            if (passWord != null)
            {
                constr += "Jet OLEDB:Database Password = " + passWord;
            }
        }

        /// <summary>
        /// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数，执行其他
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="cmdtype">SQL语句（CommandType.Text）或者存储过程(CommandType.StoredProcedure)</param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            int i = -1;
            try
            {
                using (OleDbConnection con = new OleDbConnection(constr))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, con))
                    {
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }

                        con.Open();
                        i = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

            return i;
        }

        /// <summary>
        /// 执行查询、查询多少条数据；返回第一行，第一列方法[ExecuteScalar] 返回-1执行失败 
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="cmdtype">SQL语句（CommandType.Text）或者存储过程(CommandType.StoredProcedure)</param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            try
            {
                using (OleDbConnection con = new OleDbConnection(constr))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, con))
                    {
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }

                        con.Open();
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 执行查询，返回多行、多列的方法ExecuteReader()
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="cmdtype">SQL语句（CommandType.Text）或者存储过程(CommandType.StoredProcedure)</param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static OleDbDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            using (OleDbConnection con = new OleDbConnection(constr))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }

                    try
                    {
                        con.Open();
                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                    catch
                    {
                        con.Close();
                        con.Dispose();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行返回多个查询时使用,返回DataTable类型
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="cmdtype">SQL语句（CommandType.Text）或者存储过程(CommandType.StoredProcedure)</param>
        /// <param name="pms">参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            try
            {
                //通过adapter读取数据。
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, constr))
                {
                    if (pms != null)
                    {
                        adapter.SelectCommand.Parameters.AddRange(pms);
                    }

                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}

 

转载于:https://www.cnblogs.com/yuanshuo/p/11528250.html