using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;
namespace ThreeLayer.DAL
...{
public abstract class Data
...{
// ＝＝＝数据库连接串设置＝＝＝
//public static readonly string conn_Default = ConfigurationSettings.AppSettings["LocalSqlServer"]; // 系统默认数据库连接串
public static readonly string conn_Default = ConfigurationManager.ConnectionStrings["Personal"].ConnectionString;
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝数据库底层操作＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
/**//// <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)
...{
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);
}
}
/**//// <summary>
/// 通过传递条件获得记录条数
/// </summary>
/// <param name="connString">数据库连接</param>
/// <param name="str_Sql">Sql语句</param>
/// <returns>返回记录条数</returns>
public static int GetRsCount(string connString,string str_Sql)
...{
return (int)ExecuteScalar(connString,CommandType.Text,str_Sql,null);
}
/**//// <summary>
/// 获得单个字段值
/// </summary>
/// <param name="connString">数据库连接</param>
/// <param name="str_Sql">Sql语句，比如Select Name from Table where id=2</param>
/// <returns></returns>
public static string GetFiledValue(string connString,string str_Sql)
...{
return ExecuteScalar(connString,CommandType.Text,str_Sql,null).ToString();
}
/**//// <summary>
/// 通过运行Sql语句获得IList数据源
/// </summary>
/// <param name="conn_Default">数据库连接</param>
/// <param name="int_PageSize">一页显示记录数</param>
/// <param name="int_CurrentPageIndex">当前页码</param>
/// <param name="str_Sql">Sql语句</param>
/// <param name="class_Name">实体类名</param>
/// <returns></returns>
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<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<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<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<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;
}
/**//// <summary>
/// 通过页大小，当前页数返回IList数据源
/// </summary>
/// <param name="int_PageSize">一页记录数</param>
/// <param name="int_CurrentPageIndex">当前页数</param>
/// <param name="Sql_Sel_Code">SQl语句</param>
/// <param name="ht">传递条件哈希表</param>
/// <param name="class_Name">实体类名</param>
/// <returns>表示层传递过来的条件字段参数</returns>
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<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<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<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<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;
}
/**//// <summary>
/// ＝＝＝通过页大小，当前页数返回IList数据源＝＝＝
/// </summary>
/// <param name="int_PageSize">一页记录数</param>
/// <param name="int_CurrentPageIndex">当前页数</param>
/// <param name="Sql_Sel_Code">SQl语句</param>
/// <param name="ht">传递条件哈希表</param>
/// <param name="class_Name">实体类名</param>
/// <returns>表示层传递过来的条件字段参数</returns>
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<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<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<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<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;
}
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝内部调用函数＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
/**//// <summary>
/// 获得删除Sql语句
/// </summary>
/// <param name="Table">数据库表名</param>
/// <param name="ht_Where">传递条件,比如Id=@Id</param>
/// <param name="ht">表示层传递过来的哈希表对象</param>
/// <returns>返回删除sql语句</returns>
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;
}
/**//// <summary>
/// 获得插入Sql语句
/// </summary>
/// <param name="TableName">数据库表名</param>
/// <param name="ht">表示层传递过来的哈希表对象</param>
/// <returns>返回插入Sql语句</returns>
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;
}
/**//// <summary>
/// 获得记录数sql语句
/// </summary>
/// <param name="Table">数据库表</param>
/// <param name="ht_Where">条件</param>
/// <param name="ht">表示层传递过来的哈希表对象</param>
/// <returns></returns>
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;
}
/**//// <summary>
/// 通过传递哈希表参数，获得更新Sql语句
/// </summary>
/// <param name="Table">数据库表名</param>
/// <param name="ht_Where">传递条件,比如Id=@Id</param>
/// <param name="ht">表示层传递过来的哈希表对象</param>
/// <returns></returns>
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;
}
/**//// <summary>
/// 获得IList分页Sql语句
/// </summary>
/// <param name="Table">数据库表</param>
/// <param name="ht_Where">条件</param>
/// <param name="orderby">排序</param>
/// <param name="ht">表示层传递过来的条件字段参数</param>
/// <param name="class_Name">实体类名</param>
/// <returns></returns>
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<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;
}
}
}
本文转载:
CSDN博客