/// <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());
}
