public SupplyRequestDetail Get(RestaurantDatabaseSettings ctx, int supplyRequestId, int productId) { SupplyRequestDetail result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " + $"{SupplyRequestDetail.ColumnNames.ProductId}, " + $"{SupplyRequestDetail.ColumnNames.Count}, " + $"{SupplyRequestDetail.ColumnNames.Active} " + $"FROM {SupplyRequestDetail.TableName} " + $"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestId} " + $"AND {SupplyRequestDetail.ColumnNames.ProductId} = {productId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { result = new SupplyRequestDetail() { SupplyRequestId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.SupplyRequestId]), ProductId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.ProductId]), Count = Convert.ToDecimal(reader[SupplyRequestDetail.ColumnNames.Count]), Active = Convert.ToBoolean(Convert.ToInt16(reader[SupplyRequestDetail.ColumnNames.Active].ToString())) }; } reader.Dispose(); } return(result); }
public IList <MeasurementUnit> Get(RestaurantDatabaseSettings ctx) { IList <MeasurementUnit> result = new List <MeasurementUnit>(); using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"{MeasurementUnit.ColumnNames.Id}, " + $"{MeasurementUnit.ColumnNames.Code}, " + $"{MeasurementUnit.ColumnNames.Description} " + $"FROM UnidadMedida"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { result.Add(new MeasurementUnit() { Id = Convert.ToInt32(reader[MeasurementUnit.ColumnNames.Id]), Code = reader[MeasurementUnit.ColumnNames.Code]?.ToString(), Description = reader[MeasurementUnit.ColumnNames.Description]?.ToString() }); } reader.Dispose(); } return(result); }
public Product Edit(RestaurantDatabaseSettings ctx, int productId, Product product) { Product result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"UPDATE Producto " + $"SET " + $"{Product.ColumnNames.Name} = '{product.Name}', " + $"{Product.ColumnNames.Details} = '{product.Details}', " + $"{Product.ColumnNames.ProductTypeId} = {product.ProductTypeId}, " + $"{Product.ColumnNames.Count} = {product.Count}, " + $"{Product.ColumnNames.MeasurementUnitId} = {product.MeasurementUnitId}, " + $"{Product.ColumnNames.Price} = {product.Price}, " + $"{Product.ColumnNames.ProviderId} = {product.ProviderId} " + $"WHERE {Product.ColumnNames.Id} = {productId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); cmd.ExecuteNonQuery(); result = product; } return(result); }
public MeasurementUnit Get(RestaurantDatabaseSettings ctx, int measurementUnitId) { MeasurementUnit result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"{MeasurementUnit.ColumnNames.Id}, " + $"{MeasurementUnit.ColumnNames.Code}, " + $"{MeasurementUnit.ColumnNames.Description} " + $"FROM UnidadMedida " + $"WHERE {MeasurementUnit.ColumnNames.Id} = {measurementUnitId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { result = new MeasurementUnit() { Id = Convert.ToInt32(reader[MeasurementUnit.ColumnNames.Id]), Code = reader[MeasurementUnit.ColumnNames.Code]?.ToString(), Description = reader[MeasurementUnit.ColumnNames.Description]?.ToString() }; } reader.Dispose(); } return(result); }
public SupplyRequestDetail Add(RestaurantDatabaseSettings ctx, SupplyRequestDetail supplyRequestDetail) { SupplyRequestDetail result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { conn.Open(); string query = $"INSERT INTO {SupplyRequestDetail.TableName} (" + $"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " + $"{SupplyRequestDetail.ColumnNames.ProductId}, " + $"{SupplyRequestDetail.ColumnNames.Count}, " + $"{SupplyRequestDetail.ColumnNames.Active} " + $") VALUES (" + $"{supplyRequestDetail.SupplyRequestId}, " + $"{supplyRequestDetail.ProductId}, " + $"{supplyRequestDetail.Count}, " + $"{1} " + $")"; OracleCommand cmd = new OracleCommand(query, conn); cmd.ExecuteNonQuery(); result = supplyRequestDetail; } return(result); }
public Booking Edit(RestaurantDatabaseSettings ctx, int bookingId, Booking booking) { Booking result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"UPDATE Reserva " + $"SET " + $"{Booking.ColumnNames.UserId} = {booking.UserId}, " + $"{Booking.ColumnNames.TableId} = {booking.TableId}, " + $"{Booking.ColumnNames.Date} = TO_DATE('{booking.Date:ddMMyyyyHHmm}', 'DDMMYYYYHH24MI'), " + $"{Booking.ColumnNames.Diners} = {booking.Diners}, " + $"{Booking.ColumnNames.Active} = {(booking.Active ? 1 : 0)} " + $"WHERE {Booking.ColumnNames.Id} = {bookingId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); cmd.ExecuteNonQuery(); booking.Date = booking.Date.ToLocalTime(); result = booking; } return(result); }
public SupplyRequestState GetState(RestaurantDatabaseSettings ctx, int supplyRequestStateId) { SupplyRequestState result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"{SupplyRequestState.ColumnNames.Id}, " + $"{SupplyRequestState.ColumnNames.Description} " + $"FROM {SupplyRequestState.TableName} sr " + $"WHERE {SupplyRequestState.ColumnNames.Id} = {supplyRequestStateId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { result = new SupplyRequestState() { Id = Convert.ToInt32(reader[$"{SupplyRequestState.ColumnNames.Id}"]), Description = reader[$"{SupplyRequestState.ColumnNames.Description}"]?.ToString() }; } reader.Dispose(); } return(result); }
public ProductType Get(RestaurantDatabaseSettings ctx, int productTypeId) { ProductType result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"{ProductType.ColumnNames.Id}, " + $"{ProductType.ColumnNames.Description} " + $"FROM TipoProducto " + $"WHERE {ProductType.ColumnNames.Id} = {productTypeId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { result = new ProductType() { Id = Convert.ToInt32(reader[ProductType.ColumnNames.Id]), Description = reader[ProductType.ColumnNames.Description]?.ToString() }; } reader.Dispose(); } return(result); }
public Recipe Add(RestaurantDatabaseSettings ctx, Recipe recipe) { Recipe result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { conn.Open(); OracleTransaction transaction = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); string query = $"INSERT INTO Receta (" + $"{Recipe.ColumnNames.Name}, " + $"{Recipe.ColumnNames.Price}, " + $"{Recipe.ColumnNames.Details}, " + $"{Recipe.ColumnNames.PreparationTime}, " + $"{Recipe.ColumnNames.Active} " + $") VALUES (" + $"'{recipe.Name}', " + $"{recipe.Price}, " + $"'{recipe.Details}', " + $"{recipe.PreparationTime}, " + $"{1} " + $") RETURNING {Recipe.ColumnNames.Id} INTO :{Recipe.ColumnNames.Id}"; OracleCommand cmd = new OracleCommand(query, conn); cmd.Transaction = transaction; cmd.Parameters.Add(new OracleParameter() { ParameterName = $":{Recipe.ColumnNames.Id}", OracleDbType = OracleDbType.Decimal, Direction = System.Data.ParameterDirection.Output }); try { cmd.ExecuteNonQuery(); recipe.Id = Convert.ToInt32(cmd.Parameters[$":{Recipe.ColumnNames.Id}"].Value.ToString()); if (recipe.RecipeDetails != null) { List <RecipeDetail> recipeDetails = new List <RecipeDetail>(); foreach (var recipeDetail in recipe.RecipeDetails) { RecipeDetail createdRecipeDetail = this.Add(conn, transaction, recipe.Id, recipeDetail); recipeDetails.Add(createdRecipeDetail); } } transaction.Commit(); result = recipe; } catch (Exception) { transaction.Rollback(); throw; } } return(result); }
IList <ProductType> IProductTypeService.Get(RestaurantDatabaseSettings ctx) { IList <ProductType> result = new List <ProductType>(); using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"{ProductType.ColumnNames.Id}, " + $"{ProductType.ColumnNames.Description} " + $"FROM TipoProducto"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { result.Add(new ProductType() { Id = Convert.ToInt32(reader[ProductType.ColumnNames.Id]), Description = reader[ProductType.ColumnNames.Description]?.ToString() }); } reader.Dispose(); } return(result); }
public Table Get(RestaurantDatabaseSettings ctx, int tableId) { Table result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"m.{Table.ColumnNames.Id}, " + $"m.{Table.ColumnNames.Number}, " + $"m.{Table.ColumnNames.Capacity}, " + $"m.{Table.ColumnNames.Description}, " + $"m.{Table.ColumnNames.Active}, " + $"m.{Table.ColumnNames.InUse}, " + $"m.{Table.ColumnNames.UserId}, " + $"u.{User.ColumnNames.RoleId} AS {User.TableName}{User.ColumnNames.RoleId}, " + $"u.{User.ColumnNames.Name} AS {User.TableName}{User.ColumnNames.Name}, " + $"u.{User.ColumnNames.LastName} AS {User.TableName}{User.ColumnNames.LastName}, " + $"u.{User.ColumnNames.Email} AS {User.TableName}{User.ColumnNames.Email}, " + $"u.{User.ColumnNames.Phone} AS {User.TableName}{User.ColumnNames.Phone}, " + $"u.{User.ColumnNames.Address} AS {User.TableName}{User.ColumnNames.Address} " + $"FROM {Table.TableName} m " + $"LEFT JOIN {User.TableName} u ON u.{User.ColumnNames.Id} = m.{Table.ColumnNames.UserId} " + $"WHERE m.{Table.ColumnNames.Id} = {tableId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { result = new Table() { Id = Convert.ToInt32(reader[Table.ColumnNames.Id]), Number = Convert.ToInt32(reader[Table.ColumnNames.Number]), Capacity = Convert.ToInt32(reader[Table.ColumnNames.Capacity]), Description = reader[Table.ColumnNames.Description]?.ToString(), Active = Convert.ToBoolean(reader[Table.ColumnNames.Active]), InUse = Convert.ToBoolean(reader[Table.ColumnNames.InUse]) }; if (reader[Table.ColumnNames.UserId] != DBNull.Value) { result.UserId = Convert.ToInt32(reader[Table.ColumnNames.UserId]); result.User = new User() { Id = (int)result.UserId, Email = reader[$"{User.TableName}{User.ColumnNames.Email}"]?.ToString(), Name = reader[$"{User.TableName}{User.ColumnNames.Name}"]?.ToString(), LastName = reader[$"{User.TableName}{User.ColumnNames.LastName}"]?.ToString(), Address = reader[$"{User.TableName}{User.ColumnNames.Address}"]?.ToString(), Phone = reader[$"{User.TableName}{User.ColumnNames.Phone}"]?.ToString(), RoleId = Convert.ToInt32(reader[$"{User.TableName}{User.ColumnNames.RoleId}"]) }; } } reader.Dispose(); } return(result); }
public Table Edit(RestaurantDatabaseSettings ctx, int tableId, Table table) { Table result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"UPDATE Mesa " + $"SET {Table.ColumnNames.Number} = {table.Number}, " + $"{Table.ColumnNames.Capacity} = {table.Capacity}, " + $"{Table.ColumnNames.Description} = '{table.Description}', " + $"{Table.ColumnNames.Active} = {(table.Active ? 1 : 0)}, " + $"{Table.ColumnNames.InUse} = {(table.InUse ? 1 : 0)}, " + $"{Table.ColumnNames.UserId} = :{Table.ColumnNames.UserId} " + $"WHERE {Table.ColumnNames.Id} = {tableId}"; OracleCommand cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter() { Value = table.UserId, ParameterName = $":{Table.ColumnNames.UserId}", DbType = System.Data.DbType.Int32 }); conn.Open(); cmd.ExecuteNonQuery(); result = table; } return(result); }
public RecipeController(IRecipeService recipeService, IProductService productService, IOptions <RestaurantDatabaseSettings> databaseContext) { this.recipeService = recipeService; this.productService = productService; this.dbSettings = databaseContext.Value; }
public TableController(ITableService authService, IUserService userService, IOptions <RestaurantDatabaseSettings> databaseContext) { this.tableService = authService; this.userService = userService; this.dbSettings = databaseContext.Value; }
public UserController(IUserService userService, IRoleService roleService, IOptions <RestaurantDatabaseSettings> databaseContext) { this.userService = userService; this.roleService = roleService; this.dbSettings = databaseContext.Value; }
public BookingController(IBookingService bookingService, IUserService userService, ITableService tableService, IOptions <RestaurantDatabaseSettings> databaseContext) { this.bookingService = bookingService; this.userService = userService; this.tableService = tableService; this.dbSettings = databaseContext.Value; }
public ProductController(IProductService productService, IMeasurementUnitService measurementUnitService, IProductTypeService productTypeService, IProviderService providerService, IOptions <RestaurantDatabaseSettings> databaseContext) { this.productService = productService; this.measurementUnitService = measurementUnitService; this.productTypeService = productTypeService; this.providerService = providerService; this.dbSettings = databaseContext.Value; }
public RestaurantDBContext(IOptions <RestaurantDatabaseSettings> options) { this._restaurantDatabaseSettings = options.Value; MongoClientSettings clientSettings = MongoClientSettings.FromConnectionString(_restaurantDatabaseSettings.ConnectionString); MongoClient client = new MongoClient(clientSettings); if (client != null) { this.database = client.GetDatabase(_restaurantDatabaseSettings.DatabaseName.Trim()); } //_restaurant = database.GetCollection<Restaurant>(settings.RestaurantCollectionName); }
public Product Add(RestaurantDatabaseSettings ctx, Product product) { Product result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"INSERT INTO Producto (" + $"{Product.ColumnNames.Name}, " + $"{Product.ColumnNames.Details}, " + $"{Product.ColumnNames.ProductTypeId}, " + $"{Product.ColumnNames.Count}, " + $"{Product.ColumnNames.MeasurementUnitId}, " + $"{Product.ColumnNames.CostPrice}, " + $"{Product.ColumnNames.SalePrice}, " + $"{Product.ColumnNames.ProviderId}, " + $"{Product.ColumnNames.Active} " + $") VALUES (" + $"'{product.Name}', " + $"'{product.Details}', " + $"{product.ProductTypeId}, " + $"{product.Count}, " + $"{product.MeasurementUnitId}, " + $"{product.CostPrice}, " + $":{Product.ColumnNames.SalePrice}, " + $"{product.ProviderId}, " + $"{1} " + $") RETURNING {Product.ColumnNames.Id} INTO :{Product.ColumnNames.Id}"; OracleCommand cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter() { Value = product.SalePrice, ParameterName = $":{Product.ColumnNames.SalePrice}", DbType = System.Data.DbType.Int32 }); cmd.Parameters.Add(new OracleParameter() { ParameterName = $":{Product.ColumnNames.Id}", OracleDbType = OracleDbType.Decimal, Direction = System.Data.ParameterDirection.Output }); conn.Open(); cmd.ExecuteNonQuery(); product.Id = Convert.ToInt32(cmd.Parameters[$":{Product.ColumnNames.Id}"].Value.ToString()); result = product; } return(result); }
public User Edit(RestaurantDatabaseSettings ctx, int userId, User user) { User result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"UPDATE Usuario " + $"SET " + $"{User.ColumnNames.RoleId} = {user.RoleId}, " + $"{User.ColumnNames.Name} = '{user.Name}', " + $"{User.ColumnNames.LastName} = '{user.LastName}', " + $"{User.ColumnNames.Email} = '{user.Email}', " + $"{User.ColumnNames.Password} = {(string.IsNullOrWhiteSpace(user.Password) ? User.ColumnNames.Password : $"{user.Password}")}, " +
public User SignIn(RestaurantDatabaseSettings dbSettings, string username, string password) { User user = null; using (OracleConnection conn = new OracleConnection(dbSettings.ConnectionString)) { string query = $"SELECT " + $"u.{User.ColumnNames.Id}, " + $"u.{User.ColumnNames.RoleId}, " + $"u.{User.ColumnNames.Name}, " + $"u.{User.ColumnNames.LastName}, " + $"u.{User.ColumnNames.Email}, " + $"u.{User.ColumnNames.Phone}, " + $"u.{User.ColumnNames.Address}, " + $"r.{Role.ColumnNames.Id} AS Role{Role.ColumnNames.Id}, " + $"r.{Role.ColumnNames.Description} AS Role{Role.ColumnNames.Description} " + $"FROM Usuario u " + $"join Rol r on u.{User.ColumnNames.RoleId} = r.{Role.ColumnNames.Id} " + $"WHERE LOWER({User.ColumnNames.Email}) = :username AND {User.ColumnNames.Password} = :password"; OracleCommand cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter("username", username.ToLower())); cmd.Parameters.Add(new OracleParameter("password", password)); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { user = new User() { Id = Convert.ToInt32(reader[User.ColumnNames.Id]), Email = reader[User.ColumnNames.Email]?.ToString(), Name = reader[User.ColumnNames.Name]?.ToString(), LastName = reader[User.ColumnNames.LastName]?.ToString(), Address = reader[User.ColumnNames.Address]?.ToString(), Phone = reader[User.ColumnNames.Phone]?.ToString(), RoleId = Convert.ToInt32(reader[User.ColumnNames.RoleId]), Role = new Role() { Id = Convert.ToInt32(reader[$"Role{Role.ColumnNames.Id}"]), Description = reader[$"Role{Role.ColumnNames.Description}"]?.ToString() } }; } reader.Dispose(); } return(user); }
public PurchaseController(IPurchaseService purchaseService, IOrderService orderService, IFlowService flowService, IUserService userService, ITableService tableService, IOptions <RestaurantDatabaseSettings> databaseContext) { this.purchaseService = purchaseService; this.userService = userService; this.flowService = flowService; this.tableService = tableService; this.dbSettings = databaseContext.Value; this.orderService = orderService; }
public SupplyRequestController(ISupplyRequestService supplyRequestService, IProductService productService, IProviderService providerService, IMailService mailService, IUserService userService, IOptions <RestaurantDatabaseSettings> databaseContext) { this.supplyRequestService = supplyRequestService; this.productService = productService; this.providerService = providerService; this.mailService = mailService; this.userService = userService; this.dbSettings = databaseContext.Value; }
public User Add(RestaurantDatabaseSettings ctx, User user) { User result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"INSERT INTO Usuario (" + $"{User.ColumnNames.RoleId}, " + $"{User.ColumnNames.Name}, " + $"{User.ColumnNames.LastName}, " + $"{User.ColumnNames.Email}, " + $"{User.ColumnNames.Phone}, " + $"{User.ColumnNames.Address}, " + $"{User.ColumnNames.Password}, " + $"{User.ColumnNames.Active} " + $") VALUES (" + $"{user.RoleId}, " + $"'{user.Name}', " + $"'{user.LastName}', " + $"'{user.Email}', " + $"'{user.Phone}', " + $"'{user.Address}', " + $"'{user.Password}', " + $"{1} " + $") RETURNING {User.ColumnNames.Id} INTO :{User.ColumnNames.Id}"; OracleCommand cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter() { ParameterName = $":{User.ColumnNames.Id}", OracleDbType = OracleDbType.Decimal, Direction = System.Data.ParameterDirection.Output }); conn.Open(); cmd.ExecuteNonQuery(); user.Id = Convert.ToInt32(cmd.Parameters[$":{User.ColumnNames.Id}"].Value.ToString()); result = user; } return(result); }
public bool Delete(RestaurantDatabaseSettings ctx, int tableId) { bool result = false; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"UPDATE Mesa " + $"SET {Table.ColumnNames.Active} = 0 " + $"WHERE {Table.ColumnNames.Id} = {tableId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); cmd.ExecuteNonQuery(); result = true; } return(result); }
public bool Delete(RestaurantDatabaseSettings ctx, SupplyRequestDetail supplyRequestDetail) { bool result = false; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { conn.Open(); string query = $"DELETE FROM {SupplyRequestDetail.TableName} " + $"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestDetail.SupplyRequestId} " + $"AND {SupplyRequestDetail.ColumnNames.ProductId} = {supplyRequestDetail.ProductId}"; OracleCommand deleteCommand = new OracleCommand(query, conn); deleteCommand.ExecuteNonQuery(); result = true; } return(result); }
public Booking Add(RestaurantDatabaseSettings ctx, Booking booking) { Booking result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { booking.Code = $"{booking.UserId}{booking.TableId}{booking.Date:yyMMddHH}"; string query = $"INSERT INTO Reserva (" + $"{Booking.ColumnNames.UserId}, " + $"{Booking.ColumnNames.Code}, " + $"{Booking.ColumnNames.TableId}, " + $"{Booking.ColumnNames.Date}, " + $"{Booking.ColumnNames.Diners}, " + $"{Booking.ColumnNames.Active} " + $") VALUES (" + $"{booking.UserId}, " + $"{booking.Code}, " + $"{booking.TableId}, " + $"TO_DATE('{booking.Date:ddMMyyyyHHmm}', 'DDMMYYYYHH24MI'), " + $"{booking.Diners}, " + $"{1} " + $") RETURNING {Booking.ColumnNames.Id} INTO :{Booking.ColumnNames.Id}"; OracleCommand cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter() { ParameterName = $":{Booking.ColumnNames.Id}", OracleDbType = OracleDbType.Decimal, Direction = System.Data.ParameterDirection.Output }); conn.Open(); cmd.ExecuteNonQuery(); booking.Id = Convert.ToInt32(cmd.Parameters[$":{Booking.ColumnNames.Id}"].Value.ToString()); booking.Date = booking.Date.ToLocalTime(); result = booking; } return(result); }
public IList <Recipe> Get(RestaurantDatabaseSettings ctx) { IList <Recipe> result = new List <Recipe>(); using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"SELECT " + $"{Recipe.ColumnNames.Id}, " + $"{Recipe.ColumnNames.Name}, " + $"{Recipe.ColumnNames.Price}, " + $"{Recipe.ColumnNames.Details}, " + $"{Recipe.ColumnNames.PreparationTime}, " + $"{Recipe.ColumnNames.Image}, " + $"{Recipe.ColumnNames.Active} " + $"FROM {Recipe.TableName} " + $"WHERE {Recipe.ColumnNames.Active} = 1"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Recipe recipe = new Recipe() { Id = Convert.ToInt32(reader[Recipe.ColumnNames.Id]), Name = reader[Recipe.ColumnNames.Name]?.ToString(), Price = Convert.ToDecimal(reader[Recipe.ColumnNames.Price]), Details = reader[Recipe.ColumnNames.Details]?.ToString(), PreparationTime = Convert.ToDecimal(reader[Recipe.ColumnNames.PreparationTime]), ImageBase64 = reader[Recipe.ColumnNames.Image]?.ToString(), Active = Convert.ToBoolean(Convert.ToInt16(reader[Recipe.ColumnNames.Active].ToString())) }; recipe.RecipeDetails = this.Get(conn, recipe).ToList(); result.Add(recipe); } reader.Dispose(); } return(result); }
public Table Add(RestaurantDatabaseSettings ctx, Table table) { Table result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"INSERT INTO Mesa " + $"({Table.ColumnNames.Number}, " + $"{Table.ColumnNames.Capacity}, " + $"{Table.ColumnNames.Description}, " + $"{Table.ColumnNames.Active}, " + $"{Table.ColumnNames.InUse}) " + $"VALUES " + $"({table.Number}, " + $"{table.Capacity}, " + $"'{table.Description}', " + $"{(table.Active ? 1 : 0)}, " + $"0) " + $"RETURNING {Table.ColumnNames.Id} INTO :{Table.ColumnNames.Id}"; OracleCommand cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter() { ParameterName = $":{Table.ColumnNames.Id}", OracleDbType = OracleDbType.Decimal, Direction = System.Data.ParameterDirection.Output }); conn.Open(); cmd.ExecuteNonQuery(); table.Id = Convert.ToInt32(cmd.Parameters[$":{Table.ColumnNames.Id}"].Value.ToString()); result = table; } return(result); }
public Provider Edit(RestaurantDatabaseSettings ctx, int providerId, Provider provider) { Provider result = null; using (OracleConnection conn = new OracleConnection(ctx.ConnectionString)) { string query = $"UPDATE Proveedor " + $"SET " + $"{Provider.ColumnNames.Name} = '{provider.Name}', " + $"{Provider.ColumnNames.Email} = '{provider.Email}', " + $"{Provider.ColumnNames.Phone} = '{provider.Phone}', " + $"{Provider.ColumnNames.Address} = '{provider.Address}' " + $"WHERE {Provider.ColumnNames.Id} = {providerId}"; OracleCommand cmd = new OracleCommand(query, conn); conn.Open(); cmd.ExecuteNonQuery(); result = provider; } return(result); }