private static int DoRegisterNewEmployeeInDb(EmployeeInfo employeeDetails)
{
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 employee(id,name,address,contact,post_type,doj,department) "
+ "VALUES(@id,@name,@address,@contact,@post_type,@doj,@department)";
msqlCommand.Parameters.AddWithValue("@id", employeeDetails.id);
msqlCommand.Parameters.AddWithValue("@name", employeeDetails.name);
msqlCommand.Parameters.AddWithValue("@address", employeeDetails.address);
msqlCommand.Parameters.AddWithValue("@contact", employeeDetails.contact);
msqlCommand.Parameters.AddWithValue("@post_type", employeeDetails.postType);
msqlCommand.Parameters.AddWithValue("@doj", employeeDetails.doj);
msqlCommand.Parameters.AddWithValue("@department", employeeDetails.department);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public static List<EmployeeInfo> SearchAllEmployeeList(EmployeeInfo empInfoObj)
{
return SearchAllEmployeeListDetails(empInfoObj);
}
public static List<EmployeeInfo> GetAllEstimatorList()
{
List<EmployeeInfo> EmployeeList = new List<EmployeeInfo>();
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 employee WHERE post_type=@post;";
msqlCommand.Parameters.AddWithValue("@post", "Estimator");
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
EmployeeInfo Employee = new EmployeeInfo();
Employee.id = msqlReader.GetString("id");
Employee.name = msqlReader.GetString("name");
Employee.address = msqlReader.GetString("address");
Employee.contact = msqlReader.GetString("contact");
Employee.postType = (PostType)Enum.Parse(typeof(PostType), msqlReader.GetString("post_type"), true);
Employee.doj = msqlReader.GetDateTime("doj");
Employee.department = msqlReader.GetString("department");
EmployeeList.Add(Employee);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return EmployeeList;
}
public static void EditEmployee(EmployeeInfo 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 employee SET name=@name,address=@address,contact=@contact,post_type=@postType,doj=@doj WHERE id=@id";
msqlCommand.Parameters.AddWithValue("@name", employeeToEdit.name);
msqlCommand.Parameters.AddWithValue("@address", employeeToEdit.address);
msqlCommand.Parameters.AddWithValue("@contact", employeeToEdit.contact);
msqlCommand.Parameters.AddWithValue("@postType", employeeToEdit.postType);
msqlCommand.Parameters.AddWithValue("@doj", employeeToEdit.doj);
msqlCommand.Parameters.AddWithValue("@department", employeeToEdit.department);
msqlCommand.Parameters.AddWithValue("@id", employeeToEdit.id);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public static int DoRegisterNewEmployee(EmployeeInfo employeeDetails)
{
return DoRegisterNewEmployeeInDb(employeeDetails);
}
private void searchBtn_Click(object sender, RoutedEventArgs e)
{
EmployeeInfo empInfoObj = new EmployeeInfo();
empInfoObj.name = searchTxtBlck.Text;
List<EmployeeInfo> employees = ESCMSStorage.DbInteraction.SearchAllEmployeeList(empInfoObj);
_employeeCollection.Clear();
foreach (EmployeeInfo employee in employees)
{
_employeeCollection.Add(employee);
}
}