#define DEBUG

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

using HRManager.DataAccessHelper;
using HRManager.CommonComponent;

namespace HRManager.DataAccessLayer
{
	/// <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.AppSettings["DBConnectionString"];
		}

		/// <summary>
		/// 析構函數，釋放非托管資源
		/// </summary>
		~Database()
		{
			try
			{
				if (Connection != null)
					Connection.Close();
			}
			catch(Exception e)
			{
                myEventLog.Log.Debug(e.Message);
			}
			try
			{
				Dispose();
			}
			catch{}
		}

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

		/// <summary>
		/// 公有方法，關閉數據庫連接。
		/// </summary>
		public void Close() 
		{
			try
			{
				if (Connection != null)
					Connection.Close();
			}
			catch(Exception e)
			{
                myEventLog.Log.Debug(e.Message);
			}
		}

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

		/// <summary>
		/// 公有方法，獲取數據，返回一個SqlDataReader （調用后主意調用SqlDataReader.Close()）。
		/// </summary>
		/// <param name="SqlString">Sql語句</param>
		/// <returns>SqlDataReader</returns>
		public SqlDataReader GetDataReader(String SqlString)
		{
			Open();
			try
			{
				SqlCommand cmd = new SqlCommand(SqlString,Connection);
				return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
#if DEBUG
                myEventLog.Log.Info(SqlString);
#endif
			}
			catch(Exception e)
			{
                myEventLog.Log.Debug("數據查詢（GetDataReader）失敗，SqlString=" + SqlString + ",系統異常信息：" + e.Message);
				return null;
			}
		}

		/// <summary>
		/// 公有方法，獲取數據，返回一個DataSet。
		/// </summary>
		/// <param name="SqlString">Sql語句</param>
		/// <returns>DataSet</returns>
		public DataSet GetDataSet(String SqlString)
		{
			DataSet dataset = new DataSet();
			Open();
			try
			{
				SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection);
				adapter.Fill(dataset);
#if DEBUG
                myEventLog.Log.Info(SqlString);
#endif
			}
			catch(Exception e)
			{
                myEventLog.Log.Debug("數據查詢（GetDataSet）失敗，SqlString=" + SqlString + ",系統異常信息：" + e.Message);
			}
			finally
			{
				Close();
			}
			return dataset;
		}

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

		/// <summary>
		/// 公有方法，獲取數據，返回一個DataRow。
		/// </summary>
		/// <param name="SqlString">Sql語句</param>
		/// <returns>DataRow</returns>
		public DataRow GetDataRow(String SqlString)
		{
			DataSet dataset = GetDataSet(SqlString);
			dataset.CaseSensitive = false;
			if (dataset.Tables[0].Rows.Count>0)
			{
				return dataset.Tables[0].Rows[0];
			}
			else
			{
				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();
#if DEBUG
                myEventLog.Log.Info(SqlString);
#endif
			}
			catch(Exception e)
			{
                myEventLog.Log.Debug("執行數據操作（ExecuteSQL）失敗，SqlString=" + SqlString + ",系統異常信息：" + e.Message);
				count = -1;
			}
			finally
			{
				Close();
			}
			return count;
		}

		/// <summary>
		/// 公有方法，執行一組Sql語句。
		/// </summary>
		/// <param name="SqlStrings">Sql語句組</param>
		/// <returns>是否成功</returns>
		public bool ExecuteSQL(String[] SqlStrings)
		{
			bool success = true;
			Open();
			SqlCommand cmd = new SqlCommand();
			SqlTransaction trans = Connection.BeginTransaction();
			cmd.Connection = Connection;
			cmd.Transaction = trans;

			int i=0;
			try
			{
				foreach (String str in SqlStrings)
				{
					cmd.CommandText = str;
					cmd.ExecuteNonQuery();
					i++;
#if DEBUG
                    myEventLog.Log.Info(str);
#endif
				}
				trans.Commit();
			}
			catch(Exception e)
			{
                myEventLog.Log.Debug("執行數據操作（ExecuteSQL）失敗，SqlString=" + SqlStrings[i] + ",系統異常信息：" + e.Message);
				success = false;
				trans.Rollback();
			}
			finally
			{
				Close();
			}
			return success;
		}

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

			if (Cols.Count<=0)			
			{
				return true;
			}

			String Fields = " (";
			String Values = " Values(";			
			foreach(DictionaryEntry item in Cols)
			{
				if (Count!=0)
				{
					Fields += ",";
					Values += ",";
				}
				Fields += "["+item.Key.ToString()+"]";
				Values += item.Value.ToString();
				Count ++;
			}
			Fields += ")";
			Values += ")";

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

			String[] Sqls = {SqlString};
			return ExecuteSQL(Sqls);
		}

	
		/// <summary>
		/// 公有方法，更新一個數據表。
		/// </summary>
		/// <param name="TableName">表名</param>
		/// <param name="Cols">哈西表，鍵值為字段名，值為字段值</param>
		/// <param name="Where">Where子句</param>
		/// <returns>是否成功</returns>
		public bool Update(String TableName,Hashtable Cols,String Where)
		{
			int Count = 0;
			if (Cols.Count<=0)			
			{
				return true;
			}
			String Fields = " ";
			foreach(DictionaryEntry item in Cols)
			{
				if (Count!=0)
				{
					Fields += ",";
				}
				Fields += "["+item.Key.ToString()+"]";
				Fields += "=";
				Fields += item.Value.ToString();
				Count ++;
			}
			Fields += " ";

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

			String[] Sqls = {SqlString};
			return ExecuteSQL(Sqls);
		}		
	}
}