C#通用DAO实现(六)

第2012行到3013行代码

/// <summary> /// 根据表名得到表的主键 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public string GetPrimaryKey(String tableName) { DbConnection dcon = null; DbCommand dcom = null; try { string Pkey = ""; //sp_pkeys数据库系统自带的存储过程(查找表的主键); string str = " exec sp_pkeys " + "[" + tableName + "]"; dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); dcom.CommandText = str; DbDataReader dr = dcom.ExecuteReader(); if (dr.Read()) { Pkey = dr["COLUMN_NAME"].ToString(); } dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); return Pkey; } catch (Exception ex) { throw ex; } finally { dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } /// <summary> /// 得到表的主键 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public string GetPrimaryKey<T>(String tableName) { DbConnection dcon = null; DbCommand dcom = null; string Pkey = ""; try { //sp_pkeys数据库系统自带的存储过程(查找表的主键); string str = String.IsNullOrEmpty(tableName) ? " exec sp_pkeys " + "[" + typeof(T).Name + "]" : " exec sp_pkeys " + "[" + tableName + "]"; dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); dcom.CommandText = str; DbDataReader dr = dcom.ExecuteReader(); if (dr.Read()) { Pkey = dr["COLUMN_NAME"].ToString(); } foreach (PropertyInfo pro in typeof(T).GetProperties()) { if (pro.Name.ToLower() == Pkey.ToLower()) { Pkey = pro.Name; break; } } } catch (Exception ex) { throw ex; } finally { dcom.Dispose(); dcon.Close(); dcon.Dispose(); } return Pkey; } #region IBaseDAO 成员 /// <summary> /// 保存 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> public void Persist<T>(T t) { Insert<T>(t); } public void Persist<T>(String tableName, T t) { DbConnection dcon = null; DbCommand dcom = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); string SqlStr = "insert {tablename} ({namestr}) values({varstr})"; string pKey = GetPrimaryKey<T>(); SqlStr = SqlStr.Replace("{tablename}", "[" + tableName + "]"); string varstr = ""; string namestr = ""; foreach (PropertyInfo pro in typeof(T).GetProperties()) { if (pro.Name.ToLower() != pKey.ToLower()) { namestr += "," + pro.Name; varstr += ",@" + pro.Name; DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + pro.Name; par.Value = ChargeValue(pro, t); par.DbType = GetDbType(pro); dcom.Parameters.Add(par); } } varstr = varstr.Substring(1); namestr = namestr.Substring(1); SqlStr = SqlStr.Replace("{namestr}", namestr); SqlStr = SqlStr.Replace("{varstr}", varstr); dcom.CommandText = SqlStr; int i = dcom.ExecuteNonQuery(); dcom.Parameters.Clear(); dcom.CommandText = "select @@identity"; string id = dcom.ExecuteScalar().ToString(); object idval = t.GetType().GetProperty(pKey).PropertyType.GetMethod("Parse", new Type[] { typeof(string) }).Invoke(null, new object[] { id }); t.GetType().GetProperty(pKey).SetValue(t, idval, null); } catch (Exception ex) { throw ex; } finally { dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } /// <summary> /// 带事务上下文的持久化方法 /// (该方法运行在调用者的事务上下文中,事务边界由调用者控制) /// (该方法不干涉连接的释放,否则可能事务异常) /// (优先采用tablename作为表名,若为空则采用范型类的类名作为表名) /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="ts">上下文事务</param> /// <param name="tableName">表名</param> /// <param name="t">要保存的对象</param> public void Persist<T>(DbTransaction ts,String tableName, T t) { using (DbCommand dcom = ts.Connection.CreateCommand()) { dcom.Transaction = ts; string SqlStr = "insert {tablename} ({namestr}) values({varstr})"; string pKey = GetPrimaryKey<T>(); SqlStr = SqlStr.Replace("{tablename}", "[" + (String.IsNullOrEmpty(tableName) ? typeof(T).Name : tableName) + "]"); string varstr = ""; string namestr = ""; foreach (PropertyInfo pro in typeof(T).GetProperties()) { if (pro.Name.ToLower() != pKey.ToLower()) { namestr += "," + pro.Name; varstr += ",@" + pro.Name; DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + pro.Name; par.Value = ChargeValue(pro, t); par.DbType = GetDbType(pro); dcom.Parameters.Add(par); } } varstr = varstr.Substring(1); namestr = namestr.Substring(1); SqlStr = SqlStr.Replace("{namestr}", namestr); SqlStr = SqlStr.Replace("{varstr}", varstr); dcom.CommandText = SqlStr; int i = dcom.ExecuteNonQuery(); dcom.Parameters.Clear(); dcom.CommandText = "select @@identity"; string id = dcom.ExecuteScalar().ToString(); object idval = t.GetType().GetProperty(pKey).PropertyType.GetMethod("Parse", new Type[] { typeof(string) }).Invoke(null, new object[] { id }); t.GetType().GetProperty(pKey).SetValue(t, idval, null); } } /// <summary> /// 带事务上下文的持久化方法 /// (该方法运行在调用者的事务上下文中,事务边界由调用者控制) /// (该方法不干涉连接的释放,否则可能事务异常) /// (优先采用tablename作为表名,若为空则采用范型类的类名作为表名) /// (该方法不支持范型,更加具备通用性) /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="ts">上下文事务</param> /// <param name="tableName">表名</param> /// <param name="t">要保存的对象</param> public void Persist(DbTransaction ts, String tableName, Object t) { using (DbCommand dcom = ts.Connection.CreateCommand()) { dcom.Transaction = ts; string SqlStr = "insert {tablename} ({namestr}) values({varstr})"; string pKey = GetPrimaryKey(tableName); SqlStr = SqlStr.Replace("{tablename}", "[" + (String.IsNullOrEmpty(tableName) ? t.GetType().Name : tableName) + "]"); string varstr = ""; string namestr = ""; foreach (PropertyInfo pro in t.GetType().GetProperties()) { if (pro.Name.ToLower() != pKey.ToLower()) { namestr += "," + pro.Name; varstr += ",@" + pro.Name; DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + pro.Name; par.Value = ChargeValue(pro, t); par.DbType = GetDbType(pro); dcom.Parameters.Add(par); } } varstr = varstr.Substring(1); namestr = namestr.Substring(1); SqlStr = SqlStr.Replace("{namestr}", namestr); SqlStr = SqlStr.Replace("{varstr}", varstr); dcom.CommandText = SqlStr; int i = dcom.ExecuteNonQuery(); dcom.Parameters.Clear(); dcom.CommandText = "select @@identity"; string id = dcom.ExecuteScalar().ToString(); object idval = t.GetType().GetProperty(pKey).PropertyType.GetMethod("Parse", new Type[] { typeof(string) }).Invoke(null, new object[] { id }); t.GetType().GetProperty(pKey).SetValue(t, idval, null); } } /// <summary> /// 更新 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> public void Merge<T>(String tableName,T t) { DbConnection dcon = null; DbCommand dcom = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); string SqlStr = "update {tablename} set {valuestr} where {pkstr}"; string pKey = GetPrimaryKey<T>(); SqlStr = SqlStr.Replace("{tablename}", "[" + (String.IsNullOrEmpty(tableName) ? typeof(T).Name:tableName) + "]"); SqlStr = SqlStr.Replace("{pkstr}", pKey + "=@" + pKey); string valuestr = ""; foreach (PropertyInfo pro in typeof(T).GetProperties()) { if (pro.Name.ToLower() != pKey.ToLower()) { //如果类型是字符串并且值为空那么进入下一轮循环 if (pro.PropertyType == typeof(string) && ChargeValue(pro, t) == DBNull.Value) { continue; } valuestr += "," + pro.Name + "=@" + pro.Name; } DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + pro.Name; par.Value = ChargeValue(pro, t); par.DbType = GetDbType(pro); dcom.Parameters.Add(par); } valuestr = valuestr.Substring(1); SqlStr = SqlStr.Replace("{valuestr}", valuestr); dcom.CommandText = SqlStr; int i = dcom.ExecuteNonQuery(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } catch (Exception ex) { throw ex; } finally { dcom.Dispose(); dcon.Close(); dcon.Dispose(); } } /// <summary> /// 带事务上下文的更新方法 /// (该方法运行在调用者的事务上下文中,事务边界由调用者控制) /// (该方法不干涉连接的释放,否则可能事务异常) /// (优先采用tablename作为表名,若为空则采用范型类的类名作为表名) /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="ts">上下文事务</param> /// <param name="tableName">表名</param> /// <param name="t">要更新的对象</param> public void Merge<T>(DbTransaction ts,String tableName, T t) { using (DbCommand dcom = ts.Connection.CreateCommand()) { dcom.Transaction = ts; string SqlStr = "update {tablename} set {valuestr} where {pkstr}"; string pKey = GetPrimaryKey<T>(); SqlStr = SqlStr.Replace("{tablename}", "[" + (String.IsNullOrEmpty(tableName) ? typeof(T).Name : tableName) + "]"); SqlStr = SqlStr.Replace("{pkstr}", pKey + "=@" + pKey); string valuestr = ""; foreach (PropertyInfo pro in typeof(T).GetProperties()) { if (pro.Name.ToLower() != pKey.ToLower()) { //如果类型是字符串并且值为空那么进入下一轮循环 if (pro.PropertyType == typeof(string) && ChargeValue(pro, t) == DBNull.Value) { continue; } valuestr += "," + pro.Name + "=@" + pro.Name; } DbParameter par = dcom.CreateParameter(); par.ParameterName = "@" + pro.Name; par.Value = ChargeValue(pro, t); par.DbType = GetDbType(pro); dcom.Parameters.Add(par); } valuestr = valuestr.Substring(1); SqlStr = SqlStr.Replace("{valuestr}", valuestr); dcom.CommandText = SqlStr; dcom.ExecuteNonQuery(); } } /// <summary> /// 根据主键查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="oid"></param> /// <returns></returns> public T FindById<T>(object oid) where T:new() { if (oid == null) { throw new ArgumentNullException("OID不能为空!"); } String pk = GetPrimaryKey<T>(); StringBuilder sql = new StringBuilder("SELECT * FROM {tableName} WHERE {pk}={pkValue}"); sql.Replace("{tableName}",typeof(T).Name); sql.Replace("{pk}",pk); sql.Replace("{pkValue}","@"+pk); SqlDataReader reader = SqlHelper.ExecuteReader( SqlHelper._connString, CommandType.Text, sql.ToString(), new SqlParameter("@"+pk,oid)); return EncapReaderToEntity<T>(reader)[0]; } public T FindById<T>(String tableName,object oid) where T : new() { if (oid == null) { throw new ArgumentNullException("OID不能为空!"); } String pk = GetPrimaryKey<T>(); StringBuilder sql = new StringBuilder("SELECT * FROM {tableName} WHERE {pk}={pkValue}"); sql.Replace("{tableName}", tableName); sql.Replace("{pk}", pk); sql.Replace("{pkValue}", "@" + pk); SqlDataReader reader = SqlHelper.ExecuteReader( SqlHelper._connString, CommandType.Text, sql.ToString(), new SqlParameter("@" + pk, oid)); return EncapReaderToEntity<T>(reader)[0]; } /// <summary> /// 根据属性查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="kv"></param> /// <returns></returns> public List<T> FindByProperty<T>(System.Collections.Generic.KeyValuePair<String,Object> kvp) where T:new() { List<T> list = null; StringBuilder sql = new StringBuilder("SELECT * FROM {tablename} where 1=1 "); sql.Replace("{tablename}",typeof(T).Name); sql.Append(" and "); if (kvp.Value is String) { sql.Append(kvp.Key + "='" + kvp.Value + "'"); } else { sql.Append(kvp.Key + "=" + kvp.Value + ""); } SqlDataReader reader = SqlHelper.ExecuteReader(sql.ToString()); if (reader != null) { list = EncapReaderToEntity<T>(reader); } return list; } /// <summary> /// 根据属性查询 /// (该方法不支持范型) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="kv"></param> /// <returns></returns> public List<Object> FindByProperty(Type entityClass,String libName,System.Collections.Generic.KeyValuePair<String, Object> kvp) { List<Object> list = null; StringBuilder sql = new StringBuilder("SELECT * FROM {tablename} where 1=1 "); sql.Replace("{tablename}", libName); sql.Append(" and "); if (kvp.Value is String) { sql.Append(kvp.Key + "='" + kvp.Value + "'"); } else { sql.Append(kvp.Key + "=" + kvp.Value + ""); } SqlDataReader reader = SqlHelper.ExecuteReader(sql.ToString()); if (reader != null) { list = EncapReaderToEntity(entityClass,reader); } return list; } /// <summary> /// 将reader封装成List /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader"></param> /// <returns></returns> public List<T> EncapReaderToEntity<T>(SqlDataReader reader) where T:new() { List<T> list = null; if (reader != null) { list = new List<T>(); PropertyInfo[] pis= typeof(T).GetProperties(); if (reader.HasRows) { while (reader.Read()) { T t = new T(); foreach (PropertyInfo pi in pis) { if (reader[pi.Name].GetType() != typeof(DBNull)) { { pi.SetValue(t, reader[pi.Name], null); } } } list.Add(t); } } if (!reader.IsClosed) reader.Close(); } return list; } /// <summary> /// 将reader封装成List /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader"></param> /// <returns></returns> public List<Object> EncapReaderToEntity(Type entityClass,SqlDataReader reader) { List<Object> list = null; if (reader != null) { list = new List<Object>(); PropertyInfo[] pis = entityClass.GetProperties(); if (reader.HasRows) { while (reader.Read()) { Object t = Activator.CreateInstance(entityClass); foreach (PropertyInfo pi in pis) { if (reader[pi.Name].GetType() != typeof(DBNull)) { { pi.SetValue(t, reader[pi.Name], null); } } } list.Add(t); } } if (!reader.IsClosed) reader.Close(); } return list; } /// <summary> /// 分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="pi"></param> /// <returns></returns> public virtual PageInfo<T> DoPagination<T>(PageInfo<T> pi) where T:new() { //总条数 pi.MaxRows = this.GetMaxRecodeCount<T>(pi); //总页数 pi.MaxPage = pi.MaxRows % pi.PageSize == 0 ? pi.MaxRows / pi.PageSize : pi.MaxRows / pi.PageSize + 1; //分页结果 DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<T> objs = new List<T>(); pi.CurPageNum = pi.CurPageNum <= 0 ? 1 : pi.CurPageNum; 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}", pi.PageSize.ToString()); SqlStr = SqlStr.Replace("{passcount}", (pi.PageSize * (pi.CurPageNum - 1)).ToString()); SqlStr = SqlStr.Replace( "{pkey}", String.IsNullOrEmpty(pi.TableName) ? GetPrimaryKey<T>() : GetPrimaryKey<T>(pi.TableName) ); SqlStr = SqlStr.Replace("{tablename}", String.IsNullOrEmpty(pi.TableName) ? "["+typeof(T).Name+"]":pi.TableName); SqlStr = SqlStr.Replace("{OrderPlace}", String.IsNullOrEmpty(pi.OrderPlace) ? "1" :pi.OrderPlace); string conditionStr = ""; if (pi.Conditions != null) { foreach (Connditon condition in pi.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); } pi.List = objs; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } return pi; } /// <summary> /// 非范型的单表分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="pi"></param> /// <returns></returns> public virtual PaginationInfo DoPagination(PaginationInfo pi) { //总条数 pi.MaxRows = this.GetMaxRecodeCount(pi); //总页数 pi.MaxPage = pi.MaxRows % pi.PageSize == 0 ? pi.MaxRows / pi.PageSize : pi.MaxRows / pi.PageSize + 1; //分页结果 DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); List<Object> objs = new List<Object>(); pi.CurPageNum = pi.CurPageNum <= 0 ? 1 : pi.CurPageNum; 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}", pi.PageSize.ToString()); SqlStr = SqlStr.Replace("{passcount}", (pi.PageSize * (pi.CurPageNum - 1)).ToString()); SqlStr = SqlStr.Replace( "{pkey}", String.IsNullOrEmpty(pi.TableName) ? GetPrimaryKey(pi.ClassType) : GetPrimaryKey<Object>(pi.TableName) ); SqlStr = SqlStr.Replace("{tablename}", String.IsNullOrEmpty(pi.TableName) ? "[" + pi.ClassType.Name + "]" : pi.TableName); SqlStr = SqlStr.Replace("{OrderPlace}", String.IsNullOrEmpty(pi.OrderPlace) ? "1" : pi.OrderPlace); string conditionStr = ""; if (pi.Conditions != null) { foreach (Connditon condition in pi.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(pi.ClassType.GetProperty(condition.Name)); dcom.Parameters.Add(par); } } } } dcom.CommandText = SqlStr.Replace("{condition}", conditionStr); dr = dcom.ExecuteReader(); while (dr.Read()) { Object obj = Activator.CreateInstance(pi.ClassType); PropertyInfo[] pros = pi.ClassType.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); } pi.List = objs; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } return pi; } /// <summary> /// 非范型的跨表分页(必须是同构表) /// 实现思路是: /// 1.首先UNION ALL需要跨越的表 /// 2.把UNION ALL的结果作为数据源 /// 进行分页 /// </summary> /// <param name="pi">分页对象</param> /// <param name="tbnames">表名数组</param> /// <returns></returns> public virtual PaginationInfo DoPagination(PaginationInfo pi,params String[] tbnames) { //总条数 pi.MaxRows = this.GetMaxRecodeCount(pi,tbnames); //总页数 pi.MaxPage = pi.MaxRows % pi.PageSize == 0 ? pi.MaxRows / pi.PageSize : pi.MaxRows / pi.PageSize + 1; //分页结果 DbConnection dcon = null; DbCommand dcom = null; DbDataReader dr = null; try { dcon = Conn.getConn(); dcon.Open(); dcom = dcon.CreateCommand(); pi.CurPageNum = pi.CurPageNum <= 0 ? 1 : pi.CurPageNum; List<Object> objs = new List<Object>(); string sqlUnit = @"select * from {tablename} where 1=1 {condition} union all "; string sqlAll = ""; foreach (string tname in tbnames) { sqlAll += sqlUnit.Replace("{tablename}",tname); } sqlAll = sqlAll.Substring(0,sqlAll.Length - 10); string SqlStr = @"select top {percount} * from ({tablename}) as model where {pkey} not in (select top {passcount} {pkey} from ({tablename}) as modelInner where 1=1 {condition} order by {OrderPlace}) {condition} order by {OrderPlace}"; SqlStr = SqlStr.Replace("{percount}", pi.PageSize.ToString()); SqlStr = SqlStr.Replace("{passcount}", (pi.PageSize * (pi.CurPageNum - 1)).ToString()); SqlStr = SqlStr.Replace("{pkey}",GetPrimaryKey<Object>(tbnames[0])); SqlStr = SqlStr.Replace("{tablename}", sqlAll); SqlStr = SqlStr.Replace("{OrderPlace}", String.IsNullOrEmpty(pi.OrderPlace) ? "1" : pi.OrderPlace); string conditionStr = ""; if (pi.Conditions != null) { foreach (Connditon condition in pi.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(pi.ClassType.GetProperty(condition.Name)); dcom.Parameters.Add(par); } } } } dcom.CommandText = SqlStr.Replace("{condition}", conditionStr); dr = dcom.ExecuteReader(); while (dr.Read()) { Object obj = Activator.CreateInstance(pi.ClassType); PropertyInfo[] pros = pi.ClassType.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); } pi.List = objs; } catch (Exception ex) { throw ex; } finally { dr.Close(); dcom.Dispose(); dcon.Close(); dcon.Dispose(); } return pi; } /// <summary> /// 查询所有 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public List<T> FindAll<T>() where T : new() { return DoPagination<T>(new PageInfo<T>()).List; } public List<T> FindAll<T>(String tableName) 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>(); string SqlStr = "select * from {tablename}"; SqlStr = SqlStr.Replace("{tablename}", String.IsNullOrEmpty(tableName) ? "[" + typeof(T).Name + "]" : tableName); dcom.CommandText = SqlStr; 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> /// <returns></returns> public T FindUniqueResult<T>() where T:new() { return FindAll<T>()[0]; } public void PersistWithPK<T>(T t) where T:new() { InsertWithPK<T>(t); } #endregion public String GetNextOrderNum(String tableName, long volumeCode) { StringBuilder sql = new StringBuilder("select case"); sql.Append(" when max(OrderNum) is null then 0 "); sql.Append(" when max(OrderNum) = '' then 0 "); sql.Append(" when max(OrderNum) is not null then max(OrderNum) "); sql.Append(" end "); sql.Append(" + 1"); sql.Append(" from {tablename}"); sql.Append(" where "); sql.Append(" VolumeCode={VolumeCode}"); sql.Replace("{tablename}", tableName); sql.Replace("{VolumeCode}", volumeCode.ToString()); return SqlHelper.ExecuteScalar( SqlHelper._connString, System.Data.CommandType.Text, sql.ToString(), null ).ToString(); } public long GetVolumeCodeByArchiveSign(String tableName, String archiveSign) { StringBuilder sql = new StringBuilder("select VolumeCode from {tableName} where ArchiveSign = '{archiveSign}'"); sql.Replace("{tableName}", tableName); sql.Replace("{archiveSign}", archiveSign); Object obj = SqlHelper.ExecuteScalar( SqlHelper._connString, System.Data.CommandType.Text, sql.ToString(), null ); return obj == null ? -1 : long.Parse(obj.ToString()); } }