using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace student
{
    class dataBase
    {
        public OleDbConnection Con, Con1;                    //sql數(shù)據(jù)連接組件實(shí)例化
        public OleDbCommand command = new OleDbCommand();    //初始化一個SQL命令對象

        public dataBase()                                 //類初始化，初始化數(shù)據(jù)連接
        {
            string path = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=studentSystem.mdb";
            Con = new OleDbConnection(path);
        }

        public OleDbDataReader GetReader(string search)
        {
            OleDbDataReader Reader;
            if (Con.State != ConnectionState.Open)
                Con.Open();
            OleDbCommand Com = new OleDbCommand(search, Con);
            Reader = Com.ExecuteReader();
            return Reader;

        }

        // 輸入查詢字符串，返回dataset
        public DataSet getData(string sql)
        {
            command.Connection = Con;            //配置command對象
            command.CommandText = sql;           //賦予要執(zhí)行的語句
            DataSet dt = new DataSet();          //初始化一個數(shù)據(jù)返回集合
            OleDbDataAdapter da = new OleDbDataAdapter(command);
            Con.Open();                          //打開連接
            da.Fill(dt);                     //執(zhí)行語句
            command.Connection.Close();          //關(guān)閉連接
            return dt;
        }

        //輸入存儲過程名稱，執(zhí)行查詢存儲過程
        public DataSet getDataSet(string produreName)
        {
            command.Connection = Con;            //賦予連接對象
            //執(zhí)行的類型為存儲過程
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = produreName;   //賦予執(zhí)行的存儲過程名字
            DataSet dt = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(command);
            Con.Open();                          //打開連接
            da.Fill(dt);                     //填充數(shù)據(jù)
            command.Connection.Close();
            return dt;                           //返回數(shù)據(jù)集
        }

        // 執(zhí)行非查詢SQL語句
        public int ExecuteSql(string sql)
        {
            if (Con.State != ConnectionState.Open)
                Con.Open();                      //如果數(shù)據(jù)連接關(guān)閉，則打開
            OleDbCommand Com = new OleDbCommand(sql, Con);
            Com.ExecuteNonQuery();               //執(zhí)行非查詢SQL語句
            Con.Close();
            return 1;

        }

        public int Execute(string sql)
        {
            if (Con.State != ConnectionState.Open)
                Con.Open();                      //如果數(shù)據(jù)連接關(guān)閉，則打開
            OleDbCommand Com = new OleDbCommand(sql, Con);
            Com.ExecuteNonQuery();               //執(zhí)行非查詢SQL語句
            return 1;

        }
        // 執(zhí)行非查詢數(shù)據(jù)庫操作，是否關(guān)閉數(shù)據(jù)庫連接  可以選擇
        public void ExecuteSql(string sql, bool closeConnection)
        {
            if (Con.State != ConnectionState.Open)
                Con.Open();                      //如果未打開連接，則打開
            OleDbCommand Com = new OleDbCommand(sql, Con);
            Com.ExecuteNonQuery();
            if (closeConnection) Con.Close();    //如果需要關(guān)閉，則關(guān)閉連接

        }

        //輸入存儲過程名，執(zhí)行非查詢存儲過程
        public bool exec(string produreName)
        {
            bool flag = false;                   //任務(wù)是否正確執(zhí)行，初始化為false
            command.Connection = Con;            //賦予command對象以數(shù)據(jù)連接
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = produreName;   //存儲過程名稱
            try
            {
                command.ExecuteNonQuery();        //執(zhí)行存儲過程
                flag = true;                      //正確完成任務(wù)
            }
            finally
            {
                command.Connection.Close();           //關(guān)閉連接
            }
            return flag;                         //返回成功與否的標(biāo)志

        }
        public bool SaveFP2toExcel(string Path, string sql)
        {
            try
            {
                string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'";

                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("寫入Excel發(fā)生錯誤：" + ex.Message);
            }
            return false;
        }

        //public OleDbDataReader GetReader2()
        //{
        //    OleDbConnection Con,Con1;                    //sql數(shù)據(jù)連接組件實(shí)例化
        //    OleDbCommand command = new OleDbCommand();    //初始化一個SQL命令對象
        //    string path = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\student\student\studentSystem.mdb";
        //    Con = new OleDbConnection(path);
        //    OleDbDataReader Reader;
        //    if (Con.State != ConnectionState.Open)
        //        Con.Open();
        //    OleDbCommand Com = new OleDbCommand(search, Con);
        //    Reader = Com.ExecuteReader();
        //    return Reader;

        //}
        public OleDbDataReader ExcelToDS(string Path)
        {
            OleDbDataReader Reader;
            string path1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            Con1 = new OleDbConnection(path1);
            OleDbConnection conn = new OleDbConnection(path1);
            string strExcel = "select * from [sheet1$]";
            if (Con1.State != ConnectionState.Open)
                Con1.Open();
            OleDbCommand Com = new OleDbCommand(strExcel, Con1);
            Reader = Com.ExecuteReader();
            return Reader;
        }

        //public DataSet ExcelToDS(string Path)
        //{
        //    string path1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        //    Con1 = new OleDbConnection(path1);
        //    OleDbConnection conn = new OleDbConnection(path1);
        //    conn.Open();
        //    string strExcel = "";
        //    OleDbDataAdapter dataAdapter = null;
        //    DataSet ds = null;
        //    strExcel = "select * from [sheet1$]";
        //    dataAdapter = new OleDbDataAdapter(strExcel, path1);
        //    ds = new DataSet();
        //    dataAdapter.Fill(ds, "table1");
        //    return ds;       
        //} 



    }

}