public List <Customer> RetrieveAllCustomers()
{
List <Customer> customers = new List <Customer>();
using (OleDbConnection conn = DBConnection.OLEConnCustomers())
{
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);
}
/// <summary>
/// insert a new item to customers list
/// </summary>
/// <remarks>
/// created by Awaab Elamin on 5/23/2020
/// </remarks>
/// <param name="customer"></param>
/// <returns></returns>
public bool InsertCustomer(Customer customer)
{
bool result = false;
//AppData.FilePath = "F:\\Transporter\\DeskApp\\OLEDB\\Customer.xlsx";
using (OleDbConnection conn = DBConnection.OLEConnCustomers())
{
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. = "F:\\Transporter\\DeskApp\\OLEDB\\Customer.xlsx";
using (OleDbConnection conn = DBConnection.OLEConnCustomers())
{
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);
}
