public List <Customer> Find(CustomerColumn column, string search)
{
List <Customer> c = new List <Customer>();
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = $"select id, name, phonenumber, email, username from customers where {column.ToString()} = @search";
cmd.Parameters.AddWithValue($"@search", search);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32("id");
string name = reader.GetString("name");
string phonenumber = reader.GetString("phonenumber");
string email = reader.GetString("email");
string username = reader.GetString("username");
c.Add(new Customer(name, phonenumber, email, username, id));
}
}
}
}
if (c.Count == 0)
{
return(null);
}
return(c);
}
// Returns a list of customers from the DB.
// This is the main DB functionality
public static IEnumerable <Customer> ToList(CustomerColumn orderBy, bool ascending, string filter = "")
{
//Open DB
using (var db = new LiteDB.LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + @"\uomi.db"))
{
// Retrieve 'customers' collection
var col = db.GetCollection <Customer>("customers");
// Declare a return variable
IEnumerable <Customer> retCustomers;
//Check if a filter was provided
if (string.IsNullOrEmpty(filter))
{
//No filter, fetch everything
retCustomers = col.FindAll();
}
else
{
//Apply filter in name or address or phonenumber
retCustomers = col.Find(x => true).Where(x => CultureInfo.CurrentCulture.CompareInfo.IndexOf(x.Name, filter, CompareOptions.IgnoreCase) >= 0 ||
CultureInfo.CurrentCulture.CompareInfo.IndexOf(x.Address, filter, CompareOptions.IgnoreCase) >= 0 ||
CultureInfo.CurrentCulture.CompareInfo.IndexOf(x.Phonenumber, filter, CompareOptions.IgnoreCase) >= 0);
}
//Order the output
switch (orderBy)
{
case CustomerColumn.Id:
retCustomers = ascending ? retCustomers.OrderBy(x => x.Id) : retCustomers.OrderByDescending(x => x.Id);
break;
case CustomerColumn.Name:
retCustomers = ascending ? retCustomers.OrderBy(x => x.Name) : retCustomers.OrderByDescending(x => x.Name);
break;
case CustomerColumn.Address:
retCustomers = ascending ? retCustomers.OrderBy(x => x.Address) : retCustomers.OrderByDescending(x => x.Address);
break;
case CustomerColumn.Phonenumber:
retCustomers = ascending ? retCustomers.OrderBy(x => x.Phonenumber) : retCustomers.OrderByDescending(x => x.Phonenumber);
break;
case CustomerColumn.Balance:
retCustomers = ascending ? retCustomers.OrderBy(x => x.Balance) : retCustomers.OrderByDescending(x => x.Balance);
break;
}
return(retCustomers);
}
}
public IEnumerable <Customer> FindCustomers(CustomerColumn columnName, string search)
{
return(customerController.Find(columnName, search));
}
public List <Customer> Find(CustomerColumn columnName, string search)
{
return(CustomerDB.Find(columnName, search));
}
public static string GetColumnName(CustomerColumn column)
{
switch (column) {
case CustomerColumn.CustomerID:
return "CustomerID";
case CustomerColumn.CompanyName:
return "CompanyName";
case CustomerColumn.ContactName:
return "ContactName";
case CustomerColumn.ContactTitle:
return "ContactTitle";
case CustomerColumn.Addres:
return "Address";
case CustomerColumn.City:
return "City";
case CustomerColumn.Region:
return "Region";
case CustomerColumn.PostalCode:
return "PostalCode";
case CustomerColumn.Country:
return "Country";
case CustomerColumn.Phone:
return "Phone";
case CustomerColumn.Fax:
return "Fax";
}
throw new InvalidOperationException("Invalid Enumeration Value");
}
// ----------------------------------------------------------------------
public CustomerGridViewColumn(CustomerColumn customerColumn, string header) :
base(typeof(Customer))
{
this.customerColumn = customerColumn;
Header = header;
} // CustomerGridViewColumn
