aspnet_Membership_UpdateUserInfo

  1 ALTER    PROCEDURE  dbo.aspnet_Membership_UpdateUserInfo
  2      @ApplicationName                  NVARCHAR ( 256 ),
  3      @UserName                         NVARCHAR ( 256 ),
  4      @IsPasswordCorrect                BIT ,
  5      @UpdateLastLoginActivityDate      BIT ,
  6      @MaxInvalidPasswordAttempts       INT ,
  7      @PasswordAttemptWindow            INT ,
  8      @TimeZoneAdjustment               INT
  9 AS
 10 BEGIN
 11      DECLARE   @UserId                                   UNIQUEIDENTIFIER
 12      DECLARE   @IsApproved                               BIT
 13      DECLARE   @IsLockedOut                              BIT
 14      DECLARE   @LastLockoutDate                          DATETIME
 15      DECLARE   @FailedPasswordAttemptCount               INT
 16      DECLARE   @FailedPasswordAttemptWindowStart         DATETIME
 17      DECLARE   @FailedPasswordAnswerAttemptCount         INT
 18      DECLARE   @FailedPasswordAnswerAttemptWindowStart   DATETIME
 19      /**/ /*声明关于密码的一堆变量*/    
 20     
 21
 22      DECLARE   @ErrorCode       INT
 23      SET   @ErrorCode   =   0
 24
 25      DECLARE   @TranStarted     BIT
 26      SET   @TranStarted   =   0
 27
 28      IF (  @@TRANCOUNT   =   0  )  -- 如果当前活动事务为0，开始事务并设置事务参数为1
 29      BEGIN
 30          BEGIN   TRANSACTION
 31          SET   @TranStarted   =   1
 32      END
 33      ELSE
 34          SET   @TranStarted   =   0
 35
 36      DECLARE   @DateTimeNowUTC   DATETIME   -- 声明当前时间并获取当前时间 
 37      EXEC  dbo.aspnet_GetUtcDate  @TimeZoneAdjustment ,  @DateTimeNowUTC  OUTPUT
 38
 39
 40      SELECT    @UserId   =  u.UserId,
 41              @IsApproved   =  m.IsApproved,
 42              @IsLockedOut   =  m.IsLockedOut,
 43              @LastLockoutDate   =  m.LastLockoutDate,
 44              @FailedPasswordAttemptCount   =  m.FailedPasswordAttemptCount,
 45              @FailedPasswordAttemptWindowStart   =  m.FailedPasswordAttemptWindowStart,
 46              @FailedPasswordAnswerAttemptCount   =  m.FailedPasswordAnswerAttemptCount,
 47              @FailedPasswordAnswerAttemptWindowStart   =  m.FailedPasswordAnswerAttemptWindowStart
 48      FROM     dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m  WITH  ( UPDLOCK )
 49      WHERE     LOWER ( @ApplicationName )  =  a.LoweredApplicationName  AND
 50             u.ApplicationId  =  a.ApplicationId     AND
 51             u.UserId  =  m.UserId  AND
 52              LOWER ( @UserName )  =  u.LoweredUserName
 53      /**/ /*查询此用户的信息，字段都是上面声明的有关于密码的变量*/
 54
 55      IF  (  @@rowcount   =   0  )  -- 如果受影响行数为0，回滚事物并返回错误代码
 56      BEGIN
 57          SET   @ErrorCode   =   1
 58          GOTO  Cleanup
 59      END
 60
 61      IF (  @IsLockedOut   =   1  )  -- 如果用户被锁定，也回滚，不允许操作
 62      BEGIN
 63          GOTO  Cleanup
 64      END
 65
 66      IF (  @IsPasswordCorrect   =   0  )  -- 如果密码不正确
 67      BEGIN
 68          IF (  @DateTimeNowUTC   >   DATEADD ( minute,  @PasswordAttemptWindow ,  @FailedPasswordAttemptWindowStart  ) )
 69          BEGIN
 70              SET   @FailedPasswordAttemptWindowStart   =   @DateTimeNowUTC
 71              SET   @FailedPasswordAttemptCount   =   1
 72          END
 73          ELSE
 74          BEGIN
 75              SET   @FailedPasswordAttemptWindowStart   =   @DateTimeNowUTC
 76              SET   @FailedPasswordAttemptCount   =   @FailedPasswordAttemptCount   +   1
 77          END
 78
 79          BEGIN
 80              IF (  @FailedPasswordAttemptCount   >=   @MaxInvalidPasswordAttempts  )
 81              BEGIN
 82                  SET   @IsLockedOut   =   1
 83                  SET   @LastLockoutDate   =   @DateTimeNowUTC
 84              END
 85          END
 86      END
 87       /**/ /*处理密码相关的问题*/
 88      ELSE
 89      BEGIN
 90          IF (  @UpdateLastLoginActivityDate   =   1  )  -- 如果更新最近登陆和活跃时间
 91          BEGIN
 92              UPDATE   dbo.aspnet_Membership
 93              SET      LastLoginDate  =   @DateTimeNowUTC
 94              WHERE    UserId  =   @UserId
 95              -- 更新最近登陆时间
 96
 97              IF (  @@ERROR   <>   0  )
 98              BEGIN
 99                  SET   @ErrorCode   =   - 1
100                  GOTO  Cleanup
101              END
102
103              UPDATE   dbo.aspnet_Users
104              SET      LastActivityDate  =   @DateTimeNowUTC
105              WHERE     @UserId   =  UserId
106              -- 更新最近活跃时间
107              IF (  @@ERROR   <>   0  )
108              BEGIN
109                  SET   @ErrorCode   =   - 1
110                  GOTO  Cleanup
111              END
112          END
113
114          IF (  @FailedPasswordAttemptCount   >   0   OR   @FailedPasswordAnswerAttemptCount   >   0  )
115          -- --如果密码尝试次数或者答案尝试次数大于0（此时输入正确，则全部还原处世状态）
116          BEGIN
117              SET   @FailedPasswordAttemptCount   =   0
118              SET   @FailedPasswordAttemptWindowStart   =   CONVERT (  DATETIME ,  ' 17540101 ' ,  112  )
119              SET   @FailedPasswordAnswerAttemptCount   =   0
120              SET   @FailedPasswordAnswerAttemptWindowStart   =   CONVERT (  DATETIME ,  ' 17540101 ' ,  112  )
121              SET   @LastLockoutDate   =   CONVERT (  DATETIME ,  ' 17540101 ' ,  112  )
122          END
123      END
124
125      UPDATE  dbo.aspnet_Membership
126      SET  IsLockedOut  =   @IsLockedOut , LastLockoutDate  =   @LastLockoutDate ,
127         FailedPasswordAttemptCount  =   @FailedPasswordAttemptCount ,
128         FailedPasswordAttemptWindowStart  =   @FailedPasswordAttemptWindowStart ,
129         FailedPasswordAnswerAttemptCount  =   @FailedPasswordAnswerAttemptCount ,
130         FailedPasswordAnswerAttemptWindowStart  =   @FailedPasswordAnswerAttemptWindowStart
131      WHERE   @UserId   =  UserId
132         -- -使用上面的参数更新表
133      IF (  @@ERROR   <>   0  )
134      BEGIN
135          SET   @ErrorCode   =   - 1
136          GOTO  Cleanup
137      END
138
139      IF (  @TranStarted   =   1  )
140      BEGIN
141      SET   @TranStarted   =   0
142      COMMIT   TRANSACTION
143      END
144
145      RETURN   @ErrorCode
146
147 Cleanup:
148
149      IF (  @TranStarted   =   1  )
150      BEGIN
151          SET   @TranStarted   =   0
152          ROLLBACK   TRANSACTION
153      END
154
155      RETURN   @ErrorCode
156
157 END

转载于:https://www.cnblogs.com/ruanbl/archive/2006/08/31/491057.html