/// <summary>
/// SelectMemberHistories() method
/// - Select MemberHistory table row by fromDate and toDate
/// </summary>
/// <param name="fromDate">From date</param>
/// <param name="toDate">To date</param>
/// <returns></returns>
public List <MemberHistory> SelectMemberHistories(DateTime fromDate, DateTime toDate)
{
try
{
using (connection)
{
connection.Open();
return((List <MemberHistory>)MySqlDataHelperFactory.SelectMultipleEntities <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.Text,
@"SELECT Sequence, MemberID, MemberName, Successful, Message, InsertedDate " +
@"FROM Product.MemberHistory " +
@"WHERE InsertedDate >= @FromDate AND InsertedDate <= @ToDate " +
@"ORDER BY InsertedDate DESC; ",
MySqlParameterHelperFactory.CreateParameter(providerName, "@FromDate", fromDate, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@ToDate", toDate, ParameterDirection.Input)
));
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// UpdateMember method
/// - Update Member table row by member information
/// </summary>
/// <param name="member">Member information</param>
/// <returns></returns>
public bool UpdateMember(Member member)
{
try
{
using (connection)
{
connection.Open();
int ret = MySqlDataHelperFactory.Execute(connection,
CommandType.Text,
@"UPDATE Product.Member " +
@"SET MemberName = @MemberName, IsAvailable = @IsAvailable, Email = @Email, " +
@" PhoneNumber = @PhoneNumber, Address = @Address, UpdatedDate = SYSDATE() " +
@"WHERE MemberID = @MemberID; ",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemberName", member.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@IsAvailable", member.IsAvailable, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@Email", member.Email, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@PhoneNumber", member.PhoneNumber, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@Address", member.Address, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemberID", member.MemberID, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectMembers method
/// - Select Member table rows by memberName
/// </summary>
/// <param name="memberName">Member name</param>
/// <returns></returns>
public List <Member> SelectMembers(string memberName)
{
try
{
using (connection)
{
connection.Open();
List <Member> ret = (List <Member>)MySqlDataHelperFactory.SelectMultipleEntities <Member>(connection,
typeof(Member),
CommandType.Text,
@"SELECT MemberID, MemberName, IsAvailable, Email, PhoneNumber, Address, InsertedDate, UpdatedDate " +
@"FROM Product.Member " +
@"WHERE MemberName like @MemberName; ",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemberName", String.Format("%{0}%", memberName), ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// UpdateMember method
/// - Update Member table row by member information
/// </summary>
/// <param name="member">Member information</param>
/// <returns></returns>
public bool UpdateMember(Member member)
{
try
{
using (connection)
{
connection.Open();
int ret = MySqlDataHelperFactory.Execute(connection,
CommandType.StoredProcedure,
"Product.sp_Memberships_Update_Member",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemName", member.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemIsAvailable", member.IsAvailable, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemEmail", member.Email, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemPhoneNumber", member.PhoneNumber, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemAddress", member.Address, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemID", member.MemberID, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectMember method
/// - Select Member table row by memberID
/// </summary>
/// <param name="memberID">Member ID</param>
/// <returns></returns>
public Member SelectMember(int memberID)
{
try
{
using (connection)
{
connection.Open();
Member ret = (Member)MySqlDataHelperFactory.SelectSingleEntity <Member>(connection,
typeof(Member),
CommandType.Text,
@"SELECT MemberID, MemberName, IsAvailable, Email, PhoneNumber, Address, InsertedDate, UpdatedDate " +
@"FROM Product.Member " +
@"WHERE MemberID = @MemberID; ",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@MemberID", memberID, MySqlDbType.Int32, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public MemberHistory InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
int insertedSequence = Convert.ToInt32(
MySqlDataHelperFactory.SelectScalar(connection,
CommandType.StoredProcedure,
"Product.sp_Memberships_Insert_MemberHistory",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemID", memberHistory.MemberID, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemName", memberHistory.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemSuccessful", memberHistory.IsSuccess, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemMessage", memberHistory.Message, ParameterDirection.Input)
)
);
MemberHistory ret = (MemberHistory)MySqlDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.StoredProcedure,
"Product.sp_Memberships_Select_MemberHistory_By_Sequence",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@Seq", insertedSequence, MySqlDbType.Int32, ParameterDirection.Input));
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMember method
/// - Insert Member table row from member information
/// </summary>
/// <param name="member">Member information</param>
/// <returns></returns>
public Member InsertMember(Member member)
{
try
{
using (connection)
{
if (string.IsNullOrEmpty(MySqlParameterHelperFactory.ProviderName))
{
MySqlParameterHelperFactory.ProviderName = providerName;
}
connection.Open();
int insertedMemberID = Convert.ToInt32(
MySqlDataHelperFactory.SelectScalar(connection,
CommandType.Text,
@"INSERT INTO Product.Member " +
@"( MemberName, IsAvailable, Email, PhoneNumber, Address, InsertedDate, UpdatedDate ) " +
@"VALUES " +
@"( @MemberName, @IsAvailable, @Email, @PhoneNumber, @Address, SYSDATE(), NULL ); " +
@" " +
@"SELECT LAST_INSERT_ID(); ",
MySqlParameterHelperFactory.CreateParameterWOProviderName("@MemberName", member.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@IsAvailable", member.IsAvailable, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@Email", member.Email, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@PhoneNumber", member.PhoneNumber, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@Address", member.Address, ParameterDirection.Input)
)
);
Member ret = (Member)MySqlDataHelperFactory.SelectSingleEntity <Member>(connection,
typeof(Member),
CommandType.Text,
@"SELECT MemberID, MemberName, IsAvailable, Email, PhoneNumber, Address, InsertedDate, UpdatedDate " +
@"FROM Product.Member " +
@"WHERE MemberID = @MemberID; ",
MySqlParameterHelperFactory.CreateParameterWOProviderName <MySqlDbType>("@MemberID", insertedMemberID, MySqlDbType.Int32, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public MemberHistory InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
int insertedSequence = Convert.ToInt32(
MySqlDataHelperFactory.SelectScalar(connection,
CommandType.Text,
@"INSERT INTO Product.MemberHistory " +
@"( MemberID, MemberName, Successful, Message, InsertedDate ) " +
@"VALUES " +
@"( @MemberID, @MemberName, @Successful, @Message, SYSDATE() ); " +
@" " +
@"SELECT LAST_INSERT_ID(); ",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@MemberID", memberHistory.MemberID, MySqlDbType.Int32, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemberName", memberHistory.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@Successful", memberHistory.IsSuccess, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@Message", memberHistory.Message, ParameterDirection.Input)
)
);
MemberHistory ret = (MemberHistory)MySqlDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.Text,
@"SELECT Sequence, MemberID, MemberName, Successful, Message, InsertedDate " +
@"FROM Product.MemberHistory " +
@"WHERE Sequence = @Sequence; ",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@Sequence", insertedSequence, MySqlDbType.Int32, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMember method
/// - Insert Member table row from member information
/// </summary>
/// <param name="member">Member information</param>
/// <returns></returns>
public Member InsertMember(Member member)
{
try
{
using (connection)
{
if (string.IsNullOrEmpty(MySqlParameterHelperFactory.ProviderName))
{
MySqlParameterHelperFactory.ProviderName = providerName;
}
connection.Open();
int insertedMemberID = Convert.ToInt32(
MySqlDataHelperFactory.SelectScalar(connection,
CommandType.StoredProcedure,
"Product.sp_Memberships_Insert_Member",
MySqlParameterHelperFactory.CreateParameterWOProviderName("@MemName", member.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@MemIsAvailable", member.IsAvailable, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@MemEmail", member.Email, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@MemPhoneNumber", member.PhoneNumber, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameterWOProviderName("@MemAddress", member.Address, ParameterDirection.Input)
)
);
Member ret = (Member)MySqlDataHelperFactory.SelectSingleEntity <Member>(connection,
typeof(Member),
CommandType.StoredProcedure,
"Product.sp_Memberships_Select_Member_By_MemberID",
MySqlParameterHelperFactory.CreateParameterWOProviderName <MySqlDbType>("@MemID", insertedMemberID, MySqlDbType.Int32, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// DeleteMember() method
/// - Delete Member table row by memberID
/// </summary>
/// <param name="memberID">Member ID</param>
/// <returns></returns>
public bool RemoveMember(int memberID)
{
try
{
using (connection)
{
connection.Open();
int ret = MySqlDataHelperFactory.Execute(connection,
CommandType.StoredProcedure,
"Product.sp_Memberships_Delete_Member_By_MemberID",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@MemID", memberID, MySqlDbType.Int32, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectNumsOfMembers method
/// - Select number of Member table rows by memberName
/// </summary>
/// <param name="memberName"></param>
/// <returns></returns>
public int SelectNumsOfMembers(string memberName)
{
try
{
using (connection)
{
connection.Open();
int ret = Convert.ToInt32(MySqlDataHelperFactory.SelectScalar(connection,
CommandType.StoredProcedure,
"Product.sp_Memberships_Select_Num_Of_Members_By_MemberName",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemName", String.Format("%{0}%", memberName), ParameterDirection.Input)
));
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectMemberHistories() method
/// - Select MemberHistory table row by fromDate and toDate
/// </summary>
/// <param name="fromDate">From date</param>
/// <param name="toDate">To date</param>
/// <returns></returns>
public List <MemberHistory> SelectMemberHistories(DateTime fromDate, DateTime toDate)
{
try
{
using (connection)
{
connection.Open();
return((List <MemberHistory>)MySqlDataHelperFactory.SelectMultipleEntities <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.StoredProcedure,
"Product.sp_Memberships_Select_MemberHistories_By_FromToDate",
MySqlParameterHelperFactory.CreateParameter(providerName, "@FromDate", fromDate, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@ToDate", toDate, ParameterDirection.Input)
));
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// DeleteMember() method
/// - Delete Member table row by memberID
/// </summary>
/// <param name="memberID">Member ID</param>
/// <returns></returns>
public bool RemoveMember(int memberID)
{
try
{
using (connection)
{
connection.Open();
int ret = MySqlDataHelperFactory.Execute(connection,
CommandType.Text,
@"DELETE FROM Product.Member " +
@"WHERE MemberID = @MemberID; ",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@MemberID", memberID, MySqlDbType.Int32, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectMember method
/// - Select Member table row by memberID
/// </summary>
/// <param name="memberID">Member ID</param>
/// <returns></returns>
public Member SelectMember(int memberID)
{
try
{
using (connection)
{
connection.Open();
Member ret = (Member)MySqlDataHelperFactory.SelectSingleEntity <Member>(connection,
typeof(Member),
CommandType.StoredProcedure,
"Product.sp_Memberships_Select_Member_By_MemberID",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@MemID", memberID, MySqlDbType.Int32, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectMembers method
/// - Select Member table rows by memberName
/// </summary>
/// <param name="memberName">Member name</param>
/// <returns></returns>
public List <Member> SelectMembers(string memberName)
{
try
{
using (connection)
{
connection.Open();
List <Member> ret = (List <Member>)MySqlDataHelperFactory.SelectMultipleEntities <Member>(connection,
typeof(Member),
CommandType.StoredProcedure,
"Product.sp_Memberships_Select_Members_By_MemberName",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemName", String.Format("%{0}%", memberName), ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectNumsOfMembers method
/// - Select number of Member table rows by memberName
/// </summary>
/// <param name="memberName"></param>
/// <returns></returns>
public int SelectNumsOfMembers(string memberName)
{
try
{
using (connection)
{
connection.Open();
int ret = Convert.ToInt32(MySqlDataHelperFactory.SelectScalar(connection,
CommandType.Text,
@"SELECT COUNT(*) " +
@"FROM Product.Member " +
@"WHERE MemberName like @MemberName; ",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemberName", String.Format("%{0}%", memberName), ParameterDirection.Input)
));
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
