public List <dynamic> ToPageList(int pageIndex, int pageSize, out int recordCount)
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.PagingSelect2008(_table, _fields, _where, _orderBy, pageIndex, pageSize);
var command = new SqlCommand(sql);
command.Parameters.AddRange(_parameters.Parameters);
var param = new SqlParameter("@RecordCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(param);
recordCount = 0;
List <dynamic> result;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
using (var sdr = command.ExecuteReader())
{
var handler = new SqlDataReaderSelectConverter();
result = handler.ConvertToDynamicList(sdr);
}
}
recordCount = (int)param.Value;
return(result);
}
public static List <dynamic> Fetch(this SqlConnection conn, string sql, object parameters = null, SqlTransaction trans = null)
{
try
{
var command = new SqlCommand(sql, conn, trans);
if (parameters != null)
{
command.Parameters.AddRange(ConvertToDbParameter(parameters).Parameters);
}
var mapper = new SqlDataReaderSelectConverter();
List <dynamic> result;
using (var sdr = command.ExecuteReader())
{
result = mapper.ConvertToDynamicList(sdr);
}
return(result);
}
catch (Exception)
{
trans?.Rollback();
}
finally
{
conn.Close();
}
return(new List <dynamic>());
}
public static async Task <T> SingleOrDefaultAsync <T>(this SqlConnection conn, string sql, object parameters = null, SqlTransaction trans = null)
{
try
{
var command = new SqlCommand(sql, conn, trans);
if (parameters != null)
{
command.Parameters.AddRange(ConvertToDbParameter(parameters).Parameters);
}
var mapper = new SqlDataReaderSelectConverter();
T result;
using (var sdr = await command.ExecuteReaderAsync())
{
result = mapper.ConvertToEntity <T>(sdr);
}
return(result);
}
catch (Exception)
{
trans?.Rollback();
}
finally
{
conn.Close();
}
return(default(T));
}
public async Task <PagingResult <T> > ToPageListAsync(int pageIndex, int pageSize)
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.PagingSelect2008(_table, _fields, _where, _orderBy, pageIndex, pageSize);
var command = new SqlCommand(sql);
command.Parameters.AddRange(_parameters.Parameters);
var param = new SqlParameter("@RecordCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(param);
var result = new PagingResult <T>();
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
using (var sdr = await command.ExecuteReaderAsync())
{
var handler = new SqlDataReaderSelectConverter();
result.Items = handler.ConvertToList <T>(sdr);
}
}
result.RecordCount = (int)param.Value;
return(result);
}
public async Task <dynamic> FirstOrDefaultAsync()
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(_table, _fields, _where, _orderBy, 1);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_parameters.Parameters);
var sdr = await command.ExecuteReaderAsync();
var handler = new SqlDataReaderSelectConverter();
return(handler.ConvertToDynamicEntity(sdr));
}
}
public async Task <List <dynamic> > ToListAsync()
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(_table, _fields, _where, _orderBy);
List <dynamic> result;
var visitor = new SqlDataReaderSelectConverter();
using (var conn = new SqlConnection(_connectionString))
{
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_parameters.Parameters);
conn.Open();
using (var sdr = await command.ExecuteReaderAsync())
{
result = visitor.ConvertToDynamicList(sdr);
}
}
return(result);
}
