protected StatusCode AddUserAccountInfo(UserAccountInfo userAccountInfo)
        {
            StatusCode status = StatusCode.Success;

            try
            {
                StringBuilder sql = new StringBuilder("INSERT INTO [tb_UserAccountInfo] ([UserId],[UserName],[Password],[ChangedPassword]"
                        + ",[EmailAddress],[AccountType],[VipPaymentInfo],[Activated],[CreationTime]) VALUES"
                        + "('" + userAccountInfo.UserID + "'");

                SqlCommand sc = new SqlCommand();
                if (String.IsNullOrEmpty(userAccountInfo.UserName))
                    sql.Append(",null");
                else
                    sql.Append(",'" + userAccountInfo.UserName + "'");

                if (userAccountInfo.Password == null)
                    sql.Append(",null");
                else
                {
                    sql.Append(",@pwd");
                    sc.Parameters.AddWithValue("@pwd", XMLToString(userAccountInfo.Password));
                }

                if (userAccountInfo.ChangedPassword == null)
                    sql.Append(",null");
                else
                {
                    //sql.Append( ",'" + XMLToString(userAccountInfo.ChangedPassword) + "'";
                    sql.Append(",@changedpwd");
                    sc.Parameters.AddWithValue("@changedpwd", XMLToString(userAccountInfo.ChangedPassword));
                }

                if (String.IsNullOrEmpty(userAccountInfo.EmailAddress))
                    sql.Append(",null");
                else
                    sql.Append(",'" + userAccountInfo.EmailAddress + "'");

                //accounttype will not be null
                sql.Append(",@acctype");
                sc.Parameters.AddWithValue("@acctype", userAccountInfo.AccountType.ToString());

                if (userAccountInfo.VipPaymentInfo == null)
                    sql.Append(",null");
                else
                {
                    //sql.Append( ",'" + XMLToString(userAccountInfo.VipPaymentInfo) + "'";
                    sql.Append(",@vippayment");
                    sc.Parameters.AddWithValue("@vippayment", XMLToString(userAccountInfo.VipPaymentInfo));
                }

                //Activated will not be null
                sql.Append(",'" + userAccountInfo.Activated + "','" + da.ExecuteScalar("select getdate()") + "'");
                sql.Append(")");

                da.ExecuteNonQuery(sql.ToString(), sc);
            }
            catch (Exception e)
            {
                status = StatusCode.Fail;
            }

            return status;
        }
        //tested
        public void UpdateUserAccountInfo(UserAccountInfo userAccountInfo)
        {
            try
            {
                StringBuilder sql = new StringBuilder("UPDATE tb_UserAccountInfo SET [UserName] =");
                SqlCommand sc = new SqlCommand();
                if (String.IsNullOrEmpty(userAccountInfo.UserName))
                    sql.Append("null");
                else
                    sql.Append("'" + userAccountInfo.UserName + "'");

                sql.Append(",[Password] =");
                if (userAccountInfo.Password == null)
                    sql.Append("null");
                else
                {
                    //sql.Append( "'" + XMLToString(userAccountInfo.Password) + "'";
                    sql.Append("@pwd");
                    sc.Parameters.AddWithValue("@pwd", XMLToString(userAccountInfo.Password));
                }

                sql.Append(",[ChangedPassword] =");
                if (userAccountInfo.ChangedPassword == null)
                    sql.Append("null");
                else
                {
                    //sql.Append( "'" + XMLToString(userAccountInfo.ChangedPassword) + "'";
                    sql.Append("@changedpwd");
                    sc.Parameters.AddWithValue("@changedpwd", XMLToString(userAccountInfo.ChangedPassword));
                }

                sql.Append(",[EmailAddress] =");
                if (String.IsNullOrEmpty(userAccountInfo.EmailAddress))
                    sql.Append("null");
                else
                    sql.Append("'" + userAccountInfo.EmailAddress + "'");

                //accounttype will not be null
                sql.Append(",[AccountType] =");
                //sql.Append( "'" + XMLToString(userAccountInfo.AccountType) + "'";
                sql.Append("@acctype");
                sc.Parameters.AddWithValue("@acctype", userAccountInfo.AccountType.ToString());

                //sql.Append( ",[VipPaymentInfo] =");
                //if (userAccountInfo.VipPaymentInfo == null)
                //    sql.Append( "null");
                //else
                //{
                //    //sql.Append( "'" + XMLToString(userAccountInfo.VipPaymentInfo) + "'";
                //    sql.Append( "@vippayment");
                //    sc.Parameters.AddWithValue("@vippayment", XMLToString(userAccountInfo.VipPaymentInfo));
                //}

                //Activated will not be null
                sql.Append(",[Activated] =");
                sql.Append("'" + userAccountInfo.Activated + "'");

                sql.Append(",[Suspended] =");
                sql.Append("'" + userAccountInfo.Suspended + "'");

                sql.Append(" WHERE [UserId] =@userid");
                sc.Parameters.AddWithValue("@userid", userAccountInfo.UserID);
                da.ExecuteNonQuery(sql.ToString(), sc);
            }
            catch (Exception ex)
            {

            }
        }
        public StatusCode Register(RegistrationInfo registrationInfo)
        {
            StatusCode statusCode = StatusCode.Fail;

            if (registrationInfo != null && registrationInfo.UserID != null && registrationInfo.Password != null)
            {
                if (!AccountInfoProvider.Instance.IsUserExist(registrationInfo.UserID))
                {
                    UserAccountInfo userAccountInfo = new UserAccountInfo();
                    userAccountInfo.UserID = registrationInfo.UserID;
                    userAccountInfo.Password = registrationInfo.Password;
                    userAccountInfo.EmailAddress = registrationInfo.EmailAddress;

                    if (userAccountInfo != null)
                    {
                        statusCode = AddUserAccountInfo(userAccountInfo);
                    }
                    else
                    {
                        statusCode = StatusCode.Invalid;
                    }
                }
                else
                {
                    statusCode = StatusCode.AlreadyExist;
                }
            }
            else
            {
                statusCode = StatusCode.InvalidData;
            }

            return statusCode;
        }
        //tested
        public UserAccountInfo GetUserAccountInfo(string userID)
        {
            UserAccountInfo foundAccountInfo = null;

            if (!string.IsNullOrEmpty(userID))
            {
                DataTable dtuser = GetUserAccountInfoDT(userID, null, null, false);

                if (dtuser != null && dtuser.Rows != null && dtuser.Rows.Count > 0)
                {
                    foundAccountInfo = new UserAccountInfo();
                    try
                    {
                        foundAccountInfo.UserID = userID;

                        DataRowWrapper rowWrapper = new DataRowWrapper(dtuser.Rows[0]);

                        foundAccountInfo.UserName = rowWrapper.GetColumnValueAsString("UserName");
                        foundAccountInfo.Password = rowWrapper.GetTypedColumnValue("Password", typeof(Password)) as Password;
                        foundAccountInfo.ChangedPassword = rowWrapper.GetTypedColumnValue("ChangedPassword", typeof(Password)) as Password;
                        foundAccountInfo.EmailAddress = rowWrapper.GetColumnValueAsString("EmailAddress");
                        foundAccountInfo.AccountType = (AccountType)rowWrapper.GetEnumColumnValue("AccountType", typeof(AccountType));
                        foundAccountInfo.VipPaymentInfo = rowWrapper.GetTypedColumnValue("VipPaymentInfo", typeof(PaymentInfo)) as PaymentInfo;
                        foundAccountInfo.Activated = rowWrapper.GetColumnValueAsBool("Activated");
                        foundAccountInfo.Suspended = rowWrapper.GetColumnValueAsBool("Suspended");
                    }
                    catch (Exception ex)
                    {
                    }
                }
            }
            return foundAccountInfo;
        }