public async Task <PagingResult <T> > ToPageListAsync(int pageIndex, int pageSize)
{
var fields = GetFields();
var from = GetFrom();
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.PagingSelect(from, 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 SqlDataReaderConverter <T>(_includeProperties.Select(p => p.PropertyName).ToArray());
result.Items = handler.ConvertToEntityList(sdr);
}
}
result.RecordCount = (int)param.Value;
return(result);
}
public T FirstOrDefault()
{
var fields = GetFields();
var from = GetFrom();
var sqlBuilder = new SqlServerSqlBuilder();
var sql = sqlBuilder.Select(from, fields, _where, _orderBy, 1);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_whereParameters.ToSqlParameters().ToArray());
var sdr = command.ExecuteReader();
var handler = new SqlDataReaderConverter <T>(_includeProperties.ToArray());
return(handler.ConvertToEntity2(sdr));
}
}
public async Task <T> FirstOrDefaultAsync()
{
var fields = GetFields();
var from = GetFrom();
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(from, fields, _where, _orderBy, 1);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_parameters.Parameters);
using (var sdr = await command.ExecuteReaderAsync())
{
var handler = new SqlDataReaderConverter <T>(_includeProperties.Select(p => p.PropertyName).ToArray());
return(handler.ConvertToEntity(sdr));
}
}
}
public async Task <List <T> > ToListAsync()
{
var fields = GetFields();
var from = GetFrom();
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(from, fields, _where, _orderBy);
var visitor = new SqlDataReaderConverter <T>(_includeProperties.Select(p => p.PropertyName));
List <T> result;
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.ConvertToEntityList(sdr);
}
}
return(result);
}
public List <T> ToPageList(int pageIndex, int pageSize, out int recordCount)
{
var fields = GetFields();
var from = GetFrom();
recordCount = 0;
var sqlBuilder = new SqlServerSqlBuilder();
var sql = sqlBuilder.GetPagingQuerySql(fields, from, _where, _orderBy, pageIndex, pageSize);
var command = new SqlCommand(sql);
command.Parameters.AddRange(_whereParameters.ToSqlParameters().ToArray());
var param = new SqlParameter("@RecordCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(param);
List <T> result;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
using (var sdr = command.ExecuteReader())
{
var handler = new SqlDataReaderConverter <T>(_includeProperties.ToArray());
result = handler.ConvertToEntityList(sdr);
}
}
recordCount = (int)param.Value;
return(result);
}
