Merhaba Arkadaşlar.
Session verilerini MSSQL server üzerinde kullanmak sitemizde ciddi bir performans artışı ve login sürelerinin istediğiniz ölçüde uzatılmasını sağlar.
Daha önceki yazılarımızda asp.net session verileri mssql üzerinde kullanmayı anlatmıştık fakat sitemiz bir hosting kullanıyorsa kafamıza göre istediğimiz veritabanını ve iznini kullanamamaktayız.
Bu nedenle aşağıdaki adımları izleyerek hosting üzerindeki bir çok sys fonksiyonunu kullanmadan kendi başımıza session verilerini kurabiliriz.
1ADIM :Session databasini kurmak.
-önce hosting panelinden veritabanıadı , kullanıcı adı ve şifre bilgilerini oluşturmalız.
-daha sonra hangi framework kullanıyorsak onun altındaki dosyayı çalıştırmalız
C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S
veritabanı_adı_adresi
-U
kullanıcı_adı -P
sifre -A all -d
database_adı
-----------------------------------------------------------------------------------------------------
Microsoft (R) ASP.NET SQL Kayıt Aracı sürüm 4.0.30319.18408
SQL sunucusuna ASP.NET özelliklerini yüklemek veya bunları kaldırmak için kullan
ılan yönetim yardımcı programı.
Telif hakkı (C) Microsoft Corporation. Tüm hakları saklıdır.
Bu özellikleri ekleme işlemini başlat:
Membership
Profile
RoleManager
Personalization
SqlWebEventProvider
.............
Tamamlandı.
--------------------------------------------------------------------------------------------------------
Şeklinde veritabanını istediğimiz şekilde oluşturuyoruz.
Daha sonra web.config içine aşağıdaki satırları eklemelisiniz (system.web içine eklenmeli)
<system.web>
<sessionState mode="SQLServer" sqlConnectionString="Data Source=Database_adı;Initial Catalog=veritabanı;User ID=kullanıcı;Password=şifre" cookieless="false" timeout="120" />
</system.web>
eğer hosting firmanız oluşturduğunuz veritabanına db_owner yetkisini vermişse herhangi bir sorun yaşamadan siteniz hatasız açılacaktır fakat db_owner yetkisi sa kullanıcında ise ve hosting firmanız yetkiyi vermezse ozaman MSSQL server bazı fonksiyonlarını bizim elle eklememiz gerekli.Bu fonksiyonların başında TempGetVersiyon fonksiyonu bulunmakta.
Aşağıda vermiş olduğum bu stored procedure tüm ihtiyaç duyulan fonksiyonları kendisi eklemekte.
Bu stored procedure Microsoft SQL server Management studio ile çalıştırıp hata almamışsanız.Artık session verilerinizi veritabanı üzerinde tutuyorsunuz demektir.
Türkçe hiç bir yerde bu konuyla ilgili bilgi bulamadığım için ekliyorum.2 günlük hata ayıklamının sonucu yazılmıştır.Umarım işinize yarar.
/*********************************************************************
InstallSqlState.SQL
Installs the tables, and stored procedures necessary for
supporting ASP.NET session state on SQL Azure.
Copyright Microsoft, Inc.
All Rights Reserved.
*********************************************************************/
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[happylifesessionTempSessions]') AND type in (N'U'))
DROP TABLE [dbo].[happylifesessionTempSessions]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[happylifesessionTempApplications]') AND type in (N'U'))
DROP TABLE [dbo].[happylifesessionTempApplications]
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetMajorVersion') AND (type = 'P')))
DROP PROCEDURE [dbo].GetMajorVersion
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'CreateTempTables') AND (type = 'P')))
DROP PROCEDURE [dbo].CreateTempTables
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetVersion') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetVersion
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetHashCode') AND (type = 'P')))
DROP PROCEDURE [dbo].GetHashCode
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetAppID') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetAppID
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetStateItem
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem2') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetStateItem2
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem3') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetStateItem3
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetStateItemExclusive
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive2') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetStateItemExclusive2
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive3') AND (type = 'P')))
DROP PROCEDURE [dbo].TempGetStateItemExclusive3
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempReleaseStateItemExclusive') AND (type = 'P')))
DROP PROCEDURE [dbo].TempReleaseStateItemExclusive
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertUninitializedItem') AND (type = 'P')))
DROP PROCEDURE [dbo].TempInsertUninitializedItem
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemShort') AND (type = 'P')))
DROP PROCEDURE [dbo].TempInsertStateItemShort
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemLong') AND (type = 'P')))
DROP PROCEDURE [dbo].TempInsertStateItemLong
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShort') AND (type = 'P')))
DROP PROCEDURE [dbo].TempUpdateStateItemShort
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShortNullLong') AND (type = 'P')))
DROP PROCEDURE [dbo].TempUpdateStateItemShortNullLong
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLong') AND (type = 'P')))
DROP PROCEDURE [dbo].TempUpdateStateItemLong
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLongNullShort') AND (type = 'P')))
DROP PROCEDURE [dbo].TempUpdateStateItemLongNullShort
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempRemoveStateItem') AND (type = 'P')))
DROP PROCEDURE [dbo].TempRemoveStateItem
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempResetTimeout') AND (type = 'P')))
DROP PROCEDURE [dbo].TempResetTimeout
GO
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'DeleteExpiredSessions') AND (type = 'P')))
DROP PROCEDURE [dbo].DeleteExpiredSessions
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.GetMajorVersion
@@ver int OUTPUT
AS
BEGIN
DECLARE @version
nchar(100)
DECLARE @dot
int
DECLARE @hyphen
int
DECLARE @SqlToExec
nchar(4000)
SELECT @@ver = 7
SELECT @version = @@Version
SELECT @hyphen = CHARINDEX(N' - ', @version)
IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
BEGIN
SELECT @hyphen = @hyphen + 3
SELECT @dot
= CHARINDEX(N'.', @version, @hyphen)
IF (NOT(@dot IS NULL) AND @dot > @hyphen)
BEGIN
SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
SELECT @@ver
= CONVERT(int, @version)
END
END
END
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.CreateTempTables
AS
CREATE TABLE [happylifesession].dbo.happylifesessionTempSessions (
SessionId
nvarchar(88)
NOT NULL PRIMARY KEY,
Created
datetime
NOT NULL DEFAULT GETUTCDATE(),
Expires
datetime
NOT NULL,
LockDate
datetime
NOT NULL,
LockDateLocal
datetime
NOT NULL,
LockCookie
int
NOT NULL,
Timeout
int
NOT NULL,
Locked
bit
NOT NULL,
SessionItemShort
VARBINARY(7000) NULL,
SessionItemLong
image
NULL,
Flags
int
NOT NULL DEFAULT 0,
)
CREATE NONCLUSTERED INDEX Index_Expires ON [happylifesession].dbo.happylifesessionTempSessions(Expires)
CREATE TABLE [happylifesession].dbo.happylifesessionTempApplications (
AppId
int
NOT NULL PRIMARY KEY,
AppName
char(280)
NOT NULL,
)
CREATE NONCLUSTERED INDEX Index_AppName ON [happylifesession].dbo.happylifesessionTempApplications(AppName)
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetVersion
@ver
char(10) OUTPUT
AS
SELECT @ver = "2"
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.GetHashCode
@input varchar(280),
@hash int OUTPUT
AS
/*
This sproc is based on this C# hash function:
int GetHashCode(string s)
{
int
hash = 5381;
int
len = s.Length;
for (int i = 0; i < len; i++) {
int
c = Convert.ToInt32(s[i]);
hash = ((hash << 5) + hash) ^ c;
}
return hash;
}
However, SQL 7 doesn't provide a 32-bit integer
type that allows rollover of bits, we have to
divide our 32bit integer into the upper and lower
16 bits to do our calculation.
*/
DECLARE @hi_16bit int
DECLARE @lo_16bit int
DECLARE @hi_t
int
DECLARE @lo_t
int
DECLARE @len
int
DECLARE @i
int
DECLARE @c
int
DECLARE @carry
int
SET @hi_16bit = 0
SET @lo_16bit = 5381
SET @len = DATALENGTH(@input)
SET @i = 1
WHILE (@i <= @len)
BEGIN
SET @c = ASCII(SUBSTRING(@input, @i, 1))
/* Formula:
hash = ((hash << 5) + hash) ^ c */
/* hash << 5 */
SET @hi_t = @hi_16bit * 32 /* high 16bits << 5 */
SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */
SET @lo_t = @lo_16bit * 32 /* low 16bits << 5 */
SET @carry = @lo_16bit & 0x1F0000 /* move low 16bits carryover to hi 16bits */
SET @carry = @carry / 0x10000 /* >> 16 */
SET @hi_t = @hi_t + @carry
SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */
/* + hash */
SET @lo_16bit = @lo_16bit + @lo_t
SET @hi_16bit = @hi_16bit + @hi_t + (@lo_16bit / 0x10000)
/* delay clearing the overflow */
/* ^c */
SET @lo_16bit = @lo_16bit ^ @c
/* Now clear the overflow bits */
SET @hi_16bit = @hi_16bit & 0xFFFF
SET @lo_16bit = @lo_16bit & 0xFFFF
SET @i = @i + 1
END
/* Do a sign extension of the hi-16bit if needed */
IF (@hi_16bit & 0x8000 <> 0)
SET @hi_16bit = 0xFFFF0000 | @hi_16bit
/* Merge hi and lo 16bit back together */
SET @hi_16bit = @hi_16bit * 0x10000 /* << 16 */
SET @hash = @hi_16bit | @lo_16bit
RETURN 0
GO
/*****************************************************************************/
DECLARE @cmd nchar(4000)
SET @cmd = N'
CREATE PROCEDURE dbo.TempGetAppID
@appName
varchar(280),
@appId
int OUTPUT
AS
SET @appName = LOWER(@appName)
SET @appId = NULL
SELECT @appId = AppId
FROM [happylifesession].dbo.happylifesessionTempApplications
WHERE AppName = @appName
IF @appId IS NULL BEGIN
BEGIN TRAN
SELECT @appId = AppId
FROM [happylifesession].dbo.happylifesessionTempApplications WITH (TABLOCKX)
WHERE AppName = @appName
IF @appId IS NULL
BEGIN
EXEC GetHashCode @appName, @appId OUTPUT
INSERT [happylifesession].dbo.happylifesessionTempApplications
VALUES
(@appId, @appName)
IF @@ERROR = 2627
BEGIN
DECLARE @dupApp varchar(280)
SELECT @dupApp = RTRIM(AppName)
FROM [happylifesession].dbo.happylifesessionTempApplications
WHERE AppId = @appId
RAISERROR(''SQL session state fatal error: hash-code collision between applications ''''%s'''' and ''''%s''''. Please rename the 1st application to resolve the problem.'',
18, 1, @appName, @dupApp)
END
END
COMMIT
END
RETURN 0'
EXEC(@cmd)
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetStateItem
@id
nvarchar(88),
@itemShort varbinary(7000) OUTPUT,
@locked
bit OUTPUT,
@lockDate datetime OUTPUT,
@lockCookie int OUTPUT
AS
DECLARE @length AS int
DECLARE @now AS datetime
SET @now = GETUTCDATE()
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@locked = Locked,
@lockDate = LockDateLocal,
@lockCookie = LockCookie,
@itemShort = CASE @locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@length = CASE @locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
SELECT [SessionItemLong] FROM [happylifesession].dbo.happylifesessionTempSessions WHERE SessionId = @id
END
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetStateItem2
@id
nvarchar(88),
@itemShort varbinary(7000) OUTPUT,
@locked
bit OUTPUT,
@lockAge
int OUTPUT,
@lockCookie int OUTPUT
AS
DECLARE @length AS int
DECLARE @now AS datetime
SET @now = GETUTCDATE()
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@locked = Locked,
@lockAge = DATEDIFF(second, LockDate, @now),
@lockCookie = LockCookie,
@itemShort = CASE @locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@length = CASE @locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
SELECT [SessionItemLong] FROM [happylifesession].dbo.happylifesessionTempSessions WHERE SessionId = @id
END
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetStateItem3
@id
nvarchar(88),
@itemShort varbinary(7000) OUTPUT,
@locked
bit OUTPUT,
@lockAge
int OUTPUT,
@lockCookie int OUTPUT,
@actionFlags int OUTPUT
AS
DECLARE @length AS int
DECLARE @now AS datetime
SET @now = GETUTCDATE()
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@locked = Locked,
@lockAge = DATEDIFF(second, LockDate, @now),
@lockCookie = LockCookie,
@itemShort = CASE @locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@length = CASE @locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END,
/* If the Uninitialized flag (0x1) if it is set,
remove it and return InitializeItem (0x1) in actionFlags */
Flags = CASE
WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
ELSE Flags
END,
@actionFlags = CASE
WHEN (Flags & 1) <> 0 THEN 1
ELSE 0
END
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
SELECT [SessionItemLong] FROM [happylifesession].dbo.happylifesessionTempSessions WHERE SessionId = @id
END
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetStateItemExclusive
@id
nvarchar(88),
@itemShort varbinary(7000) OUTPUT,
@locked
bit OUTPUT,
@lockDate datetime OUTPUT,
@lockCookie int OUTPUT
AS
DECLARE @length AS int
DECLARE @now AS datetime
DECLARE @nowLocal AS datetime
SET @now = GETUTCDATE()
SET @nowLocal = GETDATE()
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, @now),
LockDate = CASE Locked
WHEN 0 THEN @now
ELSE LockDate
END,
@lockDate = LockDateLocal = CASE Locked
WHEN 0 THEN @nowLocal
ELSE LockDateLocal
END,
@lockCookie = LockCookie = CASE Locked
WHEN 0 THEN LockCookie + 1
ELSE LockCookie
END,
@itemShort = CASE Locked
WHEN 0 THEN SessionItemShort
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
SELECT [SessionItemLong] FROM [happylifesession].dbo.happylifesessionTempSessions WHERE SessionId = @id
END
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetStateItemExclusive2
@id
nvarchar(88),
@itemShort varbinary(7000) OUTPUT,
@locked
bit OUTPUT,
@lockAge
int OUTPUT,
@lockCookie int OUTPUT
AS
DECLARE @length AS int
DECLARE @now AS datetime
DECLARE @nowLocal AS datetime
SET @now = GETUTCDATE()
SET @nowLocal = GETDATE()
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, @now),
LockDate = CASE Locked
WHEN 0 THEN @now
ELSE LockDate
END,
LockDateLocal = CASE Locked
WHEN 0 THEN @nowLocal
ELSE LockDateLocal
END,
@lockAge = CASE Locked
WHEN 0 THEN 0
ELSE DATEDIFF(second, LockDate, @now)
END,
@lockCookie = LockCookie = CASE Locked
WHEN 0 THEN LockCookie + 1
ELSE LockCookie
END,
@itemShort = CASE Locked
WHEN 0 THEN SessionItemShort
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
SELECT [SessionItemLong] FROM [happylifesession].dbo.happylifesessionTempSessions WHERE SessionId = @id
END
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetStateItemExclusive3
@id
nvarchar(88),
@itemShort varbinary(7000) OUTPUT,
@locked
bit OUTPUT,
@lockAge
int OUTPUT,
@lockCookie int OUTPUT,
@actionFlags int OUTPUT
AS
DECLARE @length AS int
DECLARE @now AS datetime
DECLARE @nowLocal AS datetime
SET @now = GETUTCDATE()
SET @nowLocal = GETDATE()
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, @now),
LockDate = CASE Locked
WHEN 0 THEN @now
ELSE LockDate
END,
LockDateLocal = CASE Locked
WHEN 0 THEN @nowLocal
ELSE LockDateLocal
END,
@lockAge = CASE Locked
WHEN 0 THEN 0
ELSE DATEDIFF(second, LockDate, @now)
END,
@lockCookie = LockCookie = CASE Locked
WHEN 0 THEN LockCookie + 1
ELSE LockCookie
END,
@itemShort = CASE Locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@length = CASE Locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END,
@locked = Locked,
Locked = 1,
/* If the Uninitialized flag (0x1) if it is set,
remove it and return InitializeItem (0x1) in actionFlags */
Flags = CASE
WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
ELSE Flags
END,
@actionFlags = CASE
WHEN (Flags & 1) <> 0 THEN 1
ELSE 0
END
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
SELECT SessionItemLong FROM [happylifesession].dbo.happylifesessionTempSessions WHERE SessionId = @id
END
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempReleaseStateItemExclusive
@id
nvarchar(88),
@lockCookie int
AS
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, GETUTCDATE()),
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempInsertUninitializedItem
@id
nvarchar(88),
@itemShort varbinary(7000),
@timeout
int
AS
DECLARE @now AS datetime
DECLARE @nowLocal AS datetime
SET @now = GETUTCDATE()
SET @nowLocal = GETDATE()
INSERT [happylifesession].dbo.happylifesessionTempSessions
(SessionId,
SessionItemShort,
Timeout,
Expires,
Locked,
LockDate,
LockDateLocal,
LockCookie,
Flags)
VALUES
(@id,
@itemShort,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
@nowLocal,
1,
1)
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempInsertStateItemShort
@id
nvarchar(88),
@itemShort varbinary(7000),
@timeout
int
AS
DECLARE @now AS datetime
DECLARE @nowLocal AS datetime
SET @now = GETUTCDATE()
SET @nowLocal = GETDATE()
INSERT [happylifesession].dbo.happylifesessionTempSessions
(SessionId,
SessionItemShort,
Timeout,
Expires,
Locked,
LockDate,
LockDateLocal,
LockCookie)
VALUES
(@id,
@itemShort,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
@nowLocal,
1)
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempInsertStateItemLong
@id
nvarchar(88),
@itemLong image,
@timeout
int
AS
DECLARE @now AS datetime
DECLARE @nowLocal AS datetime
SET @now = GETUTCDATE()
SET @nowLocal = GETDATE()
INSERT [happylifesession].dbo.happylifesessionTempSessions
(SessionId,
SessionItemLong,
Timeout,
Expires,
Locked,
LockDate,
LockDateLocal,
LockCookie)
VALUES
(@id,
@itemLong,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
@nowLocal,
1)
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempUpdateStateItemShort
@id
nvarchar(88),
@itemShort varbinary(7000),
@timeout
int,
@lockCookie int
AS
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
SessionItemShort = @itemShort,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempUpdateStateItemShortNullLong
@id
nvarchar(88),
@itemShort varbinary(7000),
@timeout
int,
@lockCookie int
AS
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
SessionItemShort = @itemShort,
SessionItemLong = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempUpdateStateItemLong
@id
nvarchar(88),
@itemLong image,
@timeout
int,
@lockCookie int
AS
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
SessionItemLong = @itemLong,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempUpdateStateItemLongNullShort
@id
nvarchar(88),
@itemLong image,
@timeout
int,
@lockCookie int
AS
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
SessionItemLong = @itemLong,
SessionItemShort = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempRemoveStateItem
@id
nvarchar(88),
@lockCookie int
AS
DELETE [happylifesession].dbo.happylifesessionTempSessions
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.TempResetTimeout
@id
nvarchar(88)
AS
UPDATE [happylifesession].dbo.happylifesessionTempSessions
SET Expires = DATEADD(n, Timeout, GETUTCDATE())
WHERE SessionId = @id
RETURN 0
GO
/*****************************************************************************/
CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
DECLARE @now datetime
SET @now = GETUTCDATE()
DELETE [happylifesession].dbo.happylifesessionTempSessions
WHERE Expires < @now
RETURN 0
GO
/*****************************************************************************/
EXECUTE dbo.CreateTempTables
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
PRINT ''
PRINT '------------------------------------------'
PRINT 'Completed execution of InstallSqlState.SQL'
PRINT '------------------------------------------'
IF NOT EXISTS(SELECT NAME FROM sys.sysusers where name='happylifesession')
BEGIN
CREATE USER [happylifesession] FOR LOGIN [happylifesession]
END
GO
EXEC sp_addrolemember N'db_datawriter', N'happylifesession'
GO
EXEC sp_addrolemember N'db_datareader', N'happylifesession'
GO
EXEC sp_addrolemember N'db_owner', N'happylifesession'
GO
