ASP.NET将Session保存到数据库中的方法

需要将 ASP.NET 的 Session 保存到数据库中，可以通过如下步骤实现：

步骤1：创建 SQL 数据库表

首先需要在 SQL Server 中创建一个用于存储 Session 数据的表，该表至少需要三个字段：

• SessionId（nvarchar类型）：Session的唯一标识符。
• Expires（datetime类型）：Session的过期时间。
• SessionItem（ntext类型）：Session数据。

可以使用如下的 SQL 语句进行创建：

CREATE TABLE [dbo].[Sessions]
(
    [SessionId] [nvarchar](450) NOT NULL,
    [Expires] [datetime] NOT NULL,
    [SessionItem] [ntext] NULL,
    CONSTRAINT [PK_Sessions] PRIMARY KEY CLUSTERED 
    (
        [SessionId] ASC
    )
)

步骤2：配置 Web.config 文件

需要在 Web.config 文件中配置 <sessionState> 节点，将 mode 属性设置成 SQLServer，并指定数据库连接字符串和表名，例如：

<sessionState 
    mode="SQLServer"
    sqlConnectionString="Data Source=localhost;Initial Catalog=SessionDB;Integrated Security=True"
    cookieless="false"
    timeout="20"
    allowCustomSqlDatabase="true"
    sqlCommandTimeout="30">
    <providers>
        <add name="SqlSessionStateProvider" type="System.Web.SessionState.SqlSessionStateStore"
            connectionStringName="MySqlConnection"
            sqlCommandTimeout="30"
            useHostingIdentity="false"
            writeExceptionsToEventLog="false" />
    </providers>
</sessionState>

其中的 sqlConnectionString 属性需要根据实际数据库连接信息进行修改。

步骤3：创建自定义 SessionStateStoreProvider

为了将 Session 数据保存到数据库中，我们需要创建自定义的 SessionStateStoreProvider，可以继承 SqlSessionStateStore 类并重写其中的方法。

下面是一个简单的示例：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web.SessionState;

namespace MyWebApp.SessionState
{
    public class SqlServerSessionStateStoreProvider : SqlSessionStateStore
    {
        public SqlServerSessionStateStoreProvider()
        {
        }

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            base.Initialize(name, config);

            // 设置自定义的链接字符串和表名
            string connectionStringName = config["connectionStringName"];
            string tableName = config["table"];

            if (!string.IsNullOrEmpty(connectionStringName) && !string.IsNullOrEmpty(tableName))
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);

                base.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security={2};",
                    builder.DataSource,
                    builder.InitialCatalog,
                    builder.IntegratedSecurity);
                base.CommandTimeout = (int)TimeSpan.FromSeconds((double)base.CommandTimeout).TotalSeconds;
                base.TableName = tableName;
            }
        }

        public override SessionStateStoreData CreateNewStoreData(HttpContext context, int timeout)
        {
            return base.CreateNewStoreData(context, timeout);
        }

        public override void CreateUninitializedItem(HttpContext context, string id, int timeout)
        {
            base.CreateUninitializedItem(context, id, timeout);
        }

        public override void EndRequest(HttpContext context)
        {
            base.EndRequest(context);
        }

        public override void InitializeRequest(HttpContext context)
        {
            base.InitializeRequest(context);
        }

        public override void ReleaseItemExclusive(HttpContext context, string id, object lockId)
        {
            base.ReleaseItemExclusive(context, id, lockId);
        }

        public override void RemoveItem(HttpContext context, string id, object lockId, SessionStateStoreData item)
        {
            base.RemoveItem(context, id, lockId, item);
        }

        public override void ResetItemTimeout(HttpContext context, string id)
        {
            base.ResetItemTimeout(context, id);
        }

        public override void SetAndReleaseItemExclusive(HttpContext context, string id, SessionStateStoreData item, object lockId, bool newItem)
        {
            if (item != null && item.Items.Count > 0)
            {
                string serializedItems = Serialize((SessionStateItemCollection)item.Items);

                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "dbo.SetSessionItem";
                    cmd.Parameters.Add(new SqlParameter("@SessionId", SqlDbType.NVarChar, 450)).Value = id;
                    cmd.Parameters.Add(new SqlParameter("@Timeout", SqlDbType.Int)).Value = (item.Timeout == 0) ? base.Timeout : item.Timeout;
                    cmd.Parameters.Add(new SqlParameter("@SessionItemLong", SqlDbType.NText)).Value = serializedItems;
                    cmd.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit)).Value = false;
                    cmd.Parameters.Add(new SqlParameter("@SessionItemShort", SqlDbType.VarChar, (int)(serialItems.Length))).Value = serializedItems;
                    connection.Open();
                    using (cmd)
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            base.SetAndReleaseItemExclusive(context, id, item, lockId, newItem);
        }

        public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback)
        {
            return base.SetItemExpireCallback(expireCallback);
        }

        public override SessionStateStoreData GetItem(HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actions)
        {
            return base.GetItem(context, id, out locked, out lockAge, out lockId, out actions);
        }

        public override SessionStateStoreData GetItemExclusive(HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actions)
        {
            return base.GetItemExclusive(context, id, out locked, out lockAge, out lockId, out actions);
        }
    }
}

需要注意上述代码中的 connectionStringName 和 table 需要与 Web.config 中对应节点的属性值保持一致。以 connectionStringName 为例，可以在 Web.config 中如下设置：

<connectionStrings>
    <add name="MySqlConnection" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

示例1：保存 Session 数据前缀为 Test 的值到数据库中

Session["Test1"] = "hello";
Session["Test2"] = "world";

// デバック用に全てのSession情報を表示
foreach (string key in Session.Keys)
{
    Response.Write(key + ":" + Session[key] + "<br />\n");
}

Session.RemoveAll();

// プレフィックスがTestのSession情報をSessionストアに保存
foreach(string key in Session.Keys)
{
    if (key.StartsWith("Test"))
    {
        Session[key] = "SavedSession";
    }
}
Session["Test3"] = "data";

示例2：从数据库中恢复保存的 Session 数据

Session.Clear();
string connStr = ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT SessionItem FROM dbo.Sessions WHERE SessionId = @SessionId";
cmd.Parameters.AddWithValue("@SessionId", Session.SessionID);
try
{
    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    if (reader.Read())
    {
        string serializedItems = reader.GetString(0);
        SessionStateItemCollection deserializedItems = Deserialize(serializedItems);
        foreach (string key in deserializedItems.Keys)
        {
            Session[key] = deserializedItems[key];
        }
    }
    reader.Close();
    conn.Close();
}
catch (SqlException ex)
{
    Response.Write(ex.Message);
}