/// <summary> /// Ariel Sigo /// Created 2016/11/03 /// </summary> /// <param name="status"></param> /// <returns>List of members if successful</returns> public static List <Member> RetrieveMembers(bool status = true) { var members = new List <Member>(); // get a connection var conn = DBConnection.GetConnection(); //command text var cmdText = @"SELECT MemberID, FirstName, LastName, PhoneNumber, Status, Birthday, StartDate, " + @"MembershipTypeID, MemberPoleLevel, MemberAcroLevel, MemberSilksLevel, MemberLyraLevel " + @"FROM Member " + @"WHERE Status = @status "; // create a command object var cmd = new SqlCommand(cmdText, conn); // add parameters int activeBit = status ? 1 : 0; cmd.Parameters.Add("@status", SqlDbType.Bit); cmd.Parameters["@status"].Value = activeBit; try { //open connection conn.Open(); // data reader var reader = cmd.ExecuteReader(); // check if reader has data if (reader.HasRows) { while (reader.Read()) { var mem = new Member() { MemberID = reader.GetString(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), PhoneNumber = reader.GetString(3), Status = reader.GetBoolean(4), Birthday = reader.GetDateTime(5), StartDate = reader.GetDateTime(6), MembershipTypeID = reader.GetString(7), MemberPoleLevel = reader.GetString(8), MemberAcroLevel = reader.GetString(9), MemberSilksLevel = reader.GetString(10), MemberLyraLevel = reader.GetString(11) }; // save the member before leaving the loop members.Add(mem); } reader.Close(); } } catch (Exception ex) { throw new ApplicationException("There was a problem retreiveing your Member Data.", ex); } finally { //close connection conn.Close(); } return(members); }
/// <summary> /// Ariel Sigo /// Created 2016/11/20 /// </summary> /// <param name="oldMemberID"></param> /// <param name="newMemberID"></param> /// <param name="oldFirstName"></param> /// <param name="newFirstName"></param> /// <param name="oldLastName"></param> /// <param name="newLastName"></param> /// <param name="oldPhoneNumber"></param> /// <param name="newPhoneNumber"></param> /// <param name="oldStatus"></param> /// <param name="newStatus"></param> /// <param name="oldBirthday"></param> /// <param name="newBirthday"></param> /// <param name="oldStartDate"></param> /// <param name="newStartDate"></param> /// <param name="oldMembershipTypeID"></param> /// <param name="newMembershipTypeID"></param> /// <param name="oldMemberPoleLevel"></param> /// <param name="newMemberPoleLevel"></param> /// <param name="oldMemberAcroLevel"></param> /// <param name="newMemberAcroLevel"></param> /// <param name="oldMemberSilksLevel"></param> /// <param name="newMemberSilksLevel"></param> /// <param name="oldMemberLyraLevel"></param> /// <param name="newMemberLyraLevel"></param> /// <returns>count of member (rows) affected if successful</returns> public static int UpdateMember(string oldMemberID, string newMemberID, string oldFirstName, string newFirstName, string oldLastName, string newLastName, string oldPhoneNumber, string newPhoneNumber, bool oldStatus, bool newStatus, DateTime oldBirthday, DateTime newBirthday, DateTime oldStartDate, DateTime newStartDate, string oldMembershipTypeID, string newMembershipTypeID, string oldMemberPoleLevel, string newMemberPoleLevel, string oldMemberAcroLevel, string newMemberAcroLevel, string oldMemberSilksLevel, string newMemberSilksLevel, string oldMemberLyraLevel, string newMemberLyraLevel) { var count = 0; var conn = DBConnection.GetConnection(); var cmdText = @"sp_update_member"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@MemberID", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldMemberID", SqlDbType.VarChar, 100); cmd.Parameters.Add("@NewMemberID", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldFirstName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@NewFirstName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldLastName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@NewLastName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldPhoneNumber", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewPhoneNumber", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldStatus", SqlDbType.Bit); cmd.Parameters.Add("@NewStatus", SqlDbType.Bit); cmd.Parameters.Add("@OldBirthday", SqlDbType.Date); cmd.Parameters.Add("@NewBirthday", SqlDbType.Date); cmd.Parameters.Add("@OldStartDate", SqlDbType.Date); cmd.Parameters.Add("@NewStartDate", SqlDbType.Date); cmd.Parameters.Add("@OldMembershipTypeID", SqlDbType.VarChar, 100); cmd.Parameters.Add("@NewMembershipTypeID", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldMemberPoleLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewMemberPoleLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldMemberAcroLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewMemberAcroLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldMemberSilksLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewMemberSilksLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldMemberLyraLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewMemberLyraLevel", SqlDbType.VarChar, 15); cmd.Parameters["@MemberID"].Value = oldMemberID; cmd.Parameters["@OldMemberID"].Value = oldMemberID; cmd.Parameters["@NewMemberID"].Value = newMemberID; cmd.Parameters["@OldFirstName"].Value = oldFirstName; cmd.Parameters["@NewFirstName"].Value = newFirstName; cmd.Parameters["@OldLastName"].Value = oldLastName; cmd.Parameters["@NewLastName"].Value = newLastName; cmd.Parameters["@OldPhoneNumber"].Value = oldPhoneNumber; cmd.Parameters["@NewPhoneNumber"].Value = newPhoneNumber; cmd.Parameters["@OldStatus"].Value = oldStatus; cmd.Parameters["@NewStatus"].Value = newStatus; cmd.Parameters["@OldBirthday"].Value = oldBirthday; cmd.Parameters["@NewBirthday"].Value = newBirthday; cmd.Parameters["@OldStartDate"].Value = oldStartDate; cmd.Parameters["@NewStartDate"].Value = newStartDate; cmd.Parameters["@OldMembershipTypeID"].Value = oldMembershipTypeID; cmd.Parameters["@NewMembershipTypeID"].Value = newMembershipTypeID; cmd.Parameters["@OldMemberPoleLevel"].Value = oldMemberPoleLevel; cmd.Parameters["@NewMemberPoleLevel"].Value = newMemberPoleLevel; cmd.Parameters["@OldMemberAcroLevel"].Value = oldMemberAcroLevel; cmd.Parameters["@NewMemberAcroLevel"].Value = newMemberAcroLevel; cmd.Parameters["@OldMemberSilksLevel"].Value = oldMemberSilksLevel; cmd.Parameters["@NewMemberSilksLevel"].Value = newMemberSilksLevel; cmd.Parameters["@OldMemberLyraLevel"].Value = oldMemberLyraLevel; cmd.Parameters["@NewMemberLyraLevel"].Value = newMemberLyraLevel; try { conn.Open(); count = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(count); }