/// <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();
}
