using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using eBookShop.CCL;

namespace eBookShop.DAL
{
    /// <summary>
    /// 類，用于數據訪問的類。
    /// </summary>
    public class Database : IDisposable
    {
        /// <summary>
        /// 保護變量，數據庫連接。
        /// </summary>
        protected SqlConnection Connection;

        /// <summary>
        /// 保護變量，數據庫連接串。
        /// </summary>
        protected String ConnectionString;

        /// <summary>
        /// 構造函數。
        /// </summary>
        /// <param name="DatabaseConnectionString">數據庫連接串</param>
        public Database()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
        }

        /// <summary>
        /// 析構函數，釋放非托管資源
        /// </summary>
        ~Database()
        {
            try
            {
                if (Connection != null)
                    Connection.Close();
            }
            catch (Exception ex)
            {
                Logger.Log.Error("數據庫鏈接錯誤：", ex);
            }
            try
            {
                Dispose();
            }
            catch { }
        }

        /// <summary>
        /// 保護方法，打開數據庫連接。
        /// </summary>
        protected void Open()
        {
            if (Connection == null)
            {
                Connection = new SqlConnection(ConnectionString);
            }
            if (Connection.State.Equals(ConnectionState.Closed))
            {
                Connection.Open();
            }
        }

        /// <summary>
        /// 公有方法，關閉數據庫連接。
        /// </summary>
        public void Close()
        {
            if (Connection != null)
                Connection.Close();
        }

        /// <summary>
        /// 公有方法，釋放資源。
        /// </summary>
        public void Dispose()
        {
            // 確保連接被關閉
            if (Connection != null)
            {
                Connection.Dispose();
                Connection = null;
            }
        }

        /// <summary>
        /// 公有方法，獲取數據，返回一個SqlDataReader （調用后主意調用SqlDataReader.Close()）。
        /// </summary>
        /// <param name="SqlString">Sql語句</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader GetDataReader(String SqlString)
        {
            Open();
            SqlCommand cmd = new SqlCommand(SqlString, Connection);
            SqlDataReader dataReader = null;
            try
            {
                dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                Logger.Log.Error("GetDataReader錯誤：", ex);
            }
            return dataReader;
        }

        /// <summary>
        /// 公有方法，獲取數據，返回一個DataSet。
        /// </summary>
        /// <param name="SqlString">Sql語句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(String SqlString)
        {
            Open();
            DataSet dataset = new DataSet();
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(SqlString, Connection);
                adapter.Fill(dataset);
                Close();
            }
            catch (Exception ex)
            {
                Logger.Log.Error("GetDataSet錯誤：", ex);
            }
            return dataset;
        }

        /// <summary>
        /// 公有方法，獲取數據，返回一個DataTable。
        /// </summary>
        /// <param name="SqlString">Sql語句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(String SqlString)
        {
            DataSet dataset = GetDataSet(SqlString);
            DataTable datatable = new DataTable();
            if (dataset.Tables.Count > 0)
                datatable = dataset.Tables[0];
            return datatable;
        }

        /// <summary>
        /// 公有方法，獲取數據，返回一個DataRow。
        /// </summary>
        /// <param name="SqlString">Sql語句</param>
        /// <returns>DataRow</returns>
        public DataRow GetDataRow(String SqlString)
        {
            DataTable dt = GetDataTable(SqlString);
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            return null;
        }

        /// <summary>
        /// 公有方法，執行Sql語句。
        /// </summary>
        /// <param name="SqlString">Sql語句</param>
        /// <returns>對Update、Insert、Delete為影響到的行數，其他情況為-1</returns>
        public int ExecuteSQL(String SqlString)
        {
            int count = -1;
            Open();
            try
            {
                SqlCommand cmd = new SqlCommand(SqlString, Connection);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Log.Error("ExecuteSQL錯誤：", ex);
                count = -1;
            }
            finally
            {
                Close();
            }
            return count;
        }

        /// <summary>
        /// 公有方法，在一個數據表中插入一條記錄。
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Cols">哈西表，鍵值為字段名，值為字段值</param>
        /// <returns>插入行數</returns>
        public int Insert(String TableName, Hashtable Cols)
        {
            int Count = 0;

            if (Cols.Count <= 0)
                return -1;

            String Fields = " (";
            String Values = " Values(";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                    Values += ",";
                }
                Fields += "[" + item.Key.ToString() + "]";
                string dataType = item.Value.GetType().Name;
                if (dataType == "String" || dataType == "DateTime")
                    Values += SqlStringConstructor.GetQuotedString(item.Value.ToString());
                else
                    Values += item.Value.ToString();
                Count++;
            }
            Fields += ")";
            Values += ")";

            String SqlString = "Insert into " + TableName + Fields + Values;

            return ExecuteSQL(SqlString);
        }

        /// <summary>
        /// 公有方法，更新一個數據表。
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Cols">哈西表，鍵值為字段名，值為字段值</param>
        /// <param name="Where">Where子句</param>
        /// <returns>修改行數</returns>
        public int Update(String TableName, Hashtable Cols, String Where)
        {
            int Count = 0;
            if (Cols.Count <= 0)
                return -1;

            String Fields = " ";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                }
                Fields += "[" + item.Key.ToString() + "]";
                Fields += "=";
                string dataType = item.Value.GetType().Name;
                if (dataType == "String" || dataType == "DateTime")
                    Fields += SqlStringConstructor.GetQuotedString(item.Value.ToString());
                else
                    Fields += item.Value.ToString();
                Count++;
            }
            Fields += " ";

            String SqlString = "Update " + TableName + " Set " + Fields + Where;

            return ExecuteSQL(SqlString);
        }
    }
}