Asp.net stores session values in SQL Server

If you want to put the session into the SQL database, you need to create an aspstate table in the database. This table is provided by Microsoft and contains a wide range of SQL statements. Is the generated data table.

The SQL statement is released belowCode: Directly copy and execute.

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 = 'aspstatetempsession' 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 ('enablesaspstatestartup ') if @ procid is not null and objectproperty (@ procid, 'isprocedure ') = 1 begin drop procedure tables/* Drop the obsolete startup disabler */declare @ procid intset @ procid = object_id ('disableaspstatestartup') If @ procid is not null and objectproperty (@ procid, 'isprocessed') = 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 _ exist' Enduse mastergo/* Create and populate the aspstate database */create database aspstategouse aspstategoset expiration off goset expiration on gocreate procedure droptemptablesas if exists (select * from tempdb .. sysobjects where name = 'aspstatetempsession' 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 .. values (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 limit, '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 0 gocreate 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 0 gocreate 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 = case locked when 0 then @ now else lockdate end, @ 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 gocreate procedure tempreleasestateitemexclusive @ ID tsessionid, @ lockcookie INTAS update tempdb .. aspstatetempsessions set expires = dateadd (n, timeout, getdate (), locked = 0 where sessionid = @ ID and lockcookie = @ lockcookie return 0 gocreate 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 0 gocreate 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 0 gocreate 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 0 gocreate 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 0 gocreate 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 0 gocreate 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 0 gocreate 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, @ yy_level_email = 0, @ yy_level_page = 0, @ resolve = 0, @ yy_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 _ callback', @ command = n 'execute deleteexpiredsession', @ 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 _ dynamic ', @ 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

Modify your web. config file and set the session mode to SQL Server.

Change the sessionstate section of Web. config:

<Sessionstate mode = "sqlserver" sqlconnectionstring = "sponse =.; userid = sa; Password =" cookieless = "false" timeout = "20"/>

Create ASP. NET web forms

Next we will create a test Asp.netProgramYou don't need to talk about using the session program. below is my program. This program simply stores a string of data in the session, and then displays the data in the label control.

Now all session variables are stored in the data table, rather than in the memory. You can open the aspstatetempsessions table to view the session data.

Delete these databases and tables

If you don't like the data storage method, you can delete the tables and databases. Do not worry that such deletion will affect the database (because you are afraid to delete some data by mistake), because Microsoft also has to provide you with a delete SQL file named unintallsqlstate. SQL. It is placed in the config directory of. net like intallsqlstate. SQL.