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

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

namespace ThreeLayer.DAL
{
public abstract class Data
{

// ＝＝＝数据库连接串设置＝＝＝
public static readonly string conn_Default = ConfigurationSettings.AppSettings["conn_Default"]; // 系统默认数据库连接串

//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝数据库底层操作＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
/// %26lt;summary%26gt;
/// 执行ExecuteNonQuery
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="cmdType"%26gt;Sql语句类型%26lt;/param%26gt;
/// %26lt;param name="cmdText"%26gt;Sql语句%26lt;/param%26gt;
/// %26lt;param name="cmdParms"%26gt;Parm数组%26lt;/param%26gt;
/// %26lt;returns%26gt;返回影响行数%26lt;/returns%26gt;
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;
}
}
/// %26lt;summary%26gt;
/// 返回一个SqlParameter实例
/// %26lt;/summary%26gt;
/// %26lt;param name="ParamName"%26gt;字段名%26lt;/param%26gt;
/// %26lt;param name="stype"%26gt;字段类型%26lt;/param%26gt;
/// %26lt;param name="size"%26gt;范围%26lt;/param%26gt;
/// %26lt;param name="Value"%26gt;赋值%26lt;/param%26gt;
/// %26lt;returns%26gt;返回一个SqlParameter实例%26lt;/returns%26gt;
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;
}
/// %26lt;summary%26gt;
/// 获得SqlParameter实例
/// %26lt;/summary%26gt;
/// %26lt;param name="ParamName"%26gt;字段名%26lt;/param%26gt;
/// %26lt;param name="Value"%26gt;赋值%26lt;/param%26gt;
/// %26lt;returns%26gt;返回一个SqlParameter实例%26lt;/returns%26gt;
public static SqlParameter MakeParam(string ParamName,string Value)
{
return new SqlParameter(ParamName, Value);
}
/// %26lt;summary%26gt;
/// 获得DateSet实例(获得单页记录)
/// %26lt;/summary%26gt;
/// %26lt;param name="int_PageSize"%26gt;一页显示的记录数%26lt;/param%26gt;
/// %26lt;param name="int_CurrentPageIndex"%26gt;当前页码%26lt;/param%26gt;
/// %26lt;param name="connString"%26gt;数据库连接串%26lt;/param%26gt;
/// %26lt;param name="cmdType"%26gt;Sql语句类型%26lt;/param%26gt;
/// %26lt;param name="cmdText"%26gt;Sql语句%26lt;/param%26gt;
/// %26lt;param name="cmdParms"%26gt;Parm数组%26lt;/param%26gt;
/// %26lt;returns%26gt;%26lt;/returns%26gt;
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 %26amp;%26amp; int_CurrentPageIndex==0)
{
da.Fill(ds,"12news1234567890");
}
else
{
int int_Page=int_PageSize*(int_CurrentPageIndex-1);
if (int_Page%26lt;0)
{
int_Page=0;
}
da.Fill(ds,int_Page,int_PageSize,"12news1234567890");
}
return ds;
}
catch
{
conn.Close();
throw;
}
}
/// %26lt;summary%26gt;
/// 获得DateSet实例(获得全部记录)
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接串%26lt;/param%26gt;
/// %26lt;param name="cmdType"%26gt;Sql语句类型%26lt;/param%26gt;
/// %26lt;param name="cmdText"%26gt;Sql语句%26lt;/param%26gt;
/// %26lt;param name="cmdParms"%26gt;Parm数组%26lt;/param%26gt;
/// %26lt;returns%26gt;%26lt;/returns%26gt;
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;
}
}

/// %26lt;summary%26gt;
/// 执行ExecuteScalar
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接串%26lt;/param%26gt;
/// %26lt;param name="cmdType"%26gt;Sql语句类型%26lt;/param%26gt;
/// %26lt;param name="cmdText"%26gt;Sql语句%26lt;/param%26gt;
/// %26lt;param name="cmdParms"%26gt;Parm数组%26lt;/param%26gt;
/// %26lt;returns%26gt;返回第一行第一列记录值%26lt;/returns%26gt;
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;
}
}

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

/// %26lt;summary%26gt;
/// 执行Sql语句
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="str_Sql"%26gt;sql语句(比如：insert into tablename set name='北京'')%26lt;/param%26gt;
public static void RunSql(string connString,string str_Sql)
{
Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql);
}
/// %26lt;summary%26gt;
/// 插入记录
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="cmdType"%26gt;sql语句类型%26lt;/param%26gt;
/// %26lt;param name="str_Sql"%26gt;sql语句%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的哈希表对象%26lt;/param%26gt;
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);
}
/// %26lt;summary%26gt;
/// 删除记录
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="cmdType"%26gt;sql语句类型%26lt;/param%26gt;
/// %26lt;param name="str_Sql"%26gt;sql语句%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的哈希表对象%26lt;/param%26gt;
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);
}

/// %26lt;summary%26gt;
/// 修改记录
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="TableName"%26gt;数据库表名%26lt;/param%26gt;
/// %26lt;param name="str_Where"%26gt;传递条件,比如Id=@Id%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的哈希表对象%26lt;/param%26gt;
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);
}
/// %26lt;summary%26gt;
/// 获得数字字段最大值(注：当该表记录为空,返回0)
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="id"%26gt;Key值字段名%26lt;/param%26gt;
/// %26lt;param name="table_name"%26gt;数据库名%26lt;/param%26gt;
/// %26lt;returns%26gt;返回数字字段最大值%26lt;/returns%26gt;
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;
}

/// %26lt;summary%26gt;
/// 通过传递条件获得记录条数
/// %26lt;/summary%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的条件字段参数%26lt;/param%26gt;
/// %26lt;returns%26gt;返回记录条数%26lt;/returns%26gt;
public static int GetRsCount(string connString,string Table,string ht_Where,Hashtable ht)
{
if (ht==null)
{
string str_Sql=GetPageListCountSqlbyHt(Table,ht_Where,null);
return (int)ExecuteScalar(connString,CommandType.Text,str_Sql,null);
}
else
{
string str_Sql=GetPageListCountSqlbyHt(Table,ht_Where,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;
}
return (int)ExecuteScalar(connString,CommandType.Text,str_Sql,Parms);
}
}
/// %26lt;summary%26gt;
/// 通过传递条件获得记录条数
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="str_Sql"%26gt;Sql语句%26lt;/param%26gt;
/// %26lt;returns%26gt;返回记录条数%26lt;/returns%26gt;
public static int GetRsCount(string connString,string str_Sql)
{
return (int)ExecuteScalar(connString,CommandType.Text,str_Sql,null);
}
/// %26lt;summary%26gt;
/// 获得单个字段值
/// %26lt;/summary%26gt;
/// %26lt;param name="connString"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="str_Sql"%26gt;Sql语句，比如Select Name from Table where id=2%26lt;/param%26gt;
/// %26lt;returns%26gt;%26lt;/returns%26gt;
public static string GetFiledValue(string connString,string str_Sql)
{
return ExecuteScalar(connString,CommandType.Text,str_Sql,null).ToString();
}

/// %26lt;summary%26gt;
/// 通过运行Sql语句获得IList数据源
/// %26lt;/summary%26gt;
/// %26lt;param name="conn_Default"%26gt;数据库连接%26lt;/param%26gt;
/// %26lt;param name="int_PageSize"%26gt;一页显示记录数%26lt;/param%26gt;
/// %26lt;param name="int_CurrentPageIndex"%26gt;当前页码%26lt;/param%26gt;
/// %26lt;param name="str_Sql"%26gt;Sql语句%26lt;/param%26gt;
/// %26lt;param name="class_Name"%26gt;实体类名%26lt;/param%26gt;
/// %26lt;returns%26gt;%26lt;/returns%26gt;
public static IList RunSql(string conn_Default,int int_PageSize,int int_CurrentPageIndex,string str_Sql,string class_Name)
{
// ＝＝＝获得数据库源，返回IList为数据源＝＝＝
IList Ilst=new ArrayList();
// 当没有传递条件参数时作的操作
using (DataSet ds = ExecuteDataSet(int_PageSize,int_CurrentPageIndex,conn_Default, CommandType.Text, str_Sql, null))
{
DataTable dt=ds.Tables[0];
for (int j=0;j%26lt;dt.Rows.Count;j++)
{

Type myType =Type.GetType(class_Name);// 获得“类”类型
Object o_Instance=System.Activator.CreateInstance(myType); // 实例化类
// 获得类的所有属性数组
PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public|BindingFlags.Instance);
// 循环属性数组，并给数组属性赋值
for(int k=0;k%26lt;myPropertyInfo1.Length;k++)
{
PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
Object filed_Val=dt.Rows[j][myPropInfo.Name];
switch (myPropInfo.PropertyType.ToString())
{
case "System.Int32":
myPropInfo.SetValue(o_Instance,(int)filed_Val,null);
break;
case "System.String":
myPropInfo.SetValue(o_Instance,filed_Val.ToString(),null);
break;
case "System.DateTime":
myPropInfo.SetValue(o_Instance,Convert.ToDateTime(filed_Val.ToString()),null);
break;
}

}
// 把一行类记录赋值给ILst对象
Ilst.Add(o_Instance);

}
}
return Ilst;
}

public static IList RunSql(string conn_Default,int int_PageSize,int int_CurrentPageIndex,string procName,SqlParameter[] prams,string class_Name)
{
// ＝＝＝获得数据库源，返回IList为数据源＝＝＝
IList Ilst=new ArrayList();
// 当没有传递条件参数时作的操作
using (DataSet ds = ExecuteDataSet(int_PageSize,int_CurrentPageIndex,conn_Default, CommandType.StoredProcedure, procName,prams))
{
DataTable dt=ds.Tables[0];
for (int j=0;j%26lt;dt.Rows.Count;j++)
{

Type myType =Type.GetType(class_Name);// 获得“类”类型
Object o_Instance=System.Activator.CreateInstance(myType); // 实例化类
// 获得类的所有属性数组
PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public|BindingFlags.Instance);
// 循环属性数组，并给数组属性赋值
for(int k=0;k%26lt;myPropertyInfo1.Length;k++)
{
PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
Object filed_Val=dt.Rows[j][myPropInfo.Name];
switch (myPropInfo.PropertyType.ToString())
{
case "System.Int32":
myPropInfo.SetValue(o_Instance,(int)filed_Val,null);
break;
case "System.String":
myPropInfo.SetValue(o_Instance,filed_Val.ToString(),null);
break;
case "System.DateTime":
myPropInfo.SetValue(o_Instance,Convert.ToDateTime(filed_Val.ToString()),null);
break;
}

}
// 把一行类记录赋值给ILst对象
Ilst.Add(o_Instance);

}
}
return Ilst;
}

/// %26lt;summary%26gt;
/// 通过页大小，当前页数返回IList数据源
/// %26lt;/summary%26gt;
/// %26lt;param name="int_PageSize"%26gt;一页记录数%26lt;/param%26gt;
/// %26lt;param name="int_CurrentPageIndex"%26gt;当前页数%26lt;/param%26gt;
/// %26lt;param name="Sql_Sel_Code"%26gt;SQl语句%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;传递条件哈希表%26lt;/param%26gt;
/// %26lt;param name="class_Name"%26gt;实体类名%26lt;/param%26gt;
/// %26lt;returns%26gt;表示层传递过来的条件字段参数%26lt;/returns%26gt;
public static IList GetPageList(string conn_Default,int int_PageSize,int int_CurrentPageIndex,string Table,string ht_Where,string orderby,Hashtable ht,string class_Name)
{
// ＝＝＝获得数据库源，返回IList为数据源＝＝＝
IList Ilst=new ArrayList();

if (ht==null)
{
// 当没有传递条件参数时作的操作
string str_Sql=GetPageListSqlbyHt(Table,ht_Where,orderby,null,class_Name);
using (DataSet ds = ExecuteDataSet(int_PageSize,int_CurrentPageIndex,conn_Default, CommandType.Text, str_Sql, null))
{
DataTable dt=ds.Tables[0];
for (int j=0;j%26lt;dt.Rows.Count;j++)
{

Type myType =Type.GetType(class_Name);// 获得“类”类型
Object o_Instance=System.Activator.CreateInstance(myType); // 实例化类
// 获得类的所有属性数组
PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public|BindingFlags.Instance);
// 循环属性数组，并给数组属性赋值
for(int k=0;k%26lt;myPropertyInfo1.Length;k++)
{
PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];

Object filed_Val=dt.Rows[j][myPropInfo.Name];
switch (myPropInfo.PropertyType.ToString())
{
case "System.Int32":
myPropInfo.SetValue(o_Instance,(int)filed_Val,null);
break;
case "System.String":
myPropInfo.SetValue(o_Instance,filed_Val.ToString(),null);
break;
case "System.DateTime":
myPropInfo.SetValue(o_Instance,Convert.ToDateTime(filed_Val.ToString()),null);
break;
}

}
// 把一行类记录赋值给ILst对象
Ilst.Add(o_Instance);
}
}
}
else // 当没有传递条件参数时作的操作
{

// 处理传递过来的参数
SqlParameter[] Parms=new SqlParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
int i=0;
while ( et.MoveNext() )
{
System.Data.SqlClient.SqlParameter sp=MakeParam("@"+et.Key.ToString(),et.Value.ToString());
Parms[i]=sp;
i=i+1;
}
string str_Sql=GetPageListSqlbyHt(Table,ht_Where,orderby,ht,class_Name);
// 返回ILst
using (DataSet ds = ExecuteDataSet(int_PageSize,int_CurrentPageIndex,conn_Default, CommandType.Text, str_Sql, Parms))
{
DataTable dt=ds.Tables[0];
for (int j=0;j%26lt;dt.Rows.Count;j++)
{

Type myType =Type.GetType(class_Name);// 获得“类”类型
Object o_Instance=System.Activator.CreateInstance(myType); // 实例化类
// 获得类的所有属性数组
PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public|BindingFlags.Instance);
// 循环属性数组，并给数组属性赋值
for(int k=0;k%26lt;myPropertyInfo1.Length;k++)
{
PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
Object filed_Val=dt.Rows[j][myPropInfo.Name];
switch (myPropInfo.PropertyType.ToString())
{
case "System.Int32":
myPropInfo.SetValue(o_Instance,(int)filed_Val,null);
break;
case "System.String":
myPropInfo.SetValue(o_Instance,filed_Val.ToString(),null);
break;
case "System.DateTime":
myPropInfo.SetValue(o_Instance,Convert.ToDateTime(filed_Val.ToString()),null);
break;
}

}
// 把一行类记录赋值给ILst对象

Ilst.Add(o_Instance);

}
}

}
return Ilst;
}

/// %26lt;summary%26gt;
/// ＝＝＝通过页大小，当前页数返回IList数据源＝＝＝
/// %26lt;/summary%26gt;
/// %26lt;param name="int_PageSize"%26gt;一页记录数%26lt;/param%26gt;
/// %26lt;param name="int_CurrentPageIndex"%26gt;当前页数%26lt;/param%26gt;
/// %26lt;param name="Sql_Sel_Code"%26gt;SQl语句%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;传递条件哈希表%26lt;/param%26gt;
/// %26lt;param name="class_Name"%26gt;实体类名%26lt;/param%26gt;
/// %26lt;returns%26gt;表示层传递过来的条件字段参数%26lt;/returns%26gt;
public static Object GetDetail(string conn_Default,string Table,string ht_Where,Hashtable ht,string class_Name)
{
// ＝＝＝获得数据库源，返回IList为数据源＝＝＝
IList Ilst=new ArrayList();

if (ht==null)
{
string str_Sql=GetPageListSqlbyHt(Table,ht_Where,null,null,class_Name);
// 当没有传递条件参数时作的操作
using (DataSet ds = ExecuteDataSet(conn_Default, CommandType.Text, str_Sql, null))
{
DataTable dt=ds.Tables[0];
for (int j=0;j%26lt;dt.Rows.Count;j++)
{

Type myType =Type.GetType(class_Name);// 获得“类”类型
Object o_Instance=System.Activator.CreateInstance(myType); // 实例化类
// 获得类的所有属性数组
PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public|BindingFlags.Instance);
// 循环属性数组，并给数组属性赋值
for(int k=0;k%26lt;myPropertyInfo1.Length;k++)
{
PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
Object filed_Val=dt.Rows[j][myPropInfo.Name];
switch (myPropInfo.PropertyType.ToString())
{
case "System.Int32":
myPropInfo.SetValue(o_Instance,(int)filed_Val,null);
break;
case "System.String":
myPropInfo.SetValue(o_Instance,filed_Val.ToString(),null);
break;
case "System.DateTime":
myPropInfo.SetValue(o_Instance,Convert.ToDateTime(filed_Val.ToString()),null);
break;
}

}
// 把一行类记录赋值给ILst对象
return o_Instance;
}
}
}
else // 当没有传递条件参数时作的操作
{

// 处理传递过来的参数
SqlParameter[] Parms=new SqlParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
int i=0;
while ( et.MoveNext() )
{
System.Data.SqlClient.SqlParameter sp=MakeParam("@"+et.Key.ToString(),et.Value.ToString());
Parms[i]=sp;
i=i+1;
}

string str_Sql=GetPageListSqlbyHt(Table,ht_Where,null,ht,class_Name);
// 返回ILst
using (DataSet ds = ExecuteDataSet(conn_Default, CommandType.Text, str_Sql, Parms))
{
DataTable dt=ds.Tables[0];
for (int j=0;j%26lt;dt.Rows.Count;j++)
{

Type myType =Type.GetType(class_Name);// 获得“类”类型
Object o_Instance=System.Activator.CreateInstance(myType); // 实例化类
// 获得类的所有属性数组
PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public|BindingFlags.Instance);
// 循环属性数组，并给数组属性赋值
for(int k=0;k%26lt;myPropertyInfo1.Length;k++)
{
PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
Object filed_Val=dt.Rows[j][myPropInfo.Name];
switch (myPropInfo.PropertyType.ToString())
{
case "System.Int32":
myPropInfo.SetValue(o_Instance,(int)filed_Val,null);
break;
case "System.String":
myPropInfo.SetValue(o_Instance,filed_Val.ToString(),null);
break;
case "System.DateTime":
myPropInfo.SetValue(o_Instance,Convert.ToDateTime(filed_Val.ToString()),null);
break;
}

}
// 把一行类记录赋值给ILst对象
return o_Instance;

}
}

}
return Ilst;
}

//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝内部调用函数＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
//　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
/// %26lt;summary%26gt;
/// 获得删除Sql语句
/// %26lt;/summary%26gt;
/// %26lt;param name="Table"%26gt;数据库表名%26lt;/param%26gt;
/// %26lt;param name="ht_Where"%26gt;传递条件,比如Id=@Id%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的哈希表对象%26lt;/param%26gt;
/// %26lt;returns%26gt;返回删除sql语句%26lt;/returns%26gt;
public static string GetDelSqlbyHt(string Table,string ht_Where,Hashtable ht)
{
string str_Sql="";
int i=0;

int ht_Count=ht.Count; // 哈希表个数
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=@"+myEnumerator.Key).ToLower())==-1)
{
str_Sql=myEnumerator.Key+"=@"+myEnumerator.Key;
}
}
else
{
if (ht_Where.ToString().ToLower().IndexOf(("@"+myEnumerator.Key+" ").ToLower())==-1)
{
str_Sql=str_Sql+","+myEnumerator.Key+"=@"+myEnumerator.Key;
}

}
i=i+1;
}
if (ht_Where==null || ht_Where.Replace(" ","")=="") // 更新时候没有条件
{
str_Sql="Delete "+Table;
}
else
{
str_Sql="Delete "+Table+" where "+ht_Where;
}
return str_Sql;
}

/// %26lt;summary%26gt;
/// 获得插入Sql语句
/// %26lt;/summary%26gt;
/// %26lt;param name="TableName"%26gt;数据库表名%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的哈希表对象%26lt;/param%26gt;
/// %26lt;returns%26gt;返回插入Sql语句%26lt;/returns%26gt;
public static string GetInsertSqlbyHt(string TableName, Hashtable ht)
{
string str_Sql="";
int i=0;

int ht_Count=ht.Count; // 哈希表个数
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
string before="";
string behide="";
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
before="("+myEnumerator.Key;
}
else if (i+1==ht_Count)
{
before=before+","+myEnumerator.Key+")";
}
else
{
before=before+","+myEnumerator.Key;
}
i=i+1;
}
behide=" Values"+before.Replace(",",",@").Replace("(","(@");
str_Sql="Insert into "+TableName+before+behide;
return str_Sql;

}

/// %26lt;summary%26gt;
/// 获得记录数sql语句
/// %26lt;/summary%26gt;
/// %26lt;param name="Table"%26gt;数据库表%26lt;/param%26gt;
/// %26lt;param name="ht_Where"%26gt;条件%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的哈希表对象%26lt;/param%26gt;
/// %26lt;returns%26gt;%26lt;/returns%26gt;
public static string GetPageListCountSqlbyHt(string Table,string ht_Where,Hashtable ht)
{
string str_Sql="";
if (ht_Where=="" || ht_Where==null)
{
string str_Ht="";
if (ht!=null) // 用ht做条件
{

IDictionaryEnumerator et = ht.GetEnumerator();
int k=0;
while ( et.MoveNext() )
{
if (k==0)
{
str_Ht=" "+et.Key.ToString()+"=@"+et.Key.ToString();
}
else
{
str_Ht=str_Ht+" and "+et.Key.ToString()+"=@"+et.Key.ToString();
}
k=k+1;
}
}
if (str_Ht!="")
{
str_Sql="Select Count(*) From "+Table+" where "+str_Ht;
}
else
{
str_Sql="Select Count(*) From "+Table;
}
}
else
{
str_Sql="Select Count(*) From "+Table+" where "+ht_Where;
}

return str_Sql;

}

/// %26lt;summary%26gt;
/// 通过传递哈希表参数，获得更新Sql语句
/// %26lt;/summary%26gt;
/// %26lt;param name="Table"%26gt;数据库表名%26lt;/param%26gt;
/// %26lt;param name="ht_Where"%26gt;传递条件,比如Id=@Id%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的哈希表对象%26lt;/param%26gt;
/// %26lt;returns%26gt;%26lt;/returns%26gt;
public static string GetUpdateSqlbyHt(string Table,string ht_Where,Hashtable ht)
{
string str_Sql="";
int i=0;

int ht_Count=ht.Count; // 哈希表个数
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=@"+myEnumerator.Key).ToLower())==-1)
{
str_Sql=myEnumerator.Key+"=@"+myEnumerator.Key;
}
}
else
{
if (ht_Where.ToString().ToLower().IndexOf(("@"+myEnumerator.Key+" ").ToLower())==-1)
{
str_Sql=str_Sql+","+myEnumerator.Key+"=@"+myEnumerator.Key;
}

}
i=i+1;
}
if (ht_Where==null || ht_Where.Replace(" ","")=="") // 更新时候没有条件
{
str_Sql="update "+Table+" set "+str_Sql;
}
else
{
str_Sql="update "+Table+" set "+str_Sql+" where "+ht_Where;
}
str_Sql=str_Sql.Replace("set ,","set ").Replace("update ,","update ");
return str_Sql;
}
/// %26lt;summary%26gt;
/// 获得IList分页Sql语句
/// %26lt;/summary%26gt;
/// %26lt;param name="Table"%26gt;数据库表%26lt;/param%26gt;
/// %26lt;param name="ht_Where"%26gt;条件%26lt;/param%26gt;
/// %26lt;param name="orderby"%26gt;排序%26lt;/param%26gt;
/// %26lt;param name="ht"%26gt;表示层传递过来的条件字段参数%26lt;/param%26gt;
/// %26lt;param name="class_Name"%26gt;实体类名%26lt;/param%26gt;
/// %26lt;returns%26gt;%26lt;/returns%26gt;
public static string GetPageListSqlbyHt(string Table,string ht_Where,string orderby,Hashtable ht,String class_Name)
{
string str_Sql="";

// 选择类型只能实现 Select * from table where a=@a and b=@b效果
// where 后面优先权，当ht_Where不为空或者不为null，条件应该是ht_Where参数，否则，用ht做循环

Type myType =Type.GetType(class_Name);// 获得“类”类型
Object o_Instance=System.Activator.CreateInstance(myType); // 实例化类
// 获得类的所有属性数组
PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public|BindingFlags.Instance);
// 循环属性数组，并给数组属性赋值
for(int k=0;k%26lt;myPropertyInfo1.Length;k++)
{
PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
if (k==0)
{
str_Sql=myPropInfo.Name.ToString();
}
else
{
str_Sql=str_Sql+","+myPropInfo.Name.ToString();
}

}
if (ht_Where=="" || ht_Where==null)
{
string str_Ht="";
if (ht!=null) // 用ht做条件
{

IDictionaryEnumerator et = ht.GetEnumerator();
int k=0;
while ( et.MoveNext() )
{
if (k==0)
{
str_Ht=" "+et.Key.ToString()+"=@"+et.Key.ToString();
}
else
{
str_Ht=str_Ht+" and "+et.Key.ToString()+"=@"+et.Key.ToString();
}
k=k+1;
}
}
if (orderby=="" || orderby==null)
{
if (str_Ht!="")
{
str_Sql="Select "+str_Sql+" From "+Table+" where "+str_Ht;
}
else
{
str_Sql="Select "+str_Sql+" From "+Table;
}
}
else
{
if (str_Ht!="")
{
str_Sql="Select "+str_Sql+" From "+Table+" where "+str_Ht+" order by "+orderby;

}
else
{
str_Sql="Select "+str_Sql+" From "+Table;
}

}

}
else // 用ht_Where做条件
{
if (orderby=="" || orderby==null)
{
str_Sql="Select "+str_Sql+" From "+Table+" Where "+ht_Where;
}
else
{
str_Sql="Select "+str_Sql+" From "+Table+" where "+ht_Where+" order by "+orderby;
}

}

return str_Sql;

}

}
}