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); } } }