public static bool ApproveUsers(int id) { SqlConnection connection = CompanyDB_Class.GetConnection(); string updateStatement = "UPDATE users SET approved = 1 WHERE id = @id"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@id", id); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
private static void Insers_Products(List <Order_Products> products, int order_id) //method 2 { foreach (Order_Products product in products) { SqlConnection connection_Products = CompanyDB_Class.GetConnection(); string insertStatement_Products = "INSERT INTO orders_products " + "( order_id, product_id, amount ) VALUES ( @order_id, @product_id, @amount)"; SqlCommand insertCommand_Products = new SqlCommand(insertStatement_Products, connection_Products); insertCommand_Products.Parameters.AddWithValue("@order_id", order_id); insertCommand_Products.Parameters.AddWithValue("@product_id", product.product_id); insertCommand_Products.Parameters.AddWithValue("@amount", product.ammount); try { connection_Products.Open(); int check = insertCommand_Products.ExecuteNonQuery(); if (check > 0) { } } catch (SqlException ex) { throw ex; } finally { connection_Products.Close(); } } }
private static int Order_Products() //method 1 { int order_id = 0; SqlConnection connectionOrderID = CompanyDB_Class.GetConnection(); string selectStatementOrderID = "SELECT MAX(id) as id FROM orders"; SqlCommand selectcommandOrderID = new SqlCommand(selectStatementOrderID, connectionOrderID); try { connectionOrderID.Open(); SqlDataReader order_id_Reader = selectcommandOrderID.ExecuteReader(); if (order_id_Reader.Read()) { order_id = Convert.ToInt32(order_id_Reader["id"]); } order_id_Reader.Close(); } catch (SqlException ex) { throw ex; } finally { connectionOrderID.Close(); } return(order_id); }
public static List <ProductStatusClass> GetProduct_status() { SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT id, status " + "FROM product_status"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(); List <ProductStatusClass> status = new List <ProductStatusClass>(); while (custReader.Read()) { ProductStatusClass productStatus = new ProductStatusClass(); productStatus.id = (int)custReader["id"]; productStatus.status = custReader["status"].ToString(); status.Add(productStatus); } custReader.Close(); return(status); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool DeleteUsers(int id) { SqlConnection connection = CompanyDB_Class.GetConnection(); string deleteStatement = "DELETE FROM users " + "WHERE id = @id"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@id", id); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool AddClient(ClientClass client) { SqlConnection connection = CompanyDB_Class.GetConnection(); string insertStatement = "INSERT INTO clients " + "( first_name, last_name, address, city, country, postal_code, " + "company, phone, payment, email, created, updated ) " + "VALUES ( @first_name, @last_name, @address, @city, @country, @postal_code, " + "@company, @phone, @payment, @email, @created, @updated )"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@first_name", client.firstNameClient); insertCommand.Parameters.AddWithValue( "@last_name", client.lastNameClient); insertCommand.Parameters.AddWithValue( "@address", client.addressClient); insertCommand.Parameters.AddWithValue( "@city", client.cityClient); insertCommand.Parameters.AddWithValue( "@country", client.countryClient); insertCommand.Parameters.AddWithValue( "@postal_code", client.postalCodeClient); insertCommand.Parameters.AddWithValue( "@company", client.companyClient); insertCommand.Parameters.AddWithValue( "@phone", client.phoneClient); insertCommand.Parameters.AddWithValue( "@payment", client.paymentClient); insertCommand.Parameters.AddWithValue( "@email", client.emailClient); insertCommand.Parameters.AddWithValue( "@created", client.createdClient); insertCommand.Parameters.AddWithValue( "@updated", client.updatedClient); try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool CreateProfile(UsersClass user) { SqlConnection connection = CompanyDB_Class.GetConnection(); string insertStatement = "INSERT INTO Users " + "( first_name, last_name, email, phone, " + "password, role, approved, created, updated ) " + "VALUES ( @firstNameUsers, @lastNameUsers, @emailUsers, @phoneUsers, " + " @passwordUsers, @roleUsers, @approvedUsers, @createdUsers, @updatedUsers)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@firstNameUsers", user.firstNameUsers); insertCommand.Parameters.AddWithValue( "@lastNameUsers", user.lastNameUsers); insertCommand.Parameters.AddWithValue( "@emailUsers", user.emailUsers); insertCommand.Parameters.AddWithValue( "@phoneUsers", user.phoneUsers); insertCommand.Parameters.AddWithValue( "@roleUsers", user.roleUsers); insertCommand.Parameters.AddWithValue( "@createdUsers", user.createdUsers); insertCommand.Parameters.AddWithValue( "@updatedUsers", user.updatedUsers); insertCommand.Parameters.AddWithValue( "@approvedUsers", user.approvedUsers); insertCommand.Parameters.AddWithValue( "@passwordUsers", user.passwordUsers); try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool ChangeUsers(UsersClass user) { SqlConnection connection = CompanyDB_Class.GetConnection(); string updateStatement = "UPDATE users SET " + "first_name = @first_name, " + "last_name = @last_name, " + "email = @email, " + "phone = @phone, " + "role = @role, " + "updated = @updated, " + "password = @password " + " WHERE id = @id "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@id", user.userID); updateCommand.Parameters.AddWithValue( "@first_name", user.firstNameUsers); updateCommand.Parameters.AddWithValue( "@last_name", user.lastNameUsers); updateCommand.Parameters.AddWithValue( "@email", user.emailUsers); updateCommand.Parameters.AddWithValue( "@phone", user.phoneUsers); updateCommand.Parameters.AddWithValue( "@role", user.roleUsers); updateCommand.Parameters.AddWithValue( "@updated", user.updatedUsers); updateCommand.Parameters.AddWithValue( "@password", user.passwordUsers); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <ClientClass> SearchClient(string search) { SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT id, first_name, last_name, address, city, country, postal_code, " + "company, phone, payment, email, created, updated " + "FROM clients " + "WHERE last_name = @last_name" + " OR first_name = @first_name" + " OR email = @email"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); selectcommand.Parameters.AddWithValue("@last_name", search); selectcommand.Parameters.AddWithValue("@first_name", search); selectcommand.Parameters.AddWithValue("@email", search); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(); List <ClientClass> clients = new List <ClientClass>(); while (custReader.Read()) { ClientClass client = new ClientClass(); client.idClient = (int)custReader["id"]; client.firstNameClient = custReader["first_name"].ToString(); client.lastNameClient = custReader["last_name"].ToString(); client.addressClient = custReader["address"].ToString(); client.cityClient = custReader["city"].ToString(); client.countryClient = custReader["country"].ToString(); client.postalCodeClient = custReader["postal_code"].ToString(); client.companyClient = custReader["company"].ToString(); client.phoneClient = custReader["phone"].ToString(); client.paymentClient = custReader["payment"].ToString(); client.emailClient = custReader["email"].ToString(); client.updatedClient = Convert.ToDateTime(custReader["updated"]); client.createdClient = Convert.ToDateTime(custReader["created"]); clients.Add(client); } custReader.Close(); return(clients); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static UsersClass GetUser(string userEmail) { SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT id, first_name, last_name, email, phone, " + "password, role, approved, created, updated " + "FROM users " + "WHERE email = @userEmail" + " AND role = 'user'" + " AND approved = '1'"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); selectcommand.Parameters.AddWithValue("@userEmail", userEmail); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(CommandBehavior.SingleRow); if (custReader.Read()) { UsersClass user = new UsersClass(); user.userID = (int)custReader["id"]; user.firstNameUsers = custReader["first_name"].ToString(); user.lastNameUsers = custReader["last_name"].ToString(); user.emailUsers = custReader["email"].ToString(); user.phoneUsers = custReader["phone"].ToString(); user.roleUsers = custReader["role"].ToString(); user.createdUsers = Convert.ToDateTime(custReader["created"]); user.updatedUsers = Convert.ToDateTime(custReader["updated"]); user.approvedUsers = custReader["approved"].ToString(); user.passwordUsers = custReader["password"].ToString(); return(user); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool ModifyProduct(ProductClass product) { SqlConnection connection = CompanyDB_Class.GetConnection(); string updateStatement = "UPDATE products SET " + "name = @name, " + "description = @description, " + "updated = @updated, " + "cost = @cost, " + "status = @status " + " WHERE id = @id "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@id", product.idProduct); updateCommand.Parameters.AddWithValue( "@name", product.nameProduct); updateCommand.Parameters.AddWithValue( "@description", product.descriptionProduct); updateCommand.Parameters.AddWithValue( "@updated", product.updatedProduct); updateCommand.Parameters.AddWithValue( "@cost", product.costProduct); updateCommand.Parameters.AddWithValue( "@status", product.idStatusProduct); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <UsersClass> GetUsers() { SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT id, first_name, last_name, email, phone, " + "password, role, approved, created, updated " + "FROM users"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(); List <UsersClass> users = new List <UsersClass>(); while (custReader.Read()) { UsersClass user = new UsersClass(); user.userID = (int)custReader["id"]; user.firstNameUsers = custReader["first_name"].ToString(); user.lastNameUsers = custReader["last_name"].ToString(); user.emailUsers = custReader["email"].ToString(); user.phoneUsers = custReader["phone"].ToString(); user.roleUsers = custReader["role"].ToString(); user.createdUsers = Convert.ToDateTime(custReader["created"]); user.updatedUsers = Convert.ToDateTime(custReader["updated"]); user.approvedUsers = custReader["approved"].ToString(); user.passwordUsers = custReader["password"].ToString(); users.Add(user); } custReader.Close(); return(users); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool AddOrder(OrderClass order, List <Order_Products> products) { SqlConnection connection = CompanyDB_Class.GetConnection(); string insertStatement = "INSERT INTO orders " + "( date, subtotal, tax, total, status_order_id, client_id) " + "VALUES ( @date, @subtotal, @tax, @total, @status_order_id, @client_id)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@date", order.dateOrder); insertCommand.Parameters.AddWithValue("@subtotal", order.subtotalOrders); insertCommand.Parameters.AddWithValue("@tax", order.taxOrder); insertCommand.Parameters.AddWithValue("@total", order.totalOrder); insertCommand.Parameters.AddWithValue("@status_order_id", order.statusOrderId); insertCommand.Parameters.AddWithValue("@client_id", order.client_id); try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { connection.Close(); int order_id = Order_Products(); // method 1 Insers_Products(products, order_id); // method 2 return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool AddProduct(ProductClass product) { SqlConnection connection = CompanyDB_Class.GetConnection(); string insertStatement = "INSERT INTO products " + "( name, description, created, updated, cost, status ) " + "VALUES ( @name, @description, @created, @updated, @cost, @status )"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@name", product.nameProduct); insertCommand.Parameters.AddWithValue( "@description", product.descriptionProduct); insertCommand.Parameters.AddWithValue( "@created", product.createdProduct); insertCommand.Parameters.AddWithValue( "@updated", product.updatedProduct); insertCommand.Parameters.AddWithValue( "@cost", product.costProduct); insertCommand.Parameters.AddWithValue( "@status", product.idStatusProduct); try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <ProductClass> SearchProduct(string search) //////////////////////////// { SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT id, name, description, created, updated, cost, status " + "FROM products " + "WHERE name = @name" + " OR cost = @cost"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); selectcommand.Parameters.AddWithValue("@name", search); selectcommand.Parameters.AddWithValue("@cost", search); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(); List <ProductClass> products = new List <ProductClass>(); while (custReader.Read()) { ProductClass product = new ProductClass(); product.idProduct = (int)custReader["id"]; product.nameProduct = custReader["name"].ToString(); product.descriptionProduct = custReader["description"].ToString(); product.updatedProduct = Convert.ToDateTime(custReader["updated"]); product.createdProduct = Convert.ToDateTime(custReader["created"]); product.costProduct = Convert.ToDouble(custReader["cost"]); product.idStatusProduct = Convert.ToInt32(custReader["status"]); products.Add(product); } custReader.Close(); return(products); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <OrderClass> GetOrders() { SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT id, date, subtotal, tax, total, status_order_id, client_id " + "FROM orders"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(); List <OrderClass> orders = new List <OrderClass>(); while (custReader.Read()) { OrderClass order = new OrderClass(); order.idOrders = (int)custReader["id"]; order.dateOrder = Convert.ToDateTime(custReader["date"]); order.subtotalOrders = Convert.ToDecimal(custReader["subtotal"]); order.taxOrder = Convert.ToDecimal(custReader["tax"]); order.totalOrder = Convert.ToDecimal(custReader["total"]); order.statusOrderId = Convert.ToInt32(custReader["status_order_id"]); order.client_id = Convert.ToInt32(custReader["client_id"]); orders.Add(order); } custReader.Close(); return(orders); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <ProductClass> GetProductByID(int order_id) { List <ProductClass> products = new List <ProductClass>(); SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT products.name, products.description, products.cost, orders_products.amount " + " FROM products " + " INNER JOIN orders_products ON orders_products.product_id = products.id " + " WHERE order_id = @order_id"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); selectcommand.Parameters.AddWithValue("@order_id", order_id); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(); while (custReader.Read()) { ProductClass product = new ProductClass(); product.nameProduct = custReader["name"].ToString(); product.descriptionProduct = custReader["description"].ToString(); product.costProduct = Convert.ToDouble(custReader["cost"]); product.ammountProduct = Convert.ToInt32(custReader["amount"]); products.Add(product); } return(products); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool ChangeStatusOrder(OrderClass order) { SqlConnection connection = CompanyDB_Class.GetConnection(); string updateStatement = "UPDATE orders SET " + "status_order_id = @status_order_id " + " WHERE id = @id "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@id", order.idOrders); updateCommand.Parameters.AddWithValue( "@status_order_id", order.statusOrderId); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <ProductClass> GetProductsName() { SqlConnection connection = CompanyDB_Class.GetConnection(); string selectStatement = "SELECT id, name, cost " + "FROM products"; SqlCommand selectcommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader custReader = selectcommand.ExecuteReader(); List <ProductClass> products = new List <ProductClass>(); while (custReader.Read()) { ProductClass product = new ProductClass(); product.idProduct = (int)custReader["id"]; product.nameProduct = custReader["name"].ToString(); product.costProduct = Convert.ToDouble(custReader["cost"]); products.Add(product); } custReader.Close(); return(products); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool ModifyClient(ClientClass client) { SqlConnection connection = CompanyDB_Class.GetConnection(); string updateStatement = "UPDATE clients SET " + "first_name = @first_name, " + "last_name = @last_name, " + "address = @address, " + "city = @city, " + "country = @country, " + "postal_code = @postal_code, " + "company = @company, " + "phone = @phone, " + "payment = @payment, " + "email = @email, " + "created = @created, " + "updated = @updated " + " WHERE id = @id "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@id", client.idClient); updateCommand.Parameters.AddWithValue( "@first_name", client.firstNameClient); updateCommand.Parameters.AddWithValue( "@last_name", client.lastNameClient); updateCommand.Parameters.AddWithValue( "@address", client.addressClient); updateCommand.Parameters.AddWithValue( "@city", client.cityClient); updateCommand.Parameters.AddWithValue( "@country", client.countryClient); updateCommand.Parameters.AddWithValue( "@postal_code", client.postalCodeClient); updateCommand.Parameters.AddWithValue( "@company", client.companyClient); updateCommand.Parameters.AddWithValue( "@phone", client.phoneClient); updateCommand.Parameters.AddWithValue( "@payment", client.paymentClient); updateCommand.Parameters.AddWithValue( "@email", client.emailClient); updateCommand.Parameters.AddWithValue( "@created", client.createdClient); updateCommand.Parameters.AddWithValue( "@updated", client.updatedClient); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }