aspnet_Membership

aspnet_Membership_CreateUser

ALTER PROCEDURE dbo.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,

@TimeZoneAdjustment INT,

@CreateDate DATETIME = NULL,

@UniqueEmail INT = 0,

@PasswordFormat INT = 0,

@UserId UNIQUEIDENTIFIER OUTPUT /*用户ID为输出参数*/

BEGIN /*声明变量并付初值*/

DECLARE @ApplicationId UNIQUEIDENTIFIER

SELECT @ApplicationId = NULL

DECLARE @NewUserId UNIQUEIDENTIFIER

SELECT @NewUserId = NULL

DECLARE @IsLockedOut BIT /*是否锁定*/

SET @IsLockedOut = 0

DECLARE @LastLockoutDate DATETIME /*最后一次锁帐号的时间*/

SET @LastLockoutDate = CONVERT( DATETIME, '17540101', 112 )/*转换格式(112---ISO标准---输入输出为YYMMDD,

'17540101'可能为初始值)*/

DECLARE @FailedPasswordAttemptCount INT /*失败密码尝试次数*/

SET @FailedPasswordAttemptCount = 0

DECLARE @FailedPasswordAttemptWindowStart DATETIME /*密码失败尝试窗口打开时间*/

SET @FailedPasswordAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )

DECLARE @FailedPasswordAnswerAttemptCount INT /*遗失密码问题尝试次数*/

SET @FailedPasswordAnswerAttemptCount = 0

DECLARE @FailedPasswordAnswerAttemptWindowStart DATETIME /*遗失密码问题输入窗口打开时间*/

SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )

DECLARE @NewUserCreated BIT

DECLARE @ReturnValue INT

SET @ReturnValue = 0

DECLARE @ErrorCode INT

SET @ErrorCode = 0

DECLARE @TranStarted BIT

SET @TranStarted = 0

IF( @@TRANCOUNT = 0 ) ----如果活动事务数为0,开始事务

BEGIN

BEGIN TRANSACTION

SET @TranStarted = 1 ----开始事务,设置事务参数为1

END

ELSE

SET @TranStarted = 0 -----如果活动事务不为0,设置事务参数为0

/*此存储过程在插入数据的时候先验证一些字段，然后判断用户ID是否在USER表中存在,不存在则插入数据,

存在则回滚事务.然后再为MEMMBER表插入数据.整个存储过程可以分两大部分,第一为USERS表插数据,第二为

MEMMBER表插数据.*/

EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

--执行存储过程输出@ApplicationId

IF( @@ERROR <> 0 ) --如果错误不为0

BEGIN

SET @ErrorCode = -1 --设置错误参数为-1

GOTO Cleanup --转先Cleanup

END

/*错误不为0则回滚事务,所以转向Cleanup,Cleanup处理回滚事务并返回错误参数,

下面的好几段都是判断,如果有错误就转向Cleanup,全部验证结束才开始真正插入数据*/

IF (@CreateDate IS NULL) ----如果创建日期为空,则执行存储过程输出创建日期

EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @CreateDate OUTPUT

ELSE

SELECT @CreateDate = DATEADD(n, -@TimeZoneAdjustment, @CreateDate) -- switch TO UTC time

/*DATEADD在向指定日期加上一段时间的基础上，返回新的 datetime 值(n/mi是minute的缩写)*/

SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId

/*查询用户ID,为了验证用户ID是否存在*/

IF ( @NewUserId IS NULL )----如果用户ID不存在

BEGIN

SET @NewUserId = @UserId ----用传入的用户ID给@NewUserId附值,做为下面存储过程的输入参数,也是输出参数

EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT

/*此存储过程也是创建用户的(表不同,数据字段不同,所以两个存储过程里进行的验证字段也不同.)

也是判断用户ID是否存在的,然后进行插入数据,存在返回-1,不存在返回0*/

SET @NewUserCreated = 1 ----表示存储过程已在aspnet_Users表中添加数据完成

/*因为用户数据是保存在多张表中的,所以不止在一张表中添加数据*/

END

ELSE

BEGIN----如果用户ID存在则要转向回滚状态

SET @NewUserCreated = 0 ---表示此用户存在,位能创建

IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )---此处是为了判断返回怎样的错误类型

/*符合条件的用户ID在aspnet_Users中存在,但是不等于输入的@UserId,且@UserId不为空*/

BEGIN

SET @ErrorCode = 6

GOTO Cleanup --转向回滚

END

IF( @@ERROR <> 0 ) ---继续判断(应该是判断上面有没有发生什么错误吧)

BEGIN

SET @ErrorCode = -1

GOTO Cleanup

END

IF( @ReturnValue = -1 ) ---返回值为上面添加另一张表的返回值,-1表示未添加

BEGIN

SET @ErrorCode = 10

GOTO Cleanup ---转向回滚

END

IF ( EXISTS ( SELECT UserId

FROM dbo.aspnet_Membership

WHERE @NewUserId = UserId ) )---aspnet_Membership表中是否存在要插入的用户ID

BEGIN

SET @ErrorCode = 6 ----存在返回错误代码

GOTO Cleanup ----转向回滚

END

SET @UserId = @NewUserId ---此@NewUserId为上面SELECT语句的@NewUserId

/*SET @NewUserId = @UserId是为插aspnet_Users表而副职*/

/*SET @UserId = @NewUserId是为插aspnet_Memmbership表而副职*/

IF (@UniqueEmail = 1) --判断邮箱唯一

BEGIN

IF (EXISTS (SELECT *

FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )

WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))

BEGIN ---如果存在此邮箱

SET @ErrorCode = 7

GOTO Cleanup

END

/*全部验证完毕,且为表aspnet_Users 添加成功后,插入aspnet_Membership表*/

INSERT INTO dbo.aspnet_Membership

( ApplicationId,

UserId,

Password,

PasswordSalt,

Email,

LoweredEmail,

PasswordQuestion,

PasswordAnswer,

PasswordFormat,

IsApproved,

IsLockedOut,

CreateDate,

LastLoginDate,

LastPasswordChangedDate,

LastLockoutDate,

FailedPasswordAttemptCount,

FailedPasswordAttemptWindowStart,

FailedPasswordAnswerAttemptCount,

FailedPasswordAnswerAttemptWindowStart )

VALUES ( @ApplicationId,

@UserId,

@Password,

@PasswordSalt,

@Email,

LOWER(@Email),

@PasswordQuestion,

@PasswordAnswer,

@PasswordFormat,

@IsApproved,

@IsLockedOut,

@CreateDate,

@LastLockoutDate,

@FailedPasswordAttemptCount,

@FailedPasswordAttemptWindowStart,

@FailedPasswordAnswerAttemptCount,

@FailedPasswordAnswerAttemptWindowStart )

IF( @@ERROR <> 0 )

BEGIN

SET @ErrorCode = -1

GOTO Cleanup

END

IF (@NewUserCreated = 0)

/*如果用户存在,更新用户的最近活动时间(如果不是本人添加的,那这个时间怎么可以算是这个用户

的最近活动时间呢.这里没理解.*/

BEGIN

UPDATE dbo.aspnet_Users

SET LastActivityDate = @CreateDate

WHERE @UserId = UserId

IF( @@ERROR <> 0 )

BEGIN

SET @ErrorCode = -1

GOTO Cleanup

END

/*上面的两个IF在添加成功的情况下好象不执行的样子*/

SELECT @CreateDate = DATEADD( n, @TimeZoneAdjustment, @CreateDate )

IF( @TranStarted = 1 )

BEGIN

SET @TranStarted = 0

COMMIT TRANSACTION

END

RETURN 0

Cleanup:

IF( @TranStarted = 1 ) /*如果事务参数为1*/

BEGIN

SET @TranStarted = 0 /*设置事务参数为0*/

ROLLBACK TRANSACTION /*回滚事务*/

END

RETURN @ErrorCode /*返回错误参数*/

END