private List <Customer> retriveAllCustomers() { List <Customer> customers = new List <Customer>(); AppData.FilePath = "F:\\Transporter\\DeskApp\\OLEDB\\Customer.xlsx"; using (OleDbConnection conn = DBConnection.OLEConn()) { try { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = @"select CustomerID, FirstName, MiddleName, LastName, PhoneNumber, Email, RegionID, AddressLine, Active from [Sheet1$];"; OleDbDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Customer customer = new Customer(); customer.CustomerID = reader[0].ToString(); customer.FirstName = reader[1].ToString(); customer.MiddleName = reader[2].ToString(); customer.LastName = reader[3].ToString(); customer.PhoneNumber = reader[4].ToString(); customer.Email = reader[5].ToString(); customer.RegionID = reader[6].ToString(); customer.AddressLine = reader[7].ToString(); customer.Active = Convert.ToBoolean(reader[8]); customers.Add(customer); } reader.Close(); } } catch (Exception) { throw; } finally { conn.Close(); conn.Dispose(); } } return(customers); }
//public int DeactivateProduct(int productId) //{ // throw new NotImplementedException(); //} //public bool DeactivateProduct(string productId) //{ // throw new NotImplementedException(); //} //public bool InsertProduct(Product product) //{ // throw new NotImplementedException(); //} public List <Product> RetrieveAllProducts() { List <Product> products = new List <Product>(); AppData.FilePath = "C:\\Transporter\\DeskApp\\OLEDB\\Product.xlsx"; using (OleDbConnection conn = DBConnection.OLEConn()) { try { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = @"select ProductID, ProductName, pirice, DateReceived, weight, Dimension, Description, Qoh, Active, SupplierId from [Sheet1$];"; OleDbDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Product product = new Product(); product.ProductID = reader[0].ToString(); product.ProductName = reader[1].ToString(); product.pirice = Convert.ToDecimal(reader[2]); product.DateReceived = Convert.ToDateTime(reader[3]); product.weight = Convert.ToDecimal(reader[4]); product.Dimension = Convert.ToDecimal(reader[5]); product.Description = reader[6].ToString(); product.Qoh = Convert.ToInt32(reader[7].ToString()); product.Active = Convert.ToBoolean(reader[8]); product.SupplierId = Convert.ToInt32(reader[9].ToString()); products.Add(product); } reader.Close(); } } catch (Exception) { throw; } finally { conn.Close(); conn.Dispose(); } } return(products); }
private bool InsertCustomerToDB(Customer customer) { bool result = false; AppData.FilePath = "F:\\Transporter\\DeskApp\\OLEDB\\Customer.xlsx"; using (OleDbConnection conn = DBConnection.OLEConn()) { try { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; // cmd.CommandText = @"INSERT INTO [Sheet1$] (CustomerID,FirstName,MiddleName,LastName,PhoneNumber,Email,RegionID,AddressLine,Active) VALUES('" + customer.CustomerID + "',' " + customer.FirstName + "',' " + customer.MiddleName + "',' " + customer.LastName + "',' " + customer.PhoneNumber + "',' " + customer.Email + "',' " + customer.RegionID + "',' " + customer.AddressLine + "',' " + customer.Active + "');"; int rows = cmd.ExecuteNonQuery(); if (rows == 1) { result = true; } } catch (Exception) { throw; } finally { conn.Close(); conn.Dispose(); } } return(result); }
public bool updateCustomer(Customer oldCustomer, Customer newCustomer) { bool result = false; //foreach (Customer customer in customers) //{ // if ((customer.CustomerID == oldCustomer.CustomerID) && (customer.FirstName == oldCustomer.FirstName) // && (customer.MiddleName == oldCustomer.MiddleName) && (customer.LastName == oldCustomer.LastName) // && (customer.PhoneNumber == oldCustomer.PhoneNumber) && (customer.RegionID == oldCustomer.RegionID) // && (customer.Email == oldCustomer.Email) && (customer.AddressLine== oldCustomer.AddressLine)) // { // customers.Remove(customer); // customers.Add(newCustomer); // break; // } //} AppData.FilePath = "F:\\Transporter\\DeskApp\\OLEDB\\Customer.xlsx"; using (OleDbConnection conn = DBConnection.OLEConn()) { try { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; //UPDATE table_name //SET column1 = value1, column2 = value2, ... //WHERE condition; cmd.CommandText = @"UPDATE [Sheet1$] " + "SET " + "CustomerID = '" + newCustomer.CustomerID + "'," + "FirstName = '" + newCustomer.FirstName + "'," + "MiddleName = '" + newCustomer.MiddleName + "'," + "LastName = '" + newCustomer.LastName + "'," + "PhoneNumber = '" + newCustomer.PhoneNumber + "'," + "Email = '" + newCustomer.Email + "'," + "RegionID = '" + newCustomer.RegionID + "'," + "AddressLine = '" + newCustomer.AddressLine + "'," + "Active = '" + newCustomer.Active + "'" + " WHERE " + "CustomerID = '" + oldCustomer.CustomerID + "'" + " And FirstName = '" + oldCustomer.FirstName + "'" + " And MiddleName = '" + oldCustomer.MiddleName + "'" + " And LastName = '" + oldCustomer.LastName + "'" + " And PhoneNumber = '" + oldCustomer.PhoneNumber + "'" + " And Email = '" + oldCustomer.Email + "'" + " And RegionID = '" + oldCustomer.RegionID + "'" + " And AddressLine = '" + oldCustomer.AddressLine + "'" + " And Active = '" + oldCustomer.Active + "'"; int rows = cmd.ExecuteNonQuery(); if (rows == 1) { result = true; } } catch (Exception) { throw; } finally { conn.Close(); conn.Dispose(); } } return(result); }