using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Xml;

namespace CJGL
{
	/// <summary>
	/// 一個(gè)輕量級(jí)的數(shù)據(jù)庫(kù)操作類，可用于簡(jiǎn)單的分層開(kāi)發(fā)。
	/// 數(shù)據(jù)庫(kù)連接配置在WEB.config中ConnectionString配置節(jié)。
	/// 程序作者：孤獨(dú)俠客 QQ：229014536 Http://loners.yer.cn
	/// </summary>
	public class DataAccess
	{
		#region 構(gòu)造函數(shù)
		///<summary>
		///構(gòu)造函數(shù)
		///</summary>
		public DataAccess()
		{
		}
		#endregion

		#region 配置數(shù)據(jù)庫(kù)連接字符串
		/// <summary>
		/// 配置數(shù)據(jù)庫(kù)連接字符串
		/// </summary>
		public static string ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];
		#endregion 

		#region  執(zhí)行SQL語(yǔ)句，返回Bool值
		/// <summary>
		/// 執(zhí)行SQL語(yǔ)句，返回Bool值
		/// </summary>
		/// <param name="sql">要執(zhí)行的SQL語(yǔ)句</param>
		/// <returns>返回BOOL值，True為執(zhí)行成功</returns>
		public bool ExecuteSQL(string sql)
		{
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			try
			{
				con.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				con.Close();
				con.Dispose();
				cmd.Dispose();
			}
		}
		#endregion

		#region 執(zhí)行SQL語(yǔ)句，返回SqlDataReader
		/// <summary>
		/// 執(zhí)行SQL語(yǔ)句，返回SqlDataReader
		/// </summary>
		/// <param name="sql">要執(zhí)行的SQL語(yǔ)句</param>
		/// <returns>返回SqlDataReader，需手工關(guān)閉連接</returns>
		public SqlDataReader GetReader(string sql)
		{
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			SqlDataReader dr=null;
			try
			{
				con.Open();
				dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception ex)
			{
				dr.Close();
				con.Dispose();
				cmd.Dispose();
				throw new Exception(ex.ToString());
			}
			return dr;
		}
		#endregion

		#region  分頁(yè)，返回SqlDataReader
		/// <summary>
		/// 分頁(yè)，返回SqlDataReader
		/// </summary>
		/// <param name="tblName">查詢的表名</param>
		/// <param name="fldName">排序字段名</param>
		/// <param name="PageSize">每頁(yè)中記錄的數(shù)量</param>
		/// <param name="PageIndex">當(dāng)前查詢的頁(yè)碼</param>
		/// <param name="OrderType">設(shè)置排序類型, 非 0 值則降序</param>
		/// <param name="strWhere">查詢條件(注意:不要加 where)</param>
		/// <returns>返回SqlDataReader，需手工關(guān)閉連接</returns>
		public SqlDataReader GetReaderPage(string tblName,string fldName,int PageSize,int PageIndex,int OrderType,string strWhere)
		{
			string strTmp,strOrder;
			string sql="";
			if (OrderType!=0)
			{
				strTmp="< (select min";
				strOrder=" order by " + fldName +" desc";
			}
			else
			{				
				strTmp=">(select max";
				strOrder=" order by " + fldName +" asc";
			}
			if (strWhere!="")
			{
				sql = "select top " + PageSize + " * from "+ tblName + " where " + fldName + strTmp + "(";
				sql+=fldName + ") from (select top " + (PageIndex-1)*PageSize +" "+ fldName + " from " + tblName + " where (" + strWhere + ") ";
				sql+=strOrder + ") as tblTmp) and (" + strWhere + ") " + strOrder;
			}
			if (PageIndex==1)
			{
				strTmp ="";
				if (strWhere != "")
				{
					strTmp = " where (" + strWhere + ")";
				}
				sql = "select top " + PageSize + " * from " + tblName + strTmp + " " + strOrder;
			}
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			SqlDataReader dr=null;
			try
			{
				con.Open();
				dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception ex)
			{				
				dr.Close();
				con.Dispose();
				cmd.Dispose();
				throw new Exception(ex.ToString());
			}
			return dr;
		}
		#endregion

		#region  執(zhí)行SQL語(yǔ)句，返回DataSet
		/// <summary>
		/// 執(zhí)行SQL語(yǔ)句，返回DataSet
		/// </summary>
		/// <param name="sql">要執(zhí)行的SQL語(yǔ)句</param>
		/// <param name="tablename">DataSet中要填充的表名</param>
		/// <returns>返回dataSet類型的執(zhí)行結(jié)果</returns>
		public DataSet GetDataSet(string sql,string tablename)
		{
			DataSet ds= new DataSet();
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlDataAdapter da=new SqlDataAdapter(sql,con);
			try
			{
				da.Fill(ds,tablename);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			finally
			{
				con.Close();
				con.Dispose();
				da.Dispose();
			}
			return ds;
		}
		#endregion

		#region  執(zhí)行SQL語(yǔ)句，返回DataTable
		/// <summary>
		/// 執(zhí)行SQL語(yǔ)句，返回DataTable
		/// </summary>
		/// <param name="sql">要執(zhí)行的SQL語(yǔ)句</param>
		/// <returns>返回DataTable類型的執(zhí)行結(jié)果</returns>
		public DataTable GetDataTable(string sql)  
		{
			DataSet ds= new DataSet();
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlDataAdapter da=new SqlDataAdapter(sql,con);
			try
			{
				da.Fill(ds,"tb");
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			finally
			{
				con.Close();
				con.Dispose();
				da.Dispose();
			}
			DataTable result=ds.Tables["tb"];
			return result;
		}
		#endregion

		#region  執(zhí)行SQL語(yǔ)句并返回受影響的行數(shù)
		/// <summary>
		/// 執(zhí)行SQL語(yǔ)句并返回受影響的行數(shù)
		/// </summary>
		/// <param name="sql">要執(zhí)行的SQL語(yǔ)句</param>
		/// <returns>返回Int類型的受影響的行數(shù)</returns>
		public int GetCount(string sql)
		{
			SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
			SqlCommand cmd=new SqlCommand(sql,con);
			try
			{
				con.Open();
				int count=(int)cmd.ExecuteScalar();
				return count;
			}
			catch 
			{
				return 0;
			}
			finally
			{
				con.Close();
				con.Dispose();
				cmd.Dispose();
			}
		}
		#endregion
		
		#region  過(guò)濾輸入中的非法字符
		/// <summary>
		/// 過(guò)濾用戶名中的非法字符
		/// </summary>
		/// <param name="str">要被過(guò)濾的字符串</param>
		/// <returns>返回String類型的過(guò)濾后的字符串</returns>
		public string NameReplace(string str)
		{
			str=str.Trim();
			str=str.Replace("=","");
			str=str.Replace("'","");
			return str;
		}
		#endregion

　　 

	}
}