/// <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();
OracleParameter resultPrameter = (OracleParameter)OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.Int32, ParameterDirection.Output);
int notResult = Convert.ToInt32(OracleDataHelperFactory.SelectScalar(connection,
CommandType.StoredProcedure,
"Product.sp_Num_Of_Member_By_MemName",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemName", String.Format("%{0}%", memberName), ParameterDirection.Input),
resultPrameter
));
int ret = Convert.ToInt32(Convert.ToString(resultPrameter.Value));
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 bool InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
int ret = (int)OracleDataHelperFactory.Execute(connection,
CommandType.Text,
@"INSERT INTO Product.MemberHistory " +
@"( Seq, MemberID, MemberName, IsSuccess, Message, InsertedDate ) " +
@"VALUES " +
@"( Product.SEQ_MemberHistory.NEXTVAL, :MemberID, :MemberName, :IsSuccess, :Message, SYSDATE ) ",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemberID", memberHistory.MemberID, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemberName", memberHistory.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":IsSuccess", ((memberHistory.IsSuccess) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":Message", memberHistory.Message, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
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();
OracleParameter resultPrameter = (OracleParameter)OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.Int32, ParameterDirection.Output);
int notResult = OracleDataHelperFactory.Execute(connection,
CommandType.StoredProcedure,
"Product.sp_Delete_Member_By_MemID",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemID", memberID, ParameterDirection.Input),
resultPrameter
);
int ret = Convert.ToInt32(Convert.ToString(resultPrameter.Value));
return((ret == 1) ? true : false);
}
}
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();
MemberHistory ret = (MemberHistory)OracleDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.StoredProcedure,
"Product.sp_Insert_MemberHistory",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemID", memberHistory.MemberID, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemName", memberHistory.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemIsSuccess", ((memberHistory.IsSuccess) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemMessage", memberHistory.Message, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.RefCursor, ParameterDirection.Output)
);
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)
{
if (string.IsNullOrEmpty(OracleParameterHelperFactory.ProviderName))
{
OracleParameterHelperFactory.ProviderName = providerName;
}
connection.Open();
int ret = OracleDataHelperFactory.Execute(connection,
CommandType.Text,
@"DELETE FROM Product.Member " +
@"WHERE MemberID = :MemberID ",
OracleParameterHelperFactory.CreateParameterWOProviderName(":MemberID", memberID, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
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 bool InsertMember(Member member)
{
try
{
using (connection)
{
if (string.IsNullOrEmpty(OracleParameterHelperFactory.ProviderName))
{
OracleParameterHelperFactory.ProviderName = providerName;
}
connection.Open();
int ret = (int)OracleDataHelperFactory.Execute(connection,
CommandType.Text,
@"INSERT INTO Product.Member " +
@"( MemberID, MemberName, IsAvailable, Email, PhoneNumber, Address, InsertedDate, UpdatedDate ) " +
@"VALUES " +
@"( Product.SEQ_Member.NEXTVAL, :MemberName, :IsAvailable, :Email, :PhoneNumber, :Address, SYSDATE, NULL ) ",
OracleParameterHelperFactory.CreateParameterWOProviderName(":MemberName", member.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":IsAvailable", ((member.IsAvailable) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":Email", member.Email, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":PhoneNumber", member.PhoneNumber, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":Address", member.Address, 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(
OracleDataHelperFactory.SelectScalar(connection,
CommandType.Text,
@"SELECT COUNT(*) " +
@"FROM Product.Member " +
@"WHERE MemberName LIKE '%" + memberName + "%' "
)
);
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 = OracleDataHelperFactory.Execute(connection,
CommandType.Text,
@"UPDATE Product.Member " +
@"SET MemberName = :MemberName, IsAvailable = :IsAvailable, Email = :Email, " +
@" PhoneNumber = :PhoneNumber, Address = :Address, UpdatedDate = SYSDATE " +
@"WHERE MemberID = :MemberID ",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemberName", member.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":IsAvailable", ((member.IsAvailable) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":Email", member.Email, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":PhoneNumber", member.PhoneNumber, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":Address", member.Address, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemberID", member.MemberID, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
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(OracleParameterHelperFactory.ProviderName))
{
OracleParameterHelperFactory.ProviderName = providerName;
}
connection.Open();
Member ret = (Member)OracleDataHelperFactory.SelectSingleEntity <Member>(connection,
typeof(Member),
CommandType.StoredProcedure,
"Product.sp_Insert_Member",
OracleParameterHelperFactory.CreateParameterWOProviderName(":MemName", member.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":MemIsAvailable", ((member.IsAvailable) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":MemEmail", member.Email, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":MemPhoneNumber", member.PhoneNumber, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName(":MemAddress", member.Address, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameterWOProviderName <OracleDbType>(":OutputData", null, OracleDbType.RefCursor, ParameterDirection.Output)
);
return(ret);
}
}
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)OracleDataHelperFactory.SelectSingleEntity <Member>(connection,
typeof(Member),
CommandType.Text,
@"SELECT MemberID, MemberName, IsAvailable, Email, PhoneNumber, Address, InsertedDate, UpdatedDate " +
@"FROM Product.Member " +
@"WHERE MemberID = :MemberID ",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemberID", memberID, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
public MemberHistory SelectMemberHistory(int seq)
{
try
{
using (connection)
{
connection.Open();
MemberHistory ret = (MemberHistory)OracleDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.Text,
@"SELECT Seq, MemberID, MemberName, IsSuccess, Message, InsertedDate " +
@"FROM Product.MemberHistory " +
@"WHERE Seq = :Seq ",
OracleParameterHelperFactory.CreateParameter(providerName, ":Seq", seq, 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();
OracleParameter resultPrameter = (OracleParameter)OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.Int32, ParameterDirection.Output);
int notResult = OracleDataHelperFactory.Execute(connection,
CommandType.StoredProcedure,
"Product.sp_Update_Member",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemName", member.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemIsAvailable", ((member.IsAvailable) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemEmail", member.Email, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemPhoneNumber", member.PhoneNumber, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemAddress", member.Address, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemID", member.MemberID, ParameterDirection.Input),
resultPrameter
);
int ret = Convert.ToInt32(Convert.ToString(resultPrameter.Value));
return((ret == 1) ? true : false);
}
}
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>)OracleDataHelperFactory.SelectMultipleEntities <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.Text,
@"SELECT Seq, MemberID, MemberName, IsSuccess, Message, InsertedDate " +
@"FROM Product.MemberHistory " +
@"WHERE InsertedDate >= :FromDate AND InsertedDate <= :ToDate " +
@"ORDER BY InsertedDate DESC ",
OracleParameterHelperFactory.CreateParameter(providerName, ":FromDate", fromDate, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":ToDate", toDate, ParameterDirection.Input)
));
}
}
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>)OracleDataHelperFactory.SelectMultipleEntities <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.StoredProcedure,
"Product.sp_Select_MemberHistories_Date",
OracleParameterHelperFactory.CreateParameter(providerName, ":FromDate", fromDate, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":ToDate", toDate, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.RefCursor, ParameterDirection.Output)
));
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// SelectInsertedMemberID method
/// - Select recently inserted MemberID
/// </summary>
/// <returns></returns>
public int SelectInsertedMemberID()
{
try
{
using (connection)
{
connection.Open();
int ret = Convert.ToInt32(
OracleDataHelperFactory.SelectScalar(connection,
CommandType.Text,
@"SELECT Product.SEQ_Member.CURRVAL FROM DUAL"
)
);
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>)OracleDataHelperFactory.SelectMultipleEntities <Member>(connection,
typeof(Member),
CommandType.Text,
@"SELECT MemberID, MemberName, IsAvailable, Email, PhoneNumber, Address, InsertedDate, UpdatedDate " +
@"FROM Product.Member " +
@"WHERE MemberName LIKE '%" + memberName + "%' "
);
return(ret);
}
}
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)OracleDataHelperFactory.SelectSingleEntity <Member>(connection,
typeof(Member),
CommandType.StoredProcedure,
"Product.sp_Select_Member_By_MemID",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemID", memberID, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.RefCursor, ParameterDirection.Output)
);
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>)OracleDataHelperFactory.SelectMultipleEntities <Member>(connection,
typeof(Member),
CommandType.StoredProcedure,
"Product.sp_Select_Members_By_MemName",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemName", String.Format("%{0}%", memberName), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.RefCursor, ParameterDirection.Output)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
