/// <summary> /// Load a single employee. /// </summary> /// <param name="connectionString">The connection string to the database.</param> /// <param name="filter">The filter used to select employees.</param> /// <returns></returns> public static Employee Load(EmployeeFilter filter) { List <Employee> employees = Employee.LoadCollection(filter); return(employees.Count > 0 ? employees[0] : null); }
/// <summary> /// Load a list of employees from the database. /// </summary> /// <param name="connectionString">The connection string to the database.</param> /// <param name="filter">The filter used to select employees.</param> /// <returns></returns> public static List <Employee> LoadCollection(EmployeeFilter filter) { List <Employee> employees = new List <Employee>(); SqlCommand cmd = new SqlCommand(); StringBuilder select = new StringBuilder(1000); if (!String.IsNullOrEmpty(filter.Keyword)) { // Search all columns by keyword. select.Append("SELECT * FROM Employee_Master EM WHERE EM.employee_first_name LIKE '%' + @employee_first_name + '%'"); select.Append(" OR EM.employee_last_name LIKE '%' + @employee_last_name + '%'"); select.Append(" OR EM.employee_email LIKE '%' + @employee_email + '%'"); select.Append(" OR EM.employee_phone LIKE '%' + @employee_phone + '%'"); select.Append(" OR EM.employee_state LIKE '%' + @employee_state + '%'"); select.Append(" OR EM.employee_city LIKE '%' + @employee_city + '%'"); select.Append(" OR EM.employee_street LIKE '%' + @employee_street + '%'"); select.Append(" OR EM.employee_zip LIKE '%' + @employee_zip + '%'"); select.Append(" ORDER BY EM.employee_last_name, EM.employee_first_name"); cmd.Parameters.Add(new SqlParameter("@employee_first_name", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.Parameters.Add(new SqlParameter("@employee_last_name", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.Parameters.Add(new SqlParameter("@employee_email", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.Parameters.Add(new SqlParameter("@employee_phone", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.Parameters.Add(new SqlParameter("@employee_state", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.Parameters.Add(new SqlParameter("@employee_city", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.Parameters.Add(new SqlParameter("@employee_street", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.Parameters.Add(new SqlParameter("@employee_zip", SqlDbType.NVarChar) { Value = filter.Keyword }); cmd.CommandText = select.ToString(); } else { if (filter.Guid != Guid.Empty) { select.Append("EM.employee_master_guid=@employee_master_guid"); cmd.Parameters.Add(new SqlParameter("@employee_master_guid", SqlDbType.UniqueIdentifier) { Value = filter.Guid }); } if (!String.IsNullOrEmpty(filter.FirstName)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_first_name LIKE '%' + @employee_first_name + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_first_name", SqlDbType.NVarChar) { Value = filter.FirstName }); } if (!String.IsNullOrEmpty(filter.LastName)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_last_name LIKE '%' + @employee_last_name + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_last_name", SqlDbType.NVarChar) { Value = filter.LastName }); } if (!String.IsNullOrEmpty(filter.Email)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_email LIKE '%' + @employee_email + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_email", SqlDbType.NVarChar) { Value = filter.Email }); } if (!String.IsNullOrEmpty(filter.Phone)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_phone LIKE '%' + @employee_phone + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_phone", SqlDbType.NVarChar) { Value = filter.Phone }); } if (!String.IsNullOrEmpty(filter.State)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_state LIKE '%' + @employee_state + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_state", SqlDbType.NVarChar) { Value = filter.State }); } if (!String.IsNullOrEmpty(filter.City)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_city LIKE '%' + @employee_city + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_city", SqlDbType.NVarChar) { Value = filter.City }); } if (!String.IsNullOrEmpty(filter.Street)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_street LIKE '%' + @employee_street + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_street", SqlDbType.NVarChar) { Value = filter.Street }); } if (!String.IsNullOrEmpty(filter.Zip)) { if (select.Length > 0) { select.Append(" AND "); } select.Append("EM.employee_zip LIKE '%' + @employee_zip + '%'"); cmd.Parameters.Add(new SqlParameter("@employee_zip", SqlDbType.NVarChar) { Value = filter.Zip }); } select.Insert(0, String.Format("SELECT * FROM Employee_Master EM {0} ", cmd.Parameters.Count > 0 ? "WHERE" : String.Empty)); select.Append(" ORDER BY EM.employee_last_name, EM.employee_first_name"); cmd.CommandText = select.ToString(); } using (SqlConnection cn = new SqlConnection(HConfig.DBConnectionString)) { using (DataTable dt = HDatabase.FillDataTable(cn, cmd)) { foreach (DataRow dr in dt.Rows) { employees.Add(new Employee(dr)); } } } return(employees); }
/// <summary> /// Clear filter. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnClear_Click(object sender, EventArgs e) { this.CurrentFilter = new EmployeeFilter(); }