Dynamically Setting the Connection String for Session State on SQL Server

In ASP.NET, there are four ways to store Session State.  The way I prefer to store it is using SQL Server.  However, instead of storing it in the ASPstate database, which is the default way to store it on SQL Server, I choose to store it in the application database, which is a “custom SQL database” implementation.

When storing Session State in the application database, the web.config will have an entry that points at the database server and the name of the database:

<sessionState mode="SQLServer" timeout="20" allowcustomsqldatabase="true"
   sqlconnectionstring="Data Source=Server;Initial Catalog=databasename; User ID=UserID;
   Password=Password;" cookieless="false" />

Because this is set in web.config, it suffers from the same problem that I wrote about in this post — namely, that the connection string must be changed when moving between different environments (such as development, test / staging , and production).

To remedy this, a function that returns the dynamic connection string for the application should already be in place.  Something like this will work:

using System.Configuration;

public class ConnectionStrings
   public static string _AppDB_ConnectionString = ConfigurationManager.ConnectionStrings["AppDB_ConnectionString_" + ConfigurationManager.AppSettings.Get("Environment").ToUpper()].ConnectionString;

(This is assuming that you have an “Environment” key set in the appSettings section of either web.config or machine.config, and that your connection strings are named accordingly in your web.config connectionStrings section.)

Next, a PartitionResolver class must be created that can get the correct connection string by using the function declared above:

public class MyPartitionResolver : System.Web.IPartitionResolver
   public void Initialize()
   //Empty Initializer

   public string ResolvePartition(object key)
     return ConnectionStrings._AppDB_ConnectionString;

Lastly, the sessionState entry in web.config must be modified to look like this:

<sessionState mode="SQLServer" allowCustomSqlDatabase="true" partitionResolverType="MyPartitionResolver"
regenerateExpiredSessionId="true" compressionEnabled="true" useHostingIdentity="true" timeout="480"/>

Note: The options after “partitionResolverType” can be set according to your needs.

Programmatically Creating an ASP.NET GridView Control

The biggest project I’ve worked on in the past year involved building almost all of the ASP.NET controls for a page programmatically in the code-behind and putting them into a Placeholder, rather than declaring them in the markup file.

Many ASP.NET controls are relatively easy to build this way.  Buttons, TextBoxes, Labels, DropDownLists, even SqlDataSources can be created using a few simple lines of code.

Adding a print Button can be this simple:

Dim btnPrint As New Button
btnPrint.Attributes.Add("onclick", "javascript:window.print();")
btnPrint.Visible = False

The instructions for adding simple controls such as these can be easily found on MSDN and many other sites.  Unfortunately,  instructions for building a complex GridView were nowhere to be found.  Take heart: It can be done!

Adding a GridView control is generally much more complicated than this, and also requires many more attributes.  Each column must be declared and added to the GridView prior to the GridView itself being added to the Form / Panel / Placeholder, etc.  Also, attributes like sorting, paging, Edit / Cancel Buttons must be taken into consideration.

Here is an example of the VB code for a full-featured GridView:

Dim gvExample As New GridView
With gvExample
.ID = "gvExample"
.CssClass = "gridview"
.DataSource = dvExample
.AutoGenerateColumns = False
.CellPadding = 4
.DataKeyNames = New String() {"ExampleID"}
.ForeColor = Drawing.ColorTranslator.FromHtml("#2a2723")
.GridLines = GridLines.None
.Width = Unit.Percentage(100)
.AllowSorting = True
.AllowPaging = False

Dim strHeadBack As String = "#ffcb00"
Dim strPagerBack As String = "#009ddb"
Dim strForeColor As String = "#000000"

.HeaderStyle.BackColor = Drawing.ColorTranslator.FromHtml(strHeadBack)
.HeaderStyle.Font.Bold = True
.HeaderStyle.ForeColor = Drawing.ColorTranslator.FromHtml(strForeColor)

.RowStyle.BackColor = Drawing.ColorTranslator.FromHtml("#FFFBD6")
.RowStyle.ForeColor = Drawing.ColorTranslator.FromHtml("#2a2723")
.RowStyle.HorizontalAlign = HorizontalAlign.Center

.AlternatingRowStyle.BackColor = Drawing.Color.White

.BorderColor = Drawing.ColorTranslator.FromHtml("#d80073")
.BorderStyle = BorderStyle.Groove


Dim ViewButton As New ButtonField
ViewButton.HeaderText = "View"
ViewButton.ButtonType = ButtonType.Button
ViewButton.Text = "View"
ViewButton.CommandName = "ViewExample"

Dim EditButton As New TemplateField
EditButton.HeaderText = "Edit"
EditButton.ItemTemplate = New MyButtonTemplate ' This is a user-defined class that creates this Button
EditButton.Visible = bEditVisible

Dim Voided As New CheckBoxField
Voided.HeaderText = "Voided"
Voided.DataField = "Voided"
Voided.ReadOnly = True
Voided.Visible = bVoidVisible

Dim ExampleDate As New TemplateField
ExampleDate.HeaderText = "Date/Time"
ExampleDate.SortExpression = "Date_and_Time"
ExampleDate.ItemTemplate = New MyLabelTemplate ' This is a user-defined class that creates this Label
ExampleDate.ItemStyle.Wrap = False

Dim ShortColumn As New BoundField
ShortColumn.ItemStyle.CssClass = "left"
ShortColumn.HeaderText = "Short Column"
ShortColumn.SortExpression = "Short_Column"
ShortColumn.DataField = "Short_Column"
ShortColumn.ItemStyle.Wrap = True
ShortColumn.ItemStyle.Width = 150

Dim LongColumn As New BoundField
LongColumn.ItemStyle.CssClass = "left"
LongColumn.HeaderText = "Long Column"
LongColumn.SortExpression = "Long_Column"
LongColumn.DataField = "Long_Column"
LongColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Left
LongColumn.ItemStyle.Wrap = True
LongColumn.ItemStyle.Width = 200

Dim CreatedBy As New BoundField
CreatedBy.HeaderText = "Created By"
CreatedBy.SortExpression = "CreatedBy"
CreatedBy.DataField = "CreatedBy"

.Visible = True
.PageIndex = GridViewPageIndex ' I used a Session variable to store the page number
End With

Separate routines that handle the Sorting and PageIndexChanging events must be manually created if you want these functions to be included with your GridView.  In these modules, you can use Session variables to store the page numbers and sort direction.  It might be possible to use ViewState variables instead, though I did not try that here.  Also, a module that handles RowCommand events must be created if any buttons that are added to the GridView do something that is row-specific.

It is certain that creating a GridView programmatically is much more difficult than dragging and dropping from the Toolbar onto the Design page.  However, that may not always be the best way if you are creating a highly dynamic application, such as one where even the fundamental design of a page is database-driven.

%d bloggers like this: