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