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