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