C#通用DAO实现(四)

第998行到1510行代码

  /// <summary> /// 返回实体类集合 /// </summary> /// <typeparam name="T">实体类名</typeparam> /// <param name="tableName">表名</param> /// <param name="pageIndex">页数</param> /// <param name="countPerPage">总页数</param> /// <param name="Conditions">查询条件(可为null)</param> /// <returns></returns> public List<T> Select<T>(String tableName,int pageIndex, int countPerPage, Connditon[] Conditions) where T : new() { DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<T> objs = new List<T>(); pageIndex = pageIndex <= 0 ? 1 : pageIndex; string SqlStr = @"select top {percount} * from {tablename} where {pkey} not in (select top {passcount} {pkey} from {tablename} where 1=1 {condition} order by {pkey} desc) {condition} order by {pkey} desc"; SqlStr = SqlStr.Replace("{percount}", countPerPage.ToString()); SqlStr = SqlStr.Replace("{passcount}", (countPerPage * (pageIndex - 1)).ToString()); SqlStr = SqlStr.Replace("{pkey}", GetPrimaryKey<T>()); SqlStr = SqlStr.Replace("{tablename}", "[" + tableName + "]"); string conditionStr = ""; if (Conditions != null) { foreach (Connditon condition in Conditions) { if (condition != null) { if (condition.Value != null) { conditionStr += " and " + condition.Name + " " + Connditon.GetType(condition.OpType) + " @" + GetParname(condition); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + GetParname(condition); if (condition.OpType != Op.Like) { par.Value = condition.Value; } else { par.Value = "%" + condition.Value + "%"; } par.DbType = GetDbType(typeof(T).GetProperty(condition.Name)); dcom.Parameters.Add(par); } } } } dcom.CommandText = SqlStr.Replace("{condition}", conditionStr); dr = dcom.ExecuteReader(); while (dr.Read()) { T obj = new T(); PropertyInfo[] pros = typeof(T).GetProperties(); foreach (PropertyInfo pro in pros) { if (dr[pro.Name].ToString() != "") { if (dr[pro.Name].GetType() != typeof(DBNull)) pro.SetValue(obj, dr[pro.Name], null); } } objs.Add(obj); } return objs; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } /// <summary> /// 返回实体类集合 /// </summary> /// <typeparam name="T">实体类名</typeparam> /// <param name="pageIndex">页数</param> /// <param name="countPerPage">总页数</param> /// <param name="Conditions">查询条件(可为null)</param> /// <returns></returns> public List<T> Select<T>(int pageIndex, int countPerPage, Connditon[] Conditions, string OrderPlace, bool IsDesc) where T : new() { DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<T> objs = new List<T>(); pageIndex = pageIndex <= 0 ? 1 : pageIndex; string SqlStr = @"select top {percount} * from {tablename} where {pkey} not in (select top {passcount} {pkey} from {tablename} where 1=1 {condition} order by {OrderPlace} {Desc}) {condition} order by {OrderPlace} {Desc}"; SqlStr = SqlStr.Replace("{percount}", countPerPage.ToString()); SqlStr = SqlStr.Replace("{passcount}", (countPerPage * (pageIndex - 1)).ToString()); SqlStr = SqlStr.Replace("{pkey}", GetPrimaryKey<T>()); SqlStr = SqlStr.Replace("{tablename}", "[" + typeof(T).Name + "]"); SqlStr = SqlStr.Replace("{OrderPlace}", OrderPlace); if (IsDesc) { SqlStr = SqlStr.Replace("{Desc}", "Desc"); } else { SqlStr = SqlStr.Replace("{Desc}", ""); } string conditionStr = ""; if (Conditions != null) { foreach (Connditon condition in Conditions) { if (condition != null) { if (condition.Value != null) { conditionStr += " and " + condition.Name + " " + Connditon.GetType(condition.OpType) + " @" + GetParname(condition); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + GetParname(condition); if (condition.OpType != Op.Like) { par.Value = condition.Value; } else { par.Value = "%" + condition.Value + "%"; } par.DbType = GetDbType(typeof(T).GetProperty(condition.Name)); dcom.Parameters.Add(par); } } } } dcom.CommandText = SqlStr.Replace("{condition}", conditionStr); dr = dcom.ExecuteReader(); while (dr.Read()) { T obj = new T(); PropertyInfo[] pros = typeof(T).GetProperties(); foreach (PropertyInfo pro in pros) { if (dr[pro.Name].ToString() != "") { if (dr[pro.Name].GetType() != typeof(DBNull)) pro.SetValue(obj, dr[pro.Name], null); } } objs.Add(obj); } return objs; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } public List<T> Select<T>(int pageIndex, int countPerPage, Connditon[] Conditions, string OrderPlace, bool IsDesc, string PkPlace) where T : new() { DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<T> objs = new List<T>(); pageIndex = pageIndex <= 0 ? 1 : pageIndex; string SqlStr = @"select top {percount} * from {tablename} where {pkey} not in (select top {passcount} {pkey} from {tablename} where 1=1 {condition} order by {OrderPlace} {Desc}) {condition} order by {OrderPlace} {Desc}"; SqlStr = SqlStr.Replace("{percount}", countPerPage.ToString()); SqlStr = SqlStr.Replace("{passcount}", (countPerPage * (pageIndex - 1)).ToString()); SqlStr = SqlStr.Replace("{pkey}", PkPlace); SqlStr = SqlStr.Replace("{tablename}", "[" + typeof(T).Name + "]"); SqlStr = SqlStr.Replace("{OrderPlace}", OrderPlace); if (IsDesc) { SqlStr = SqlStr.Replace("{Desc}", "Desc"); } else { SqlStr = SqlStr.Replace("{Desc}", ""); } string conditionStr = ""; if (Conditions != null) { foreach (Connditon condition in Conditions) { if (condition != null) { if (condition.Value != null) { conditionStr += " and " + condition.Name + " " + Connditon.GetType(condition.OpType) + " @" + GetParname(condition); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + GetParname(condition); if (condition.OpType != Op.Like) { par.Value = condition.Value; } else { par.Value = "%" + condition.Value + "%"; } par.DbType = GetDbType(typeof(T).GetProperty(condition.Name)); dcom.Parameters.Add(par); } } } } dcom.CommandText = SqlStr.Replace("{condition}", conditionStr); dr = dcom.ExecuteReader(); while (dr.Read()) { T obj = new T(); PropertyInfo[] pros = typeof(T).GetProperties(); foreach (PropertyInfo pro in pros) { if (dr[pro.Name].ToString() != "") { if (dr[pro.Name].GetType() != typeof(DBNull)) pro.SetValue(obj, dr[pro.Name], null); } } objs.Add(obj); } return objs; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } public List<T> Select<T>(String tableName,int pageIndex, int countPerPage, Connditon[] Conditions, string OrderPlace) where T : new() { DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<T> objs = new List<T>(); pageIndex = pageIndex <= 0 ? 1 : pageIndex; string SqlStr = @"select top {percount} * from {tablename} where {pkey} not in (select top {passcount} {pkey} from {tablename} where 1=1 {condition} order by {OrderPlace}) {condition} order by {OrderPlace}"; SqlStr = SqlStr.Replace("{percount}", countPerPage.ToString()); SqlStr = SqlStr.Replace("{passcount}", (countPerPage * (pageIndex - 1)).ToString()); SqlStr = SqlStr.Replace("{pkey}", GetPrimaryKey<T>(tableName)); SqlStr = SqlStr.Replace("{tablename}", tableName); SqlStr = SqlStr.Replace("{OrderPlace}", OrderPlace); string conditionStr = ""; if (Conditions != null) { foreach (Connditon condition in Conditions) { if (condition != null) { if (condition.Value != null) { conditionStr += " and " + condition.Name + " " + Connditon.GetType(condition.OpType) + " @" + GetParname(condition); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + GetParname(condition); if (condition.OpType != Op.Like) { par.Value = condition.Value; } else { par.Value = "%" + condition.Value + "%"; } par.DbType = GetDbType(typeof(T).GetProperty(condition.Name)); dcom.Parameters.Add(par); } } } } dcom.CommandText = SqlStr.Replace("{condition}", conditionStr); dr = dcom.ExecuteReader(); while (dr.Read()) { T obj = new T(); PropertyInfo[] pros = typeof(T).GetProperties(); foreach (PropertyInfo pro in pros) { if (dr[pro.Name].ToString() != "") { if (dr[pro.Name].GetType() != typeof(DBNull)) pro.SetValue(obj, dr[pro.Name], null); } } objs.Add(obj); } return objs; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } /// <summary> /// 返回一个实体类 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="obj"></param> /// <param name="PkPlace">主键名</param> /// <returns></returns> public T GetEntity<T>(T obj, string PkPlace) { DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); string SqlStr = "select * from {tablename} where {pkstr}"; string pKey = GetPrimaryKey<T>(); SqlStr = SqlStr.Replace("{tablename}", "[" + typeof(T).Name + "]"); if (PkPlace == "") { SqlStr = SqlStr.Replace("{pkstr}", pKey + "=@" + pKey); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + pKey; par.Value = typeof(T).GetProperty(pKey).GetValue(obj, null); par.DbType = GetDbType(typeof(T).GetProperty(pKey)); dcom.Parameters.Add(par); dcom.CommandText = SqlStr; } else { SqlStr = SqlStr.Replace("{pkstr}", PkPlace + "=@" + PkPlace); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + PkPlace; par.Value = FindProName<T>(PkPlace, obj).GetValue(obj, null); par.DbType = GetDbType(FindProName<T>(PkPlace, obj)); dcom.Parameters.Add(par); dcom.CommandText = SqlStr; } //SqlStr = SqlStr.Replace("{pkstr}", pKey + "=@" + pKey); dr = dcom.ExecuteReader(); if (dr.Read()) { PropertyInfo[] pros = typeof(T).GetProperties(); foreach (PropertyInfo pro in pros) { if (dr[pro.Name].GetType() != typeof(DBNull)) pro.SetValue(obj, dr[pro.Name], null); } } return obj; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } /// <summary> /// 判断一个属性是否包含在实体类中,并返回此属性的反射信息 /// </summary> /// <typeparam name="T">实体类名</typeparam> /// <param name="Name">要判断的属性</param> /// <param name="obj">实体类实例</param> /// <returns></returns> public PropertyInfo FindProName<T>(string Name, T obj) { foreach (PropertyInfo pro in obj.GetType().GetProperties()) { if (pro.Name.ToLower() == Name.ToLower()) { return pro; } } return null; } /// <summary> /// 判断一个属性是否包含在实体类中,并返回此属性的反射信息 /// </summary> /// <typeparam name="T">实体类名</typeparam> /// <param name="Name">要判断的属性</param> /// <param name="obj">实体类实例</param> /// <returns></returns> public PropertyInfo FindProName<T>(string Name) { foreach (PropertyInfo pro in typeof(T).GetProperties()) { if (pro.Name.ToLower() == Name.ToLower()) { return pro; } } return null; } /// <summary> /// 返回一个实体类 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="obj">实体类实例</param> /// <returns></returns> public T GetEntity<T>(T obj) { DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); string SqlStr = "select * from {tablename} where {pkstr}"; string pKey = GetPrimaryKey<T>(); SqlStr = SqlStr.Replace("{tablename}", "[" + typeof(T).Name + "]"); SqlStr = SqlStr.Replace("{pkstr}", pKey + "=@" + pKey); DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + pKey; par.Value = FindProName<T>(pKey, obj).GetValue(obj, null); par.DbType = GetDbType(FindProName<T>(pKey, obj)); dcom.Parameters.Add(par); dcom.CommandText = SqlStr; dr = dcom.ExecuteReader(); if (dr.Read()) { PropertyInfo[] pros = typeof(T).GetProperties(); foreach (PropertyInfo pro in pros) { if (dr[pro.Name].GetType() != typeof(DBNull)) pro.SetValue(obj, dr[pro.Name], null); } } return obj; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } }