//Insere na tabela de incomes no banco de dados e atualiza o estoque de produtos public Response Insert(Income income) { using (TransactionScope scope = new TransactionScope()) { Response resultInsertIncome = base.InsertScalar(income); if (!resultInsertIncome.Success) { return(resultInsertIncome); } using (DbCommand command = DbFactory.GetCurrentCommand()) { foreach (IncomeItem incomeItem in income.IncomeItems) { incomeItem.IncomeID = resultInsertIncome.GeneratedId; command.Parameters.Clear(); command.CommandText = @"INSERT INTO INCOMEITEMS (INCOMEID,PRODUCTID,QUANTITY,UNITYPRICE,PROFIT) VALUES (@INCOMEID,@PRODUCTID,@QUANTITY,@UNITYPRICE,@PROFIT); UPDATE PRODUCTS SET PRICE = @UNITYPRICE, STOCK += @QUANTITY WHERE ID = @PRODUCTID"; command.Parameters.AddWithValue("@INCOMEID", incomeItem.IncomeID); command.Parameters.AddWithValue("@PRODUCTID", incomeItem.ProductID); command.Parameters.AddWithValue("@QUANTITY", incomeItem.Quantity); command.Parameters.AddWithValue("@UNITYPRICE", incomeItem.UnityPrice); command.Parameters.AddWithValue("@PROFIT", incomeItem.Profit); Response responseInsert = new DbExecuter().ExecuteQuery(command); if (!responseInsert.Success) { return(responseInsert); } } } scope.Complete(); return(Response.CreateSuccess("Operação efetuada com sucesso!")); } }
//Inserena tabela de sales e recupera o ID gerado e faze update no estoque de products public Response Insert(Sale sale) { using (TransactionScope scope = new TransactionScope()) { Response resultInsert = base.InsertScalar(sale); if (!resultInsert.Success) { return(resultInsert); } using (DbCommand cmd = DbFactory.GetCurrentCommand()) { foreach (var item in sale.SaleItems) { item.SaleID = resultInsert.GeneratedId; cmd.Parameters.Clear(); cmd.CommandText = @"INSERT INTO SALEITEMS (SALEID,PRODUCTID,QUANTITY,UNITYPRICE) VALUES (@SALEID,@PRODUCTID,@QUANTITY,@UNITYPRICE); UPDATE PRODUCTS SET STOCK -= @QUANTITY WHERE ID = @PRODUCTID"; cmd.Parameters.AddWithValue("@SALEID", item.SaleID); cmd.Parameters.AddWithValue("@PRODUCTID", item.ProductID); cmd.Parameters.AddWithValue("@QUANTITY", item.Quantity); cmd.Parameters.AddWithValue("@UNITYPRICE", item.UnityPrice); Response responseInsertSale = new DbExecuter().ExecuteQuery(cmd); if (!responseInsertSale.Success) { return(responseInsertSale); } } } scope.Complete(); return(Response.CreateSuccess("Operação efetuada com sucesso")); } }
public void Test2() { var tbd = TableDefinition.FromType <TypeTestModel>(); var table = GetSchema().CreateTable("Test1", tbd); var model = new TypeTestModel(); model.String = "Testing..."; var statement = doob.PgSql.Statements.Insert.Into("TestTable") .AddColumnsFromTableDefinition(tbd) .AddValuesFromObject(model) .AddClause(Returning.Columns(tbd.PrimaryKeys().Select(p => p.GetNameForDb()).ToArray())); var sql = statement.GetSqlCommand(tbd); table.Insert(model); var executor = new DbExecuter(table.GetConnectionString()); var resp = executor.ExecuteReader <TypeTestModel>($"SELECT * FROM {table}"); }
//Método que insere na tabela de checkout no banco de dados e junto atualiza o checkin para inativo e atualiza o status do quarto para disponível public Response Insert(CheckOut checkOut) { using (TransactionScope scope = new TransactionScope()) { Response resultInsertIncome = base.Insert(checkOut); if (!resultInsertIncome.Success) return resultInsertIncome; using (DbCommand command = DbFactory.GetCurrentCommand()) { CheckIn checkIn = new CheckIn() { ID = checkOut.CheckInID }; command.Parameters.Clear(); command.CommandText = @"UPDATE CHECKINS SET ACTIVE = @ACTIVE WHERE ID = @CHECKINID; UPDATE ROOMS SET ROOMSTATUS = @ROOMSTATUS WHERE ID = @ROOMID"; command.Parameters.AddWithValue("@CHECKINID", checkOut.CheckInID); command.Parameters.AddWithValue("@ACTIVE", false); command.Parameters.AddWithValue("@ROOMID", checkIn.RoomID); command.Parameters.AddWithValue("@ROOMSTATUS", RoomStatus.Disponível); Response responseInsert = new DbExecuter().ExecuteQuery(command); if (!responseInsert.Success) return responseInsert; } scope.Complete(); return Response.CreateSuccess("Operação efetuada com sucesso!"); } }
public StateUpdater(DbExecuter dbExecuter, string stateId, string stateTableName, JsonSerializerSettings jsonSettings) { _dbExecuter = dbExecuter; _stateId = stateId; _stateTableName = stateTableName; _jsonSettings = jsonSettings; }
public bool Exists(TipoVaga item) { DbCommand command = DbFactory.GetCommand(); command.CommandText = "SELECT ID FROM TIPOSVAGA WHERE NOME = @NOME"; command.Parameters.AddWithValue("@NOME", item.Nome); DataTable table = new DbExecuter().GetData(command); return(table.Rows.Count > 0); }
public void Setup() { DbExecuter = new DbExecuter(); MockCODCreator = new Mock <ICODCreator>(); DbExecuter.CODCreator = MockCODCreator.Object; MockConnectionStringMaker = new Mock <IConnectionStringMaker>(); DbExecuter.ConnectionStringMaker = MockConnectionStringMaker.Object; }
public PostgreSqlTransactionalStateStorage2(StateReference stateReference, PostgreSqlTransactionalStateOptions options, JsonSerializerSettings jsonSettings, ILogger <PostgreSqlTransactionalStateStorage2 <TState> > logger) { _stateId = stateReference.ToString(); _options = options; _dbExecuter = new DbExecuter(_options.ConnectionString); _jsonSettings = jsonSettings; _logger = logger; _jsonSettings.TypeNameHandling = TypeNameHandling.Auto; _jsonSettings.DefaultValueHandling = DefaultValueHandling.Include; _jsonSettings.Converters.Add(new Newtonsoft.Json.Converters.StringEnumConverter()); }
//public OperationResult<IEnumerable<Region>> GetRegions(int petitonLevelId) //{ // OperationResult<IEnumerable<Region>> result; // try // { // using (var db = new EDEntities()) // { // var regions = this.FakeRegions.Where(r => r.Level != null && r.Level.ID == petitonLevelId); // foreach (var fakeRegion in this.FakeRegions) // { // // load full level info // } // result = OperationResult<IEnumerable<Region>>.Success(regions); // } // } // catch (Exception exc) // { // result = OperationResult<IEnumerable<Region>>.ExceptionResult(exc); // } // return result; //} public OperationResult <IEnumerable <Region> > GetRegions(int petitonLevelId) { Func <EDEntities, OperationResult <IEnumerable <Region> > > getRegions = (db) => { var regions = this.FakeRegions.Where(r => r.Level != null && r.Level.ID == petitonLevelId); foreach (var fakeRegion in this.FakeRegions) { // load full level info } var result = OperationResult <IEnumerable <Region> > .Success(regions); return(result); }; var operationResult = DbExecuter.Execute(getRegions); return(operationResult); }
//Autalia o preço dos produtos de acordo com a entrada public Response UpdatePrice(Income income) { using (DbCommand command = DbFactory.GetCurrentCommand()) { foreach (IncomeItem incomItemEntry in income.IncomeItems) { command.Parameters.Clear(); command.CommandText = @"UPDATE PRODUCTS SET PRICE = @PRICE WHERE ID = @ID"; command.Parameters.AddWithValue("@ID", incomItemEntry.ProductID); command.Parameters.AddWithValue("@PRICE", incomItemEntry.UnityPrice); Response responseInsert = new DbExecuter().ExecuteQuery(command); if (!responseInsert.Success) { return(responseInsert); } } return(Response.CreateSuccess("Operação efetuada com sucesso!")); } }
public TipoVaga GetById(int id) { DbCommand command = DbFactory.GetCommand(); command.CommandText = "SELECT * FROM TIPOSVAGA WHERE ID = @ID"; command.Parameters.AddWithValue("@ID", id); DataTable table = new DbExecuter().GetData(command); if (table.Rows.Count == 0) { return(null); } TipoVaga tipoVaga = new TipoVaga(); tipoVaga.ID = id; tipoVaga.Nome = (string)table.Rows[0]["NOME"]; tipoVaga.Valor = Convert.ToDouble(table.Rows[0]["VALOR"]); return(tipoVaga); }
//atualiza public Response Update(Income income) { using (TransactionScope scope = new TransactionScope()) { Response resultUpdateIncome = base.Update(income); if (!resultUpdateIncome.Success) { return(resultUpdateIncome); } Response resultDelete = DeleteWhereId <IncomeItem>("INCOMEID", income.ID); if (!resultDelete.Success) { return(resultDelete); } using (DbCommand command = DbFactory.GetCurrentCommand()) { foreach (IncomeItem incomeItemEntry in income.IncomeItems) { incomeItemEntry.IncomeID = income.ID; command.Parameters.Clear(); command.CommandText = @"INSERT INTO INCOMEITEMS (INCOMEID,PRODUCTID,QUANTITY,UNITYPRICE) VALUES (@INCOMEID,@PRODUCTID,@QUANTITY,@UNITYPRICE); UPDATE PRODUCTS SET STOCK += @QUANTITY WHERE ID = @PRODUCTID"; command.Parameters.AddWithValue("@INCOMEID", incomeItemEntry.IncomeID); command.Parameters.AddWithValue("@PRODUCTID", incomeItemEntry.ProductID); command.Parameters.AddWithValue("@QUANTITY", incomeItemEntry.Quantity); command.Parameters.AddWithValue("@UNITYPRICE", incomeItemEntry.UnityPrice); Response responseInsert = new DbExecuter().ExecuteQuery(command); if (!responseInsert.Success) { return(responseInsert); } } } scope.Complete(); return(Response.CreateSuccess("Operação efetuada com sucesso!")); } }
public Vaga GetById(int id) { DbCommand command = DbFactory.GetCommand(); command.CommandText = "SELECT * FROM VAGAS WHERE ID = @ID"; command.Parameters.AddWithValue("@ID", id); DataTable table = new DbExecuter().GetData(command); if (table.Rows.Count == 0) { return(null); } return(new Vaga() { ID = id, Coberta = (bool)table.Rows[0]["COBERTA"], Codigo = (string)table.Rows[0]["CODIGO"], Especial = (bool)table.Rows[0]["ESPECIAL"], Idoso = (bool)table.Rows[0]["IDOSO"], Ocupada = (bool)table.Rows[0]["OCUPADA"], TipoVagaID = (int)table.Rows[0]["TIPOVAGAID"] }); }
public List <TipoVaga> GetAll() { DbCommand command = DbFactory.GetCommand(); command.CommandText = "SELECT * FROM TIPOSVAGA"; DataTable table = new DbExecuter().GetData(command); if (table.Rows.Count == 0) { return(null); } List <TipoVaga> tiposVagas = new List <TipoVaga>(); for (int i = 0; i < table.Rows.Count; i++) { TipoVaga tipoVaga = new TipoVaga(); tipoVaga.ID = (int)table.Rows[i]["ID"]; tipoVaga.Nome = (string)table.Rows[i]["NOME"]; tipoVaga.Valor = Convert.ToDouble(table.Rows[i]["VALOR"]); tiposVagas.Add(tipoVaga); } return(tiposVagas); }
public List <Vaga> GetAll() { DbCommand command = DbFactory.GetCommand(); command.CommandText = "SELECT * FROM VAGAS"; DataTable table = new DbExecuter().GetData(command); List <Vaga> vagas = new List <Vaga>(); for (int i = 0; i < table.Rows.Count; i++) { Vaga vaga = new Vaga() { ID = (int)table.Rows[i]["ID"], Coberta = (bool)table.Rows[i]["COBERTA"], Codigo = (string)table.Rows[i]["CODIGO"], Especial = (bool)table.Rows[i]["ESPECIAL"], Idoso = (bool)table.Rows[i]["IDOSO"], Ocupada = (bool)table.Rows[i]["OCUPADA"], TipoVagaID = (int)table.Rows[i]["TIPOVAGAID"] }; vagas.Add(vaga); } return(vagas); }
public DTO.Movimentacao LerMovimentacao(string placa) { DbCommand command = DbFactory.GetCommand(); command.CommandText = @"SELECT * FROM MOVIMENTACOES WHERE PLACA = @PLACA AND VALOR IS NULL"; command.Parameters.AddWithValue("@PLACA", placa); DataTable table = new DbExecuter().GetData(command); if (table.Rows.Count == 0) { return(null); } return(new Movimentacao() { ID = (int)table.Rows[0]["ID"], Cor = (int)table.Rows[0]["COR"], Entrada = (DateTime)table.Rows[0]["ENTRADA"], Modelo = (string)table.Rows[0]["MODELO"], Placa = (string)table.Rows[0]["PLACA"], VagaID = (int)table.Rows[0]["VAGAID"], }); }
protected DbExecuter Execute() { return(_dbExecuter ?? (_dbExecuter = new DbExecuter(_connectionString))); }
public OperationResult <Model.BusinessEntities.PetitionEmailVote> CreateEmailVoteRequest(EmailVote vote) { OperationResult <Model.BusinessEntities.PetitionEmailVote> emailVoteRequestResult; Func <EDEntities, OperationResult <Model.BusinessEntities.PetitionEmailVote> > procedure = (db) => { OperationResult <Model.BusinessEntities.PetitionEmailVote> result; var emailVote = db.PetitionEmailVotes.SingleOrDefault(v => v.PetitionID == vote.ID && v.Email == vote.Email); if (emailVote != null) { var votedPetition = db.Petitions.SingleOrDefault(p => p.ID == vote.ID); result = emailVote.IsConfirmed ? OperationResult <Model.BusinessEntities.PetitionEmailVote> .Fail( int.Parse(PetitionVoteOperationResult.AlreadyVotedCode), PetitionVoteOperationResult.AlreadyVotedMessage) : OperationResult <Model.BusinessEntities.PetitionEmailVote> .Success( int.Parse(PetitionVoteOperationResult.WaitingConfirmationCode), string.Format(PetitionVoteOperationResult.WaitingConfirmationMessage, emailVote.Email), new Model.BusinessEntities.PetitionEmailVote(emailVote, votedPetition)); return(result); } emailVote = new PetitionEmailVote { PetitionID = vote.ID, Email = vote.Email, CreatedDate = DateTime.Now, IsConfirmed = false, Hash = HashGenerator.Generate() }; db.PetitionEmailVotes.Add(emailVote); db.SaveChanges(); var petition = new Model.BusinessEntities.Petition(db.Petitions.SingleOrDefault(p => p.ID == emailVote.PetitionID)); var clientEmailVote = new Model.BusinessEntities.PetitionEmailVote() { ID = emailVote.ID, Petition = petition, Hash = emailVote.Hash, Email = emailVote.Email, CreatedDate = emailVote.CreatedDate, IsConfirmed = emailVote.IsConfirmed }; result = OperationResult <Model.BusinessEntities.PetitionEmailVote> .Success( int.Parse(PetitionVoteOperationResult.EmailVoteRequestCreatedCode), string.Format(PetitionVoteOperationResult.EmailVoteRequestCreatedMessage, emailVote.Email), clientEmailVote); return(result); }; emailVoteRequestResult = DbExecuter.Execute(procedure); return(emailVoteRequestResult); }