/// <summary>
/// 查询显示分页后的列表数据,根据实体对象生成条件.
/// </summary>
/// <param name="t">实体类,将查询条件赋给实体类的属性</param>
/// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
/// <param name="pageCurrent">当前页</param>
/// <param name="pageSize">每页显示的数量</param>
/// <param name="columnName">列名,默认为*,查询所有</param>
/// <returns></returns>
public DataTable SelectPage(T t, String order, int pageCurrent, int pageSize = 10, string columnName = "*")
{
string sql = string.Empty;
try
{
string strTable = InternalBase <T> .GetTableName();
string strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);
string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段
int number;
InternalBase <T> .GetPageNumber(pageCurrent, pageSize, out number);//获取分页信息
string tt, ttt;
InternalBase <T> .GetColumnName(columnName, out tt, out ttt);
sql = "SELECT " + columnName + " FROM (SELECT " + ttt + ", ROWNUM AS rowno FROM ( SELECT " + tt + " FROM " + strTable + " t " + strCondition + strOrder + ") tt WHERE ROWNUM <= " + (number + pageSize) + ") table_alias WHERE table_alias.rowno >= " + (number + 1);
return(dbl.ExecuteQuery(sql));
}
catch (Exception ex)
{
LogHelper.WriteLog(ex, sql);
return(null);
}
}
/// <summary>
/// 获取唯一值
/// </summary>
/// <param name="myCode">实体类的条件</param>
/// <param name="columnName">获取唯一值的列名,默认codevalue</param>
/// <returns></returns>
public object GetValue(CODES myCode, string columnName = "codevalue")
{
string strCondition = InternalBase <CODES> .GetParamForSelectAndDelete(myCode);
string sql = "select " + columnName + " from CODES " + strCondition;
DataTable dt = this.SelectBySQL(sql);
if (dt != null && dt.Rows.Count > 0)
{
return(dt.Rows[0][columnName]);
}
return("");
}
/// <summary>
/// 获取最小值
/// </summary>
/// <param name="columnName">最大值的列名</param>
/// <param name="t">实体类,传入的值都将当做查询条件参数</param>
/// <returns></returns>
internal int GetMin(string columnName, T t = null)
{
try
{
string strTable = InternalBase <T> .GetTableName();
string strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);
string sql = "select min(" + columnName + ") from " + strTable + strCondition;
return(SelectScalar(sql));
}
catch (Exception ex)
{
LogHelper.WriteLog(ex);
return(-1);
}
}
/// <summary>
/// 获取delete语句
/// </summary>
/// <param name="t">实体类型</param>
/// <returns></returns>
public string GetDeleteSql(T t)
{
try
{
string strTable = "";
string strCondition = "";
//获取表名
strTable = InternalBase <T> .GetTableName();
//获取条件
strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);
return(string.Format("delete from {0} {1}", strTable, strCondition));
}
catch (Exception ex)
{
LogHelper.WriteLog(ex);
return("-1");
}
}
/// <summary>
/// 查询返回所有数据,根据实体对象生成条件.
/// </summary>
/// <param name="t">实体类,将查询条件赋给实体类的属性</param>
/// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
/// <param name="columnName">列名,默认为*,查询所有</param>
/// <returns></returns>
public DataTable Select(T t, string order = "", string columnName = "*")
{
string sql = string.Empty;
try
{
string strTable = InternalBase <T> .GetTableName();
string strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);
string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段
sql = string.Format("select {0} from {1} {2} {3}", columnName, strTable, strCondition, strOrder);
return(dbl.ExecuteQuery(sql));
}
catch (Exception ex)
{
LogHelper.WriteLog(ex, sql);
return(null);
}
}
/// <summary>
/// 查询显示分页数据.返回json字符串(包含列表数据和总数据量数据,配合easyui使用),根据实体对象生成条件.
/// </summary>
/// <param name="t">实体类,将查询条件赋给实体类的属性</param>
/// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
/// <param name="pageCurrent">当前页</param>
/// <param name="pageSize">每页显示的数量</param>
/// <param name="columnName">列名,默认为*,查询所有</param>
/// <returns></returns>
public string SelectPageToJSON(T t, String order, int pageCurrent, int pageSize = 10, string columnName = "*")
{
string sql = string.Empty;
string countSql = string.Empty;
try
{
string strTable = InternalBase <T> .GetTableName();
string strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);
DataTable dt = SelectPage(t, order, pageCurrent, pageSize, columnName);// dbl.ExecuteQuery(sql);
countSql = "select count (*) from " + strTable + strCondition;
var pageCount = dbl.GetSingle(countSql);
return(JSONHelper.ObjectToJson(new { total = pageCount, rows = dt }));
}
catch (Exception ex)
{
LogHelper.WriteLog(ex, sql + "\r\n" + countSql);
return("");
}
}
