private static List<ContractorInfo> QueryAllContractorList() { List<ContractorInfo> ContractorList = new List<ContractorInfo>(); 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 contractor group by name;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ContractorInfo Contractor = new ContractorInfo(); Contractor.id = msqlReader.GetString("id"); Contractor.name = msqlReader.GetString("name"); Contractor.address = msqlReader.GetString("address"); Contractor.contact = msqlReader.GetString("contact"); Contractor.details = msqlReader.GetString("contract_details"); ContractorList.Add(Contractor); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ContractorList; }
private void searchBtn_Click(object sender, RoutedEventArgs e) { ContractorInfo conInfoObj = new ContractorInfo(); conInfoObj.name = searchTxtBlck.Text; List<ContractorInfo> contractors = ESCMSStorage.DbInteraction.SearchContractorList(conInfoObj); _contractorCollection.Clear(); foreach (ContractorInfo contractor in contractors) { _contractorCollection.Add(contractor); } }
private static int DoRegisterNewContractorInDb(ContractorInfo contractorDetails) { 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 contractor(id,name,address,contact,contract_details) " + "VALUES(@id,@name,@address,@contact,@contract_details)"; msqlCommand.Parameters.AddWithValue("@id", contractorDetails.id); msqlCommand.Parameters.AddWithValue("@name", contractorDetails.name); msqlCommand.Parameters.AddWithValue("@address", contractorDetails.address); msqlCommand.Parameters.AddWithValue("@contact", contractorDetails.contact); msqlCommand.Parameters.AddWithValue("@contract_details", contractorDetails.details); msqlCommand.ExecuteNonQuery(); returnVal = 1; } catch (Exception er) { returnVal = 0; } finally { //always close the connection msqlConnection.Close(); } return returnVal; }
public static List<ContractorInfo> SearchContractorList(ContractorInfo conInfoObj) { return searchAllContractorList(conInfoObj); }
public static void EditContractor(ContractorInfo contractorToEdit) { 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 contractor SET name=@name,address=@address,contact=@contact,contract_details=@details WHERE id=@id"; msqlCommand.Parameters.AddWithValue("@name", contractorToEdit.name); msqlCommand.Parameters.AddWithValue("@address", contractorToEdit.address); msqlCommand.Parameters.AddWithValue("@contact", contractorToEdit.contact); msqlCommand.Parameters.AddWithValue("@details", contractorToEdit.details); msqlCommand.Parameters.AddWithValue("@id", contractorToEdit.id); msqlCommand.ExecuteNonQuery(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
public static int DoRegisterNewContractor(ContractorInfo contractorDetails) { return DoRegisterNewContractorInDb(contractorDetails); }
private static List<ContractorInfo> searchAllContractorList(ContractorInfo coninfoObj) { List<ContractorInfo> ContractorList = new List<ContractorInfo>(); 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 contractor where name = @input or address = @input or contact = @input or contract_details = @input;"; msqlCommand.Parameters.AddWithValue("@input", coninfoObj.name); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ContractorInfo Contractor = new ContractorInfo(); Contractor.id = msqlReader.GetString("id"); Contractor.name = msqlReader.GetString("name"); Contractor.address = msqlReader.GetString("address"); Contractor.contact = msqlReader.GetString("contact"); Contractor.details = msqlReader.GetString("contract_details"); ContractorList.Add(Contractor); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ContractorList; }