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