public async Task Debe_Ejecutar_Consulta_SQL()
{
var totalRegistros = 5;
//Preparar datos
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
//1. Eliminar todos los datos existentes
var items = await context.Parametros.ToListAsync();
context.Parametros.RemoveRange(items);
//2. Agregar Datos.
var list = FakeParametros(totalRegistros);
foreach (var item in list)
{
context.Parametros.Add(item);
}
context.SaveChanges();
}
//Ejecutar Test
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
//3. Consultar datos
var valorCambiar = "new.value.foo";
var registrosModificados = sqlExecuter.Execute("UPDATE ParametroSistemas SET valor = @value", new SqlParameter("@value", valorCambiar));
registrosModificados.ShouldBe(totalRegistros);
//4. Verificar
foreach (var parametro in context.Parametros)
{
parametro.Valor.ShouldBe(valorCambiar);
}
}
}
// GET: Home
public ActionResult Index(int page = 0, int size = 15)
{
var context = new SimpleDbContextProvider <CodeFirstDbContext>(new UserDbContext());
var respository = new EfRepositoryBase <CodeFirstDbContext, User, long>(context);
var clsResp = new EfRepositoryBase <CodeFirstDbContext, MClass, string>(context);
// sql
// var users =
// respository.SqlQuery(
// "select top 15 [UserID] as [Id],[Email],[TrueName],[AddedAt],[Role],[Status] from [TU_User] where [Role]=@role order by AddedAt desc",
// new SqlParameter("@role", (byte)4));
var managers = clsResp.Query(
c => c.ClassManagerID.HasValue && c.ClassManagerID > 0 && c.Status == (byte)0)
.Include(c => c.Manager)
.Include(c => c.Agency);
Mapper.CreateMap <MClass, ClassDto>()
.ForMember(s => s.Manager, d => d.MapFrom(c => c.Manager.TrueName))
.ForMember(s => s.AgencyName, d => d.MapFrom(c => c.Agency.AgencyName));
var cls = Mapper.Map <List <ClassDto> >(managers);
// inner join
// var users = respository
// .Query(u => u.Status == 0)
// .Join(managers, u => u.Id, m => m.ClassManagerID, (e, o) => e)
// .Distinct()
// .OrderByDescending(u => u.AddedAt)
// .Skip(page * size)
// .Take(size)
// .ToList();
// exists
// var users = respository
// .Query(u => managers.Any(m => m.ClassManagerID == u.Id))
// .OrderByDescending(u => u.AddedAt)
// .Skip(page * size)
// .Take(size)
// .ToList();
// LEFT OUTER JOIN
// var users = clsResp.Query(
// c => c.ClassManagerID.HasValue && c.ClassManagerID > 0 && c.Status == (byte)0)
// .Select(c => c.Manager).Distinct().ToList();
return(View(cls));
}
// GET: Home
public ActionResult Index(int page = 0, int size = 15)
{
var context = new SimpleDbContextProvider<CodeFirstDbContext>(new UserDbContext());
var respository = new EfRepositoryBase<CodeFirstDbContext, User, long>(context);
var clsResp = new EfRepositoryBase<CodeFirstDbContext, MClass, string>(context);
// sql
// var users =
// respository.SqlQuery(
// "select top 15 [UserID] as [Id],[Email],[TrueName],[AddedAt],[Role],[Status] from [TU_User] where [Role]=@role order by AddedAt desc",
// new SqlParameter("@role", (byte)4));
var managers = clsResp.Query(
c => c.ClassManagerID.HasValue && c.ClassManagerID > 0 && c.Status == (byte) 0)
.Include(c => c.Manager)
.Include(c => c.Agency);
Mapper.CreateMap<MClass, ClassDto>()
.ForMember(s => s.Manager, d => d.MapFrom(c => c.Manager.TrueName))
.ForMember(s => s.AgencyName, d => d.MapFrom(c => c.Agency.AgencyName));
var cls = Mapper.Map<List<ClassDto>>(managers);
// inner join
// var users = respository
// .Query(u => u.Status == 0)
// .Join(managers, u => u.Id, m => m.ClassManagerID, (e, o) => e)
// .Distinct()
// .OrderByDescending(u => u.AddedAt)
// .Skip(page * size)
// .Take(size)
// .ToList();
// exists
// var users = respository
// .Query(u => managers.Any(m => m.ClassManagerID == u.Id))
// .OrderByDescending(u => u.AddedAt)
// .Skip(page * size)
// .Take(size)
// .ToList();
// LEFT OUTER JOIN
// var users = clsResp.Query(
// c => c.ClassManagerID.HasValue && c.ClassManagerID > 0 && c.Status == (byte)0)
// .Select(c => c.Manager).Distinct().ToList();
return View(cls);
}
public async Task Debe_Recuperar_Valor_Consulta_SQL()
{
var totalRegistros = 3;
//Preparar datos
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
//1. Eliminar todos los datos existentes
var items = await context.Parametros.ToListAsync();
context.Parametros.RemoveRange(items);
//2. Agregar Datos.
var list = FakeParametros(totalRegistros);
foreach (var item in list)
{
context.Parametros.Add(item);
}
context.SaveChanges();
}
//Ejecutar Test
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
//2. Consultar datos
var verificarTotalRegistros = await sqlExecuter.SqlQuery <int>("SELECT count(*) FROM ParametroSistemas");
//3. Verificar
//Los valores recuperados con consulta SQL debe ser iguales a los total de registros insertados
verificarTotalRegistros.ShouldBe(totalRegistros);
}
}
public async Task Debe_Ejecutar_Consulta_SQL_Con_Bloque_Transaccion()
{
var totalRegistros = 5;
//Preparar datos
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
//1. Eliminar todos los datos existentes
var items = await context.Parametros.ToListAsync();
context.Parametros.RemoveRange(items);
//2. Agregar Datos.
var list = FakeParametros(totalRegistros);
foreach (var item in list)
{
context.Parametros.Add(item);
}
context.SaveChanges();
}
var valorTransaccionComplementa = "new.value.bar";
var valorTransaccionRollback = "foo.rollback";
var valorCambiar = valorTransaccionComplementa;
using (var scope = new TransactionScope())
{
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
var registrosModificados = sqlExecuter.Execute("UPDATE ParametroSistemas SET valor = @value", new SqlParameter("@value", valorCambiar));
registrosModificados.ShouldBe(totalRegistros);
}
//transctional code…
scope.Complete();
}
//Realizar otra actualizacion. Rollback
valorCambiar = valorTransaccionRollback;
try
{
using (var scope = new TransactionScope())
{
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
var registrosModificados = sqlExecuter.Execute("UPDATE ParametroSistemas SET valor = @value", new SqlParameter("@value", valorCambiar));
registrosModificados.ShouldBe(totalRegistros);
}
//transctional rollback…
throw new Exception("RollBack..");
}
}
catch (Exception ex)
{
output.WriteLine(ex.Message);
}
using (var context = new SqlExecuterTestDbContext())
{
var simpleDbContextProvider = new SimpleDbContextProvider <SqlExecuterTestDbContext>(context);
var sqlExecuter = new SqlExecuter(simpleDbContextProvider);
//4. Verificar
foreach (var parametro in context.Parametros)
{
parametro.Valor.ShouldBe(valorTransaccionComplementa);
}
}
}
