//===========================================================================
// Smarclient FPMS Demo Project a sample project for SmartClient technology.
// Smarclient FPMS Demo is designed and implemented bases Microsoft Application Block tech.
// Author: SFPMS Team, Date 2004-11
// Vision 1.0
// Revison History
//=============================================================================

using System;
using System.Configuration; 
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using JSW.FPMS.Tool;

namespace JSW.FPMS.ShuJuKu
{
	/// <summary>
	/// ShuJuKuFangWen 实现巡系统中连接数据库，查询数据库执行
	/// </summary>
	public class clsShuJuKuFangWen
	{
		#region 常量区域
		public const String    SJK_YONGHUMING				= "yonghuming";
		public const String    SJK_YONGHUMIMA				= "yonghumima";
		public const String    SJK_SHUJUYUAN				= "shujuyuan";
		public const String    SJK_SHUJUFUWUQI				= "shujufuwuqi";
		public const String    SJK_TIMEOUT					= "timeout";
		#endregion

		#region 成员变量区域	
		private String								shujukuLianJieZFC	= null;//数据库连接字符串
		private System.Data.SqlClient.SqlConnection shujukuLianJie		= null;//数据库连接

		#endregion

		#region 私有方法区域
		/// <summary>
		/// 构造器
		/// </summary>
		private clsShuJuKuFangWen()
		{
		}

		/// <summary>
		/// 从XML文件中得到连接串;
		/// </summary>
		/// <returns>连接字符串</returns>
		private void ShengChengLianJieZiFuChuan()
		{
			if ( shujukuLianJieZFC == null ) 
			{			
				
				//user id=sa;password=;initial catalog=cathay;data source=jsw-server;Connect Timeout=30
				String YHM  = ConfigurationSettings.AppSettings[clsShuJuKuFangWen.SJK_YONGHUMING].Trim();
				String YHMM = ConfigurationSettings.AppSettings[clsShuJuKuFangWen.SJK_YONGHUMIMA].Trim();
				String SJY  = ConfigurationSettings.AppSettings[clsShuJuKuFangWen.SJK_SHUJUYUAN].Trim();
				String SJK  = ConfigurationSettings.AppSettings[clsShuJuKuFangWen.SJK_SHUJUFUWUQI].Trim();
				String TO   = ConfigurationSettings.AppSettings[clsShuJuKuFangWen.SJK_TIMEOUT].Trim();
				String LianJieZFC ="user id=" + YHM +";password="+YHMM+";initial catalog="+SJY+";";
				LianJieZFC = LianJieZFC + "data source=" +SJK+";Connect Timeout="+TO;
				clsRiZhiChuLi.JiLuRiZhi("打开连接:"+ LianJieZFC);
				shujukuLianJieZFC = 	LianJieZFC;
			}
		}

		/// <summary>
		/// 打开数据库连结
		/// 如果发生错误，抛出异常
		/// </summary>		
		private  void HuoDeLianJie()
		{
			try 
			{
				this.ShengChengLianJieZiFuChuan(); 
				if (shujukuLianJie == null||shujukuLianJie.State==ConnectionState.Closed) 
				{
					shujukuLianJie  = new SqlConnection(shujukuLianJieZFC);
					shujukuLianJie.Open();
				}	
			}
			catch(Exception genException) 
			{
				Exception reGenException = new Exception(genException.Message);
				shujukuLianJie = null;
				throw;
			}
		}

		/// <summary>
		/// 关闭数据库连结
		/// 如果发生错误，抛出异常
		/// </summary>
		private void GuanBiLiangJie()
		{
			try 
			{
				if (shujukuLianJie != null && shujukuLianJie.State != ConnectionState.Closed) 
				{
					shujukuLianJie.Close();				
				}
			}
			catch(Exception genException) 
			{
				Exception reGenException = new Exception(genException.Message);
				shujukuLianJie = null;
				throw;
			}
		}
		#endregion

		#region 公共方法区域
		/// <summary>
		/// 获得数据库访问的实例
		/// </summary>
		public static clsShuJuKuFangWen  Instance 
		{
			get
			{				
				return new clsShuJuKuFangWen(); 				
			}
		}
		


		/// <summary>	
		/// 执行单条更新查询:Insert,Update,Delete。出现错误，则抛出异常
		/// </summary>
		/// <param name="SqlZiFuChuan">sql语句</param>
		/// <returns>该sql影响到的行数，整形数值</returns> 
		public int ZhiXingGengXin(String SqlZiFuChuan)
		{			
			SqlCommand mingLing  = null;
			SqlTransaction shiWu = null;
			try
			{	
				HuoDeLianJie();	//得到连接
				mingLing				= shujukuLianJie.CreateCommand();//生成sql命令对象
				shiWu					= shujukuLianJie.BeginTransaction(IsolationLevel.ReadCommitted);//生成事务控制单元
				mingLing.Connection		= this.shujukuLianJie;
				mingLing.Transaction	= shiWu;
				mingLing.CommandText	= SqlZiFuChuan; 				
				int yingxiangHang		= mingLing.ExecuteNonQuery();
				shiWu.Commit();//事务提交，确认更新
				return yingxiangHang;
			}
			catch(Exception genException)
			{
				try 
				{
					shiWu.Rollback(); 
				}
				catch(Exception genExceptionEx) 
				{					
					clsRiZhiChuLi.JiLuRiZhi("事务回滚错误:"+genExceptionEx.Message);
				}
				throw genException;

			}		
			finally
			{
				GuanBiLiangJie(); 
			}		
		}
		
		/// <summary>	
		/// 执行多条更新查询:Insert,Update,Delete。出现错误，则抛出异常。
		/// 整体执行标志。true:如果任一条出现错误，抛出异常。false:如果任一条出现错误，仍然执行，返回值记录-1
		/// </summary>
		/// <param name="SqlZiFuChuan">sql语句组</param>
		/// <param name="shifouZhengTi">整体执行</param> 
		/// <returns>int[]:影响行数组</returns> 
		public int[] ZhiXingGengXin(ArrayList SqlZiFuChuan,bool ShiFouZhengTi) 
		{
			if(SqlZiFuChuan == null || SqlZiFuChuan.Count <=0 ) 
			{
				throw new NullReferenceException("SQL语句组不能为空") ;
			}			
			int[] zhixingJieGuo = new int[SqlZiFuChuan.Count];
			SqlCommand mingLing  = null;
			SqlTransaction shiWu = null;

			try
			{	
				HuoDeLianJie();	//得到连接
				mingLing				= shujukuLianJie.CreateCommand();//生成sql命令对象
				shiWu					= shujukuLianJie.BeginTransaction(IsolationLevel.ReadCommitted);//生成事务控制单元
				mingLing.Connection		= this.shujukuLianJie;
				mingLing.Transaction	= shiWu;
				//遍历字符串组合，执行更新
				for(int Loop=0;Loop<SqlZiFuChuan.Count;Loop++) 
				{
					try //嵌套try，执行更新语句
					{
						mingLing.CommandText	= SqlZiFuChuan[Loop].ToString().Trim() ; 										
						int yingxiangHang		= mingLing.ExecuteNonQuery();
						zhixingJieGuo[Loop]		= yingxiangHang;
						//记录日志
						clsRiZhiChuLi.JiLuRiZhi("执行成功>所影响行数为"+yingxiangHang+">"+ SqlZiFuChuan[Loop].ToString().Trim());
					}
					catch(Exception genException) 
					{
						if(ShiFouZhengTi) //如果所有的更新字符串是整体运行的。
						{
							throw new Exception(genException.Message);//扔出错误
						}
						else 
						{
							zhixingJieGuo[Loop] = -1;//执行错误
						}
					}
				}
				shiWu.Commit(); 
			}
			catch(Exception genException)
			{
				try 
				{
					if(shiWu != null) 
					{
						shiWu.Rollback(); 
					}
				}
				catch(Exception genExceptionEx) 
				{					
					clsRiZhiChuLi.JiLuRiZhi("事务回滚错误:"+genExceptionEx.Message);
				}
				throw genException;

			}		
			finally
			{
				GuanBiLiangJie(); 
			}		
			return zhixingJieGuo;
		}


		/// <summary>
		/// 返回数据集合，其中只包含多个表的数据
		/// </summary>
		/// <param name="SqlZiFuChuan">查询SQL语句</param>
		/// <param name="Biao">表名</param>
		/// <returns>数据集</returns>
		public DataSet HuoDeDuoDataSet(String SqlZiFuChuan,String Biao)
		{
			String[] SqlZu=null;
			String[] BiaoZu=null;
			//切割参数
			if(SqlZiFuChuan !="" )
			{
				SqlZu=SqlZiFuChuan.Split('\r');
			}
			if( Biao !="")
			{
				BiaoZu=Biao.Split('\r');
			}
			return HuoDeDuoDataSet(SqlZu,BiaoZu);
		}


		/// <summary>
		///  返回数据集合，其中只包含多个表的数据
		/// </summary>
		/// <param name="SqlZu">查询SQL语句数组</param>
		/// <param name="BiaoZu">表名数组</param>
		/// <returns>DataSet</returns>
		public DataSet HuoDeDuoDataSet(String[] SqlZu,String[] BiaoZu)
		{
			if(SqlZu==null || BiaoZu==null) return null;
			if(SqlZu.Length != BiaoZu.Length ) return null;

			DataSet ds=new DataSet();
			for(int Loop=0;Loop<SqlZu.Length;Loop++)
			{
				ds.Tables.Add(HuoDeDataSet(SqlZu[Loop].ToString(),BiaoZu[Loop].ToString()).Tables[BiaoZu[Loop].ToString()].Copy());
			}
			return ds;
		}


		/// <summary>
		/// 返回数据集合，其中只包含单个表的数据
		/// </summary>
		/// <param name="Sql">查询SQL语句</param>
		/// <param name="Biao">表名</param>
		/// <returns>DataSet</returns>
		public DataSet HuoDeDataSet(String Sql,String Biao)
		{
			DataSet ds = null;
			try
			{
				this.HuoDeLianJie();//获得连接
				ds =new DataSet();
				SqlDataAdapter ladp=new SqlDataAdapter(Sql,this.shujukuLianJie) ;
				if(Biao==null)
					ladp.Fill(ds);
				else
					ladp.Fill(ds,Biao);
			}
			catch(SqlException genException)
			{
				throw genException;
			}
			finally
			{
				this.GuanBiLiangJie();
			}		
			//返回数据集合
			return ds;
		}
		
		/// <summary>
		/// 获得数据集
		/// </summary>
		/// <param name="Sql">查询SQL语句</param>
		/// <returns>数据集</returns>
		public DataSet HuoDeDataSet(String Sql)
		{
			return this.HuoDeDataSet(Sql,null);
		}
		
		/// <summary>
		/// 
		/// </summary>
		/// <param name="Sql"></param>
		/// <returns></returns>
		public String HuoDeJianDan(String Sql)
		{			
			String simple="";

			DataSet ds=HuoDeDataSet(Sql);
			if( ds!=null )
			{
				if(ds.Tables[0].Rows.Count>0)
				{
					simple=ds.Tables[0].Rows[0][0].ToString();
				}
			}
			return simple;
			
		}

		#endregion
	}
}
