using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace Data_Access
{
	/// <summary>
	/// Sqlhelp 的摘要說明---顏政.。
	/// </summary>
	public class Sqlhelp
	{
		private static Hashtable parmCache=Hashtable.Synchronized(new Hashtable());

		public Sqlhelp()
		{}

		/// <summary>
		/// 將當前參數添加到哈希表
		/// </summary>
		/// <param name="cacheKey">鍵</param>
		/// <param name="cmdParms">值</param>
		public static void CacheParameters(string cacheKey,params SqlParameter[] cmdParms)
		{
			parmCache.Add(cacheKey,cmdParms);
		}

		/// <summary>
		/// 根據鍵值從緩沖區獲取參數，如果沒有從自動生成
		/// </summary>
		/// <param name="cacheKey">鍵</param>
		/// <returns>參數集合</returns>
		public static SqlParameter[] GetCachedParameters(string cacheKey)
		{
			SqlParameter[] CachedParms=(SqlParameter[])parmCache[cacheKey];
			if(CachedParms==null)
			{
				return null;
			}
			else
			{
				SqlParameter[] ClonedParms=new SqlParameter[CachedParms.Length];
				for(int i=0,j=CachedParms.Length;i<j;i++)
				{
					ClonedParms[i]=(SqlParameter)((ICloneable)CachedParms[i]).Clone();
				}
				return ClonedParms;
			}
		}
		/// <summary>
		/// 初始化SqlCommand
		/// </summary>
		/// <param name="cmd">數據庫命令對象</param>
		/// <param name="cn">數據庫連接對象</param>
		/// <param name="tran">事務</param>
		/// <param name="cmdType">參數類型</param>
		/// <param name="cmdText">參數的字符串</param>
		/// <param name="cmdParms">參數集合</param>
		private static void PrepareCommand(SqlCommand cmd,SqlConnection cn,SqlTransaction tran,CommandType cmdType,string cmdText,SqlParameter[] cmdParms)
		{

//			if(cn.State!=ConnectionState.Open)
//			{
//				cn.Open();
//			}
			cn=new SqlConnection("Server=.;uid=sa;pwd=sa;database=TrainDB");
			cn.Open();
			cmd.Connection=cn;
			cmd.CommandText=cmdText;		
			cmd.CommandType=cmdType;

			if(tran!=null)
			{
				cmd.Transaction=tran;
			}

			if(cmdParms!=null)
			{
				foreach(SqlParameter parm in cmdParms)
				{
					cmd.Parameters.Add(parm);
				}
			}
		}

		public static SqlDataReader ExecuteReader(string connectionString,string cmdText,CommandType cmdType,params SqlParameter[] cmdParms)
		{
			SqlConnection cn=new SqlConnection(connectionString);
			SqlCommand cm=new SqlCommand();

			PrepareCommand(cm,cn,null,cmdType,cmdText,cmdParms);

			SqlDataReader dr=cm.ExecuteReader(CommandBehavior.CloseConnection);
			cm.Parameters.Clear();
			return dr;
		}

		/// <summary>
		/// 根據參數返回所DataTable
		/// </summary>
		/// <param name="connectionString">數據庫連接對象</param>
		/// <param name="cmdType">參數類型</param>
		/// <param name="cmdText">參數的字符串</param>
		/// <param name="cmdParms">參數集合</param>
		public static DataTable ExecuteDataTable(string connectionString,string cmdText,CommandType cmdType,params SqlParameter[] cmdParms)
		{
			SqlConnection cn=new SqlConnection(connectionString);
			SqlCommand cm=new SqlCommand();
			SqlDataAdapter da=new SqlDataAdapter();
			da.SelectCommand=cm;
			PrepareCommand(cm,cn,null,cmdType,cmdText,cmdParms);
			
			DataTable dt=new DataTable();
			da.Fill(dt);
			return dt;
		}

		/// <summary>
		/// 影響的記錄的行數
		/// </summary>
		/// <param name="connectionString">數據庫連接對象</param>
		/// <param name="cmdType">參數類型</param>
		/// <param name="cmdText">參數的字符串</param>
		/// <param name="cmdParms">參數集合</param>
		/// <returns>影響的行數</returns>
		public static int ExecuteNonQuery(string connectionString,string cmdText,CommandType cmdType,params SqlParameter[] cmdParms)
		{
			SqlCommand cmd=new SqlCommand();
			using(SqlConnection conn=new SqlConnection(connectionString))
			{
				PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);
				int iResult=cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				return iResult;
			}
		}
	}
}