/// <summary> /// 获得满足条件的数据集 /// </summary> /// <param name="strWhere">Where子句</param> /// <param name="listPm">Where子句的参数列表</param> /// <param name="sColList">以逗号分隔的查询字段名称(默认为*)</param> /// <returns></returns> public virtual List <T> GetList(string strWhere, List <dbParam> listPm, string sColList = "*") { List <T> list = new List <T>(); StringBuilder strSql = new StringBuilder(); strSql.Append(string.Format("select {0} from {1}", CString.GetSQLFildList(sColList), TableName)); if (!string.IsNullOrEmpty(strWhere)) { strSql.Append(" where " + strWhere); } Database db = DatabaseFactory.CreateDatabase(connName); using (DbCommand cmd = db.GetSqlStringCommand(strSql.ToString())) { if (listPm != null) { foreach (dbParam pm in listPm) { db.AddInParameter(cmd, pm.ParamName, pm.ParamDbType, pm.ParamValue); } } using (IDataReader dr = db.ExecuteReader(cmd)) { while (dr.Read()) { list.Add(GetModel(dr)); } } } return(list); }
/// <param name="strWhere">组成SQL语句的Where子句</param> /// <param name="PageSize">每页显示记录数</param> /// <param name="PageIndex">当前需要获取的页码</param> /// <param name="OrderType">排序规则(true-降序;flase-升序)</param> /// <param name="colList">逗号分隔的字段名字符串</param> /// <param name="fldOrder">排序字段</param> /// <param name="tblName">表名</param> private string BuildSql(string strWhere, int PageSize, int PageIndex, bool OrderType, string colList, string fldOrder, string tblName) { string sColList = ""; if (string.IsNullOrEmpty(colList) || colList == "*") { PropertyInfo[] pis = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public); foreach (PropertyInfo pi in pis) { sColList += "[" + pi.Name + "],"; } sColList = sColList.Substring(0, sColList.Length - 1); } else { sColList = CString.GetSQLFildList(colList); } StringBuilder strSql = new StringBuilder(); string strOrder; // -- 排序类型 if (string.IsNullOrEmpty(fldOrder)) { fldOrder = PrimaryKey; } if (OrderType) { strOrder = string.Format(" order by {0} desc", fldOrder); } else { strOrder = string.Format(" order by {0} asc", fldOrder); } if (string.IsNullOrEmpty(strWhere)) { strSql.Append(string.Format("select {0} from(select {1}, row_number() over({2}) as row from {3}", sColList, sColList, strOrder, tblName)); strSql.Append(string.Format(") a where row between {0} and {1}", (PageIndex - 1) * PageSize + 1, PageIndex * PageSize)); } else { strSql.Append(string.Format("select {0} from(select {1}, row_number() over({2}) as row from {3}", sColList, sColList, strOrder, tblName)); strSql.Append(string.Format(" where {0}", strWhere)); strSql.Append(string.Format(") a where row between {0} and {1}", (PageIndex - 1) * PageSize + 1, PageIndex * PageSize)); } return(strSql.ToString()); }