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

namespace UAS_MES.DataOperate
{
    class AccessDBHelper
    {
        private string _fileName;
        private string _connectionString;
        private OleDbConnection _odcConnection = null;

        public AccessDBHelper(string fileName)
        {
            this._fileName = fileName;
            this._connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";";
        }


        public AccessDBHelper(string fileName,string password)
        {
            this._fileName = fileName;
            this._connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Jet OLEDB:Database Password="+password+";";
        }

        public void Open()
        {
            try
            {
                // 建立连接
                this._odcConnection = new OleDbConnection(this._connectionString);
                // 打开连接
                this._odcConnection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void Close()
        {
            this._odcConnection.Close();
        }


        public DataTable GetDataTable(string sql)
        {
            DataTable ds = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this._odcConnection);
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception("sql語句： " + sql + " 執行失敗！" + ex.Message);
            }
            adapter.Dispose();
            return ds;
        }

        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this._odcConnection);
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception("sql語句： " + sql + " 執行失敗！"+ex.Message);
            }
            adapter.Dispose();
            return ds;
        }

        /// <summary>
        /// 通过条件更新
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="update"></param>
        /// <param name="condition"></param>
        public string UpdateByCondition(string TableName, string update, string condition)
        {
            DataTable result = new DataTable();
            string sql = "update " + TableName + " set " + update + " where " + condition;
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this._odcConnection);
            try
            {
                adapter.Fill(result);
            }
            catch (Exception ex)
            {
                throw new Exception("sql語句： " + sql + " 執行失敗！" + ex.Message);
            }
            adapter.Dispose();
            return sql;
        }      
    }
}