获取SQL Server 数据库中Session的值

在asp.net 中，session可存储于数据库，但怎样在其他应用中获取数据库存放session的值呢？

怎样把session存储在数据库中的参考文章 http://blog.csdn.net/ojekleen/article/details/7377126 

现再展示数据库中存储session数据模型

      ASP.NET Session状态数据库数据模型
　　1.ASPStateTempSessions表定义

列名	类型	描述
SessionId	nvarchar(88)	Session ID + application ID
Created	datetime	Date and time session was created (UTC)
Expires	datetime	Date and time session expires (UTC)
LockDate	datetime	UTC date and time session was locked
LockDateLocal	datetime	Local date and time session was locked
LockCookie	int	Lock ID
Timeout	int	Session timeout in minutes
Locked	bit	1=Session locked, 0=Session not locked
SessionItemShort	varbinary(7000)	Serialized session state (if <= 7,000 bytes)
SessionItemLong	image	Serialized session state (if > 7,000 bytes)
Flags	int	Session state flags (1=Uninitialized session)

　　2.ASPStateTempApplications表定义

列名	类型	描述
AppId	int	Application ID
AppName	char(280)	Application name

表aspstatetempsessions存储session具体信息 ,sessionitemshort或者sessionitemlong存储session序列化后的值。

session序列化状态后的值t在小于或等于7000字节的情况下存储在sessionitemshor字段，当session序列化状态后的值长于7000则存储于sessionitemlong字段中。sessionid为网站中的sessionid 加上 aspstatetempapplications中的appid, appid为标准8位，在数据库中的sessionid的后8位为appid.即substring(sessionid,1,8)与网站中sessionid比对就为相同， 当网站执行 Session.Abandon(); 后，aspstatetempsessions表中会自动删除相应数据行。

以下代码为根据网站sessionid查询aspstatetempsessions表获取session值。

[csharp] view plain copy print ?

1. /// <summary>   
2.    /// aspstatetempsessions表映射实体   
3.    /// </summary>   
4.    public class SSOSessionEntity  
5.    {  
6.        private string sessionId;  
7.        private DateTime created;  
8.        private DateTime expires;  
9.        private DateTime lockDate;  
10.        private DateTime lockDateLocal;  
11.        private int lockCookie;  
12.        private int timeOut;  
13.        private bool locked;  
14.        private byte[] sessionItemShort;  
15.        private byte[] sessionItemLong;  
16.        private int flags;  
17.   
18.        public string SessionId  
19.        {  
20.            get { return this.sessionId; }  
21.            set { this.sessionId = value; }  
22.        }  
23.   
24.        public DateTime Created  
25.        {  
26.            get { return this.created; }  
27.            set { this.created=value; }  
28.        }  
29.   
30.        public DateTime Expires  
31.        {  
32.            get { return this.expires; }  
33.            set { this.expires = value; }  
34.        }  
35.   
36.        public DateTime LockDate  
37.        {  
38.            get { return this.lockDate; }  
39.            set { this.lockDate = value; }  
40.        }  
41.   
42.        public DateTime LockDateLocal  
43.        {  
44.            get { return this.lockDateLocal; }  
45.            set { this.lockDateLocal = value; }  
46.        }  
47.   
48.        public int LockCooKie  
49.        {  
50.            get { return this.lockCookie; }  
51.            set { this.lockCookie = value; }  
52.        }  
53.   
54.        public int TimeOut  
55.        {  
56.            get { return this.timeOut; }  
57.            set { this.timeOut = value; }  
58.        }  
59.   
60.        public bool Locked  
61.        {  
62.            get { return this.locked; }  
63.            set { this.locked = value; }  
64.        }  
65.   
66.        public byte[] SessionItemShort  
67.        {  
68.            get { return this.sessionItemShort; }  
69.            set { this.sessionItemShort = value; }  
70.        }  
71.   
72.        public byte[] SessionItemLong  
73.        {  
74.            get { return this.sessionItemLong; }  
75.            set { this.sessionItemLong = value; }  
76.        }  
77.   
78.        public int Flags  
79.        {  
80.            get { return this.flags; }  
81.            set { this.flags = value; }  
82.        }  
83.    }  

 /// <summary>
    /// aspstatetempsessions表映射实体
    /// </summary>
    public class SSOSessionEntity
    {
        private string sessionId;
        private DateTime created;
        private DateTime expires;
        private DateTime lockDate;
        private DateTime lockDateLocal;
        private int lockCookie;
        private int timeOut;
        private bool locked;
        private byte[] sessionItemShort;
        private byte[] sessionItemLong;
        private int flags;

        public string SessionId
        {
            get { return this.sessionId; }
            set { this.sessionId = value; }
        }

        public DateTime Created
        {
            get { return this.created; }
            set { this.created=value; }
        }

        public DateTime Expires
        {
            get { return this.expires; }
            set { this.expires = value; }
        }

        public DateTime LockDate
        {
            get { return this.lockDate; }
            set { this.lockDate = value; }
        }

        public DateTime LockDateLocal
        {
            get { return this.lockDateLocal; }
            set { this.lockDateLocal = value; }
        }

        public int LockCooKie
        {
            get { return this.lockCookie; }
            set { this.lockCookie = value; }
        }

        public int TimeOut
        {
            get { return this.timeOut; }
            set { this.timeOut = value; }
        }

        public bool Locked
        {
            get { return this.locked; }
            set { this.locked = value; }
        }

        public byte[] SessionItemShort
        {
            get { return this.sessionItemShort; }
            set { this.sessionItemShort = value; }
        }

        public byte[] SessionItemLong
        {
            get { return this.sessionItemLong; }
            set { this.sessionItemLong = value; }
        }

        public int Flags
        {
            get { return this.flags; }
            set { this.flags = value; }
        }
    }

[csharp] view plain copy print ?

1. public interface ISessionDal  
2.     {  
3.         /// <summary>   
4.         /// 通过sessionid获取实体，具体实现为查询数据库返回dbdatareader并赋值于实体实例   
5.         /// sql: select * from dbo.ASPStateTempSessions where substring(sessionid,1,len(sessionid)-8)=@sessionid   
6.         /// </summary>   
7.         /// <param name="sessionId">sessionid</param>   
8.         /// <returns>SSOSessionEntity实例</returns>   
9.         SSOSessionEntity GetEntity(string sessionId);  
10.     }  

public interface ISessionDal
    {
        /// <summary>
        /// 通过sessionid获取实体，具体实现为查询数据库返回dbdatareader并赋值于实体实例
        /// sql: select * from dbo.ASPStateTempSessions where substring(sessionid,1,len(sessionid)-8)=@sessionid
        /// </summary>
        /// <param name="sessionId">sessionid</param>
        /// <returns>SSOSessionEntity实例</returns>
        SSOSessionEntity GetEntity(string sessionId);
    }

关键问题怎样把sessionitem反序列化。代码如下：

[csharp] view plain copy print ?

1. /// <summary>   
2.         /// 通过key获取session   
3.         /// </summary>   
4.         /// <param name="sentity">SSOSessionEntity实例</param>   
5.         /// <param name="sessionkey">Sesssin["sessionkey"]获取中的key</param>   
6.         /// <returns>Session[""]获取的值，未类型转换</returns>   
7.         public object GetSession(SSOSessionEntity sentity,string sessionkey)  
8.         {  
9.             object obj = null;  
10.   
11.             System.IO.MemoryStream stream = new System.IO.MemoryStream();  
12.             System.IO.BinaryReader reader = new System.IO.BinaryReader(stream);  
13.             stream.SetLength(0);  
14.   
15.             if (sentity.SessionItemShort != null)  
16.                 stream.Write(sentity.SessionItemShort, 0, sentity.SessionItemShort.Length);  
17.             else  
18.                 stream.Write(sentity.SessionItemLong, 0, sentity.SessionItemLong.Length);  
19.   
20.             stream.Seek(0, System.IO.SeekOrigin.Begin);  
21.             reader.ReadInt32();  
22.             bool bol_flag = reader.ReadBoolean();  
23.             reader.ReadBoolean();  
24.             if (bol_flag)  
25.             {  
26.                 System.Web.SessionState.SessionStateItemCollection sessionItems = System.Web.SessionState.SessionStateItemCollection.Deserialize(reader);  
27.                 foreach (string key in sessionItems.Keys)//当前sessionid中所有session   
28.                 {  
29.                     if (sessionkey.Equals(key))  
30.                     {  
31.                         obj = sessionItems[key];  
32.                         break;  
33.                     }  
34.                 }  
35.             }  
36.   
37.             return obj;  
38.         }  

/// <summary>
        /// 通过key获取session
        /// </summary>
        /// <param name="sentity">SSOSessionEntity实例</param>
        /// <param name="sessionkey">Sesssin["sessionkey"]获取中的key</param>
        /// <returns>Session[""]获取的值，未类型转换</returns>
        public object GetSession(SSOSessionEntity sentity,string sessionkey)
        {
            object obj = null;

            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            System.IO.BinaryReader reader = new System.IO.BinaryReader(stream);
            stream.SetLength(0);

            if (sentity.SessionItemShort != null)
                stream.Write(sentity.SessionItemShort, 0, sentity.SessionItemShort.Length);
            else
                stream.Write(sentity.SessionItemLong, 0, sentity.SessionItemLong.Length);

            stream.Seek(0, System.IO.SeekOrigin.Begin);
            reader.ReadInt32();
            bool bol_flag = reader.ReadBoolean();
            reader.ReadBoolean();
            if (bol_flag)
            {
                System.Web.SessionState.SessionStateItemCollection sessionItems = System.Web.SessionState.SessionStateItemCollection.Deserialize(reader);
                foreach (string key in sessionItems.Keys)//当前sessionid中所有session
                {
                    if (sessionkey.Equals(key))
                    {
                        obj = sessionItems[key];
                        break;
                    }
                }
            }

            return obj;
        }

在编写同域名单点登录时，可在登录点创建sessionid和其他可公开的验证信息的cookie，在其他二级域名验证是否登录时， 可先获取cookie中sessionid及其他公开信息，查询数据库是否具有当前sessionid并进行比较，获取session值后可进行其他公开信息进一步验证。其他公开信息比如：用户名或者用户编号。