Michael Morozov

ASP.NET 2.0 two-way binding and calculated fields

Did you get that feeling of excitement, when during the ASP.NET 2.0 presentation the two-way bound data grid was built with a few mouse clicks? It does look impressive. However, when you get home and try to do something a little bit more complicated, you hit the wall and end up scratching your head, looking for a magic property that will suddenly make everything work. While Microsoft provides a great number of magic properties and attributes, sometimes you have to resort to actually using the keyboard and writing some code. I particularly like it what a problem can be solved in exactly one line of code. I even wanted to call my blog the "one line fix", but it sounds too much like a stupid cocaine movie title. But the idea remains. I encounter quite a few problems when programming in .NET and if I find a one-line solution for something I will share it with you.

1.

One such problem I encountered when doing the aforementioned two-way data binding. It has to do with having a calculated column. 

It's really quite easy to create a two-way bound data grid:

1. Drop DetailsView control onto the form
2. Click on the smart-tag and choose "New data source" in the drop-down list.
3. Follow the wizard which will build a SELECT statement for you
4. Click on WHERE button to add a parameter to you SELECT query
5. Click on ADVANCE button and check "Generate INSERT, UPDATE and DELETE statemens". Your table should have a primary key for this option to be enabled. You also must select the primary key column as one of the retrieved columns.
6. Finish the wizard and check "Enable editing" in the smart tag panel

Your page source will look something like the following listing. Here is your DetailsView element with 5 bound fields - Id, FirstName, LastName, BirthDate and SunSign (zodiac sign).

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="id" DataSourceID="SqlDataSource1" Height="50px" Width="125px">
   <Fields>
      <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" SortExpression="id" />
      <asp:BoundField DataField="firstname" HeaderText="firstname" SortExpression="firstname" />
      <asp:BoundField DataField="lastname" HeaderText="lastname" SortExpression="lastname" />
      <asp:BoundField DataField="birthdate" HeaderText="birthdate" SortExpression="birthdate" DataFormatString="{0:d}" />
      <asp:BoundField DataField="sunsign" HeaderText="sunsign" SortExpression="sunsign" />
      <asp:CommandField ShowEditButton="True" />
   </Fields>
</asp:DetailsView>

Here is your SqlDataSource with generated SELECT, UPDATE, INSERT and DELETE commands and parameter lists for each command:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SocionicsConnectionString %>"
DeleteCommand="DELETE FROM [PEOPLE] WHERE [id] = @id"
InsertCommand="INSERT INTO [PEOPLE] ([birthdate], [sunsign], [firstname], [lastname]) VALUES (@birthdate, @sunsign, @firstname, @lastname)"
SelectCommand="SELECT [id], [birthdate], [sunsign], [firstname], [lastname] FROM [PEOPLE] WHERE ([id] = @id)"
UpdateCommand="UPDATE [PEOPLE] SET [birthdate] = @birthdate, [sunsign] = @sunsign, [firstname] = @firstname, [lastname] = @lastname WHERE [id] = @id">

   <DeleteParameters>
      <asp:Parameter Name="id" Type="Int32" />
   </DeleteParameters>

   <UpdateParameters>
      <asp:Parameter Name="birthdate" Type="DateTime" />
      <asp:Parameter Name="sunsign" Type="Int32" />
      <asp:Parameter Name="firstname" Type="String" />
      <asp:Parameter Name="lastname" Type="String" />
      <asp:Parameter Name="id" Type="Int32" />
   </UpdateParameters>

   <SelectParameters>
      <asp:QueryStringParameter DefaultValue="0" Name="id" QueryStringField="id" Type="Int32" />
   </SelectParameters>

   <InsertParameters>
      <asp:Parameter Name="birthdate" Type="DateTime" />
      <asp:Parameter Name="sunsign" Type="Int32" />
      <asp:Parameter Name="firstname" Type="String" />
      <asp:Parameter Name="lastname" Type="String" />
   </InsertParameters>

</asp:SqlDataSource>

Note that a SelectParameter is a QueryStringParameter. It will automatically pull the "id" from the QueryString and pass it to the SELECT statement. It can also be a ControlParameter pulling the value from another control on the form. It works great for building Master-Detail data forms. The INSERT and UPDATE parameters will be populated from BoundField items in the DetailsView automatically. Very simple.

2.

There is one bug I want to mention. Obviously you would want to format the output for the BirthDate column, since the default representation includes hours, minutes and seconds. Fortunately Microsoft provides a magic property DataFormatString="{0:d}". Unfortunately it doesn't work. Another magic property can be applied to make the data formatting also work in edit and insert mode - ApplyFormatInEditMode="True". This one does work, but in the view mode the value is still shown as a full date-time string. Ahh, screw this magic, let's convert this column to a TemplateField and see if we can format it manually. Open the columns editor, select the "birthdate" column and click on "Convert this field into a TemplateField". You will see that the BoundField expands into this:

Note that it created a template for each mode. Edit and Insert templates have a TextBox in them. The view item template has a Label. Also notice the Bind() method call in the server-side tag. This is where the magic happens. Bind() is the new feature in ASP.NET 2.0 which does the two-way data binding. It populates the field with the value from the DataSource and carries the value back into the DataSource parameters. The second parameter is the data formatting string and luckily it works here. The birthdate is formatted now. My guess is that the BoundField implementation simply forgets to pass the DataFormatString into the underlying control. Hopefully it will be fixed in the next version of .NET. But for now we can deal with it by simply converting the column to a template column.

3.

Everything looks good so far, but let's dig a little deeper. Take a look at the "sunsign" column. It is an integer with numbers 1-12 indicating the zodiac sign corresponding to the birthdate. While we can modify this field via the form, it is not the best idea. It would make more sense to calculate this column based on the birth date and update it automatically. Can we do it easily? We can certainly try. Let's convert that field to a TemplateField, and change the TextBox in the Edit template to the Label:

Now we have a read-only column. Let's try to change the binding statement. Suppose that we have a CalcSign method that takes a date as a parameter and returns a zodiac sign. Can we do something like this? -

   <EditItemTemplate>
      <asp:Label ID="TextBox2" runat="server" Text='<%# CalcSign(Bind("sunsign")) %>'></asp:Label>
   </EditItemTemplate>

Nope we cannot. The Bind() method is not valid in this context. It cannot appear in the parameter list. Fortunately we also have the Eval() method that can:

   <EditItemTemplate>
      <asp:Label ID="TextBox2" runat="server" Text='<%# CalcSign(Eval("sunsign")) %>'></asp:Label>
   </EditItemTemplate>

Unforunately, the Eval() method doesn't provide the luxury of two-way binding. While it will work for displaying the correct value, the parameter that is passed into the UPDATE statement will be NULL. So we arrived at the head-scratching post. I still want my two-way binding and I cannot have a calculated column!

4.

The easiest way to solve this problem is to write one line of code. Let's implement an event handler for the ItemUpdating event of the DetailsView. It fires right before the UPDATE statement is executed and in its arguments it has two important collections - NewValues and OldValues. And we can do anything we want with these values:

In this case we only want to take the new value of the "birthdate" column, convert it to the zodiac sign and replace the value of the "sunsign" column. We should implement the same event handler for ItemInserting event. So here is the one line solution to a common problem.

5.

Another useful event is ItemUpdated (ItemInserted). What we can do here is catch and process the database exception:

This is really useful, because you don't want the yellow screen with errors to appear and since everything is so "code-less", there is no place to implement a try-catch block if anything goes wrong. This try-catch is implemented for you and is wired to the ItemUpdated event. Make sure you set the ExceptionHandled property to true, otherwise the exception will bubble up to the user.

Welcome to the 2-way binding!