aspnet_Profile_GetProperties
ALTER PROCEDURE dbo.aspnet_Profile_GetProperties
@ApplicationName NVARCHAR(256),
@UserName NVARCHAR(256),
@TimeZoneAdjustment INT
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN
----------这里也少了 SELECT 0 ,所以直接返回,没用BEGIN END----------------------------------
DECLARE @UserId UNIQUEIDENTIFIER
SELECT @UserId = NULL
DECLARE @DateTimeNowUTC DATETIME -----声明时间并调用存储过程获取时间
EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
-------------查询此用户的用户ID,并给声明的参数---------------------------
IF (@UserId IS NULL) ----如果ID不存在,返回
RETURN
SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
FROM dbo.aspnet_Profile
WHERE UserId = @UserId
----查询并显示第一条记录-------------------
IF (@@ROWCOUNT > 0) ---如果受影响记录大于0
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate=@DateTimeNowUTC
WHERE UserId = @UserId
END ---------更新此用户的最后活跃时间
END
aspnet_Profile_SetProperties
ALTER PROCEDURE dbo.aspnet_Profile_SetProperties
@ApplicationName NVARCHAR(256),
@PropertyNames NTEXT,
@PropertyValuesString NTEXT,
@PropertyValuesBinary IMAGE,
@UserName NVARCHAR(256),
@IsUserAnonymous BIT,
@TimeZoneAdjustment INT
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER --声明应用程序ID
SELECT @ApplicationId = NULL
DECLARE @ErrorCode INT ---声明错误
SET @ErrorCode = 0
DECLARE @TranStarted BIT ---声明事务
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 ) ---如果当前活动事务为0,开始事务并设置事务参数为1
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
/**//*执行此存储过程创建记录,并返回@ApplicationId(输入参数不为空且数据表中存在记录的话就
返回存在的@ApplicationId)*/
IF( @@ERROR <> 0 ) --有错误的话就跳转到回滚段
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
DECLARE @DateTimeNowUTC DATETIME ---声明时间,获取时间
EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
DECLARE @UserId UNIQUEIDENTIFIER
DECLARE @LastActivityDate DATETIME
SELECT @UserId = NULL
SELECT @LastActivityDate = @DateTimeNowUTC
/**//*声明用户ID和最近活跃时间,设置最近活跃时间为当前时间@DateTimeNowUTC*/
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
------查询符合条件的用户ID
IF (@UserId IS NULL) ----如果不存在
EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT
--------执行存储过程创建用户---------------------------
IF( @@ERROR <> 0 ) ---如果有错误,回滚
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
-----------如果存在此用户ID的记录的话更新表,不存在就插入新记录-------------------------------
IF (EXISTS( SELECT *
FROM dbo.aspnet_Profile
WHERE UserId = @UserId))
UPDATE dbo.aspnet_Profile
SET PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@DateTimeNowUTC
WHERE UserId = @UserId
ELSE
INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @DateTimeNowUTC)
IF( @@ERROR <> 0 ) --如果有错误，回滚
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
---------------同步更新aspnet_Users表中的用户活动时间 -------------------------
UPDATE dbo.aspnet_Users
SET LastActivityDate=@DateTimeNowUTC
WHERE UserId = @UserId
IF( @@ERROR <> 0 ) ---有错误就回滚
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 ) ---如果事务运行到这里
BEGIN
SET @TranStarted = 0 ---设置事务参数为0
COMMIT TRANSACTION --结束事务
END
RETURN 0
Cleanup: --回滚块
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END