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);
}
