public void Close()
{
if (reader != null)
{
reader.Dispose();
}
}
/// <summary>
/// 取数据，返回多个结果集的读取方式
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="connString">链接字符串</param>
/// <param name="proc">存储过程名</param>
/// <param name="procParams">参数</param>
/// <param name="readResult">对结果集的处理函数</param>
/// <returns></returns>
public static T InnerQueryMultiple <T>(String connString, String proc, DynamicParameters procParams, Func <Dapper.SqlMapper.GridReader, T> readResult)
{
IDbConnection conn = null;
Dapper.SqlMapper.GridReader reader = null;
try
{
conn = new SqlConnection(connString);
reader = conn.QueryMultiple(proc, procParams, commandType: CommandType.StoredProcedure);
return(readResult(reader));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (conn != null)
{
conn.Dispose();
}
}
}
//PageInfo参数必须是dynamic par = new ExpandoObject();
public void QueryByPage <T>(PageInfo <T> pageinfo)
{
if (pageinfo.ReturnFields == null)
{
pageinfo.ReturnFields = "[id],[username],[pwd],[sex]";
}
if (pageinfo.Where != null)
{
pageinfo.Where = "WHERE " + pageinfo.Where;
}
if (pageinfo.OrderBy != null)
{
pageinfo.OrderBy = "ORDER BY " + pageinfo.OrderBy;
}
else
{
pageinfo.OrderBy = "ORDER BY [id] DESC";
}
string sqlData = null;
if (pageinfo.Skip == 0)
{
sqlData = string.Format("SELECT TOP {0} {1} FROM [admins] {2} {3}", pageinfo.Take, pageinfo.ReturnFields, pageinfo.Where, pageinfo.OrderBy);
}
else
{
sqlData = string.Format("SELECT TOP {3} {0} FROM (SELECT ROW_NUMBER() OVER({1}) AS Num,{0} FROM [admins] {2}) AS [T] WHERE [T].[Num]>@skip", pageinfo.ReturnFields, pageinfo.OrderBy, pageinfo.Where, pageinfo.Take);
}
string sql = string.Format(@"
DECLARE @total BIGINT
SET @total=(SELECT COUNT(1) FROM [admins] {0})
SELECT @total
IF(@total!=0)
BEGIN
{1}
END" , pageinfo.Where, sqlData);
using (var conn = SqlDapperHelper.CreateConnection())
{
Dapper.SqlMapper.GridReader Reader = null;
if (pageinfo.Params == null)
{
Reader = conn.QueryMultiple(sql, new { skip = pageinfo.Skip });
}
else
{
pageinfo.Params.skip = pageinfo.Skip;
Reader = conn.QueryMultiple(sql, (object)pageinfo.Params);
}
pageinfo.Total = Reader.Read <long>().FirstOrDefault();
if (pageinfo.Total != 0)
{
pageinfo.Data = Reader.Read <T>();
}
else
{
pageinfo.Data = new List <T>();
}
Reader.Dispose();
}
}
//PageInfo参数必须是dynamic par = new ExpandoObject();
public void QueryByPage <T>(PageInfo <T> pageinfo)
{
if (pageinfo.ReturnFields == null)
{
pageinfo.ReturnFields = "[id],[exam_id],[grade_class],[exam_subject],[exam_avg],[exam_stdevp],[exam_max],[exam_allpassnum],[exam_onepassnum],[exam_twopassnum],[exam_threepassnum],[exam_greatnum],[exam_passnum],[exam_allrank],[exam_onerank],[exam_tworank],[exam_threerank]";
}
if (pageinfo.Where != null)
{
pageinfo.Where = "WHERE " + pageinfo.Where;
}
if (pageinfo.OrderBy != null)
{
pageinfo.OrderBy = "ORDER BY " + pageinfo.OrderBy;
}
else
{
pageinfo.OrderBy = "ORDER BY [id] DESC";
}
string sqlData = null;
if (pageinfo.Skip == 0)
{
sqlData = string.Format("SELECT TOP {0} {1} FROM [examtotalcount_without] {2} {3}", pageinfo.Take, pageinfo.ReturnFields, pageinfo.Where, pageinfo.OrderBy);
}
else
{
sqlData = string.Format("SELECT TOP {3} {0} FROM (SELECT ROW_NUMBER() OVER({1}) AS Num,{0} FROM [examtotalcount_without] {2}) AS [T] WHERE [T].[Num]>@skip", pageinfo.ReturnFields, pageinfo.OrderBy, pageinfo.Where, pageinfo.Take);
}
string sql = string.Format(@"
DECLARE @total BIGINT
SET @total=(SELECT COUNT(1) FROM [examtotalcount_without] {0})
SELECT @total
IF(@total!=0)
BEGIN
{1}
END" , pageinfo.Where, sqlData);
using (var conn = SqlDapperHelper.CreateConnection())
{
Dapper.SqlMapper.GridReader Reader = null;
if (pageinfo.Params == null)
{
Reader = conn.QueryMultiple(sql, new { skip = pageinfo.Skip });
}
else
{
pageinfo.Params.skip = pageinfo.Skip + 1;
pageinfo.Params.take = pageinfo.Take;
Reader = conn.QueryMultiple(sql, (object)pageinfo.Params);
}
pageinfo.Total = Reader.Read <long>().FirstOrDefault();
if (pageinfo.Total != 0)
{
pageinfo.Data = Reader.Read <T>();
}
else
{
pageinfo.Data = new List <T>();
}
Reader.Dispose();
}
}
public void Dispose() => _gridReader.Dispose();