private void searchBtn_Click(object sender, RoutedEventArgs e) { CustomerInfo custinfo = new CustomerInfo(); custinfo.name = searchTxtBlck.Text; List<CustomerInfo> customers = ESCMSStorage.DbInteraction.searchCustomerList(custinfo); foreach (CustomerInfo customer in customers) { _customerCollection.Add(customer); } }
private static List<CustomerInfo> QueryAllCustomerList() { List<CustomerInfo> CustomerList = new List<CustomerInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From customer group by name;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { CustomerInfo Customer = new CustomerInfo(); Customer.id = msqlReader.GetString("id"); Customer.name = msqlReader.GetString("name"); Customer.address = msqlReader.GetString("address"); Customer.contact = msqlReader.GetString("contact"); CustomerList.Add(Customer); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return CustomerList; }
private static int DoRegisterNewCustomerInDb(CustomerInfo customerDetails) { int returnVal = 0; MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "INSERT INTO customer(id,name,address,contact) " + "VALUES(@id,@name,@address,@contact)"; msqlCommand.Parameters.AddWithValue("@id", customerDetails.id); msqlCommand.Parameters.AddWithValue("@name", customerDetails.name); msqlCommand.Parameters.AddWithValue("@address", customerDetails.address); msqlCommand.Parameters.AddWithValue("@contact", customerDetails.contact); msqlCommand.ExecuteNonQuery(); returnVal = 1; } catch (Exception er) { returnVal = 0; } finally { //always close the connection msqlConnection.Close(); } return returnVal; }
public static List<CustomerInfo> searchCustomerList(CustomerInfo custinfo) { return searchAllCustomerList(custinfo); }
public static CustomerInfo GetCustomerInfo(string cusId) { CustomerInfo customer = new CustomerInfo(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From customer WHERE id=@id;"; msqlCommand.Parameters.AddWithValue("@id", cusId); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); msqlReader.Read(); customer.name = msqlReader.GetString("name"); customer.address = msqlReader.GetString("address"); customer.contact = msqlReader.GetString("contact"); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return customer; }
public static void EditCustomer(CustomerInfo employeeToEdit) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "UPDATE customer SET name=@name,address=@address,contact=@contact WHERE id=@id"; msqlCommand.Parameters.AddWithValue("@name", employeeToEdit.name); msqlCommand.Parameters.AddWithValue("@address", employeeToEdit.address); msqlCommand.Parameters.AddWithValue("@contact", employeeToEdit.contact); msqlCommand.Parameters.AddWithValue("@id", employeeToEdit.id); msqlCommand.ExecuteNonQuery(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
public static int DoRegisterNewCustomer(CustomerInfo customerDetails) { return DoRegisterNewCustomerInDb(customerDetails); }
private static List<CustomerInfo> searchAllCustomerList(CustomerInfo custinfo) { List<CustomerInfo> CustomerList = new List<CustomerInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From customer where name = @input or address = @input or contact = @input or id = @input ; "; msqlCommand.Parameters.AddWithValue("@input", custinfo.name); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { CustomerInfo Customer = new CustomerInfo(); Customer.id = msqlReader.GetString("id"); Customer.name = msqlReader.GetString("name"); Customer.address = msqlReader.GetString("address"); Customer.contact = msqlReader.GetString("contact"); CustomerList.Add(Customer); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return CustomerList; }