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

namespace SOH.Data
{
    public class DBHelpAccess
    {
        //public static readonly string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("yuyue.mdb") + ";Persist Security Info=False ";

        //引导数据库连接数据库调用Web.Config文件
        private string constr;
        private OleDbConnection connection;

        public DBHelpAccess()
        { }

        public DBHelpAccess(string connstr)
        {
            constr = connstr;
        }

        public string ConnectionString
        {
            get
            {
                return constr;
            }
            set
            {
                constr = value;
            }
        }
        //创建连接|DataDirectory|/SchoolDB.mdb  
        public OleDbConnection Connection
        {//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("tushu.mdb")";Persist Security Info=False  
            get
            {
                //OleDbConnection myConn = new OleDbConnection(constr);
                try
                {
                    string connectionString = constr;
                    if (connection == null)
                    {
                        connection = new OleDbConnection(connectionString);
                        //打开连接  
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Broken)
                    {
                        connection.Close();
                        connection.Open();
                    }
                    return connection;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        //（无参）返回执行的行数(删除修改更新)  
        public int ExecuteCommand(string safeSql)
        {
            OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            Connection.Close();

            return result;
        }
        //（有参）  
        public int ExecuteCommand(string sql, params OleDbParameter[] values)
        {
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int a= cmd.ExecuteNonQuery();
            Connection.Close();
            return a;
        }
        //（无参）返回第一行第一列(删除修改更新)  
        public int GetScalar(string safeSql)
        {
            OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            Connection.Close();

            return result;
        }
        //（有参）  
        public int GetScalar(string sql, params OleDbParameter[] values)
        {
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            Connection.Close();
            return result;
        }
        //返回一个DataReader（查询）  
        public OleDbDataReader GetReader(string safeSql)
        {
            OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
            OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        public OleDbDataReader GetReader(string sql, params OleDbParameter[] values)
        {
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        //返回一个DataTable  
        public DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(ds);
            Connection.Close();
            return ds.Tables[0];
        }
        public DataTable GetDataSet(string sql, params OleDbParameter[] values)
        {
            DataSet ds = new DataSet();
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(ds);
            Connection.Close();
            return ds.Tables[0];
        }
    }
}