/// <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>
/// Load a list of states from the database.
/// </summary>
/// <param name="connectionString">The connection string to the database.</param>
/// <param name="filter">The filter used to select states.</param>
/// <returns></returns>
public static List <State> LoadCollection(StateFilter filter)
{
List <State> states = new List <State>();
SqlCommand cmd = new SqlCommand();
StringBuilder select = new StringBuilder(1000);
if (!String.IsNullOrEmpty(filter.Code))
{
select.Append("SM.state_ansi_code LIKE '%' + @state_ansi_code + '%'");
cmd.Parameters.Add(new SqlParameter("@state_ansi_code", SqlDbType.NVarChar)
{
Value = filter.Code
});
}
if (!String.IsNullOrEmpty(filter.Name))
{
if (select.Length > 0)
{
select.Append(" OR ");
}
select.Append("SM.state_name LIKE '%' + @state_name + '%'");
cmd.Parameters.Add(new SqlParameter("@state_name", SqlDbType.NVarChar)
{
Value = filter.Name
});
}
if (!String.IsNullOrEmpty(filter.Capital))
{
if (select.Length > 0)
{
select.Append(" OR ");
}
select.Append("SM.state_capital LIKE '%' + @state_capital + '%'");
cmd.Parameters.Add(new SqlParameter("@state_capital", SqlDbType.NVarChar)
{
Value = filter.Capital
});
}
if (!String.IsNullOrEmpty(filter.LargestCity))
{
if (select.Length > 0)
{
select.Append(" OR ");
}
select.Append("SM.state_largest_city LIKE '%' + @state_largest_city + '%'");
cmd.Parameters.Add(new SqlParameter("@state_largest_city", SqlDbType.NVarChar)
{
Value = filter.LargestCity
});
}
if (!String.IsNullOrEmpty(filter.LargestMetro))
{
if (select.Length > 0)
{
select.Append(" OR ");
}
select.Append("SM.state_largest_metro LIKE '%' + @state_largest_metro + '%'");
cmd.Parameters.Add(new SqlParameter("@state_largest_metro", SqlDbType.NVarChar)
{
Value = filter.LargestMetro
});
}
select.Insert(0, String.Format("SELECT * FROM State_Master SM {0} ", cmd.Parameters.Count > 0 ? "WHERE" : String.Empty));
select.Append(" ORDER BY SM.state_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)
{
states.Add(new State(dr));
}
}
}
return(states);
}
/// <summary>
/// Find all changes matching the filters provided.
/// </summary>
/// <param name="filter">Find all changes matching the filters provided.</param>
/// <returns></returns>
/// <remarks></remarks>
public static List <ChangeLogItem> LoadCollection(ChangeLogItemFilter filter)
{
List <ChangeLogItem> results = new List <ChangeLogItem>();
SqlCommand cmd = new SqlCommand();
StringBuilder select = new StringBuilder(1000);
if (filter.ID != null)
{
select.Append("CLM.change_log_master_id=@change_log_master_id");
cmd.Parameters.Add(new SqlParameter("@change_log_master_id", SqlDbType.Int)
{
Value = filter.ID
});
}
if (filter.OwnerID != null)
{
if ((select.Length > 0))
{
select.Append(" AND ");
}
select.Append("CLM.owner_id=@owner_id");
cmd.Parameters.Add(new SqlParameter("@owner_id", SqlDbType.Int)
{
Value = filter.OwnerID
});
}
if (filter.OwnerName != null)
{
if ((select.Length > 0))
{
select.Append(" AND ");
}
select.Append("CLM.owner_name=@owner_name");
cmd.Parameters.Add(new SqlParameter("@owner_name", SqlDbType.NVarChar)
{
Value = filter.OwnerName
});
}
if (filter.Type != null)
{
if ((select.Length > 0))
{
select.Append(" AND ");
}
select.Append("CLM.log_type=@log_type");
cmd.Parameters.Add(new SqlParameter("@log_type", SqlDbType.NVarChar)
{
Value = filter.Type
});
}
if (filter.Timestamp != null)
{
if ((select.Length > 0))
{
select.Append(" AND ");
}
select.Append("CLM.timestamp=@timestamp");
cmd.Parameters.Add(new SqlParameter("@timestamp", SqlDbType.DateTime)
{
Value = filter.Timestamp
});
}
if (filter.PropertyName != null)
{
if ((select.Length > 0))
{
select.Append(" AND ");
}
select.Append("CLM.property_name=@property_name");
cmd.Parameters.Add(new SqlParameter("@property_name", SqlDbType.NVarChar)
{
Value = filter.PropertyName
});
}
select.Insert(0, "SELECT * FROM Change_Log_Master CLM {0}");
using (SqlConnection cn = new SqlConnection(HConfig.DBConnectionString))
{
using (DataTable dt = HDatabase.FillDataTable(cn, cmd))
{
foreach (DataRow dr in dt.Rows)
{
results.Add(new ChangeLogItem(dr));
}
}
}
return(results);
}
/// <summary>
/// Load a list of users from the database.
/// </summary>
/// <param name="connectionString">The connection string to the database.</param>
/// <param name="filter">The filter used to select users.</param>
/// <returns></returns>
public static List <User> LoadCollection(UserFilter filter)
{
List <User> users = new List <User>();
SqlCommand cmd = new SqlCommand();
StringBuilder select = new StringBuilder(1000);
if (filter.Guid != Guid.Empty)
{
select.Append("UM.user_master_guid=@user_master_guid");
cmd.Parameters.Add(new SqlParameter("@user_master_guid", SqlDbType.UniqueIdentifier)
{
Value = filter.Guid
});
}
if (!String.IsNullOrEmpty(filter.UserName))
{
if (select.Length > 0)
{
select.Append(" AND ");
}
select.Append("UM.user_name=@user_name");
cmd.Parameters.Add(new SqlParameter("@user_name", SqlDbType.NVarChar)
{
Value = filter.UserName
});
}
if (!String.IsNullOrEmpty(filter.UserPassword))
{
if (select.Length > 0)
{
select.Append(" AND ");
}
select.Append("UM.user_password=@user_password");
cmd.Parameters.Add(new SqlParameter("@user_password", SqlDbType.NVarChar)
{
Value = filter.UserPassword
});
}
if (!String.IsNullOrEmpty(filter.UserSalt))
{
if (select.Length > 0)
{
select.Append(" AND ");
}
select.Append("UM.user_salt=@user_salt");
cmd.Parameters.Add(new SqlParameter("@user_salt", SqlDbType.NVarChar)
{
Value = filter.UserSalt
});
}
select.Insert(0, String.Format("SELECT * FROM User_Master UM {0} ", cmd.Parameters.Count > 0 ? "WHERE" : String.Empty));
select.Append(" ORDER BY UM.user_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)
{
users.Add(new User(dr));
}
}
}
return(users);
}
/// <summary>
/// Load a list of blobs from the database.
/// </summary>
/// <param name="connectionString">The connection string to the database.</param>
/// <param name="filter">The filter used to select blobs.</param>
/// <returns></returns>
public static List <Blob> LoadCollection(BlobFilter filter)
{
List <Blob> blobs = new List <Blob>();
SqlCommand cmd = new SqlCommand();
StringBuilder select = new StringBuilder(1000);
if (filter.ID > 0)
{
select.Append("BM.binary_id=@binary_id");
cmd.Parameters.Add(new SqlParameter("@binary_id", SqlDbType.Int)
{
Value = filter.ID
});
}
if (!String.IsNullOrEmpty(filter.Name))
{
if (select.Length > 0)
{
select.Append(" AND ");
}
select.Append("BM.binary_name LIKE '%' + @binary_name + '%'");
cmd.Parameters.Add(new SqlParameter("@binary_name", SqlDbType.NVarChar)
{
Value = filter.Name
});
}
if (!String.IsNullOrEmpty(filter.MimeType))
{
if (select.Length > 0)
{
select.Append(" AND ");
}
select.Append("BM.binary_mime_type LIKE '%' + @binary_mime_type + '%'");
cmd.Parameters.Add(new SqlParameter("@binary_mime_type", SqlDbType.NVarChar)
{
Value = filter.MimeType
});
}
if (filter.SizeGreaterThan > 0)
{
if (select.Length > 0)
{
select.Append(" AND ");
}
select.Append("BM.binary_size >= @binary_size");
cmd.Parameters.Add(new SqlParameter("@binary_size", SqlDbType.Int)
{
Value = filter.SizeGreaterThan
});
}
if (filter.SizeLessThan > 0)
{
if (select.Length > 0)
{
select.Append(" AND ");
}
select.Append("BM.binary_size <= @binary_size");
cmd.Parameters.Add(new SqlParameter("@binary_size", SqlDbType.Int)
{
Value = filter.SizeLessThan
});
}
select.Insert(0, String.Format("SELECT * FROM Binary_Master BM {0} ", cmd.Parameters.Count > 0 ? "WHERE" : String.Empty));
select.Append(" ORDER BY BM.binary_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)
{
blobs.Add(new Blob(dr, filter.IncludeBinaryData));
}
}
}
return(blobs);
}
