sqlhelp.cs

火车订票系统 火车订票系统 火车订票系统

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;
			}
		}
	}
}