asp.net 將Session值儲存於SQL Server中

如果想把session放入sql資料庫中我們需要在資料庫中建立一個ASPState 這個表是微軟自己提供的，裡面有很全的SQL語句，大家放心使用。就是產生的資料表。

下面放出sql代碼：直接拷貝執行即可

USE masterGO/* Drop the database containing our sprocs */IF DB_ID('ASPState') IS NOT NULL BEGIN    DROP DATABASE ASPStateENDGO/* Drop temporary tables */IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempSessions' AND type = 'U') BEGIN    DROP TABLE tempdb..ASPStateTempSessionsENDGOIF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempApplications' AND type = 'U') BEGIN    DROP TABLE tempdb..ASPStateTempApplicationsENDGO/* Drop the startup procedure */DECLARE @PROCID intSET @PROCID = OBJECT_ID('ASPState_Startup') IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN    DROP PROCEDURE ASPState_Startup ENDGO/* Drop the obsolete startup enabler */DECLARE @PROCID intSET @PROCID = OBJECT_ID('EnableASPStateStartup') IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN    DROP PROCEDURE EnableASPStateStartupENDGO/* Drop the obsolete startup disabler */DECLARE @PROCID intSET @PROCID = OBJECT_ID('DisableASPStateStartup') IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN    DROP PROCEDURE DisableASPStateStartupENDGO/* 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' ENDUSE masterGO/* Create and populate the ASPState database */CREATE DATABASE ASPStateGOUSE ASPstateGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOCREATE PROCEDURE DropTempTablesAS    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 0GO    CREATE PROCEDURE CreateTempTablesAS    /*     * 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 0GO     CREATE PROCEDURE ResetDataAS    EXECUTE DropTempTables    EXECUTE CreateTempTables    RETURN 0GO   EXECUTE sp_addtype tSessionId, 'CHAR(32)',  'NOT NULL'GOEXECUTE sp_addtype tAppName, 'VARCHAR(280)', 'NOT NULL'GOEXECUTE sp_addtype tSessionItemShort, 'VARBINARY(7000)'GOEXECUTE sp_addtype tSessionItemLong, 'IMAGE'GOEXECUTE sp_addtype tTextPtr, 'VARBINARY(16)'GOCREATE PROCEDURE TempGetAppId    @appName    tAppName,    @appId      INT OUTPUTAS    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 0GOCREATE PROCEDURE TempGetStateItem    @id         tSessionId,    @itemShort  tSessionItemShort OUTPUT,    @locked     BIT OUTPUT,    @lockDate   DATETIME OUTPUT,    @lockCookie INT OUTPUTAS    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 0GOCREATE PROCEDURE TempGetStateItemExclusive    @id         tSessionId,    @itemShort  tSessionItemShort OUTPUT,    @locked     BIT OUTPUT,    @lockDate   DATETIME OUTPUT,    @lockCookie INT OUTPUTAS    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 0GOCREATE PROCEDURE TempReleaseStateItemExclusive    @id         tSessionId,    @lockCookie INTAS    UPDATE tempdb..ASPStateTempSessions    SET Expires = DATEADD(n, Timeout, GETDATE()),         Locked = 0    WHERE SessionId = @id AND LockCookie = @lockCookie    RETURN 0GOCREATE PROCEDURE TempInsertStateItemShort    @id         tSessionId,    @itemShort  tSessionItemShort,    @timeout    INTAS       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 0GOCREATE PROCEDURE TempInsertStateItemLong    @id         tSessionId,    @itemLong   tSessionItemLong,    @timeout    INTAS        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 0GOCREATE PROCEDURE TempUpdateStateItemShort    @id         tSessionId,    @itemShort  tSessionItemShort,    @timeout    INT,    @lockCookie INTAS        UPDATE tempdb..ASPStateTempSessions    SET Expires = DATEADD(n, Timeout, GETDATE()),         SessionItemShort = @itemShort,         Timeout = @timeout,        Locked = 0    WHERE SessionId = @id AND LockCookie = @lockCookie    RETURN 0GOCREATE PROCEDURE TempUpdateStateItemShortNullLong    @id         tSessionId,    @itemShort  tSessionItemShort,    @timeout    INT,    @lockCookie INTAS        UPDATE tempdb..ASPStateTempSessions    SET Expires = DATEADD(n, Timeout, GETDATE()),         SessionItemShort = @itemShort,         SessionItemLong = NULL,         Timeout = @timeout,        Locked = 0    WHERE SessionId = @id AND LockCookie = @lockCookie    RETURN 0GOCREATE PROCEDURE TempUpdateStateItemLong    @id         tSessionId,    @itemLong   tSessionItemLong,    @timeout    INT,    @lockCookie INTAS        UPDATE tempdb..ASPStateTempSessions    SET Expires = DATEADD(n, Timeout, GETDATE()),         SessionItemLong = @itemLong,        Timeout = @timeout,        Locked = 0    WHERE SessionId = @id AND LockCookie = @lockCookie    RETURN 0GOCREATE PROCEDURE TempUpdateStateItemLongNullShort    @id         tSessionId,    @itemLong   tSessionItemLong,    @timeout    INT,    @lockCookie INTAS        UPDATE tempdb..ASPStateTempSessions    SET Expires = DATEADD(n, Timeout, GETDATE()),         SessionItemLong = @itemLong,         SessionItemShort = NULL,        Timeout = @timeout,        Locked = 0    WHERE SessionId = @id AND LockCookie = @lockCookie    RETURN 0GOCREATE PROCEDURE TempRemoveStateItem    @id     tSessionId,    @lockCookie INTAS    DELETE tempdb..ASPStateTempSessions    WHERE SessionId = @id AND LockCookie = @lockCookie    RETURN 0GO            CREATE PROCEDURE TempResetTimeout    @id     tSessionIdAS    UPDATE tempdb..ASPStateTempSessions    SET Expires = DATEADD(n, Timeout, GETDATE())    WHERE SessionId = @id    RETURN 0GO            CREATE PROCEDURE DeleteExpiredSessionsAS    DECLARE @now DATETIME    SET @now = GETDATE()    DELETE tempdb..ASPStateTempSessions    WHERE Expires < @now    RETURN 0GO            EXECUTE CreateTempTablesGO/* Create the startup procedure */USE masterGOCREATE PROCEDURE ASPState_Startup AS    EXECUTE ASPState..CreateTempTables    RETURN 0GO     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部分改成：

<sessionState mode="SQLServer" sqlConnectionString="datasourse=.;userid=sa;password=" cookieless= "false"timeout="20" />

建立Asp.Net Web Forms

下面就建立一個測試的ASP.net程式，使用Session程式這裡就不用多說了，下面是我的程式的。這個程式只是簡單的儲存一個字串資料於Session中，然後再顯示這個資料在Label控制項中。

現在所有的Session變數都儲存在資料表中，而不是記憶體中了。你可開啟ASPStateTempSessions表來查看這些Session資料了。

刪除這些資料庫和表

如果你不喜歡這個資料儲存方式，看得實在是不爽，那麼你可以把這些表和資料庫完全刪除掉。這個也不要擔心這種刪除會影響資料庫（因為害怕誤刪除一些資料），因為微軟同樣也得供給你們一個刪除SQL 檔案，名叫UnintallSQLState.sql。它與IntallSQLState.sql一樣放在.Net的Config目錄中。