using System;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Xml;

namespace CallCenter.OracleDAL
{
	/// <summary>
	/// OraHelper 的摘要說明。
	/// </summary>
	/// <summary>
	/// OraHelper 的摘要說明。
	/// </summary>
	public abstract class OraHelper 
	{
		private static string getConnString()
		{
			string connstring="";
			XmlDocument xmlDoc=new XmlDocument();
			xmlDoc.Load("ConnectConfig.xml");
			XmlNode xn=xmlDoc.SelectSingleNode("database");
			XmlNodeList xnl=xn.ChildNodes;
			foreach(XmlNode xnf in xnl)
			{
				XmlElement xe=(XmlElement)xnf;
				connstring=xe.GetAttribute("string");//顯示屬性值:數據庫連接字符串
			}
			return connstring;

		}

		// 從配置文件讀取連接字符串,需要另外編寫類來實現

		public static readonly string CONN_STRING_NON_DTC = getConnString();

		public static string GetOracleConnection()
		{
			return CONN_STRING_NON_DTC;
		}

		public static string GetSqlserverConnection()
		{
			return CONN_STRING_NON_DTC;
		}
		//Create a hashtable for the parameter cached
		private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

		/// <summary>
		/// Execute a database query which does not include a select
		/// 執行數據庫操作，但不包括查詢語句
		/// </summary>
		/// <param name="connString">Connection string to database</param>
		/// 數據庫連接字符串
		/// <param name="cmdType">Command type either stored procedure or SQL</param>
		/// 要執行的是sql語句還是存儲過程
		/// <param name="cmdText">Acutall SQL Command</param>
		/// 實際的sql命令
		/// <param name="cmdParms">Parameters to bind to the command</param>
		/// <returns></returns>
		public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
		{
			
			// 建立Oracle命令對象
			OracleCommand cmd = new OracleCommand();
			//創建Oracle連接
			using (OracleConnection conn = new OracleConnection(connString)) 
			{
				
				//Prepare the command
				SetCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				
				//Execute the command				
				int val = cmd.ExecuteNonQuery();
			
				cmd.Parameters.Clear();		
				conn.Close();
				return val;
			}
		}

		public static int SqlserverExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			
			// 建立sqlserver命令對象
			SqlCommand cmd = new SqlCommand();
			//創建sqlserver連接
			using (SqlConnection conn = new SqlConnection(connString)) 
			{
				
				//Prepare the command
				SetSqlCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				
				//Execute the command				
				int val = cmd.ExecuteNonQuery();
			
				cmd.Parameters.Clear();		
				conn.Close();
				return val;
			}
		}

		/// <summary>
		/// Execute an OracleCommand (that returns no resultset) against an existing database transaction 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
		/// </remarks>
		/// <param name="trans">an existing database transaction</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or PL/SQL command</param>
		/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
		/// <returns>an int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
		{
			OracleCommand cmd = new OracleCommand();
			SetCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		public static int SqlserverExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			SqlCommand cmd = new SqlCommand();
			SetSqlCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}
		/// <summary>
		/// Execute an OracleCommand (that returns no resultset) against an existing database connection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
		/// </remarks>
		/// <param name="conn">an existing database connection</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or PL/SQL command</param>
		/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
		/// <returns>an int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQuery(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
		{

			OracleCommand cmd = new OracleCommand();

			SetCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		public static int SqlserverExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{

			SqlCommand cmd = new SqlCommand();

			SetSqlCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}
		/// <summary>
		/// Execute a select query that will return a result set
		/// </summary>
		/// <param name="connString">Connection string</param>
		//// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or PL/SQL command</param>
		/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
		/// <returns></returns>
		public static OracleDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
		{
			
			//Create the command and connection
			OracleConnection conn = new OracleConnection(connString);

			OracleCommand cmd = new OracleCommand();
					
			try 
			{
				//Prepare the command to execute
				SetCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				
				//Execute the query, stating that the connection should close when the resulting datareader has been read
				OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return rdr;
			
			}
			catch (Exception e) 
			{

				//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
				throw e;
			}
		}

		public static SqlDataReader SqlserverExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			
			//Create the command and connection
			SqlConnection conn = new SqlConnection(connString);

			SqlCommand cmd = new SqlCommand();
					
			try 
			{
				//Prepare the command to execute
				SetSqlCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				
				//Execute the query, stating that the connection should close when the resulting datareader has been read
				SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return rdr;
			
			}
			catch (Exception e) 
			{

				//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
				throw e;
			}
		}
		/// <summary>
		/// Execute a select query that will return a result set
		/// </summary>
		/// <param name="connString">Connection string</param>
		//// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or PL/SQL command</param>
		/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
		/// <returns></returns>
		public static DataSet ExecuteDataSet(string connString, CommandType cmdType, string tableName, string cmdText, params OracleParameter[] cmdParms) 
		{
			
			//Create the command and connection
			OracleConnection conn = new OracleConnection(connString);

			OracleCommand cmd = new OracleCommand();
					
			try 
			{
				//Prepare the command to execute
				SetCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				OracleDataAdapter oda = new OracleDataAdapter(cmd);
				cmd.ExecuteNonQuery();