ALTER PROCEDURE dbo.aspnet_Membership_CreateUser
@ApplicationName NVARCHAR(256),
@UserName NVARCHAR(256),
@Password NVARCHAR(128),
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
@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,
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
@UserId UNIQUEIDENTIFIER OUTPUT /**//*用户ID为输出参数*/
AS
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
BEGIN /**//*声明变量并付初值*/
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
![](/Images/OutliningIndicators/None.gif)
DECLARE @NewUserId UNIQUEIDENTIFIER
SELECT @NewUserId = NULL
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
DECLARE @IsLockedOut BIT /**//*是否锁定*/
SET @IsLockedOut = 0
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
DECLARE @LastLockoutDate DATETIME /**//*最后一次锁帐号的时间*/
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
SET @LastLockoutDate = CONVERT( DATETIME, '17540101', 112 )/**//*转换格式(112---ISO标准---输入输出为YYMMDD,
'17540101'可能为初始值)*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
DECLARE @FailedPasswordAttemptCount INT /**//*失败密码尝试次数*/
SET @FailedPasswordAttemptCount = 0
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
DECLARE @FailedPasswordAttemptWindowStart DATETIME /**//*密码失败尝试窗口打开时间*/
SET @FailedPasswordAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
DECLARE @FailedPasswordAnswerAttemptCount INT /**//*遗失密码问题尝试次数*/
SET @FailedPasswordAnswerAttemptCount = 0
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
DECLARE @FailedPasswordAnswerAttemptWindowStart DATETIME /**//*遗失密码问题输入窗口打开时间*/
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )
![](/Images/OutliningIndicators/None.gif)
DECLARE @NewUserCreated BIT
DECLARE @ReturnValue INT
SET @ReturnValue = 0
![](/Images/OutliningIndicators/None.gif)
DECLARE @ErrorCode INT
SET @ErrorCode = 0
![](/Images/OutliningIndicators/None.gif)
DECLARE @TranStarted BIT
SET @TranStarted = 0
![](/Images/OutliningIndicators/None.gif)
IF( @@TRANCOUNT = 0 ) ----如果活动事务数为0,开始事务
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1 ----开始事务,设置事务参数为1
END
ELSE
SET @TranStarted = 0 -----如果活动事务不为0,设置事务参数为0
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*此存储过程在插入数据的时候先验证一些字段,然后判断用户ID是否在USER表中存在,不存在则插入数据,
存在则回滚事务.然后再为MEMMBER表插入数据.整个存储过程可以分两大部分,第一为USERS表插数据,第二为
MEMMBER表插数据.*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
![](/Images/OutliningIndicators/None.gif)
--执行存储过程输出@ApplicationId
IF( @@ERROR <> 0 ) --如果错误不为0
BEGIN
SET @ErrorCode = -1 --设置错误参数为-1
GOTO Cleanup --转先Cleanup
END
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*错误不为0则回滚事务,所以转向Cleanup,Cleanup处理回滚事务并返回错误参数,
下面的好几段都是判断,如果有错误就转向Cleanup,全部验证结束才开始真正插入数据*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
IF (@CreateDate IS NULL) ----如果创建日期为空,则执行存储过程输出创建日期
EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @CreateDate OUTPUT
ELSE
SELECT @CreateDate = DATEADD(n, -@TimeZoneAdjustment, @CreateDate) -- switch TO UTC time
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*DATEADD在向指定日期加上一段时间的基础上,返回新的 datetime 值(n/mi是minute的缩写)*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*查询用户ID,为了验证用户ID是否存在*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
IF ( @NewUserId IS NULL )----如果用户ID不存在
BEGIN
SET @NewUserId = @UserId ----用传入的用户ID给@NewUserId附值,做为下面存储过程的输入参数,也是输出参数
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*此存储过程也是创建用户的(表不同,数据字段不同,所以两个存储过程里进行的验证字段也不同.)
也是判断用户ID是否存在的,然后进行插入数据,存在返回-1,不存在返回0*/
![](/Images/OutliningIndicators/None.gif)
SET @NewUserCreated = 1 ----表示存储过程已在aspnet_Users表中添加数据完成
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*因为用户数据是保存在多张表中的,所以不止在一张表中添加数据*/
END
ELSE
BEGIN----如果用户ID存在则要转向回滚状态
SET @NewUserCreated = 0 ---表示此用户存在,位能创建
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )---此处是为了判断返回怎样的错误类型
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*符合条件的用户ID在aspnet_Users中存在,但是不等于输入的@UserId,且@UserId不为空*/
BEGIN
SET @ErrorCode = 6
GOTO Cleanup --转向回滚
END
END
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
IF( @@ERROR <> 0 ) ---继续判断(应该是判断上面有没有发生什么错误吧)
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
![](/Images/OutliningIndicators/None.gif)
IF( @ReturnValue = -1 ) ---返回值为上面添加另一张表的返回值,-1表示未添加
BEGIN
SET @ErrorCode = 10
GOTO Cleanup ---转向回滚
END
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )---aspnet_Membership表中是否存在要插入的用户ID
BEGIN
SET @ErrorCode = 6 ----存在返回错误代码
GOTO Cleanup ----转向回滚
END
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SET @UserId = @NewUserId ---此@NewUserId为上面SELECT语句的@NewUserId
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*SET @NewUserId = @UserId是为插aspnet_Users表而副职*/
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*SET @UserId = @NewUserId是为插aspnet_Memmbership表而副职*/
![](/Images/OutliningIndicators/None.gif)
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
END
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*全部验证完毕,且为表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,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )
![](/Images/OutliningIndicators/None.gif)
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
![](/Images/OutliningIndicators/None.gif)
IF (@NewUserCreated = 0)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*如果用户存在,更新用户的最近活动时间(如果不是本人添加的,那这个时间怎么可以算是这个用户
的最近活动时间呢.这里没理解.*/
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*上面的两个IF在添加成功的情况下好象不执行的样子*/
SELECT @CreateDate = DATEADD( n, @TimeZoneAdjustment, @CreateDate )
![](/Images/OutliningIndicators/None.gif)
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
![](/Images/OutliningIndicators/None.gif)
RETURN 0
![](/Images/OutliningIndicators/None.gif)
Cleanup:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
IF( @TranStarted = 1 ) /**//*如果事务参数为1*/
BEGIN
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
SET @TranStarted = 0 /**//*设置事务参数为0*/
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
ROLLBACK TRANSACTION /**//*回滚事务*/
END
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
RETURN @ErrorCode /**//*返回错误参数*/
![](/Images/OutliningIndicators/None.gif)
END
![](/Images/OutliningIndicators/None.gif)
GO
posted on
2006-08-29 14:46
MainIsUsed
阅读(
1671)
评论()
编辑
收藏
举报