解决asp.net丢失session的方法文件

我想此时不妨使用字符串参数来帮助我们解决这种情况,利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子:
假设现在给你一个产品信息列表,现在我想要根据所选择商品进行统计,例如统计出价格10,11-20,21-30,31-40,41-50,50以上的商品个有多少个。此时如果使用存储过程就势必需要传入所选商品的id作为参数,但是id个数是不固定的。此时估计会有人这样写:
复制代码 代码如下: SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO —
============================================= — Author: jianxin160 —
Create date: 2010.11.05 — Description: 统计商品 —
============================================= ALTER PROCEDURE
StatProductInfo ( @ids VARCHAR(8000) ) AS BEGIN DECLARE @followingTen
INT DECLARE @elevenToTwenty INT DECLARE @twentyOneToThirty INT DECLARE
@thirtyOneToFourty INT DECLARE @fourtyOneToFifty INT DECLARE
@fiftyOrMore INT SELECT @followingTen=COUNT(*) FROM dbo.Products WHERE
ProductID IN(@ids) AND UnitPrice10 SELECT @elevenToTwenty=COUNT(*) FROM
dbo.Products WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 11 AND 20
SELECT @twentyOneToThirty=COUNT(*) FROM dbo.Products WHERE ProductID
IN(@ids) AND UnitPrice BETWEEN 21 AND 30 SELECT
@thirtyOneToFourty=COUNT(*) FROM dbo.Products WHERE ProductID IN(@ids)
AND UnitPrice BETWEEN 31 AND 40 SELECT @fourtyOneToFifty=COUNT(*) FROM
dbo.Products WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 41 AND 50
SELECT @fiftyOrMore=COUNT(*) FROM dbo.Products WHERE ProductID IN(@ids)
AND UnitPrice50 SELECT @followingTen AS ‘$10’,@elevenToTwenty AS
‘$11-$20’, @twentyOneToThirty AS ‘$21-$30’,@thirtyOneToFourty AS
‘$31-$40’, @fourtyOneToFifty AS ‘$41-$50’,@fiftyOrMore AS ‘$50′ END GO
其实如果你测试一下是有问题的,sql
server认为这整个是一个参数,转换时出错。此时我们想一下如果这些字段在一个虚表中就容易操作多了,但是一般虚表是有其他表通过查询得到,现在根本无法查询又哪来的虚表呢?聪明的朋友或许已经想到可以使用”表值函数”。对,答案就是使用”表值函数”。我们知道”表值函数”可以返回一个”Table”类型的变量,我们首先将字符串分割存放到”表值函数”的一个字段中,然后我们再从”表值函数”中查询就可以了。具体sql如下:
复制代码 代码如下: SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO —
============================================= — Author: cmj — Create
date: 2010.11.05 — Description:
返回一个Table,只有一列,每一行的数据就是分割好的字符串 —
============================================= CREATE FUNCTION
GetSplitFieldsByString ( @toSplitString varchar(1000), @splitChar
varchar(10) ) RETURNS @tb TABLE(sp varchar(100)) AS BEGIN DECLARE @i INT
SET @toSplitString=RTRIM(LTRIM(@toSplitString)) SET
@i=CHARINDEX(@splitChar,@toSplitString) WHILE @i0 BEGIN INSERT @tb
VALUES(LEFT(@toSplitString,@i-1)) SET
@toSplitString=RIGHT(@toSplitString,LEN(@toSplitString)-@i) SET
@i=CHARINDEX(@splitChar,@toSplitString) END IF LEN(@toSplitString)0
INSERT @tb VALUES(@toSplitString) RETURN END GO
然后我们稍微修改一下存储过程: 复制代码
代码如下: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO —
============================================= — Author: jianxin160 —
Create date: 2010.11.05 — Description: 统计商品 —
============================================= ALTER PROCEDURE
StatProductInfo ( @ids VARCHAR(8000) ) AS BEGIN DECLARE @followingTen
INT DECLARE @elevenToTwenty INT DECLARE @twentyOneToThirty INT DECLARE
@thirtyOneToFourty INT DECLARE @fourtyOneToFifty INT DECLARE
@fiftyOrMore INT SELECT @followingTen=COUNT(*) FROM dbo.Products WHERE
ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,’,’)) AND
UnitPrice10 SELECT @elevenToTwenty=COUNT(*) FROM dbo.Products WHERE
ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,’,’)) AND
UnitPrice BETWEEN 11 AND 20 SELECT @twentyOneToThirty=COUNT(*) FROM
dbo.Products WHERE ProductID IN(SELECT sp FROM
dbo.GetSplitFieldsByString(@ids,’,’)) AND UnitPrice BETWEEN 21 AND 30
SELECT @thirtyOneToFourty=COUNT(*) FROM dbo.Products WHERE ProductID
IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,’,’)) AND UnitPrice
BETWEEN 31 AND 40 SELECT @fourtyOneToFifty=COUNT(*) FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,’,’))
AND UnitPrice BETWEEN 41 AND 50 SELECT @fiftyOrMore=COUNT(*) FROM
dbo.Products WHERE ProductID IN(SELECT sp FROM
dbo.GetSplitFieldsByString(@ids,’,’)) AND UnitPrice50 SELECT
@followingTen AS ‘$10’,@elevenToTwenty AS ‘$11-$20’,@twentyOneToThirty
AS ‘$21-$30’, @thirtyOneToFourty AS ‘$31-$40’,@fourtyOneToFifty AS
‘$41-$50’,@fiftyOrMore AS ‘$50’ END GO 这样通过执行EXEC dbo .
StatProductInfo ‘3,4,8,10,22’ 就可以得到想要的结果了:
试试这样会不会快一些 复制代码 代码如下:
SELECT SUM(CASE WHEN UnitPrice 10 THEN 1 ELSE 0 END) ‘$10’, SUM(CASE
WHEN UnitPrice BETWEEN 11 AND 20 THEN 1 ELSE 0 END) ‘$11-$20’, SUM(CASE
WHEN UnitPrice BETWEEN 21 AND 30 THEN 1 ELSE 0 END) ‘$21-$30’, …
SUM(CASE WHEN UnitPrice 50 THEN 1 ELSE 0 END) ‘$10′ FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,’,’))

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO
QuitWithRollback 

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()

RETURN 0
GO

CREATE PROCEDURE TempRemoveStateItem
@id tSessionId,
@lockCookie INT
AS
DELETE tempdb..ASPStateTempSessions
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO

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

— 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 

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

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

RETURN 0
GO

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

RETURN 0
GO

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

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

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

RETURN 0
GO

/* Create the startup procedure */
USE master
GO

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,
)

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

CREATE PROCEDURE ResetData
AS
EXECUTE DropTempTables
EXECUTE CreateTempTables
RETURN 0
GO

— 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

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

/* 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

USE ASPstate
GO

RETURN 0
GO

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

CREATE PROCEDURE TempResetTimeout
@id tSessionId
AS
UPDATE tempdb..ASPStateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE())
WHERE SessionId = @id
RETURN 0
GO

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

RETURN 0
GO

RETURN 0
GO

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

RETURN 0
GO

EXECUTE sp_addtype tSessionId, ‘CHAR(32)’, ‘NOT NULL’
GO

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

RETURN 0
GO

EXECUTE sp_addtype tSessionItemLong, ‘IMAGE’
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

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