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