namespace DAL
{
/// <summary>
///Persistence 的摘要说明
/// </summary>
public class Persistence<T> where T : new()
{
public delegate string DelegeteGetId(string tbName, string str);
public Persistence()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
#region 添加
public int insert(T obj, string tbName)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
Database database = DatabaseFactory.CreateDatabase();
string sql = "insert into " + tbName + "(";
string _sql = "values(";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (info.GetValue(obj, null) != null)
{
string iName = info.Name;
if (iName != "id" && iName != tbName + "_id")
{
if (flag)
{
flag = false;
sql = sql + " " + iName;
_sql = _sql + " @" + iName;
}
else
{
sql = sql + "," + iName;
_sql = _sql + ",@" + iName;
}
}
SqlParameter parm = new SqlParameter("@" + info.Name, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
sql = sql + ")";
_sql = _sql + ")";
sql = sql + _sql;
cmd.CommandText = sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int insertAll(T obj, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;
string id = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "insert into " + tbName + "(";
string _sql = "values(";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (info.GetValue(obj, null) != null)
{
string iName = info.Name;
if (id != iName)
{
if (flag)
{
flag = false;
sql = sql + " " + iName;
_sql = _sql + " @" + iName;
}
else
{
sql = sql + "," + iName;
_sql = _sql + ",@" + iName;
}
SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
}
sql = sql + ")";
_sql = _sql + ")";
sql = sql + _sql;
cmd.CommandText = sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int insert(T obj, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;
string id = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "insert into " + tbName + "(";
string _sql = "values(";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
T t = new T();
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (!Equals(info.GetValue(obj, null), info.GetValue(t, null)))
{
string iName = info.Name;
if (id != iName)
{
if (flag)
{
flag = false;
sql = sql + " " + iName;
_sql = _sql + " @" + iName;
}
else
{
sql = sql + "," + iName;
_sql = _sql + ",@" + iName;
}
SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
}
sql = sql + ")";
_sql = _sql + ")";
sql = sql + _sql;
cmd.CommandText = sql;
if (testFlag)
{
foreachModel(obj);
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
#endregion
#region 更新数据
public int update(T obj, string tbName)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
Database database = DatabaseFactory.CreateDatabase();
string sql = "update " + tbName + " set ";
string _sql = "where ";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (info.GetValue(obj, null) != null)
{
string iName = info.Name;
if (iName == tbName + "_id")
{
_sql = _sql + "where " + iName + "=@" + info.Name;
}
else if (iName == "id")
{
_sql = _sql + "where id=@" + info.Name;
}
else
{
if (flag)
{
sql = sql + " " + info.Name + "@" + info.Name;
}
else
{
sql = sql + "," + info.Name + "@" + info.Name;
}
}
SqlParameter parm = new SqlParameter("@" + info.Name, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
cmd.CommandText = sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int update(T obj, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
string id = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "update " + tbName + " set ";
string _sql = " where ";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
T t = new T();
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
//Object ddd = info.GetValue(obj, null);
//string str = info.GetValue(obj, null).ToString();
//testResponse.write("info:"+info.GetValue(obj, null).ToString());
//testResponse.write("t"+info.GetValue(t, null).ToString());
//testResponse.write(info.GetValue(obj,null).ToString());
if (!Equals(info.GetValue(obj, null), info.GetValue(t, null)))
//if (info.GetValue(obj, null)!=null)
{
/**
* 这里有些问题需要研究
*1:下面的两个输出语句在用Equals对比后就出错
*2:并且在用Equals(info.GetValue(obj, null).Equals(info.GetValue(t, null)) 时也出错
*/
//testResponse.write("info:"+info.GetValue(obj, null).ToString());
//testResponse.write("t"+info.GetValue(t, null).ToString());
string iName = info.Name;
if (iName == id)
{
_sql = _sql + iName + "=@" + iName;
}
else
{
if (flag == true)
{
sql = sql + iName + "=@" + iName;
flag = false;
}
else
{
sql = sql + "," + iName + "=@" + iName;
}
}
SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
cmd.CommandText = sql + _sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
#endregion
#region 删除
public int delete(int id, string tbName)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
Database database = DatabaseFactory.CreateDatabase();
string sql = "delete from " + tbName + " where id= @id";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter("@id", id);
cmd.Parameters.Add(parm);
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int delete(int id, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
string idName = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "delete from " + tbName + " where " + idName + "= @id";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter("@id", id);
cmd.Parameters.Add(parm);
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
#endregion
#region 显示页面
/// <summary>
/// 返回详细页面
/// </summary>
/// <param name="id"></param>
/// <param name="tbName"></param>
/// <param name="append"></param>
/// <param name="getId"></param>
/// <returns></returns>
public T getView(int id, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;
string ids = getId(tbName, append);
string sql = "select * from " + tbName + " where " + ids + " =@id";
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@id",id)
};
T obj = new T();
if (testFlag)
{
testResponse.write(sql);
foreach (SqlParameter item in parms)
{
testResponse.write(item.ParameterName + ": " + item.Value);
}
return default(T);
}
else
{
using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms))
{
if (reader.Read())
{
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
}
}
return obj;
}
}
/// <summary>
/// 根据sql 返回详细页面
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public T getViewWhere(string sql)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
T obj = new T();
using (SqlDataReader reader = SqlHelper.ExecuteReader(cmd, null))
{
if (reader.Read())
{
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
}
}
return obj;
}
/// <summary>
/// 根据sql 返回页面内容
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public T getViewWhere(string sql, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
T obj = new T();
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, cmdParms))
{
if (reader.Read())
{
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
}
}
return obj;
}
#endregion
#region 列表页
/// <summary>
/// 返回列表页的所有内容
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public List<T> getAll(string tbName)
{
Database database = DatabaseFactory.CreateDatabase();
string sql = "select * from " + tbName;
DbCommand cmd = database.GetSqlStringCommand(sql);
List<T> items = new List<T>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
items.Add(obj);
}
}
return items;
}
/// <summary>
/// 根据sql语句和参数数组返回内容
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public List<T> getListWhere(string sql, SqlParameter[] cmdParms)
{
Database database = DatabaseFactory.CreateDatabase();
//string sql = "";
SqlCommand cmd = new SqlCommand();
List<T> items = new List<T>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, cmdParms))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
items.Add(obj);
}
}
return items;
}
public List<T> getListWhere(string sql)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
List<T> items = new List<T>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(cmd))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
info.SetValue(obj, reader[info.Name], null);
}
items.Add(obj);
}
}
return items;
}
/// <summary>
/// 分页列表
/// </summary>
/// <param name="pageDal"></param>
/// <returns></returns>
public DataRecordList<T> getList(PageT pageDal)
{
DataRecordList<T> dataRecordList = new DataRecordList<T>();
List<T> items = new List<T>();
SqlCommand cmd = new SqlCommand();
string sql = "proc_getpagedata";
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@TableName", DbType.String),
new SqlParameter("@FieldList", DbType.String),
new SqlParameter("@PageSize", DbType.Int32),
new SqlParameter("@PageIndex", DbType.Int32),
new SqlParameter("@OrderField", DbType.String),
new SqlParameter("@OrderType", DbType.Boolean),
new SqlParameter("@Where", DbType.String),
new SqlParameter("@RecordCount", DbType.Int32),
new SqlParameter("@PageCount", DbType.Int32)
};
parms[0].Value = pageDal._table;
parms[1].Value = pageDal.fieldList;
parms[2].Value = pageDal.pageSize;
parms[3].Value = pageDal.pageIndex;
parms[4].Value = pageDal.orderField;
parms[5].Value = pageDal.orderBy;
parms[6].Value = pageDal.where;
parms[7].Direction = ParameterDirection.Output;
parms[8].Direction = ParameterDirection.Output;
using (IDataReader reader = SqlHelper.ExecuteReader(cmd,parms))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
items.Add(obj);
}
}
int recordCount = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
int pageCount = int.Parse(cmd.Parameters["@PageCount"].Value.ToString());
dataRecordList.listData = items;
dataRecordList.PageSize = pageDal.pageSize;
dataRecordList.PageIndex = pageDal.pageIndex;
dataRecordList.PageCount = pageCount;
dataRecordList.RecordCount = recordCount;
return dataRecordList;
}
#endregion
#region 公共函数->测试
/// <summary>
/// 遍历模型
/// </summary>
/// <param name="obj"></param>
public static void foreachModel(object obj)
{
Type type = obj.GetType();
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
System.Web.HttpContext.Current.Response.Write("<br>name:" + info.Name + "--value:--" + info.GetValue(obj, null));
}
}
#endregion
}
}
{
/// <summary>
///Persistence 的摘要说明
/// </summary>
public class Persistence<T> where T : new()
{
public delegate string DelegeteGetId(string tbName, string str);
public Persistence()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
#region 添加
public int insert(T obj, string tbName)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
Database database = DatabaseFactory.CreateDatabase();
string sql = "insert into " + tbName + "(";
string _sql = "values(";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (info.GetValue(obj, null) != null)
{
string iName = info.Name;
if (iName != "id" && iName != tbName + "_id")
{
if (flag)
{
flag = false;
sql = sql + " " + iName;
_sql = _sql + " @" + iName;
}
else
{
sql = sql + "," + iName;
_sql = _sql + ",@" + iName;
}
}
SqlParameter parm = new SqlParameter("@" + info.Name, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
sql = sql + ")";
_sql = _sql + ")";
sql = sql + _sql;
cmd.CommandText = sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int insertAll(T obj, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;
string id = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "insert into " + tbName + "(";
string _sql = "values(";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (info.GetValue(obj, null) != null)
{
string iName = info.Name;
if (id != iName)
{
if (flag)
{
flag = false;
sql = sql + " " + iName;
_sql = _sql + " @" + iName;
}
else
{
sql = sql + "," + iName;
_sql = _sql + ",@" + iName;
}
SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
}
sql = sql + ")";
_sql = _sql + ")";
sql = sql + _sql;
cmd.CommandText = sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int insert(T obj, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;
string id = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "insert into " + tbName + "(";
string _sql = "values(";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
T t = new T();
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (!Equals(info.GetValue(obj, null), info.GetValue(t, null)))
{
string iName = info.Name;
if (id != iName)
{
if (flag)
{
flag = false;
sql = sql + " " + iName;
_sql = _sql + " @" + iName;
}
else
{
sql = sql + "," + iName;
_sql = _sql + ",@" + iName;
}
SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
}
sql = sql + ")";
_sql = _sql + ")";
sql = sql + _sql;
cmd.CommandText = sql;
if (testFlag)
{
foreachModel(obj);
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
#endregion
#region 更新数据
public int update(T obj, string tbName)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
Database database = DatabaseFactory.CreateDatabase();
string sql = "update " + tbName + " set ";
string _sql = "where ";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
if (info.GetValue(obj, null) != null)
{
string iName = info.Name;
if (iName == tbName + "_id")
{
_sql = _sql + "where " + iName + "=@" + info.Name;
}
else if (iName == "id")
{
_sql = _sql + "where id=@" + info.Name;
}
else
{
if (flag)
{
sql = sql + " " + info.Name + "@" + info.Name;
}
else
{
sql = sql + "," + info.Name + "@" + info.Name;
}
}
SqlParameter parm = new SqlParameter("@" + info.Name, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
cmd.CommandText = sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int update(T obj, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
string id = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "update " + tbName + " set ";
string _sql = " where ";
Type type = obj.GetType();
bool flag = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
T t = new T();
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
//Object ddd = info.GetValue(obj, null);
//string str = info.GetValue(obj, null).ToString();
//testResponse.write("info:"+info.GetValue(obj, null).ToString());
//testResponse.write("t"+info.GetValue(t, null).ToString());
//testResponse.write(info.GetValue(obj,null).ToString());
if (!Equals(info.GetValue(obj, null), info.GetValue(t, null)))
//if (info.GetValue(obj, null)!=null)
{
/**
* 这里有些问题需要研究
*1:下面的两个输出语句在用Equals对比后就出错
*2:并且在用Equals(info.GetValue(obj, null).Equals(info.GetValue(t, null)) 时也出错
*/
//testResponse.write("info:"+info.GetValue(obj, null).ToString());
//testResponse.write("t"+info.GetValue(t, null).ToString());
string iName = info.Name;
if (iName == id)
{
_sql = _sql + iName + "=@" + iName;
}
else
{
if (flag == true)
{
sql = sql + iName + "=@" + iName;
flag = false;
}
else
{
sql = sql + "," + iName + "=@" + iName;
}
}
SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
cmd.Parameters.Add(parm);
}
}
cmd.CommandText = sql + _sql;
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
#endregion
#region 删除
public int delete(int id, string tbName)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
Database database = DatabaseFactory.CreateDatabase();
string sql = "delete from " + tbName + " where id= @id";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter("@id", id);
cmd.Parameters.Add(parm);
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
public int delete(int id, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;//测试标志 取消注释可以查看生产的sql语句及模型类的属性
string idName = getId(tbName, append);
Database database = DatabaseFactory.CreateDatabase();
string sql = "delete from " + tbName + " where " + idName + "= @id";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter("@id", id);
cmd.Parameters.Add(parm);
if (testFlag)
{
testResponse.write(sql);
return 0;
}
else
{
return database.ExecuteNonQuery(cmd);
}
}
#endregion
#region 显示页面
/// <summary>
/// 返回详细页面
/// </summary>
/// <param name="id"></param>
/// <param name="tbName"></param>
/// <param name="append"></param>
/// <param name="getId"></param>
/// <returns></returns>
public T getView(int id, string tbName, string append, DelegeteGetId getId)
{
//测试标志
bool testFlag = false;
//testFlag = true;
string ids = getId(tbName, append);
string sql = "select * from " + tbName + " where " + ids + " =@id";
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@id",id)
};
T obj = new T();
if (testFlag)
{
testResponse.write(sql);
foreach (SqlParameter item in parms)
{
testResponse.write(item.ParameterName + ": " + item.Value);
}
return default(T);
}
else
{
using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms))
{
if (reader.Read())
{
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
}
}
return obj;
}
}
/// <summary>
/// 根据sql 返回详细页面
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public T getViewWhere(string sql)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
T obj = new T();
using (SqlDataReader reader = SqlHelper.ExecuteReader(cmd, null))
{
if (reader.Read())
{
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
}
}
return obj;
}
/// <summary>
/// 根据sql 返回页面内容
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public T getViewWhere(string sql, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
T obj = new T();
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, cmdParms))
{
if (reader.Read())
{
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
}
}
return obj;
}
#endregion
#region 列表页
/// <summary>
/// 返回列表页的所有内容
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public List<T> getAll(string tbName)
{
Database database = DatabaseFactory.CreateDatabase();
string sql = "select * from " + tbName;
DbCommand cmd = database.GetSqlStringCommand(sql);
List<T> items = new List<T>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
items.Add(obj);
}
}
return items;
}
/// <summary>
/// 根据sql语句和参数数组返回内容
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public List<T> getListWhere(string sql, SqlParameter[] cmdParms)
{
Database database = DatabaseFactory.CreateDatabase();
//string sql = "";
SqlCommand cmd = new SqlCommand();
List<T> items = new List<T>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, cmdParms))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
items.Add(obj);
}
}
return items;
}
public List<T> getListWhere(string sql)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
List<T> items = new List<T>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(cmd))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
info.SetValue(obj, reader[info.Name], null);
}
items.Add(obj);
}
}
return items;
}
/// <summary>
/// 分页列表
/// </summary>
/// <param name="pageDal"></param>
/// <returns></returns>
public DataRecordList<T> getList(PageT pageDal)
{
DataRecordList<T> dataRecordList = new DataRecordList<T>();
List<T> items = new List<T>();
SqlCommand cmd = new SqlCommand();
string sql = "proc_getpagedata";
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@TableName", DbType.String),
new SqlParameter("@FieldList", DbType.String),
new SqlParameter("@PageSize", DbType.Int32),
new SqlParameter("@PageIndex", DbType.Int32),
new SqlParameter("@OrderField", DbType.String),
new SqlParameter("@OrderType", DbType.Boolean),
new SqlParameter("@Where", DbType.String),
new SqlParameter("@RecordCount", DbType.Int32),
new SqlParameter("@PageCount", DbType.Int32)
};
parms[0].Value = pageDal._table;
parms[1].Value = pageDal.fieldList;
parms[2].Value = pageDal.pageSize;
parms[3].Value = pageDal.pageIndex;
parms[4].Value = pageDal.orderField;
parms[5].Value = pageDal.orderBy;
parms[6].Value = pageDal.where;
parms[7].Direction = ParameterDirection.Output;
parms[8].Direction = ParameterDirection.Output;
using (IDataReader reader = SqlHelper.ExecuteReader(cmd,parms))
{
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo info in obj.GetType().GetProperties())
{
if (reader[info.Name] != Convert.DBNull)
{
info.SetValue(obj, reader[info.Name], null);
}
}
items.Add(obj);
}
}
int recordCount = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
int pageCount = int.Parse(cmd.Parameters["@PageCount"].Value.ToString());
dataRecordList.listData = items;
dataRecordList.PageSize = pageDal.pageSize;
dataRecordList.PageIndex = pageDal.pageIndex;
dataRecordList.PageCount = pageCount;
dataRecordList.RecordCount = recordCount;
return dataRecordList;
}
#endregion
#region 公共函数->测试
/// <summary>
/// 遍历模型
/// </summary>
/// <param name="obj"></param>
public static void foreachModel(object obj)
{
Type type = obj.GetType();
foreach (System.Reflection.PropertyInfo info in type.GetProperties())
{
System.Web.HttpContext.Current.Response.Write("<br>name:" + info.Name + "--value:--" + info.GetValue(obj, null));
}
}
#endregion
}
}