// View all employees deails
public IEnumerable <Chemical> ChemicalViewAll()
{
List <Chemical> lstchemical = new List <Chemical>();
using (MySqlConnection sqlCon = new MySqlConnection(connectionString))
{
MySqlCommand sqlCmd = new MySqlCommand("ChemicalViewAll", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
MySqlDataReader rdr = sqlCmd.ExecuteReader();
while (rdr.Read())
{
Chemical chemical = new Chemical();
chemical.Chemical_ID = Convert.ToInt32(rdr["Chemical_ID"]);
chemical.Chemical_Name = rdr["Chemical_Name"].ToString();
chemical.Molecular_Weight = Convert.ToDecimal(rdr["Molecular_Weight"]);
chemical.Product_Number = rdr["Product_Number"].ToString();
chemical.Vendor = rdr["Vendor"].ToString();
lstchemical.Add(chemical);
}
sqlCon.Close();
}
return(lstchemical);
}
public IEnumerable <Chemical> ChemicalSearchByValue(string searchString)
{
List <Chemical> filteredChemicals = new List <Chemical>();
using (MySqlConnection sqlCon = new MySqlConnection(connectionString))
{
string mySqlQuery = "SELECT * FROM Chemical WHERE Chemical_Name LIKE CONCAT('%', '" + searchString + "' , '%') || Product_Number LIKE CONCAT('%', '" + searchString + "' , '%')";
MySqlCommand sqlCmd = new MySqlCommand(mySqlQuery, sqlCon);
sqlCon.Open();
MySqlDataReader rdr = sqlCmd.ExecuteReader();
while (rdr.Read())
{
Chemical chemical = new Chemical();
chemical.Chemical_ID = Convert.ToInt32(rdr["Chemical_ID"]);
chemical.Chemical_Name = rdr["Chemical_Name"].ToString();
chemical.Molecular_Weight = Convert.ToDecimal(rdr["Molecular_Weight"]);
chemical.Product_Number = rdr["Product_Number"].ToString();
chemical.Vendor = rdr["Vendor"].ToString();
filteredChemicals.Add(chemical);
}
sqlCon.Close();
}
return(filteredChemicals);
}
//Add chemical record
public void ChemicalAddOrEdit(Chemical chemical)
{
using (MySqlConnection sqlCon = new MySqlConnection(connectionString))
{
MySqlCommand sqlCmd = new MySqlCommand("ChemicalAddOrEdit", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("_Chemical_ID", chemical.Chemical_ID);
sqlCmd.Parameters.AddWithValue("_Chemical_Name", chemical.Chemical_Name);
sqlCmd.Parameters.AddWithValue("_Molecular_Weight", chemical.Molecular_Weight);
sqlCmd.Parameters.AddWithValue("_Product_Number", chemical.Product_Number);
sqlCmd.Parameters.AddWithValue("_Vendor", chemical.Vendor);
sqlCon.Open();
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
}
}
//View specific chemical information
public Chemical ViewChemicalByID(int?id)
{
Chemical chemical = new Chemical();
using (MySqlConnection sqlCon = new MySqlConnection(connectionString))
{
string mySqlQuery = "SELECT * FROM Chemical WHERE Chemical_ID= " + id;
MySqlCommand sqlCmd = new MySqlCommand(mySqlQuery, sqlCon);
sqlCon.Open();
MySqlDataReader rdr = sqlCmd.ExecuteReader();
while (rdr.Read())
{
chemical.Chemical_ID = Convert.ToInt32(rdr["Chemical_ID"]);
chemical.Chemical_Name = rdr["Chemical_Name"].ToString();
chemical.Molecular_Weight = Convert.ToDecimal(rdr["Molecular_Weight"]);
chemical.Product_Number = rdr["Product_Number"].ToString();
chemical.Vendor = rdr["Vendor"].ToString();
}
}
return(chemical);
}