Aspnetdb Stored Procedure (2) member Qualification Management

1. Create user membership. Create procedure aspnet_membership_createuser (@ applicationname nvarchar (256), @ username nvarchar (256), @ password nvarchar (128), @ passwordsalt nvarchar (128), @ email nvarchar (256 ), @ passwordquestion nvarchar (256), @ passwordanswer nvarchar (128), @ isapproved bit, @ currenttimeutc datetime, @ createdate datetime = NULL, @ uniqueemail Int = 0, @ passwordformat Int = 0, @ userid uniqueidentifier output)
   This process first calls aspnet_applications_createapplication to register the application;
   Then, check whether the user table (aspnet_users) already exists. If yes,
   Update the last activity time (lastactivitydate field );
   If it does not exist, call aspnet_users_createuser to create a user;
   Finally, add the user membership data to the aspnet_membership table. Return Value: 0 is returned for success;
   Failed to call aspnet_applications_createapplication.-1 is returned;
   Failed to call aspnet_users_createuser.-1 is returned;
   If aspnet_users_createuser is returned as-1, 10 is returned;
   @ Userid is inconsistent with the userid in the User table (aspnet_users). 6 is returned;
   When the emai field value in the user membership table (aspnet_memberships) is required to be unique,
   Email duplication occurs when user membership is created. 7 is returned;
   The user already exists in the user membership table (aspnet_memberships), and 6 is returned;
   If an error occurs,-1 is returned.
2. Change User membership data. Create procedure aspnet_membership_updateuser
   (@ Applicationname nvarchar (256), @ username nvarchar (256), @ email nvarchar (256), @ comment ntext, @ isapproved bit, @ lastlogindate datetime, @ lastactivitydate datetime, @ uniqueemail int, @ currenttimeutc datetime
   In this process, the lastactivitydate field in the User table (aspnet_users) is updated first;
   Then, modify the email, comment, and ),
   Whether to enable the isapproved and lastlogindate fields. Return Value: 0 is returned for success;
   If the user does not exist, 1 is returned;
   If the email request is unique, @ email is the same as that of other users, and 7 is returned;
   If an error occurs,-1 is returned.

3. Modify user membership (Security) information. Create procedure alert (@ applicationname nvarchar (256), @ username nvarchar (256), @ ispasswordcorrect bit, @ updatelastloginactivitydate bit, @ passwordattemptwindow int, @ currenttimeutc datetime, @ lastlogindate datetime, @ lastactivitydate datetime) fields related to user account security in the user account qualification table (aspnet_memberships) are calculated and set (changed) during this process:
   Islockedout, lastlockoutdate, failedpasswordattemptcount,
   Failedpasswordattemptwindowstart, failedpasswordanswerattemptcount,
   Failedpasswordanswerattemptwindowstart. The input parameter @ updatelastloginactivitydate indicates whether or not the preceding operation was performed,
   First, update the lastlogindate field and the lastactivitydate field in the User table (aspnet_users.

   Return Value: 0 is returned for success;
   If the user does not exist, 1 is returned;
   If an error occurs,-1 is returned.

4. Change Password Questions and answers
   
   Create procedure success (@ applicationname nvarchar (256), @ username nvarchar (256), @ newpasswordquestion nvarchar (256), @ newpasswordanswer nvarchar (128) Return Value: 0 is returned successfully;
   1 is returned for failure (the user does not exist.

5. Reset the user password.
   Create procedure aspnet_membership_resetpassword
   (@ Applicationname nvarchar (256), @ username nvarchar (256), @ newpassword nvarchar (128), @ maxinvalidpasswordattempts int, @ passwordattemptwindow int, @ passwordsalt nvarchar (128 ), @ currenttimeutc datetime, @ passwordformat Int = 0, @ passwordanswer nvarchar (128) = NULL
   ) To change the user password and related fields,
   Then, calculate and set the fields related to user account security.
   (See aspnet_membership_updateuserinfo ).

   Return Value: 0 is returned for success;
   If the user does not exist, 1 is returned;
   The user is locked and 99 is returned;
   If the answer to the password is incorrect, 3 is returned;
   If an error occurs,-1 is returned.

6. Set the user password. Create procedure aspnet_membership_setpassword
   (@ Applicationname nvarchar (256), @ username nvarchar (256), @ newpassword nvarchar (128), @ passwordsalt nvarchar (128), @ currenttimeutc datetime, @ passwordformat Int = 0
   ) Return Value: 0 is returned for success;
   If the user does not exist, 1 is returned;

7. Unlock user lock create procedure aspnet_membership_unlockuser
   (@ Applicationname nvarchar (256), @ username nvarchar (256)
   ) Return Value: 0 is returned for success;
   User does not exist. 1 is returned.
8. (Like) query the user membership data matching the email and @ emailtomatch, and return the paging user dataset.
   
   Create procedure aspnet_membership_findusersbyemail (@ applicationname nvarchar (256), @ emailtomatch nvarchar (256), @ pageindex int, @ pagesize INT)

   Returned value: the number of rows that meet the condition.

9. (Like) query the user membership data that matches @ usernametomatch and return the paging user dataset.
   Create procedure aspnet_membership_findusersbyname (@ applicationname nvarchar (256), @ usernametomatch nvarchar (256), @ pageindex int, @ pagesize INT)

   Returned value: the number of rows that meet the condition.

10. Returns the page (all) User membership dataset.
    
    Create procedure aspnet_membership_getallusers (@ applicationname nvarchar (256), @ pageindex int, @ pagesize INT)

    Returned value: the number of rows that meet the condition.

11. Query user membership data by email and return the user dataset (one row ).
    
    Create procedure aspnet_membership_getuserbyemail
    (@ Applicationname nvarchar (256), @ email nvarchar (256)
    )

    Return Value: 0 is returned for success;
    User does not exist. 1 is returned.

12. Query user membership data by user name, and return the user dataset (one row ).
    
    Create procedure aspnet_membership_getuserbyname
    (@ Applicationname nvarchar (256), @ username nvarchar (256), @ currenttimeutc datetime, @ updatelastactivity bit = 0
    )

    The input parameter @ updatelastactivity specifies whether to update the lastactivitydate field in the User table (aspnet_users.

    Return Value: 0 is returned for success;
    If the user does not exist,-1 is returned.

13. Query user membership data by user ID, and return the user dataset (one row ).

    Create procedure aspnet_membership_getuserbyuserid (@ userid uniqueidentifier, @ currenttimeutc datetime, @ updatelastactivity bit = 0)

    The input parameter @ updatelastactivity specifies whether to update the lastactivitydate field in the User table (aspnet_users.

    Return Value: 0 is returned for success;
    If the user does not exist,-1 is returned.

14. Query the user's password and storage password format, and return the data set (one row) containing the user password and Password Storage Format fields ).
    
    Create procedure aspnet_membership_getpassword (@ applicationname nvarchar (256), @ username nvarchar (256), @ symbol int, @ passwordattemptwindow int, @ currenttimeutc datetime, @ passwordanswer nvarchar (128) = NULL)

    If the answer to the password is incorrect, you need to calculate and set the user account security fields.
    (See aspnet_membership_updateuserinfo ).

    Return Value: 0 is returned for success;
    If the user does not exist, 1 is returned;
    The user is locked and 99 is returned;
    If the answer to the password is incorrect, 3 is returned;
    If an error occurs,-1 is returned.

15. Query the detailed information about the user password and return the User Account Information Dataset (one row ).

    Create procedure aspnet_membership_getpasswordwithformat
    (@ Applicationname nvarchar (256), @ username nvarchar (256), @ updatelastloginactivitydate bit, @ currenttimeutc datetime
    )

    Enter the parameter @ updatelastloginactivitydate to indicate whether to update the Last Logon Time (lastlogindate) field.
    And the lastactivitydate field in the User table (aspnet_users.

    Return Value: 0 is returned for success;
    If the user does not exist, 1 is returned;
    The user is locked and 99 is returned.

16. Obtain the number of online users.
    Create procedure aspnet_membership_getnumberofusersonline (@ applicationname nvarchar (256), @ minutessincelastinactive int, @ currenttimeutc datetime)

    The user logging on to the last @ minutessincelastinactive minute is an online user,
    That is, the condition that lastactivitydate (datetime) >=@ currenttimeutc (datetime)-@ minutessincelastinactive (number of minutes) is met. Returned value: number of online users.