public ourCustomerList SearchRecord(string updateitem, string itemdetail)
{
ourCustomerList searchresult = new ourCustomerList();
SqlDataReader sqlsearchresult;
var sqlsearchcustomer = "SELECT * FROM customer WHERE " + updateitem.ToLower() + " = @_Detail";
try
{
using (var cmd = new SqlCommand(sqlsearchcustomer, conn))
{
cmd.Parameters.AddWithValue("@_Detail", itemdetail);
sqlsearchresult = cmd.ExecuteReader();
if (sqlsearchresult.HasRows)
{
while (sqlsearchresult.Read())
{
DateTime dt = DateTime.Parse(sqlsearchresult[DOB].ToString());
ourCustomer acustomer = new ourCustomer(int.Parse(sqlsearchresult[CUSTOMERID].ToString()));
acustomer.FirstName = sqlsearchresult[FRISTNAME].ToString();
acustomer.LastName = sqlsearchresult[LASTNAME].ToString();
acustomer.Email = sqlsearchresult[EMAIL].ToString();
acustomer.Website = sqlsearchresult[WEBSITE].ToString();
acustomer.DOB = dt.Day + "-" + dt.Month + "-" + dt.Year;
acustomer.LoyaltyMember = (sqlsearchresult[LOYALTYMEMBER].ToString().CompareTo("True") == 0) ? "true" : "false";
acustomer.Phone = sqlsearchresult[PHONE].ToString();
acustomer.Mobile = sqlsearchresult[MOBILE].ToString();
acustomer.Fax = sqlsearchresult[FAX].ToString();
acustomer.MemberCard = sqlsearchresult[MEMBERCARD].ToString();
//
searchresult.Addcustomer(acustomer);
}
}
}
if (searchresult != null)
{
return(searchresult);
}
else
{
throw new ApplicationException("[Error] Cannot retrieve the data!");
}
}
catch
{
throw new ApplicationException("[Error] Cannot retrieve the SQL server!");
}
}
public ourCustomerList RetrievalCustomerList(string sortItem, Boolean desc)
{//SELECT * FROM CUSTOMER ORDER BY firstname DESC;
ourCustomerList customerlist = new ourCustomerList();
string storMode = (desc == true) ? "DESC" : "ASC";
var sqlgetcustomerlist = "SELECT * FROM CUSTOMER ORDER BY " + sortItem + " " + storMode;
SqlDataReader customerreader;
using (var cmd = new SqlCommand(sqlgetcustomerlist, conn))
{
customerreader = cmd.ExecuteReader();
if (customerreader.HasRows)
{
while (customerreader.Read())
{
DateTime dt = DateTime.Parse(customerreader[DOB].ToString());
ourCustomer acustomer = new ourCustomer(int.Parse(customerreader[CUSTOMERID].ToString()));
acustomer.FirstName = customerreader[FRISTNAME].ToString();
acustomer.LastName = customerreader[LASTNAME].ToString();
acustomer.Email = customerreader[EMAIL].ToString();
acustomer.Website = customerreader[WEBSITE].ToString();
acustomer.DOB = dt.Day + "-" + dt.Month + "-" + dt.Year;
acustomer.LoyaltyMember = (customerreader[LOYALTYMEMBER].ToString().CompareTo("True") == 0) ? "true" : "false";
acustomer.Phone = customerreader[PHONE].ToString();
acustomer.Mobile = customerreader[MOBILE].ToString();
acustomer.Fax = customerreader[FAX].ToString();
acustomer.MemberCard = customerreader[MEMBERCARD].ToString();
customerlist.Addcustomer(acustomer);
}
return(customerlist);
}
else
{
throw new ApplicationException("[Error] Cannot retrieve the data!");
}
}
}
// 1. add a new customer into list
public Boolean Addcustomer(ourCustomer acustomer)
{
ourcustomerlist.Add(acustomer);
return(true);
}
// Update customer card
public Boolean SaveCustomer(ourCustomer newinfo)
{
Boolean successFlag = false;
// Divide two different entry, first one for insert new one; second for update.
sqlAccess sqlconnforcustomerupdate = new sqlAccess();
// 1. FirstName check
if (this._firstName.CompareTo(newinfo._firstName) != 0)
{
this._firstName = newinfo._firstName;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.UFIRSTNAME, this._firstName);
}
// 2. LastName check
if (this._lastName.CompareTo(newinfo._lastName) != 0)
{
this._lastName = newinfo._lastName;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.ULASTNAME, this._lastName);
}
// 3. Email check
if (this._email.CompareTo(newinfo._email) != 0)
{
this._email = newinfo._email;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.UEMAIL, this._email);
}
// 4. Website check
if (this._website.CompareTo(newinfo._website) != 0)
{
this._website = newinfo._website;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.UWEBSITE, this._website);
}
// 5.DOB check
if (this._dOB.CompareTo(newinfo._dOB) != 0)
{
this._dOB = newinfo._dOB;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.UDOB, this._dOB);
}
// 6. Loyaltymember check
if (this._loyaltyMember.CompareTo(newinfo._loyaltyMember) != 0)
{
this._loyaltyMember = newinfo._loyaltyMember;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.ULOYALTYMEMBER, this._loyaltyMember);
}
// 7. phone check
if (this._phone.CompareTo(newinfo._phone) != 0)
{
this._phone = newinfo._phone;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.UPHONE, this._phone);
}
// 8. moblie check
if (this._mobile.CompareTo(newinfo._mobile) != 0)
{
this._mobile = newinfo._mobile;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.UMOBILE, this._mobile);
}
// 9. fax check
if (this._fax.CompareTo(newinfo._fax) != 0)
{
this._fax = newinfo._fax;
successFlag = sqlconnforcustomerupdate.SaveRecord(this._customerId, sqlAccess.UFAX, this._fax);
}
return(successFlag);
}
