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

namespace AccessIReaderDemo
{
    public class AccessHelper
    {
        string str = @"provider=Microsoft.Jet.OLEDB.4.0; Data Source=F:\邯钢工作计划\物理站\华龙冲击\CBDdata2018.mdb";
        private static string Str;

        public AccessHelper(string _connstr)
        {
            Str = _connstr;
        }
        public static DataSet ExceDataSet(string sql, OleDbParameter[] parm = null,
            CommandType type = CommandType.Text)
        {
            using (OleDbConnection conn = new OleDbConnection(Str))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    if (parm != null)
                        cmd.Parameters.AddRange(parm);
                    OleDbDataAdapter abs = new OleDbDataAdapter(cmd);
                    DataSet set = new DataSet();
                    conn.Open();
                    abs.Fill(set);
                    return set;
                }
            }
        }

        /// <summary>
        /// reader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static OleDbDataReader Reader(string sql, OleDbParameter[] parm = null,
            CommandType type = CommandType.Text)
        {
            OleDbConnection conn = new OleDbConnection(Str);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            if (parm != null)
                cmd.Parameters.AddRange(parm);
            conn.Open();
            OleDbDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception e)
            {

                throw;
            }
            return reader;
        }

        /// <summary>
        /// 查询首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static object ExceScale(string sql, OleDbParameter[] parm = null, CommandType type = CommandType.Text)
        {
            using (OleDbConnection conn = new OleDbConnection(Str))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    if (parm != null)
                        cmd.Parameters.AddRange(parm);
                    cmd.CommandType = type;
                    conn.Open();
                    return cmd.ExecuteScalar();

                }
            }
        }

        /// <summary>
        /// 几行受影响
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static int ExceQuery(string sql, OleDbParameter[] parm = null, CommandType type = CommandType.Text)
        {
            using (OleDbConnection conn = new OleDbConnection(Str))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    if (parm != null)
                        cmd.Parameters.AddRange(parm);
                    cmd.CommandType = type;
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

    }
}