using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Web;

namespace MonitorSystem.BasicClass
{
	/// <summary>
	///     存儲過程的返回值紀錄類
	///     DataSet : 表示返回的表
	///     Output  : 存儲過程的輸出參數
	///     Value   : 存儲過程的返回值
	/// </summary>
	public class SqlResult
	{
		public int Value;
		public Hashtable Output;
		public DataSet dataSet;

		public SqlResult()
		{
			Value = 0;
			Output = new Hashtable();
			dataSet = new DataSet();
		}
	}

	public class DataAccess
	{
		private string connectString;
		private SqlConnection sqlConnection;
		private SqlCommand sqlCommand;
		private SqlDataAdapter sqlDataAdapter;

		/// <summary>
		/// DataAccess 的摘要說明。
		/// </summary>
		public DataAccess(string connectString)
		{
			this.connectString = connectString;
			this.sqlConnection = new SqlConnection(connectString);
			this.sqlCommand = new SqlCommand();
			this.sqlCommand.Connection = this.sqlConnection;
			this.sqlDataAdapter = new SqlDataAdapter(this.sqlCommand);
		}
		/// <summary>
		/// 清除參數
		/// </summary>
		public void ClearParameters()
		{
			try
			{
				this.sqlCommand.Parameters.Clear();
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		/// <summary>
		/// 添加輸入參數
		/// </summary>
		/// <param name="paramName">參數名</param>
		/// <param name="theValue">參數值</param>
		/// <param name="dirction">輸入類型：ParameterDirection.Input、ParameterDirection.InputOutput</param>
		public void AddParameter(string paramName, Object theValue, ParameterDirection dirction)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,theValue);
				param.Direction = dirction;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		/// <summary>
		/// 添加輸出參數
		/// </summary>
		/// <param name="paramName">參數名</param>
		/// <param name="sqlDbType">參數類型</param>
		/// <param name="dirction">輸出類型：ParameterDirection.Output、ParameterDirection.ReturnValue</param>
		public void AddParameter(string paramName, SqlDbType sqlDbType, ParameterDirection dirction)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,sqlDbType);
				param.Direction = dirction;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		public void AddParameter(string paramName, SqlDbType sqlDbType, ParameterDirection dirction, Object theValue)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,sqlDbType);
				param.Direction = dirction;
				param.Value = theValue;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		public void AddParameter(string paramName, SqlDbType sqlDbType, int size, ParameterDirection dirction, Object theValue)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,sqlDbType,size);
				param.Direction = dirction;
				param.Value = theValue;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		/// <summary>
		/// 執行一個存儲過程或 SQL 語句，并將結果集輸出到 dataReader 中。
		/// 注意：在正常執行后，請執行 dataReader.Close()
		/// e.g:
		/// SqlDataReader dataReader = new DataSet();
		/// DataAccess dataAccess = new DataAccess("....");
		/// dataAccess.ClearParameters();
		/// dataAccess.AddParameter("@SendType","1",ParameterDirection.Input));
		/// dataAccess.AddParameter("@oResult",SqlDbType.Int,ParameterDirection.Output);
		/// try {
		///		if(dataAccess.Excute(CommandType.StoredProcedure,"SDPPushGetTask",ref dataReader)) {
		///			//成功后的處理
		///			while(dataReader.Read()) {	
		///				......	
		///			}
		///		}
		///		else {
		///			//失敗后的處理
		///		}
		/// } catch(exception e) { }
		///	finally {
		///		//關閉 DataReader
		///		if((dataReader!=null) && (dataReader.IsClosed==false)) {
		///			dataReader.Close();
		///		}
		/// }	
		/// </summary>
		/// <param name="cmdType">命令的類型，可以為 CommandType.StoredProcedure或者CommandType.Text</param>
		/// <param name="cmdText">命令的文本</param>
		/// <param name="dataReader">用來保存結果集</param>
		/// <returns>成功返回 true ； 否則返回 false</returns>
		public bool Excute( CommandType cmdType, string cmdText, ref SqlDataReader dataReader)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				dataReader = this.sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			return true;
		}

		public Object ExecuteScalar( CommandType cmdType, string cmdText)
		{
			Object result = null;
			if(this.connectString != null && this.connectString.Length > 0)
			{
				this.sqlCommand.CommandText = cmdText;
				this.sqlCommand.CommandType = cmdType;

				try
				{
					this.sqlCommand.Connection.Open();
					this.sqlCommand.Prepare();
					result = this.sqlCommand.ExecuteScalar();
				}
				catch(SqlException e )
				{
					throw new Exception( e.Message);
				}
				finally
				{
					this.sqlCommand.Connection.Close();
				}
			}

			return result;
		}

		public bool Excute( CommandType cmdType, string cmdText,ref int affectedRow)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				affectedRow = this.sqlCommand.ExecuteNonQuery();
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool Excute( CommandType cmdType, string cmdText, DataTable dt)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				this.sqlDataAdapter.Fill( dt );
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool Excute( CommandType cmdType, string cmdText, DataSet dataSet , string tableName)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				if( dataSet.Tables.Contains( tableName ) )
					dataSet.Tables.Remove( tableName );
				dataSet.Tables.Add( tableName );
				this.sqlDataAdapter.Fill( dataSet ,tableName );
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool Excute( CommandType cmdType, string cmdText, DataSet dataSet ,int startRecord,int maxRecords, string tableName)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				if( dataSet.Tables.Contains( tableName ) )
					dataSet.Tables.Remove( tableName );
				dataSet.Tables.Add( tableName );
				this.sqlDataAdapter.Fill( dataSet, startRecord, maxRecords ,tableName );
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool BatchExcute(string[] SqlArgs)