data.cs

ThreeLayer有关详细信息

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;

/// <summary>
//========高处不胜寒Asp.Net三层结构数据层公共类V1.1=========
//    
//                   '''
//                  (0 0)
//      +-----oOO----(_)-------------------+
//      |                                  | 
//      |     作者:高处不胜寒              |
//      |     QQ:28767360                  |
//      |     AspXCn QQ群：14094415        |  
//      |     类型：Web版                  |
//      |     适用数据库：Sql Sever        |
//      |     更新时间：2005-08－13        |
//      |     技术支持网站:www.AspxCn.Org  |
//      |                                  |
//      +------------------oOO-------------+
//                 |__|__|
//                  || ||
//                 ooO Ooo  
//
//============================================================
/// </summary>

namespace ThreeLayer.DAL 
{
	public abstract class Data 
	{
		
		// ＝＝＝数据库连接串设置＝＝＝
		public static readonly string conn_Default = ConfigurationSettings.AppSettings["conn_Default"]; // 系统默认数据库连接串
		

		//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==  
		//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝数据库底层操作＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
		//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==  
		/// <summary>
		/// 执行ExecuteNonQuery
		/// </summary>
		/// <param name="connString">数据库连接</param>
		/// <param name="cmdType">Sql语句类型</param>
		/// <param name="cmdText">Sql语句</param>
		/// <param name="cmdParms">Parm数组</param>
		/// <returns>返回影响行数</returns>
		public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			SqlCommand cmd = new SqlCommand();
			using (SqlConnection conn = new SqlConnection(connString)) 
			{
				conn.Open();
				cmd.Connection=conn;
				cmd.CommandText = cmdText;
				if (cmdParms != null) 
				{
					foreach (SqlParameter parm in cmdParms)
						cmd.Parameters.Add(parm);
				}
				int val = cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				conn.Close();
				return val;
			}
		}
		/// <summary>
		/// 返回一个SqlParameter实例
		/// </summary>
		/// <param name="ParamName">字段名</param>
		/// <param name="stype">字段类型</param>
		/// <param name="size">范围</param>
		/// <param name="Value">赋值</param>
		/// <returns>返回一个SqlParameter实例</returns>
		public static SqlParameter MakeParam(string ParamName,System.Data.SqlDbType stype,int size,Object Value) 
		{
			SqlParameter para=new SqlParameter(ParamName,Value);
			para.SqlDbType=stype;
			para.Size=size;
			return para;
		}
		/// <summary>
		/// 获得SqlParameter实例
		/// </summary>
		/// <param name="ParamName">字段名</param>
		/// <param name="Value">赋值</param>
		/// <returns>返回一个SqlParameter实例</returns>
		public static SqlParameter MakeParam(string ParamName,string Value) 
		{
			return new SqlParameter(ParamName, Value);
		}
		/// <summary>
		/// 获得DateSet实例(获得单页记录)
		/// </summary>
		/// <param name="int_PageSize">一页显示的记录数</param>
		/// <param name="int_CurrentPageIndex">当前页码</param>
		/// <param name="connString">数据库连接串</param>
		/// <param name="cmdType">Sql语句类型</param>
		/// <param name="cmdText">Sql语句</param>
		/// <param name="cmdParms">Parm数组</param>
		/// <returns></returns>
		public static DataSet ExecuteDataSet(int int_PageSize,int int_CurrentPageIndex,string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			SqlConnection conn = new SqlConnection(connString);
			try 
			{
				conn.Open();
				System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter(cmdText,conn);
				da.SelectCommand.CommandType=cmdType;
				if (cmdParms != null) 
				{
					foreach (SqlParameter parm in cmdParms)
						da.SelectCommand.Parameters.Add(parm);
				}
				conn.Close();

				DataSet ds=new DataSet();
				if (int_PageSize==0 && int_CurrentPageIndex==0)
				{
					da.Fill(ds,"12news1234567890");
				}
				else
				{
					int int_Page=int_PageSize*(int_CurrentPageIndex-1);
					if (int_Page<0)
					{
						int_Page=0;
					}
					da.Fill(ds,int_Page,int_PageSize,"12news1234567890");
				}
				return ds;
			}
			catch 
			{
				conn.Close();
				throw;
			}
		}
		/// <summary>
		/// 获得DateSet实例(获得全部记录)
		/// </summary>
		/// <param name="connString">数据库连接串</param>
		/// <param name="cmdType">Sql语句类型</param>
		/// <param name="cmdText">Sql语句</param>
		/// <param name="cmdParms">Parm数组</param>
		/// <returns></returns>
		public static DataSet ExecuteDataSet(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			SqlConnection conn = new SqlConnection(connString);
			try 
			{
				conn.Open();
				System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter(cmdText,conn);
				da.SelectCommand.CommandType=cmdType;
				if (cmdParms != null) 
				{
					foreach (SqlParameter parm in cmdParms)
						da.SelectCommand.Parameters.Add(parm);
				}
				conn.Close();

				DataSet ds=new DataSet();
				da.Fill(ds,"12news1234567890");

				return ds;
			}
			catch 
			{
				conn.Close();
				throw;
			}
		}
		
		/// <summary>
		/// 执行ExecuteScalar
		/// </summary>
		/// <param name="connString">数据库连接串</param>
		/// <param name="cmdType">Sql语句类型</param>
		/// <param name="cmdText">Sql语句</param>
		/// <param name="cmdParms">Parm数组</param>
		/// <returns>返回第一行第一列记录值</returns>
		public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			SqlCommand cmd = new SqlCommand();

			using (SqlConnection conn = new SqlConnection(connString)) 
			{
				conn.Open();
				cmd.Connection = conn;
				cmd.CommandText = cmdText;
				if (cmdParms != null) 
				{
					foreach (SqlParameter parm in cmdParms)
						cmd.Parameters.Add(parm);
				}
				object val = cmd.ExecuteScalar();
				cmd.Parameters.Clear();
				conn.Close();
				return val;
			}
		}
	


		//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==  
		//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝数据库操作：插入，修改，列表显示，以及获得详细记录＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
		//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==  

		/// <summary>
		/// 执行Sql语句
		/// </summary>
		/// <param name="connString">数据库连接</param>
		/// <param name="str_Sql">sql语句(比如：insert into tablename set name='北京'')</param>
		public static void RunSql(string connString,string str_Sql)
		{
			Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql);
		}
		/// <summary>
		/// 插入记录
		/// </summary>
		/// <param name="connString">数据库连接</param>
		/// <param name="cmdType">sql语句类型</param>
		/// <param name="str_Sql">sql语句</param>
		/// <param name="ht">表示层传递过来的哈希表对象</param>
		public static void Insert(string connString,string TableName,Hashtable ht)
		{
			SqlParameter[] Parms=new SqlParameter[ht.Count];
			IDictionaryEnumerator et = ht.GetEnumerator();
			int i=0;
			// 作哈希表循环
			while ( et.MoveNext() )
			{
				System.Data.SqlClient.SqlParameter sp=Data.MakeParam("@"+et.Key.ToString(),et.Value.ToString());
				Parms[i]=sp; // 添加SqlParameter对象
				i=i+1;
			}
			string str_Sql=GetInsertSqlbyHt(TableName,ht); // 获得插入sql语句
			Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql,Parms);
		}
		/// <summary>
		/// 删除记录
		/// </summary>
		/// <param name="connString">数据库连接</param>
		/// <param name="cmdType">sql语句类型</param>
		/// <param name="str_Sql">sql语句</param>
		/// <param name="ht">表示层传递过来的哈希表对象</param>
		public static void Del(string connString,string TableName,string ht_Where,Hashtable ht)
		{
			SqlParameter[] Parms=new SqlParameter[ht.Count];
			IDictionaryEnumerator et = ht.GetEnumerator();
			int i=0;
			// 作哈希表循环
			while ( et.MoveNext() )
			{
				System.Data.SqlClient.SqlParameter sp=Data.MakeParam("@"+et.Key.ToString(),et.Value.ToString());
				Parms[i]=sp; // 添加SqlParameter对象
				i=i+1;
			}
			string str_Sql=GetDelSqlbyHt(TableName,ht_Where,ht); // 获得删除sql语句
			Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql,Parms);
		}

		/// <summary>
		/// 修改记录
		/// </summary>
		/// <param name="connString">数据库连接</param>
		/// <param name="TableName">数据库表名</param>
		/// <param name="str_Where">传递条件,比如Id=@Id</param>
		/// <param name="ht">表示层传递过来的哈希表对象</param>
		public static void Update(string connString,string TableName,string ht_Where, Hashtable ht)
		{
			SqlParameter[] Parms=new SqlParameter[ht.Count];
			IDictionaryEnumerator et = ht.GetEnumerator();
			int i=0;
			// 作哈希表循环
			while ( et.MoveNext() )
			{
				System.Data.SqlClient.SqlParameter sp=Data.MakeParam("@"+et.Key.ToString(),et.Value.ToString());
				Parms[i]=sp; // 添加SqlParameter对象
				i=i+1;
			}
			string str_Sql=GetUpdateSqlbyHt(TableName,ht_Where,ht); // 获得插入sql语句
			Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql,Parms);
		}
		/// <summary>
		/// 获得数字字段最大值(注：当该表记录为空,返回0)
		/// </summary>
		/// <param name="connString">数据库连接</param>
		/// <param name="id">Key值字段名</param>
		/// <param name="table_name">数据库名</param>
		/// <returns>返回数字字段最大值</returns>
		public static int GetMaxId(string connString,string id,string table_name)
		{
			string str_Sql="Select Max("+id+") from "+table_name;
			int int_MaxId=0;
			object obj=Data.ExecuteScalar(connString,CommandType.Text,str_Sql,null);
			if (obj==System.DBNull.Value)
			{
				int_MaxId=0;
			}
			else
			{
				int_MaxId = Convert.ToInt32(obj);
			}
			
			return int_MaxId;
		}

		/// <summary>
		/// 通过传递条件获得记录条数
		/// </summary>
		/// <param name="ht">表示层传递过来的条件字段参数</param>
		/// <returns>返回记录条数</returns>
		public static int GetRsCount(string connString,string Table,string ht_Where,Hashtable ht)
		{