public bool SaveStaff(Domain.OfficeStaff.OfficeStaff staff) { if (staff.ID.HasValue) { return(update(staff)); } else { return(saveNew(staff)); } }
public Domain.OfficeStaff.OfficeStaff GetOfficeStaff(int id) { using (SqlConnection conn = new SqlConnection(this.connectionString)) using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "SELECT ID, StaffActive, StaffFirstName, StaffLastName, StaffPrimaryPhone, StaffPrimaryEmail, StaffHireDate, StaffTerminatedDate " + "FROM dbo.Staff " + "WHERE ID = @ID;"; cmd.Parameters.AddWithValue("@ID", id); try { DataTable table = cmd.GetTable(); if (table.Rows.Count != 1) { throw new DataException("Item not found"); } DataRow r = table.Rows[0]; Domain.OfficeStaff.OfficeStaff staff = new Domain.OfficeStaff.OfficeStaff(); staff.ID = r.ToInt("ID"); staff.Active = r.ToBool("StaffActive"); staff.FirstName = r.ToStringValue("StaffFirstName"); staff.LastName = r.ToStringValue("StaffLastName"); staff.Phone = r.ToStringValue("StaffPrimaryPhone"); staff.Email = r.ToStringValue("StaffPrimaryEmail"); staff.HireDate = r.ToDateTimeOrNull("StaffHireDate"); staff.TerminationDate = r.ToDateTimeOrNull("StaffTerminatedDate"); return(staff); } catch (Exception e) { Exceptions.Handle(e); throw new DataException(e.Message, e); } } }
public List <Domain.OfficeStaff.OfficeStaff> GetOfficeStaffUsers() { var optionList = Enum.GetValues(typeof(Options)).Cast <Options>().ToArray(); using (SqlConnection conn = new SqlConnection(this.connectionString)) using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "select Max(StaffId) as StaffId, Max(Username) as Username, Email, FirstName, LastName from ( " + "select Id as StaffId, '' as UserName, StaffPrimaryEmail as Email, StaffFirstName as FirstName, StaffLastName as LastName from staff " + "union " + "select StaffId, UserName, WebUserEmail as Email, WebUserFirstName as FirstName, WebUserLastName as LastName from webusers " + ") s " + "group by Email, FirstName, LastName " + "order by StaffId, UserName"; List <Domain.OfficeStaff.OfficeStaff> r = new List <Domain.OfficeStaff.OfficeStaff>(); try { var dt = cmd.GetTable(); foreach (DataRow dr in dt.Rows) { var m = new Domain.OfficeStaff.OfficeStaff { ID = dr.ToIntOrNull("StaffId"), UserName = dr.ToStringValue("UserName"), FirstName = dr.ToStringValue("FirstName"), LastName = dr.ToStringValue("LastName"), Email = dr.ToStringValue("Email") }; r.Add(m); } return(r); } catch (Exception e) { Exceptions.Handle(e); throw new DataException(e.Message, e); } } }
private bool saveNew(Domain.OfficeStaff.OfficeStaff staff) { using (SqlConnection conn = new SqlConnection(this.connectionString)) using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO dbo.Staff (" + "StaffActive, StaffFirstName, StaffLastName, StaffPrimaryPhone, " + "StaffPrimaryEmail, StaffHireDate, StaffTerminatedDate" + ") VALUES (" + "@Active, @FirstName, @LastName, @Phone, @Email, @HireDate, @TerminationDate);"; cmd.Parameters.AddWithValue("@Active", staff.Active); cmd.Parameters.AddWithValue("@FirstName", staff.FirstName); cmd.Parameters.AddWithValue("@LastName", staff.LastName); cmd.Parameters.AddWithNullableValue("@Phone", staff.Phone); cmd.Parameters.AddWithNullableValue("@Email", staff.Email); cmd.Parameters.AddWithNullableValue("@HireDate", staff.HireDate); cmd.Parameters.AddWithNullableValue("@TerminationDate", staff.TerminationDate); try { int?id = cmd.InsertToIdentityOrNull(); if (id.HasValue) { staff.ID = id; return(true); } else { return(false); } } catch (Exception e) { Exceptions.Handle(e); throw new DataException(e.Message, e); } } }
private bool update(Domain.OfficeStaff.OfficeStaff staff) { using (SqlConnection conn = new SqlConnection(this.connectionString)) using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "UPDATE dbo.Staff SET " + "StaffActive = @Active, StaffFirstName = @FirstName, StaffLastName = @LastName, " + "StaffPrimaryPhone = @Phone, StaffPrimaryEmail = @Email, StaffHireDate = @HireDate, StaffTerminatedDate = @TerminationDate " + "WHERE ID = @ID;"; cmd.Parameters.AddWithValue("@Active", staff.Active); cmd.Parameters.AddWithValue("@FirstName", staff.FirstName); cmd.Parameters.AddWithValue("@LastName", staff.LastName); cmd.Parameters.AddWithNullableValue("@Phone", staff.Phone); cmd.Parameters.AddWithNullableValue("@Email", staff.Email); cmd.Parameters.AddWithNullableValue("@HireDate", staff.HireDate); cmd.Parameters.AddWithNullableValue("@TerminationDate", staff.TerminationDate); cmd.Parameters.AddWithValue("@ID", staff.ID.Value); try { int recsAffected = cmd.ExecuteNonQueryToInt(); if (recsAffected == 0) { return(false); } else { return(true); } } catch (Exception e) { Exceptions.Handle(e); throw new DataException(e.Message, e); } } }
public List <Domain.OfficeStaff.OfficeStaff> GetOfficeStaffList() { using (SqlConnection conn = new SqlConnection(this.connectionString)) using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "SELECT ID, DateCreated, StaffActive, StaffFirstName, StaffLastName, " + "StaffPrimaryPhone, StaffPrimaryEmail, StaffHireDate, StaffTerminatedDate " + "FROM dbo.Staff;"; try { DataTable table = cmd.GetTable(); List <Domain.OfficeStaff.OfficeStaff> staffs = new List <Domain.OfficeStaff.OfficeStaff>(); foreach (DataRow r in table.Rows) { Domain.OfficeStaff.OfficeStaff staff = new Domain.OfficeStaff.OfficeStaff(); staff.ID = r.ToInt("ID"); staff.DateCreated = r.ToDateTime("DateCreated"); staff.Active = r.ToBool("StaffActive"); staff.FirstName = r.ToStringValue("StaffFirstName"); staff.LastName = r.ToStringValue("StaffLastName"); staff.Phone = r.ToStringValue("StaffPrimaryPhone"); staff.Email = r.ToStringValue("StaffPrimaryEmail"); staff.HireDate = r.ToDateTimeOrNull("StaffHireDate"); staff.TerminationDate = r.ToDateTimeOrNull("StaffTerminatedDate"); staffs.Add(staff); } return(staffs); } catch (Exception e) { Exceptions.Handle(e); throw new DataException(e.Message, e); } } }
//CTORs public OfficeStaffViewModel() { ViewHelper = new WebViewHelper(this); Detail = new Domain.OfficeStaff.OfficeStaff(); }