asp.net 将Session值储存于SQL Server中

最新推荐文章于 2019-07-11 10:33:00 发布

weixin_30642267

最新推荐文章于 2019-07-11 10:33:00 发布

阅读量116

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/feelboy/archive/2010/08/20/1804711.html

版权

如果想把session放入sql数据库中我们需要在数据库中创建一个ASPState 这个表是微软自己提供的，里面有很全的SQL语句，大家放心使用。下图就是生成的数据表。

下面放出sql代码：直接拷贝执行即可

USE master
GO

/* Drop the database containing our sprocs */
IF DB_ID('ASPState') IS NOT NULL BEGIN
    DROP DATABASE ASPState
END
GO

/* Drop temporary tables */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempSessions' AND type = 'U') BEGIN
    DROP TABLE tempdb..ASPStateTempSessions
END
GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempApplications' AND type = 'U') BEGIN
    DROP TABLE tempdb..ASPStateTempApplications
END
GO

/* Drop the startup procedure */
DECLARE @PROCID int
SET @PROCID = OBJECT_ID('ASPState_Startup') 
IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN
    DROP PROCEDURE ASPState_Startup 
END
GO

/* Drop the obsolete startup enabler */
DECLARE @PROCID int
SET @PROCID = OBJECT_ID('EnableASPStateStartup') 
IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN
    DROP PROCEDURE EnableASPStateStartup
END
GO

/* Drop the obsolete startup disabler */
DECLARE @PROCID int
SET @PROCID = OBJECT_ID('DisableASPStateStartup') 
IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN
    DROP PROCEDURE DisableASPStateStartup
END
GO

/* Drop the ASPState_DeleteExpiredSessions_Job */
DECLARE @JobID BINARY(16)  
SELECT @JobID = job_id     
FROM   msdb.dbo.sysjobs    
WHERE (name = N'ASPState_Job_DeleteExpiredSessions')       
IF (@JobID IS NOT NULL)    
BEGIN  
    -- Check if the job is a multi-server job  
    IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
    BEGIN 
        -- There is, so abort the script 
        RAISERROR (N'Unable to import job ''ASPState_Job_DeleteExpiredSessions'' since there is already a multi-server job with this name.', 16, 1) 
    END 
    ELSE 
        -- Delete the [local] job 
        EXECUTE msdb.dbo.sp_delete_job @job_name = N'ASPState_Job_DeleteExpiredSessions' 
END

USE master
GO

/* Create and populate the ASPState database */
CREATE DATABASE ASPState
GO

USE ASPstate
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE DropTempTables
AS
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempSessions' AND type = 'U') BEGIN
        DROP TABLE tempdb..ASPStateTempSessions
    END

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempApplications' AND type = 'U') BEGIN
        DROP TABLE tempdb..ASPStateTempApplications
    END

    RETURN 0
GO
    
CREATE PROCEDURE CreateTempTables
AS
    /*
     * Note that we cannot create user-defined data types in
     * tempdb because sp_addtype must be run in the context
     * of the current database, and we cannot switch to 
     * tempdb from a stored procedure.
     */

    CREATE TABLE tempdb..ASPStateTempSessions (
        SessionId           CHAR(32)        NOT NULL PRIMARY KEY,
        Created             DATETIME        NOT NULL DEFAULT GETDATE(),
        Expires             DATETIME        NOT NULL,
        LockDate            DATETIME        NOT NULL,
        LockCookie          INT             NOT NULL,
        Timeout             INT             NOT NULL,
        Locked              BIT             NOT NULL,
        SessionItemShort    VARBINARY(7000) NULL,
        SessionItemLong     IMAGE           NULL,
    )

    CREATE TABLE tempdb..ASPStateTempApplications (
        AppId               INT             NOT NULL IDENTITY PRIMARY KEY,
        AppName             CHAR(280)       NOT NULL,
    )

    CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName)

    RETURN 0
GO     

CREATE PROCEDURE ResetData
AS
    EXECUTE DropTempTables
    EXECUTE CreateTempTables
    RETURN 0
GO
   
EXECUTE sp_addtype tSessionId, 'CHAR(32)',  'NOT NULL'
GO

EXECUTE sp_addtype tAppName, 'VARCHAR(280)', 'NOT NULL'
GO

EXECUTE sp_addtype tSessionItemShort, 'VARBINARY(7000)'
GO

EXECUTE sp_addtype tSessionItemLong, 'IMAGE'
GO

EXECUTE sp_addtype tTextPtr, 'VARBINARY(16)'
GO

CREATE PROCEDURE TempGetAppId
    @appName    tAppName,
    @appId      INT OUTPUT
AS
    SELECT @appId = AppId
    FROM tempdb..ASPStateTempApplications
    WHERE AppName = @appName

    IF @appId IS NULL BEGIN
        INSERT tempdb..ASPStateTempApplications
            (AppName)
        VALUES
            (@appName)

        SELECT @appId = AppId
        FROM tempdb..ASPStateTempApplications
        WHERE AppName = @appName
    END

    RETURN 0
GO

CREATE PROCEDURE TempGetStateItem
    @id         tSessionId,
    @itemShort  tSessionItemShort OUTPUT,
    @locked     BIT OUTPUT,
    @lockDate   DATETIME OUTPUT,
    @lockCookie INT OUTPUT
AS
    DECLARE @textptr AS tTextPtr
    DECLARE @length AS INT
    DECLARE @now as DATETIME
    SET @now = GETDATE()

    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, @now), 
        @locked = Locked,
        @lockDate = LockDate,
        @lockCookie = LockCookie,
        @itemShort = CASE @locked
            WHEN 0 THEN SessionItemShort
            ELSE NULL
            END,
        @textptr = CASE @locked
            WHEN 0 THEN TEXTPTR(SessionItemLong)
            ELSE NULL
            END,
        @length = CASE @locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END
    WHERE SessionId = @id
    IF @length IS NOT NULL BEGIN
        READTEXT tempdb..ASPStateTempSessions.SessionItemLong @textptr 0 @length
    END

    RETURN 0
GO


CREATE PROCEDURE TempGetStateItemExclusive
    @id         tSessionId,
    @itemShort  tSessionItemShort OUTPUT,
    @locked     BIT OUTPUT,
    @lockDate   DATETIME OUTPUT,
    @lockCookie INT OUTPUT
AS
    DECLARE @textptr AS tTextPtr
    DECLARE @length AS INT
    DECLARE @now as DATETIME

    SET @now = GETDATE()
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, @now), 
        @lockDate = LockDate = CASE Locked
            WHEN 0 THEN @now
            ELSE LockDate
            END,
        @lockCookie = LockCookie = CASE Locked
            WHEN 0 THEN LockCookie + 1
            ELSE LockCookie
            END,
        @itemShort = CASE Locked
            WHEN 0 THEN SessionItemShort
            ELSE NULL
            END,
        @textptr = CASE Locked
            WHEN 0 THEN TEXTPTR(SessionItemLong)
            ELSE NULL
            END,
        @length = CASE Locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END,
        @locked = Locked,
        Locked = 1
    WHERE SessionId = @id
    IF @length IS NOT NULL BEGIN
        READTEXT tempdb..ASPStateTempSessions.SessionItemLong @textptr 0 @length
    END

    RETURN 0
GO

CREATE PROCEDURE TempReleaseStateItemExclusive
    @id         tSessionId,
    @lockCookie INT
AS
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO


CREATE PROCEDURE TempInsertStateItemShort
    @id         tSessionId,
    @itemShort  tSessionItemShort,
    @timeout    INT
AS   

    DECLARE @now as DATETIME
    SET @now = GETDATE()

    INSERT tempdb..ASPStateTempSessions 
        (SessionId, 
         SessionItemShort, 
         Timeout, 
         Expires, 
         Locked, 
         LockDate,
         LockCookie) 
    VALUES 
        (@id, 
         @itemShort, 
         @timeout, 
         DATEADD(n, @timeout, @now), 
         0, 
         @now,
         1)

    RETURN 0
GO

CREATE PROCEDURE TempInsertStateItemLong
    @id         tSessionId,
    @itemLong   tSessionItemLong,
    @timeout    INT
AS    
    DECLARE @now as DATETIME
    SET @now = GETDATE()

    INSERT tempdb..ASPStateTempSessions 
        (SessionId, 
         SessionItemLong, 
         Timeout, 
         Expires, 
         Locked, 
         LockDate,
         LockCookie) 
    VALUES 
        (@id, 
         @itemLong, 
         @timeout, 
         DATEADD(n, @timeout, @now), 
         0, 
         @now,
         1)

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemShort
    @id         tSessionId,
    @itemShort  tSessionItemShort,
    @timeout    INT,
    @lockCookie INT
AS    
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemShort = @itemShort, 
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemShortNullLong
    @id         tSessionId,
    @itemShort  tSessionItemShort,
    @timeout    INT,
    @lockCookie INT
AS    
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemShort = @itemShort, 
        SessionItemLong = NULL, 
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemLong
    @id         tSessionId,
    @itemLong   tSessionItemLong,
    @timeout    INT,
    @lockCookie INT
AS    
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemLong = @itemLong,
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemLongNullShort
    @id         tSessionId,
    @itemLong   tSessionItemLong,
    @timeout    INT,
    @lockCookie INT
AS    
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemLong = @itemLong, 
        SessionItemShort = NULL,
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempRemoveStateItem
    @id     tSessionId,
    @lockCookie INT
AS
    DELETE tempdb..ASPStateTempSessions
    WHERE SessionId = @id AND LockCookie = @lockCookie
    RETURN 0
GO
            
CREATE PROCEDURE TempResetTimeout
    @id     tSessionId
AS
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE())
    WHERE SessionId = @id
    RETURN 0
GO
            
CREATE PROCEDURE DeleteExpiredSessions
AS
    DECLARE @now DATETIME
    SET @now = GETDATE()

    DELETE tempdb..ASPStateTempSessions
    WHERE Expires < @now

    RETURN 0
GO
            
EXECUTE CreateTempTables
GO

/* Create the startup procedure */
USE master
GO

CREATE PROCEDURE ASPState_Startup 
AS
    EXECUTE ASPState..CreateTempTables

    RETURN 0
GO     

EXECUTE sp_procoption @procname='ASPState_Startup', @optionname='startup', @optionvalue='true'

/* Create the job to delete expired sessions */
BEGIN TRANSACTION            
    DECLARE @JobID BINARY(16)  
    DECLARE @ReturnCode INT    
    SELECT @ReturnCode = 0    

    -- Add job category
    IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
        EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

    -- Add the job
    EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
            @job_id = @JobID OUTPUT, 
            @job_name = N'ASPState_Job_DeleteExpiredSessions', 
            @owner_login_name = NULL, 
            @description = N'Deletes expired sessions from the session state database.', 
            @category_name = N'[Uncategorized (Local)]', 
            @enabled = 1, 
            @notify_level_email = 0, 
            @notify_level_page = 0, 
            @notify_level_netsend = 0, 
            @notify_level_eventlog = 0, 
            @delete_level= 0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    -- Add the job steps
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
            @job_id = @JobID,
            @step_id = 1, 
            @step_name = N'ASPState_JobStep_DeleteExpiredSessions', 
            @command = N'EXECUTE DeleteExpiredSessions', 
            @database_name = N'ASPState', 
            @server = N'', 
            @database_user_name = N'', 
            @subsystem = N'TSQL', 
            @cmdexec_success_code = 0, 
            @flags = 0, 
            @retry_attempts = 0, 
            @retry_interval = 1, 
            @output_file_name = N'', 
            @on_success_step_id = 0, 
            @on_success_action = 1, 
            @on_fail_step_id = 0, 
            @on_fail_action = 2

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    -- Add the job schedules
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
            @job_id = @JobID, 
            @name = N'ASPState_JobSchedule_DeleteExpiredSessions', 
            @enabled = 1, 
            @freq_type = 4,     
            @active_start_date = 20001016, 
            @active_start_time = 0, 
            @freq_interval = 1, 
            @freq_subday_type = 4, 
            @freq_subday_interval = 1, 
            @freq_relative_interval = 0, 
            @freq_recurrence_factor = 0, 
            @active_end_date = 99991231, 
            @active_end_time = 235959

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    -- Add the Target Servers
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    COMMIT TRANSACTION          
    GOTO   EndSave              
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 
GO

然后修改你的web.config文件，指定Session的mode为SQL Server

将web.config的sessionState部分改成：

创建Asp.Net Web Forms

下面就建立一个测试的ASP.net程序，使用Session程序这里就不用多说了，下面是我的程序的截图。这个程序只是简单的储存一个字符串数据于Session中，然后再显示这个数据在Label控件中。

现在所有的Session变量都储存在数据表中，而不是内存中了。你可打开ASPStateTempSessions表来查看这些Session数据了。

删除这些数据库和表

如果你不喜欢这个数据储存方式，看得实在是不爽，那么你可以把这些表和数据库完全删除掉。这个也不要担心这种删除会影响数据库（因为害怕误删除一些数据），因为微软同样也得供给你们一个删除SQL 文件，名叫UnintallSQLState.sql。它与IntallSQLState.sql一样放在.Net的Config目录中。

转载于:https://www.cnblogs.com/feelboy/archive/2010/08/20/1804711.html

weixin_30642267

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
asp.net 将Session值储存于SQL Server中

如果想把session放入sql数据库中我们需要在数据库中创建一个ASPState 这个表是微软自己提供的，里面有很全的SQL语句，大家放心使用。下图就是生成的数据表。下面放出sql代码：直接拷贝执行即可USE masterGO/* Drop the database containing our sprocs */IF DB_ID('ASPState') IS...
复制链接

扫一扫