Exemplo n.º 1
0
        /// <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;
            }
        }
Exemplo n.º 3
0
        /// <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>
        /// 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>
        /// 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;
            }
        }