public static IDbConnection GetDBConn(DBConnNames dbConnName)
{
return(CreateConnection(ConfigurationManager.AppSettings[dbConnName.ToString()]));
}
/// <summary>
/// 如果遇到没有构建返回实体或查询语句很复杂不太知道怎么构建实体 可以把T定义为dynamic
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">sql查询语句</param>
/// <param name="sort">排序字段</param>
/// <param name="ordering">asc/desc</param>
/// <param name="num">每页多少行</param>
/// <param name="page">第几页</param>
/// <param name="messageEntity"></param>
/// <returns></returns>
public static List <T> EntityForSqlToPager <T>(string sql, string sort, string ordering, int num, int page, out MessageEntity messageEntity, DBConnNames dbname = DBConnNames.GisPlateform)
{
int beginNum = (page - 1) * (num);
//if (beginNum == 0) { beginNum = 1; }
int endNum = beginNum + num;
string sqlString = "SELECT * FROM(SELECT * , ROW_NUMBER() OVER ( Order by " + sort + " " + ordering + " ) AS Pos FROM (" + sql + ") as T) AS TT where TT.Pos > " + beginNum.ToString() + " and TT.Pos <= " + endNum.ToString();
string sqlCountString = "select count(0) as RowsCount from (" + sql + " ) as TT";
try
{
using (IDbConnection conn = ConnectionFactory.GetDBConn(dbname))
{
dynamic result = conn.Query <T>(sqlString);
dynamic resultTotalResult = conn.Query <dynamic>(sqlCountString).FirstOrDefault();
messageEntity = MessageEntityTool.GetMessage(result.Count, result, true, "完成", Convert.ToInt32(((IDictionary <string, object>)resultTotalResult)["RowsCount"]));
return(result);
}
}
catch (Exception e)
{
messageEntity = MessageEntityTool.GetMessage(ErrorType.SqlError, e.Message);
}
return(null);
}
