public static int RetrieveMemberCount(bool status = true) { var count = 0; // connection var conn = DBConnection.GetConnection(); // variable for clause var activeState = status ? "1" : "0"; // command text var cmdText = @"SELECT COUNT(MemberID) " + @"FROM Member " + @"WHERE Status = " + activeState; // command object var cmd = new SqlCommand(cmdText, conn); try { //open connection conn.Open(); // exectute command count = (int)cmd.ExecuteScalar(); } catch (Exception ex) { throw new ApplicationException("There was a problem accessing the member count", ex); } finally { //close the connection conn.Close(); } return(count); }
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("@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["@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["@newStaus"].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); }
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(3), PhoneNumber = reader.GetString(4), Status = reader.GetBoolean(5), Birthday = reader.GetDateTime(6), StartDate = reader.GetDateTime(7), MembershipTypeID = reader.GetString(8), MemberPoleLevel = reader.GetString(9), MemberAcroLevel = reader.GetString(10), MemberSilksLevel = reader.GetString(11), MemberLyraLevel = reader.GetString(12) }; // 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/07 /// </summary> /// <param name="OldTrainerID"></param> /// <param name="NewTrainerID"></param> /// <param name="OldTrainerFirstName"></param> /// <param name="NewTrainerFirstName"></param> /// <param name="OldTrainerLastName"></param> /// <param name="NewTrainerLastName"></param> /// <param name="OldTrainerPhoneNumber"></param> /// <param name="NewTrainerPhoneNumber"></param> /// <param name="OldTrainerStatus"></param> /// <param name="NewTrainerStatus"></param> /// <param name="OldTrainerPoleLevel"></param> /// <param name="NewTrainerPoleLevel"></param> /// <param name="OldTrainerAcroLevel"></param> /// <param name="NewTrainerAcroLevel"></param> /// <param name="OldTrainerSilksLevel"></param> /// <param name="NewTrainerSilksLevel"></param> /// <param name="OldTrainerLyraLevel"></param> /// <param name="NewTrainerLyraLevel"></param> /// <returns>Count of rows affected and trainers updated if successful</returns> public static int UpdateTrainer(string OldTrainerID, string NewTrainerID, string OldTrainerFirstName, string NewTrainerFirstName, string OldTrainerLastName, string NewTrainerLastName, string OldTrainerPhoneNumber, string NewTrainerPhoneNumber, bool OldTrainerStatus, bool NewTrainerStatus, string OldTrainerPoleLevel, string NewTrainerPoleLevel, string OldTrainerAcroLevel, string NewTrainerAcroLevel, string OldTrainerSilksLevel, string NewTrainerSilksLevel, string OldTrainerLyraLevel, string NewTrainerLyraLevel) { var count = 0; var conn = DBConnection.GetConnection(); var cmdText = "sp_update_trainer"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@OldTrainerID", SqlDbType.VarChar, 100); cmd.Parameters.Add("@NewTrainerID", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldTrainerFirstName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@NewTrainerFirstName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldTrainerLastName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@NewTrainerLastName", SqlDbType.VarChar, 100); cmd.Parameters.Add("@OldTrainerPhoneNumber", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewTrainerPhoneNumber", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldTrainerStatus", SqlDbType.Bit); cmd.Parameters.Add("@NewTrainerStatus", SqlDbType.Bit); cmd.Parameters.Add("@OldTrainerPoleLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewTrainerPoleLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldTrainerAcroLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewTrainerAcroLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldTrainerSilksLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewTrainerSilksLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@OldTrainerLyraLevel", SqlDbType.VarChar, 15); cmd.Parameters.Add("@NewTrainerLyraLevel", SqlDbType.VarChar, 15); cmd.Parameters["@OldTrainerID"].Value = OldTrainerID; cmd.Parameters["@NewTrainerID"].Value = NewTrainerID; cmd.Parameters["@OldTrainerFirstName"].Value = OldTrainerFirstName; cmd.Parameters["@NewTrainerFirstName"].Value = NewTrainerFirstName; cmd.Parameters["@OldTrainerLastName"].Value = OldTrainerLastName; cmd.Parameters["@NewTrainerLastName"].Value = NewTrainerLastName; cmd.Parameters["@OldTrainerPhoneNumber"].Value = OldTrainerPhoneNumber; cmd.Parameters["@NewTrainerPhoneNumber"].Value = NewTrainerPhoneNumber; cmd.Parameters["@OldTrainerStatus"].Value = OldTrainerStatus; cmd.Parameters["@NewTrainerStatus"].Value = NewTrainerStatus; cmd.Parameters["@OldTrainerPoleLevel"].Value = OldTrainerPoleLevel; cmd.Parameters["@NewTrainerPoleLevel"].Value = NewTrainerPoleLevel; cmd.Parameters["@OldTrainerAcroLevel"].Value = OldTrainerAcroLevel; cmd.Parameters["@NewTrainerAcroLevel"].Value = NewTrainerAcroLevel; cmd.Parameters["@OldTrainerSilksLevel"].Value = OldTrainerSilksLevel; cmd.Parameters["@NewTrainerSilksLevel"].Value = NewTrainerSilksLevel; cmd.Parameters["@OldTrainerLyraLevel"].Value = OldTrainerLyraLevel; cmd.Parameters["@NewTrainerLyraLevel"].Value = NewTrainerLyraLevel; try { conn.Open(); count = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(count); }