Store View State in SQL Server

• Download source - 110 Kb

Introduction

Storing view state in SQL Server is a pretty old game by now, but I think another take on the subject could not hurt. The code in this article is intended mainly for large scale web sites with lots of web pages and lots of users. In these conditions, cutting down on view states that go back and forth between client and server is a must for any web site. Most of the code in this article is bits and pieces from others but there are some things in here that I didn't find anywhere else and I've put my own spin on it. This is a tried-and-true code, so I hope it will be of some use to you.

I would be remiss if I didn't mention two articles. The first one is Server Side Viewstate by Robert Boedigheimer from 2003. Like I said, it's an old game. His article is the basis of this code. The second is a related article, which also references Boedigheimer's, Analysis of Keeping ViewState out of the Page by Peter Bromberg from NullSkull.

Before delving into the code, you should know that there is one assumption here and it is that you can set up a SQL Server job. The purpose of this job is to clean expired view states and without it the view state table will simply bloat out of control.

View State Table

We start with the View_State table. The View_State_Key is the unique key column that identifies the stored view state. This key will be injected to the web page instead of the actual view state. The type of the key is string, but if you like otherwise, you can change the type from string to something else (uniqueidentifier) but don't forget to reflect it in the .NET code accordingly. View_State_Value column stores the actual view state string. Insert_Time column is the time signature and is populated with getdate() by default.

create table [dbo].[View_State] (
    View_State_Key nvarchar(250) not null,
    View_State_Value nvarchar(max) null,
    Insert_Time datetime not null
        constraint DF_View_State_Insert_Time default (getdate()),
    
    constraint PK_View_State primary key clustered (
        View_State_Key asc
    ) on [PRIMARY]
) on [PRIMARY] textimage_on [PRIMARY]

Get and Set View State

The stored procedures that retrieve and store a view state are pretty straightforward.

create procedure [dbo].[sp_get_view_state]
    @View_State_Key nvarchar(250)
as
begin
    set nocount on;

    select top 1 View_State_Value
    from View_State with (nolock)
    where View_State_Key = @View_State_Key

end


create procedure [dbo].[sp_set_view_state]
    @View_State_Key nvarchar(250),
    @View_State_Value nvarchar(max)
as
begin
    set xact_abort on;
    begin try
        begin transaction

            insert into View_State(View_State_Key, View_State_Value)
            values(@View_State_Key, @View_State_Value)

        commit transaction
        return 0
    end try
    begin catch
        if @@TRANCOUNT > 0
            rollback transaction
        return -1
    end catch
end

Delete View State

The delete stored procedure deletes all the view states that are at least 2 hours old. The value of @hours must be larger than the session time out, so the working assumption here is that the web sites session time out is less than 2 hours. Obviously, you need to replace that accordingly with your sites' time out.

create procedure [dbo].[sp_delete_view_state]
as
begin
    set nocount on;

    -- higher than the web site's session time out
    declare @hours int = 2

    delete from View_State
    where datediff(hour, Insert_Time, getdate()) > @hours

end

This stored procedure will be executed by a periodic SQL Server job, not directly from .NET. The job will run every 2 hours and clean up the View_State table of expired view states. In SSMS, open Object Explorer and under the server name you'll find SQL Server Agent and under that you'll see Jobs. Right click on Jobs and start a new job. In the General page, put "Delete View State" as the name of the job and "Delete view state every 2 hours" as the description. Change these values to your liking.

Move to the Steps page and start a new job step. Put "Delete View State" as the name of the step. Change the database from master to your database and finally put exec sp_delete_view_state as the step command. Under the step's Advanced page, change the action to quit the job on success.

Go to Schedules page and click New. Put "Every 2 Hours" as the schedule name. Change the frequency to Daily every (one) day. Change the daily frequency to 2 hours. To finish the job, no pun intended, I would also recommend that you add notification when the job fails, but that is just optional.

View State Management

The helper class ViewStateManagement is responsible for reading and writing view states. It mitigates between the web pages and the database. The method SetViewState stores a view state. First, the method serializes the view state object to a string using a dedicated .NET class LosFormatter. Then, it constructs the key that will uniquely identifies the view state. The key is constructed out of the IP address of the request, a time signature (DateTime.Now.Ticks) and project-specific information (uniqueKey), for example a user code. Once successful, the method injects the value of the view state key into the web page as a hidden field __VIEWSTATE_KEY. Here's an example of how the hidden field should look. If the user code is 10, the IP is 127.0.0.1 and the current time in ticks is 636250201790017849, then the hidden field will be __VIEWSTATE_KEY=VIEWSTATE_10_127.0.0.1_636250201790017849.

public static class ViewStateManagement
{
    public static bool SetViewState(
        Page page, 
        HttpContext context, 
        string connectionString, 
        object viewState, 
        string uniqueKey = null)
    {
        StringBuilder sb = new StringBuilder();
        using (StringWriter swr = new StringWriter(sb))
            new System.Web.UI.LosFormatter().Serialize(swr, viewState);

        string viewStateKey = string.Format("VIEWSTATE_{0}_{1}_{2}", 
            uniqueKey, 
            GetIP(context), // retrieves the IP from the HTTP Request
            DateTime.Now.Ticks
        );

        // database call
        bool succeeded = SetViewState(connectionString, viewStateKey, sb.ToString());

        if (succeeded)
            page.ClientScript.RegisterHiddenField("__VIEWSTATE_KEY", viewStateKey);

        return succeeded;
    }
}

The get method extracts the __VIEWSTATE_KEY from the HTTP request. With this key, it will query the database, get the view state string from the database and deserialize it, from string to object, with LosFormatter.

public static class ViewStateManagement
{
    public static object GetViewState(HttpContext context, string connectionString)
    {
        if (context == null || context.Request == null)
            return null;

        string viewStateKey = context.Request.Form["__VIEWSTATE_KEY"];

        if (string.IsNullOrEmpty(viewStateKey) == false &&
            viewStateKey.StartsWith("VIEWSTATE_"))
        {
            // database call
            string viewState = GetViewState(connectionString, viewStateKey);

            if (string.IsNullOrEmpty(viewState) == false)
                return new System.Web.UI.LosFormatter().Deserialize(viewState);
        }

        return null;
    }
}

Now we are ready to hook the view state. System.Web.UI.Page has two methods for saving and loading view state, LoadPageStateFromPersistenceMedium & SavePageStateToPersistenceMedium, and we need to override them both. Since this code is going to be the same regardless what web page it is, we are going to write it in a base class which all the web pages inherit from. If you don't need that then you're simply have to copy this code individually to each and every web page.

The SavePageStateToPersistenceMedium method builds uniqueKey from values that are specific to the page and the current session. This is where you need to write your own code to reflect your own project. Then, it calls ViewStateManagement.SetViewState to save the view state and to inject the key into the page. If the whole process fails, it falls back to the default implementation of SavePageStateToPersistenceMedium of System.Web.UI.Page.

public abstract class BasePage : System.Web.UI.Page
{
    protected override void SavePageStateToPersistenceMedium(object viewState)
    {
        // Any unique project-related values. user code, guid, ...
        string uniqueKey = null;
        
        // Change this to however you retrieve the connection string
        string connectionString = 
            HttpContext.Current.Session["ConnectionString"] as string;

        // set view state
        bool succeeded = ViewStateManagement.SetViewState(
            this,
            HttpContext.Current,
            connectionString,
            viewState,
            uniqueKey
        );

        if (succeeded == false)
            base.SavePageStateToPersistenceMedium(viewState); // fallback
    }
}

Very similarly, LoadPageStateFromPersistenceMedium retrieves the deserialized view state by calling ViewStateManagement.GetViewState. If it fails to do so, it will fall back to LoadPageStateFromPersistenceMedium of System.Web.UI.Page.

public abstract class BasePage : System.Web.UI.Page
{
    protected override object LoadPageStateFromPersistenceMedium()
    {
        // Change this to however you retrieve the connection string
        string connectionString =
            HttpContext.Current.Session["ConnectionString"] as string;

        // get view state
        object viewState = ViewStateManagement.GetViewState(
            HttpContext.Current,
            connectionString
        );

        if (viewState != null)
            return viewState;
        else
            return base.LoadPageStateFromPersistenceMedium(); // fallback
    }
}