/// <summary>
/// 分页查询
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">页记录条数</param>
/// <param name="where">where过滤条件</param>
/// <param name="orderBy">排序的集合，默认id升序</param>
/// <param name="rowCount">总行数</param>
/// <returns>结果集合</returns>
public List <T> QueryPage <T>(string tblName, int pageIndex, int pageSize, string where, Dictionary <string, string> orderBy, out int rowCount)
{
List <T> res = null;
SqlFactory sqlMaker = SqlFactory.CreateInstance();
string sqlCount = sqlMaker.SqlCountMake(tblName, where);
string sqlPage = sqlMaker.SqlPageMake(tblName, where, orderBy);
if (string.IsNullOrEmpty(sqlCount) || string.IsNullOrEmpty(sqlPage))
{
rowCount = 0;
return(new List <T>());
}
object obj = null;
Dictionary <string, object> pms = new Dictionary <string, object>();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
pms.Add("@startIndex", startIndex);
pms.Add("@endIndex", endIndex);
obj = QueryObj(sqlCount, pms);
using (IDbConnection conn = PrepareConnection())
{
try
{
res = Dapper.SqlMapper.Query <T>(conn, sqlPage, new{ startIndex = startIndex, endIndex = endIndex }).ToList();
}
catch (SqlException ex)
{
throw ex;
}
}
rowCount = int.Parse((obj ?? "0").ToString());
return(res);
}
/// <summary>
/// 类的单例创建函数
/// </summary>
/// <returns>创建类的构造函数</returns>
public static SqlFactory CreateInstance()
{
// 当第一个线程运行到这里时，此时会对locker对象 "加锁"，
// 当第二个线程运行该方法时，首先检测到locker对象为"加锁"状态，该线程就会挂起等待第一个线程解锁
// lock语句运行完之后（即线程运行完之后）会对该对象"解锁"
// 双重锁定只需要一句判断就可以了
if (_sqlFactory == null)
{
lock (_locker)
{
// 如果类的实例不存在则创建，否则直接返回
if (_sqlFactory == null)
{
_sqlFactory = new SqlFactory();
}
}
}
return(_sqlFactory);
}
/// <summary>
/// 插入、更新、删除对象列表操作泛型方法,目标为单表
/// </summary>
/// <returns>受影响行数</returns>
public int QueryInt <T>(string dbOperate, List <T> modelList)
{
if (!Illegal(dbOperate))
{
throw new Exception("数据库操作类型非法，请查证后重试！");
}
int res = 0;
SqlFactory sqlMaker = SqlFactory.CreateInstance();
using (IDbConnection conn = PrepareConnection())
{
try
{
res = Dapper.SqlMapper.Execute(conn, sqlMaker.SqlCreater <T>(dbOperate), modelList);
}
catch (OleDbException ex)
{
throw ex;
}
}
return(res);
}
/// <summary>
/// 查询操作泛型方法,目标为单表
/// </summary>
/// <param name="where">where过滤条件字符串</param>
public List <T> QueryList <T>(string where = null)
{
List <T> res = new List <T>();
SqlFactory sqlMaker = SqlFactory.CreateInstance();
using (IDbConnection conn = PrepareConnection())
{
StringBuilder sql = new StringBuilder();
sql.Append(sqlMaker.SqlCreater <T>("Select"));
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where {0}", where);
}
try
{
res = Dapper.SqlMapper.Query <T>(conn, sql.ToString(), null).ToList();
}
catch (OleDbException ex)
{
throw ex;
}
}
return(res);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="startIndex">开始索引</param>
/// <param name="endIndex">结束索引</param>
/// <param name="where">where过滤条件</param>
/// <param name="orderBy">排序的集合，默认id升序</param>
/// <param name="rowCount">总行数</param>
/// <returns>结果集合</returns>
public DataTable QueryPage(string tblName, int startIndex, int endIndex, string where, Dictionary <string, string> orderBy, out int rowCount)
{
DataTable dtRes = new DataTable();
SqlFactory sqlMaker = SqlFactory.CreateInstance();
string sqlCount = sqlMaker.SqlCountMake(tblName, where);
string sqlPage = sqlMaker.SqlPageMake(tblName, where, orderBy);
if (string.IsNullOrEmpty(sqlCount) || string.IsNullOrEmpty(sqlPage))
{
rowCount = 0;
return(dtRes);
}
object obj = null;
Dictionary <string, object> pms = new Dictionary <string, object>();
pms.Add("@startIndex", startIndex);
pms.Add("@endIndex", endIndex);
obj = QueryObj(sqlCount, pms);
DataTable dt = QueryTable(sqlPage, pms);
dtRes = dt;
rowCount = int.Parse((obj ?? "0").ToString());
return(dtRes);
}
