Deep in the Code

2012.05.292022.12.16

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
Placeholder1.Controls.Add(btnPrint)

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

.Columns.Clear()

Dim ViewButton As New ButtonField
ViewButton.HeaderText = "View"
ViewButton.ButtonType = ButtonType.Button
ViewButton.Text = "View"
ViewButton.CommandName = "ViewExample"
.Columns.Add(ViewButton)

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
.Columns.Add(EditButton)

Dim Voided As New CheckBoxField
Voided.HeaderText = "Voided"
Voided.DataField = "Voided"
Voided.ReadOnly = True
Voided.Visible = bVoidVisible
.Columns.Add(Voided)

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
.Columns.Add(ExampleDate)

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
.Columns.Add(ShortColumn)

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
.Columns.Add(LongColumn)

Dim CreatedBy As New BoundField
CreatedBy.HeaderText = "Created By"
CreatedBy.SortExpression = "CreatedBy"
CreatedBy.DataField = "CreatedBy"
.Columns.Add(CreatedBy)

.Visible = True
.PageIndex = GridViewPageIndex ' I used a Session variable to store the page number
Placeholder1.Controls.Add(gvExample)
.DataBind()
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.

2012.05.242022.12.16

Dynamically Set the ConnectionString Attribute on a SqlDataSource ASP.NET Control

I store the connection strings for all three of my environments (development, test, production) in the “connectionstrings” section of web.config on most of my ASP.NET Web applications. When deploying the app to one of these environments, I either have to change a key called “Environment” in the appSettings section of web.config, or I can just put it in machine.config on the server. Connection strings:

<connectionStrings>
<add name="AppDB_ConnectionString_DEVELOPMENT"
         connectionString="Data Source=DEV_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString_TEST"
         connectionString="Data Source=TEST_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString_PRODUCTION"
         connectionString="Data Source=PROD_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
</connectionStrings>

Environment key:

<appSettings>
   <add key="Environment" value="PRODUCTION"/>
</appSettings>

Setting the connection string attribute on a SqlDataSource control can be done in a number of ways. It can be programmatically done in the code-behind file, and this is the way it is most often done dynamically. This is not always convenient, as you may have to spend lots of time determining the event during which the string must be set.

SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("whatever")

For instance, if the SqlDataSource control is embedded inside the EditTemplate of a FormView control, it will not be accessible during Page.Load, and if a parameter of the SqlDataSource control is databound, you may have to navigate a Byzantine maze of ASP.NET lifecycle events to find out that it needs to be set in the Load event of some seemingly unrelated control.

To solve this problem, many people give up and use only one active connection string at a time in web.config, and comment the others out:

<connectionStrings>
<add name="AppDB_ConnectionString" connectionString="
         Data Source=DEV_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<!-- <add name="AppDB_ConnectionString" connectionString="
         Data Source=TEST_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString" connectionString="
         Data Source=PROD_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/> -->
</connectionStrings>

Then, “dynamically” setting the string in the SqlDataSource markup like this:

<asp:SqlDataSource ID="SqlChildren" runat="server"
   ConnectionString="<%$ ConnectionStrings:AppDB_ConnectionString %>"
   SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
   <SelectParameters>
      <asp:ControlParameter ControlID="lblAppID" DefaultValue="0"
        Name="SourceID" PropertyName="Text" Type="Int32" />
   </SelectParameters>
</asp:SqlDataSource>

This works fine, but will require a change in the connection string in web.config to move from one environment to another. At first, I assumed (and we all know what that does!) that one could dynamically set the attribute using the standard inline < %= %> notation like this:

<asp:SqlDataSource ID="SqlChildren" runat="server"
   ConnectionString="<%=
   System.Configuration.ConfigurationManager.AppSettings.Get("Environment") &
   ...[connection string] ... %>"
   SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
   <SelectParameters>
      <asp:ControlParameter ControlID="lblAppID" DefaultValue="0" Name="SourceID"
        PropertyName="Text" Type="Int32" />
   </SelectParameters>
</asp:SqlDataSource>

Anyone who has tried this already knows what happened next. This caused a run-time error when the SqlDataSource tried to bind to the expression. After much wailing and gnashing of teeth, I found that it IS possible to dynamically set the connection string in a manner similar to the standard way! Instead of the < %= %> expression, a custom expression using the < %$ %> notation will work.

First, to build the custom expression in a class:

Imports System
Imports System.CodeDom
Imports System.Web.UI
Imports System.ComponentModel
Imports System.Web.Compilation

' Apply ExpressionEditorAttributes to allow the
' expression to appear in the designer.
<ExpressionPrefix("ConnStringExpression")> _
<ExpressionEditor("ConnStringExpressionEditor")> _
Public Class ConnStringExpressionBuilder
   Inherits ExpressionBuilder
   ' Create a method that will return the result
   ' set for the expression argument.
   Public Shared Function GetEvalData(ByVal expression As String, _
     ByVal target As Type, ByVal entry As String) As Object
      Return System.Configuration.ConfigurationManager.AppSettings("Environment") &
         "_" & _
         System.Configuration.ConfigurationManager.ConnectionStrings("whatever")
   End Function

   Public Overrides Function GetCodeExpression(ByVal entry _
     As BoundPropertyEntry, ByVal parsedData As Object, ByVal context _
     As ExpressionBuilderContext) As CodeExpression
      Dim type1 As Type = entry.DeclaringType
      Dim descriptor1 As PropertyDescriptor = _
      TypeDescriptor.GetProperties(type1)(entry.PropertyInfo.Name)
      Dim expressionArray1(2) As CodeExpression
      expressionArray1(0) = New CodePrimitiveExpression(entry.Expression.Trim())
      expressionArray1(1) = New CodeTypeOfExpression(type1)
      expressionArray1(2) = New CodePrimitiveExpression(entry.Name)
      Return New CodeCastExpression(descriptor1.PropertyType, _
      New CodeMethodInvokeExpression(New CodeTypeReferenceExpression _
      (MyBase.GetType()), "GetEvalData", expressionArray1))
   End Function
End Class

Next, in your web.config file, add this to the compilation section:

<expressionBuilders>
<add expressionPrefix="ConnStringExpression"
type="ConnStringExpressionBuilder"/>
</expressionBuilders>

Lastly, change the ConnectionString attribute in the markup for the SqlDataSource control to:

<asp:SqlDataSource ID="SqlChildren" runat="server"
    ConnectionString="<%$ ConnStringExpression:AppDB %>"
SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblAppID" DefaultValue="0"
            Name="SourceID" PropertyName="Text" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

Voila!

You can now have multiple connection strings declared in the web.config, and set them dynamically in your markup rather than in the code-behind.

Note: What goes after the “ConnStringExpression:” is incidental in this case; I just put “AppDB” for readability purposes.

2012.05.232022.12.07

Guide for the Perplexed

Dave is disabling HAL in 2001: A Space Odyssey

With few exceptions, Web sites today are not built on a no-frills text editor using only static HTML code. Most sites today use a minimum of four different technologies. A relatively simple page will likely use HTML for static markup, inline (such as ASP or PHP) or code-behind (ASP.NET) dynamic code, CSS for consistent styles, and JavaScript for dialog box popups. Given the multitude of technologies that are used to build even a single dynamic data-driven Web page, it’s impossible to know it all.

Though I’ve been writing programs since before most people had even heard of email, I still must frequently use Google (or my new favorite search engine, Duck Duck Go) to look up functions, formatted connection strings, .NET namespaces, SQL stored procedures, and the like.

Most of the time, if I can properly articulate what coding problem I’m trying to solve, a few minutes of searching will likely result in finding the code that will fix it. In many cases, if I’m having a problem, it’s very likely that others have had it before, and that at least one of them posted the solution somewhere.

For those difficult times that I have had to either conjure up the code on my own or to use multiple other sources to build a solution that worked for me, I created this blog. I hope to shed light on questions that were (at least to me) very perplexing, but for which I now have answers.

Welcome to my blog, and happy coding!

P.S.:

While I got my start on an Apple, and just recently bought my first Mac, the bulk of my professional work is using Windows-based technologies. For my IDE, I generally use Visual Studio 2010. Most of my applications are ASP.NET using VB, though I am doing all new development in C# where possible. SQL Server 2008 is my DBMS of choice, though I do get to dabble in Oracle Database programming from time to time. With any luck, I’ll get a chance to post some Cocoa / Objective-C code before the end of the year! 😀