Introduction

I seem to be putting an ever increasing amount of appSettings keys into my web and app.config files these days, and often these keys take the form of a list of settings that can be grouped together and apply to a single object/class. For example, we had an application that generates a report containing Excel chart objects and the colour palettes for these charts need to be user, or at least client, configurable. To achieve this with appSettings keys only we’d end up with a list resembling the following: –

<add key="chart9index1" value="Blue1"/>
<add key="chart9index2" value="Orange1"/>
<add key="chart9index3" value="Green1"/>
<add key="chart9index4" value="Pink1"/>
<add key="chart9index5" value="Blue3"/>
<add key="chart9index6" value="Orange3"/>

These settings define the colours used for a single chart in the report, so we’d need a list like this for each chart in the application. The first issue with this approach is that the list gets unwieldy pretty quickly; imagine if the application had twenty or so charts (which it does). Secondly, we’re tied to the one key/one value structure, suppose we also wanted to define what the red, green and blue components were of the colour Blue1. We would need something along the lines of: –

<add key="Blue1R" value="63"/>
<add key="Blue1G" value="166"/>
<add key="Blue1B" value="204"/>

Subsequently, we’d then need to write the code to read these values and associate them with the colour Blue1. Wouldn’t it be nicer if we could write something like: –

<add key="Blue1" R="63" G="166" B="204"/>

Well, you can’t, at least, not in the appSettings section of your config file. You can however create your own configuration section and do exactly this.

The configSections element

<configuration>
  <configSections>
    <sectionGroup name="colors">
      <section name="custom" type="StuartWhiteford.Reporting.Custom, StuartWhiteford.Reporting"/>
      <section name="palettes" type="StuartWhiteford.Reporting.ReportConsole.Palettes, StuartWhiteford.Reporting.ReportConsole"/>
    </sectionGroup>
  </configSections>
  ...
</configuration>

Within our main configuration element we’ve added a configSections element. This will contain the definitions for any custom configuration sections that we want to include. Within this element we have a sectionGroup element and named it colors. The sectionGroup isn’t a requirement, but it does give us a way of organising our sections logically.

The ConfigurationSection class

Delving deeper, we get to the section elements, and you’ll notice that these elements have a type attribute. This identifies the classes that we will de-serialise our subsequent configuration entries into. With hindsight giving a class the name Custom is a bit daft, it tells you precisely nothing about it. However, the code for this class is as follows: –

public class Custom : ConfigurationSection
{
    public static Custom GetConfig()
    {
        return ConfigurationManager.GetSection("colors/custom") as Custom;
    }
 
    [ConfigurationProperty("customColors")]
    public CustomColorCollection CustomColors 
    {
        get
        {
            return this["customColors"] as CustomColorCollection;
        }
    }
}

Not much to it. Firstly, we’ve inherited from the ConfigurationSection class, which provides the mechanisms for serialising and de-serialising to and from our config file. Secondly we require a way to access this data in code, so we have the GetConfig method that returns an object of type Custom, de-serialised from the information contained in the section with the name custom that resides in the sectionGroup colors. Generically we’d call ConfigurationManager.GetSection(“<sectionGroup name>/<section name>”) as <Class> omitting the sectionGroup if necessary.

Lastly, we define what we can put into our custom section. In this case we have a customColors element which de-serialises as a CustomColorCollection object, shown below: –

public class CustomColorCollection : ConfigurationElementCollection
{
 
    public CustomColor this[int index]
    {
        get
        {
            return base.BaseGet(index) as CustomColor;
        }
        set
        {
            if (base.BaseGet(index) != null)
            {
                base.BaseRemoveAt(index);
            }
            this.BaseAdd(index, value);
        }
    }
 
    public CustomColor this[object name]
    {
        get
        {
            return base.BaseGet(name) as CustomColor;
        }
    }
 
    protected override ConfigurationElement CreateNewElement()
    {
        return new CustomColor();
    }
 
    protected override object GetElementKey(ConfigurationElement element)
    {
        return ((CustomColor)element).Name;
    }
 
}

Since this is a collection object, the two public properties allow us to retrieve a CustomColor object either by its index or name. The CreateNewElement method returns a new instance of the CustomColor class and finally the GetElementKey method identifies which property of our CustomColor class we will use as the key in the collection, in this case we’ll use the Name. The CustomColor class is as follows: –

public class CustomColor : ConfigurationElement
{
 
    [ConfigurationProperty("name", IsRequired = true)]
    public string Name
    {
        get
        {
            return this["name"] as string;
        }
    }
 
    [ConfigurationProperty("R", IsRequired = true)]
    public int R
    {
        get
        {
            return (int)this["R"];
        }
    }
 
    [ConfigurationProperty("G", IsRequired = true)]
    public int G
    {
        get
        {
            return (int)this["G"];
        }
    }
 
    [ConfigurationProperty("B", IsRequired = true)]
    public int B
    {
        get
        {
            return (int)this["B"];
        }
    }
 
    public Color ToColor()
    {
        return Color.FromArgb(this.R, this.G, this.B);
    }
 
    public int ToColorRef()
    {
        return (this.R + (this.G << 8) + (this.B << 16));
    }
 
}

We have four properties: Name, R, G and B, all hopefully self-explanatory. We also have two methods that aren’t exposed in the XML.ToColor(), which returns a System.Drawing.Color object consisting of the defined red, green and blue values and ToColorRef(), which returns an integer that Excel understands as a colour.

The final piece of the puzzle to make this all work is the section in the config file that will describe our custom colours: –

<colors>
  <custom>
    <customColors>
      <add name="Blue1" R="63" G="166" B="204"/>
      <add name="Blue2" R="108" G="188" B="216"/>
      <add name="Blue3" R="155" G="210" B="229"/>
      <add name="Blue4" R="210" G="234" B="242"/>
      <add name="Orange1" R="240" G="106" B="0"/>
      <add name="Orange2" R="243" G="152" B="68"/>
      ...
    </customColors>
  </custom> 
</colors>

Now, in our application code, we can call CustomColorCollection _colors = Custom.GetConfig().CustomColors; and we’ve loaded all our stunning custom colours into a single object. No more messing around with ConfigurationManager.AppSettings[] calls and string manipulation. If we need the Blue2 Color object all we need to do is call _colors[“Blue2”].ToColor();

Conclusion

Granted, this is a fair amount of work for something so simple, but hopefully it has shown how you can store application configuration data, that you might have previously used a database for, in its proper place.

Introduction

There’s nothing particularly original about creating a PRODUCT function in SQL Server using the CLR. Follow this link to see how to implement one. Recently, however, I had the need to return an annualised product of a set of values. For example, consider the following table of quarterly percentage return figures: –

Date Return
30/06/2006 5.5 %
30/09/2006 7.0 %
31/12/2006 3.1 %
31/03/2007 1.8 %
30/06/2007 1.0 %
30/09/2007 2.2 %
31/12/2007 0.8 %
31/03/2008 6.5 %

The percentage return over the period as a whole would be: –

(((1.055 * 1.07 * 1.031 * 1.018 * 1.01 * 1.022 * 1.008 * 1.065) – 1) * 100) = 31.29 %

and to calculate the annual return we need to raise this result to the power of ½ (if we had three years of data then we’d use ⅓).

31.29 % ^ 0.5 = 14.58 %

Now the T-SQL to do this isn’t particularly difficult, but it can get pretty untidy, so what follows is a CLR implementation written in C#.

Aggregate functions are defined as structs. The struct should be marked as serializable so that SQL Server 2005 can store a representation of the aggregate on disk and the SqlUserDefinedAggregate attribute indicates that this is a user-defined aggregate. The Format.Native property tells the Framework to handle the serialization process using a simple algorithm and it works only with value types which is why the aggregate is defined as a struct. Within our struct, we have defined four private variables: –

  • _product: holds the running total of the product of all the values currently aggregated.
  • _annualisedProduct: takes the value of the _product variable and, if the _count of values is greater than the defined _num value, raises the value to the power of the _num divided by the _count.
  • _num: is the number of periods in one year. For this function (quarterly) the value is 4, to implement a monthly variation the value would be 12.
  • _count: stores the running count of the values in the set.

Within the struct, four methods need to be implemented: –

Init()
The Init() method initializes any variables or resources required and is called once when the aggregate function is invoked. Our Init() method set the _product variable to 1, _num to 4 and _count to 0.
Accumulate()
The Accumulate() method contains the implementation of the aggregate function and is called once for each row. Our implementation increments the _count variable, calculates the current _product and then determines the _annualisedProduct value.
Merge()
If the column to aggregate contains a large number of rows, SQL Server may split the rows into groups, aggregate each group and then merge the results of each group.
Terminate()
The Terminate() function is called to free any resources used and returns the result of the function, the _annualisedProduct.

Below is the final code for the struct: –

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 300, IsNullIfEmpty = true)]
public struct AnnualisedProductQuarterlyData : IBinarySerialize
{ 
 
    private Decimal _product;
    private Decimal _annualisedProduct;
    private Int32 _num;
    private Int32 _count;
 
    public void Init()
    {
        _product = 1;
        _num = 4;
        _count = 0;
    }
 
    public void Accumulate(SqlDecimal value)
    {
        _count++;
        _product = _product * (Decimal)value;
        if (_count > _num)
        {
            _annualisedProduct = (Decimal)Math.Pow((Double)_product, (Double)(_num / _count));
        }
        else
        {
            _annualisedProduct = _product;
        }
    }
 
    public void Merge(AnnualisedProductQuarterlyData Group)
    {
        Accumulate(Group.Terminate());
    }
 
    public SqlDecimal Terminate()
    {
        return _annualisedProduct;
    }
 
#region IBinarySerialize Members
 
    public void Read(System.IO.BinaryReader r)
    {
        _annualisedProduct = r.ReadDecimal();
    }
 
    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(_annualisedProduct);
    }
 
#endregion
 
}

The T-SQL to register the assembly and functions in your database is as follows: –

CREATE ASSEMBLY [StuartWhiteford] 
FROM C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\StuartWhiteford.SQLServer.dll'
WITH PERMISSION_SET = SAFE
 
CREATE AGGREGATE [dbo].[Product] (@input decimal(38,10))
RETURNS decimal(38,10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.Product];
 
CREATE AGGREGATE [dbo].[AnnualisedProductQuarterlyData] (@input decimal(38, 10))
RETURNS decimal(38, 10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.AnnualisedProductQuarterlyData]
 
CREATE AGGREGATE [dbo].[AnnualisedProductMonthlyData] (@input decimal(38, 10))
RETURNS decimal(38, 10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.AnnualisedProductMonthlyData]

Introduction

This is the first in a (possible) series of posts about the Dynamic Data feature in ASP.NET 3.5. The purpose of Dynamic Data is to make it easy to quickly develop a data-driven web application. The purpose of this post specifically, is to demonstrate how to take what you get at the end of the wizard and turn it into a slightly more polished web application. This post will not go into detail regarding the concepts involved in Dynamic Data (LINQ, ASP.NET Routing, etc.), see the further resources section at the end for links to these topics.

What you need

  • Visual Studio 2008
  • ASP.NET 3.5 Service Pack 1 Beta or Visual Studio 2008 Service Pack 1 Beta
  • Dynamic Data Runtime and Templates

Once you’ve got Visual Studio 2008 up and running, install the ASP.NET 3.5 Service Pack or the Visual Studio 2008 Service Pack, then run the Install.cmd file from the Dynamic Data Runtime and Templates zip file.

Building the Website

  • Open Visual Studio 2008 and create a New Web Site….
  • Select Dynamic Data Website Wizard (Preview) from the templates, change the name if required and press OK.

  • If you’ve got an existing data model and want to use it select it from the drop down list, otherwise click New Connection….

  • Select your server and database and click OK.

  • Click Next > when you return to the main dialogue box.
  • Leave the Data Context Namespace blank as this can lead to problems later.
  • Change the Data Context Class Name if required.
  • In the tree view, select the items that you want the wizard to model. For the purposes of this demo, we’ll only select the Tables.
  • Click Next >.

  • The next screen asks you to add Data Pages to your website. Note that you don’t actually have to add any pages here, what this screen should really be asking is what pages do you want to be able to customise in the finished website. In the screenshot below, we have chosen the Product table.
  • Click Next >.

  • The following screen displays the pages that we have selected to customise in the left hand tree view; on the right hand side you can set the properties. Currently the only layout option is tabular, although more are promised in future releases. The content options provide a way of changing the way data is input on the Insert and Edit screens. Your options here are a separate page (which has an editable DetailsView) or Inline in the GridView. You can also choose whether records can be deleted and edit the list of fields that are displayed to the user.
  • Click Finish.

  • You’ll now get a warning about overwriting items in the App_Code folder. Check the Apply to all items box and click Yes.

  • Press F5 (or Ctrl + F5) to run the application.
  • What we have now is a reasonably functional web application.

Customising the Website

  • First up, let’s change the style sheet. Note from the structure of the solution below that we don’t have an App_Themes folder. Feel free to create one if you like, but for this demo we’re just going to overwrite the existing Site.css file.
  • Secondly, we’ll change the site title in the Site.master file and add a background image to the header (defined in the style sheet).

  • Next, let’s make some changes to how the data is displayed. You can see in the solution that we have a FieldTemplates folder. Contained in here are user controls for each data type that define how data is displayed and edited in the website. Changes to these controls affect the entire site.
  • We’ll create two new user controls called RadioBoolean and RadioBoolean_Edit that will display fields for Boolean data types as a RadioButtonList rather than the default CheckBox.
  • The contents of RadioBoolean.ascx are as follows: –
<%@ Control Language="VB" AutoEventWireup="false" CodeFile="RadioBoolean.ascx.vb" Inherits="FieldTemplates_RadioBoolean" %>
<asp:RadioButtonList ID="RadioButtonList1" runat="server" Enabled="false" RepeatLayout="Flow" RepeatDirection="Horizontal">
    <asp:ListItem Text="True" Value="true"></asp:ListItem>
    <asp:ListItem Text="False" Value="false"></asp:ListItem>
</asp:RadioButtonList>
  • And the contents of RadioBoolean.ascx.vb: –
Partial Class FieldTemplates_RadioBoolean
 
    Inherits System.Web.DynamicData.FieldTemplateUserControl
 
    Public Overrides ReadOnly Property DataControl() As Control
        Get
            Return RadioButtonList1
        End Get
    End Property
 
    Protected Overrides Sub OnDataBinding(ByVal e As EventArgs)
        MyBase.OnDataBinding(e)
        Dim val As Object = FieldValue
        If (Not (val) Is Nothing) Then
            If CType(val, Boolean) Then
               RadioButtonList1.SelectedValue = "true"
            Else
               RadioButtonList1.SelectedValue = "false"
            End If
        End If
    End Sub
 
End Class
  • The class inherits from FieldTemplateUserControl and overrides the DataControl property, returning the RadioButtonList defined in the mark-up.
  • We override the OnDataBinding method, converting the public FieldValue property to a Boolean and selecting the appropriate value in the RadioButtonList.
  • The RadioBoolean_Edit.ascx has the same mark-up as RadioBoolean.ascx but sets the Enabled property of the RadioButtonList to true.
  • The RadioBoolean_Edit.ascx.vb file is the same as the RadioBoolean.ascx.vb file with the following addition (which add the selected value of the field to a dictionary of fields and their values): –
Protected Overrides Sub ExtractValues(ByVal dictionary As IOrderedDictionary)
    dictionary(Column.Name) = CType(RadioButtonList1.SelectedValue, Boolean)
End Sub
  • To use this new control we need to add a UIHint to the fields that we want to display a RadioButtonList for. To do this, open up the generated NorthwindDataContextPartialClass.vb file and look for the Discontinued public property of the Product_Metadata class. These _Metadata classes provide a method to further enhance the user interface with custom user controls or additional validators.
  • Change the Discontinued property to the following: –
<UIHint("RadioBoolean")> _
Public Property Discontinued() As Object
    Get
        Return _Discontinued
    End Get
    Set(ByVal value As Object)
        _Discontinued = Value
    End Set
End Property
  • If you re-run the application, and navigate to the Products table, you should see that the Discontinued field is rendered as a RadioButtonList.

  • If we wanted all Boolean fields to be rendered as RadioButtonLists then it would be significantly easier to modify the existing Boolean and Boolean_Edit user controls than assign UIHints to all Boolean properties.
  • Let’s make a change to the DateTime_Edit control that will affect all DateTime fields across the application by adding a calendar control to it.
  • First up, you’ll need to install the AJAX Control Toolkit. Once that has installed add the following line to your web.config file in the system.web > pages > controls section.
<add tagPrefix="ajax" namespace="AjaxControlToolkit" assembly="AjaxControlToolkit"/>
  • Next, add the following snippets to the DateTime_Edit.ascx file.
<ajax:CalendarExtender ID="CalendarExtender1" runat="server" CssClass="popupcalendar" TargetControlID="TextBox1" Format="dd/MM/yyyy" OnClientDateSelectionChanged="hideCalendar"></ajax:CalendarExtender>
<script type="text/javascript" language="javascript">
    function hideCalendar(cb)
    {
        cb.hide();
        cb.get_element().blur();
    }
</script>
  • Re-run the application, navigate to the Employees table, select an Employee to edit and click on a TextBox containing a date to see the calendar.

Further Resources

Webcasts

Websites

Blogs