/// <summary> /// Get Specific person /// </summary> /// <param name="personId"></param> /// <param name="connection"></param> /// <returns></returns> public static Person GetPersonByPersonId(Guid personId, SqlConnection connection) { List<Person> list = new List<Person>(); try { SqlDataReader rdr = null; connection.Open(); // 1. create a command object identifying // the stored procedure SqlCommand cmd = new SqlCommand( "JOB_GetPersonByPersonID", connection); // 2. set the command object so it knows // to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; // 3. add parameter to command, which //// will be passed to the stored procedure SqlParameter parameter = new SqlParameter(); parameter.SqlDbType = SqlDbType.UniqueIdentifier; parameter.ParameterName = "@PersonID"; parameter.Value = personId; cmd.Parameters.Add(parameter); // execute the command rdr = cmd.ExecuteReader(); while (rdr.Read()) { Person person = new Person ( new Guid(rdr.GetValue(0).ToString()), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString(), true, Convert.ToBoolean(rdr.GetValue(5).ToString()) ); list.Add(person); } } finally { if (connection != null) { connection.Close(); } } return list[0]; }
/// <summary> /// Update Current Person with new values /// </summary> /// <param name="person"></param> /// <param name="connection"></param> public static void UpdatePerson(Person person, SqlConnection connection) { try { connection.Open(); // 1. create a command object identifying // the stored procedure SqlCommand cmd = new SqlCommand( "JOB_UpdatePerson", connection); // 2. set the command object so it knows // to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; // 3. add parameter to command, which // will be passed to the stored procedure SetParametersForPerson(cmd, person); // execute the command cmd.ExecuteReader(); } finally { if (connection != null) { connection.Close(); } } }
/// <summary> /// Get Personal that is busy for a specific date /// </summary> /// <param name="dateTime"></param> /// <param name="connection"></param> /// <returns></returns> public static List<Person> GetBusyPersonalListForDate(DateTime dateTime, SqlConnection connection) { var list = new List<Person>(); try { SqlDataReader rdr = null; connection.Open(); // 1. create a command object identifying // the stored procedure SqlCommand cmd = new SqlCommand( "JOB_JobBusyPersonalByDate", connection); // 2. set the command object so it knows // to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; // 3. add parameter to command, which // will be passed to the stored procedure SqlParameter parameter = new SqlParameter(); parameter.SqlDbType = SqlDbType.Date; parameter.ParameterName = "@JobStartDate"; parameter.Value = dateTime.ToShortDateString(); cmd.Parameters.Add(parameter); // execute the command rdr = cmd.ExecuteReader(); while (rdr.Read()) { Person person = new Person ( new Guid(rdr.GetValue(0).ToString()), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString(), Convert.ToBoolean(rdr.GetValue(5).ToString()), true ); // Add To list list.Add(person); } } finally { if (connection != null) { connection.Close(); } } return list; }
private static void SetParametersForPerson(SqlCommand cmd, Person person) { cmd.Parameters.Add(new SqlParameter("@PersonID", person.PersonId)); cmd.Parameters.Add(new SqlParameter("@FirstName", person.Firstname)); cmd.Parameters.Add(new SqlParameter("@LastName", person.Lastname)); cmd.Parameters.Add(new SqlParameter("@Mobile", person.Mobile)); cmd.Parameters.Add(new SqlParameter("@Email", person.Email)); cmd.Parameters.Add(new SqlParameter("@Active", person.Active)); }
public static void SetPersonInactive(Person person) { person.Active = false; DataLayer.UpdatePerson(person, sqlConnection); }
public static Person AddPerson(MamutV2.MamutEmployee personMamut) { var person = GetPersonByName(personMamut.FirstName.Trim(), personMamut.LastName.Trim()); if (person != null) { Person newperson = new Person(person.PersonId, personMamut.FirstName.Trim(), personMamut.LastName.Trim(), personMamut.Phone.Trim(), string.Empty, true, person.Active); DataLayer.UpdatePerson(newperson, sqlConnection); return newperson; } else { Person newperson = new Person(Guid.NewGuid(), personMamut.FirstName.Trim(), personMamut.LastName.Trim(), personMamut.Phone.Trim(), string.Empty, true, true); DataLayer.CreatePerson(newperson, sqlConnection); return newperson; } }
public static Person AddPerson(PersonMamut personMamut) { var person = GetPersonByName(personMamut.FirstName, personMamut.LastName); if (person != null) { Person newperson = new Person(person.PersonId, personMamut.FirstName, personMamut.LastName, personMamut.Mobile, personMamut.Email, true, person.Active); DataLayer.UpdatePerson(newperson, sqlConnection); return newperson; } else { Person newperson = new Person(Guid.NewGuid(), personMamut.FirstName, personMamut.LastName, personMamut.Mobile, personMamut.Email, true, true); DataLayer.CreatePerson(newperson, sqlConnection); return newperson; } }