ASP / HTML / C# Data Access

The <%# ....%> indicates a data source. A data source could be a page property, an expression, function call or any object that implements the ICollection / IEnumerable / IListSource interface (i.e. ArrayList, Hashtable, DataView, DataReader, etc.).

Values are not substituted until the DataBind() method of the control is called. DataBind() is recursive, so calling it for a container object results in all the controls within that container calling their DataBind() methods. As such, DataBind() is normally called only once, called for the Page object and called in the Page_Load event.

The DropDown, DataGrid, DataView, ListBox and HTMLSelect bind to a datasource using the datasource parameter.  ?? Radio buttons?

Examples:

Hello <%# cUserName %>. How are you today?

You are <%# GetAge(customer.DOB).ToString() %>

oDropDown.DataSource = myArray;

<asp:ListBox datasource ='<%#myArray%>' runat="server" />

The result of a <%# ... %> substitution must be a character string. Rather than writing expressions such as <%# String.Format("{0:c}", ((DataRowView)Container.DataItem)["iField5"])%> .Net supports the equivalent syntax <%# DataBinder.Eval(Container.DataItem, "iField5", "{0:c}") %> (actually this isn't strictly equivalent as the first version executes much faster as the second version uses late binding and reflection).

The parameters for DataBinder.Eval are Container, Field Name[, Format string].

Equivalent to using System.Data.SQL.... is <%@ Import Namespace="System.Data...." %>

DataSet objects give "disconnected views" of the data. Reader objects maintain a connection to the server. DataSets can also be filled from XML using a FileStream object and the various ReadXML... functions.

Example:

SqlConnection oConnection = new SqlConnection("server=(local);database=pubs;user id=sa;password=");

//DataSet - Note oConnection is opened / closed automatically by the SqlAdapter object
SqlDataAdapter oAdapter = new SqlDataAdapter("select * from authors", oConnection);
DataSet oDS = new DataSet();
oAdapter.Fill(ds, "authors");        <-- Note that it is a method of the adapter that is called to fill the dataset, not a method of the dataset.

//Reader - Note that oConnection has to be opened/closed manually and that the connection does not have to be open for the
//                construction of oCommand.
SqlCommand oCommand = new SqlCommand("select * from authors", oConnection);
oConnection.Open();
SqlDataReader oReader = oCommand.ExecuteReader();
oConnection.Close();

SqlDataReader uses SQL's good-old TDS (Tabular Data Stream) protocol directly, so (a) should give good performance and (b) is forward only.

There is a potential security issue with using connection strings in that users could embed additional commands in the username and/or password fields. For example entering a user name of "sa;database=someOtherDB".

Parameterised views can be created using the @placeholder syntax. For example:

SqlDataAdapter oDA = new SqlDataAdapter("select * from authors where city = @city", oConnection);
...
oDA.SelectCommand.Parameters.Add(new SqlParameter("@city", SqlDbType.NVarChar, 2));
oDA.SelectCommand.Parameters["@city"].Value = "Bristol";
...
oDA.Fill(oDataSet, "myResults");

Note: Placeholders are not limited to SELECT statements, but can also be used, for example, in the VALUES clause of a INSERT statement.

Useful Connection Properties

• Application Name
   
• Connect Timeout / Connection Timeout
   
• Connection Lifetime
  When a pooled connection is returned to the pool it is destroyed if it's creation time was further in the past than this period (in seconds). The idea is to force load balancing with multiple servers where servers are regularly added / removed.
   
• Connection Reset [bool]
  By default, pooled connections returned to the pool are reset to the default state. By setting this value to false, this is prevented giving a performance hit with the proviso that when the connection is next taken from the pool it may not be in the configuration the programmer expected.
   
• Integrated Security / Trusted_Connection [bool]
  SQL: Uses the security context in which the server is running to connect. ?? I think its the server's security context, but it could be the browser user's ?? If this is set to true the password and user id field are not required.
   
• Min Pool Size & Max Pool Size
  Default to 0 & 100 respectively
   
• Pooling [bool]
  Defaults to true!

Note that (a) "yes" and "no" can be used in place of "true" and "false" for boolean properties and (b) integer properties are set/read as strings.

Calling a Stored Procedure

Set the SqlCommand object's CommandText property to the name of the stored procedure and it's CommandType property to CommandType.StoredProcedure. Parameters can be declared and passed in the normal manner (note: the parameters should not be included in the CommandText line ?? really true ??)

DataSource property accepts a IEnumerable, ICollection, DataSet or DataReader object.

DataSource property is overloaded such that oDataGrid.DataSource = oDataSet.Tables("myTable"); equates to oDataGrid.DataSource = oDataSet; oDataGrid.DataMember = "myTable";.

As ever, the DataBind() method must be called to expand the results.

Example:

    oDataSet1.DataSource = oDataSet.Tables("table1");
    oDataSet2.DataSource = oSQLDataReader;
    Page.BindData();

All controls (i.e. everything descended from the Control object) have a property called EnableViewState which determines whether the state of the control is retained across subsequent HTTP requests (via a hidden field). In many cases it is unnecessary to save this information for DataGrids as you are likely to want to refresh the page (i.e. reissue & rebind the select) whenever the user hits refresh. To prevent the system recording the contents of the datagrid (it could be a large amount of data to transfer) only to have it replaced by your custom requery code, set this property to false.

Editing in a Grid

A specific row in the DataGrid can be replaced with a series of text boxes by setting the DataGrid's EditItemIndex to the line number (set to -1 (the default) to prevent editing). The EditItemStyle property object can be used to change the appearance of the line being edited. Note: Changes to EditItemIndex & EditItemStyle do not take place until the next BindData() call.

The OnEditCommand, OnCancelCommand and OnUpdateCommand function pointer properties of the DataGrid control can be set to name of a specific handler to be called upon each of those events. For example: oDataGrid.OnEditCommand = "myEditCommand". The handler prototype should be (Object sender, DataGridCommandEventArgs E). The DataKeyField property can be used to identify a column in the grid that can be referenced from the handler to identify the record the row relates to.

Examples:

    (In HTML form)
    <asp:DataGrid id="myGrid" runat="server"
        OnEditCommand="myEdit" OnCancelCommand="myCancel" OnUpdateCommand="myUpdate"
        DataKeyField="au_id">
        <Columns>
            <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
        </Columns>
    </asp:DataGrid>

    (In code)
    public void myEdit(Object s, DataGridCommandEventArgs e) {
        // Move to selected line
        myGrid.EditItemIndex = (int) e.Item.ItemIndex;
        // Apply this change - Note that we have to require the data (myGrid.DataBind() on its one
        // will indeed put us into edit mode, but will also leave us with no data in the grid!)
        BindGrid();
    }
    public void myCancel(Object s, DataGridCommandEventArgs e) {
        // Cancel edit
        myGrid.EditItemIndex = -1;
        BindGrid();
    }
    public void myUpdate(Object s, DataGridCommandEventArgs e) {
        // Send update (only do forename and surname fields for brevity)
        SqlConnection oConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
        SqlCommand oCommand = new SqlCommand(
            "update authors set au_lname = @au_lname, au_fname = @au_fname where au_id = @au_id", oConnection);
        oCommand.Parameters.Add(new SqlParameter("@au_lname", SqlDbType.NVarChar, 40));
        oCommand.Parameters.Add(new SqlParameter("@au_fname", SqlDbType.NVarChar, 20));
        oCommand.Parameters.Add(new SqlParameter("@au_id", SqlDbType.NVarChar, 11));
        oCommand.Parameters["@au_lname"].Value = ((TextBox) e.Item.Cells[2].Controls[0].Text;
        oCommand.Parameters["@au_fname"].Value = ((TextBox) e.Item.Cells[3].Controls[0].Text;
        oCommand.Parameters["@au_id"].Value = myGrid.DataKeys[(int) e.Item.ItemIndex];
        oConnection.Open();
        oCommand.ExecuteNonQuery();
        oConnection.Close();
    }
    public void Page_Load(object sender, System.EventArgs e) {
        // Only want data from server once
        if (!Page.IsPostBack) BindGrid();
    }
    public void BindGrid() {
        SqlConnection oConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
        SqlDataAdapter oCommand = new SqlDataAdapter("select * from authors", oConnection);
        DataSet oDS = new DataSet();
        oCommand.Fill(ds, "authors");
         myGrid.DataSource = ds.Tables["authors"].DefaultView;
         myGrid.DataBind();
    }

Column Formatting and Validation

Unlike automatically generated columns, BoundColumns can have properties set at an individual level, including the ReadOnly setting. Bound columns are created using the <asp:BoundColumn... /> tag in the columns section of the grid declaration. Properties valid for a BoundColumn are:

• DataField
• DataFormatString       -    A standard formatting string
• HeaderStyle / FooterStyle / HeaderText / FooterText / ItemStyle
• HeaderImageUrl        -     Image to be shown in header    ?? can it be used at the same time as HeaderText
• ReadOnly                  -     Set to false to prevent this column being editable
• SortExpression          -     The name of the field to be passed to the OnSortCommand handler. Setting this value causes the header to be rendered as a LinkButton.
• Visible

Example:

    <asp:DataGrid id="myGrid" runat="server"
        OnEditCommand="myEdit" OnCancelCommand="myCancel" OnUpdateCommand="myUpdate" OnSortCommand="mySort"
        DataKeyField="au_id" AutoGenerateColumns="false" AllowSorting="true">
        <Columns>
            <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
            <asp:BoundColumn HeaderText="Primary Key" SortExpression="au_id" ReadOnly="True" DataField="au_id" ItemStyle-Wrap="false"/>
            <asp:BoundColumn HeaderImageUrl="images\myHeader.gif" DataField="au_fname" />
            <asp:BoundColumn HeaderText="Last Name" SortExpression="au_lname" DataField="au_lname" />
        </Columns>
    </asp:DataGrid>

Displaying Different Control Types in View and Edit mode

Individual columns can be formatted using <asp:TemplateColumn> tag within the <Columns> tag. Within <TemplateColumn> <ItemTemplate> determined the normal display format and <EditItemTemplate> the display format when editing. For example (paraphrased from MS code):

(In HTML form)
<Columns>
    <asp:TemplateColumn HeaderText="State" SortExpression="state">
        <ItemTemplate>
            <asp:Label runat=server Text='<%# DataBinder.Eval(Container.DataItem, "state") %> />
        </ItemTemplate>
        <EditItemTemplate>
            <asp:DropDownList runat=server SelectedItem = '<%# GetStateIndex(DataBinder.Eval(Continer.DataItem", "state"))%> >
                <asp:ListItem>CA</asp:ListItem>
                <asp:ListItem>ND<asp:ListItem>
                ...
            </asp:DropDownList>
        </EditItemTemplate>
    </asp:TemplateColumn>
    ....
</Columns>

(In Code)
public Hashtable StateIndex;

protected void Page_Load(Object source, EventArgs E) {
    ....
    // Convert state data to an index (as expected by asp:DropDownList)
    StateIndex = new Hashtable();
    StateIndex["CA"] = 0;
    StateIndex["ND"] = 1;
    ....
}

public int GetStateIndex(string strState) {return StateIndex[strState]==null?0:(int)StateIndex[strState].;}

Notes:

• As asp:TemplateColumn replaces asp:BoundColumn we have to use an expression rather than simply setting DataField.
• Although its in quotes, SelectedItem requires an int value not a string.
• Because there are now two controls in the state column and additional a DropDownList cannot be cast to TextBox, we would have to write our update placeholder code for state as oCommand.Parameters["@state"].Value = ((DropDownList) e.Item.Cells[3].Controls[1].SelectedItem.ToString();
• For clarity & future proofing, it would probably be better to give each control it's own ID value and use the find function instead of using cell offsets. For example: oCommand.Parameters["@state"].Value = ((DropDownList) e.Item.FindControl("edtState")).SelectedItem.ToString();. [why the compiler can't resolve e.Item.edtState I'm not sure]

Button Columns

A button column can be defined within the <Columns> clause using the <asp:ButtonColumn> tag. For example:

        <Columns>
            ...
            <asp:ButtonColumn Text="Do Something" CommandName="myCommand" ButtonType="LinkButton"/>
            ...
        </Columns>

DataField can be used in place of Text to generate the button caption dynamically. Standard formatting can be applied to the data source using DataFormatString.

When clicked the OnItemCommand handler identified in the DataGrid tag is called and passed the value of CommandName as one of the properties of the event args parameter.

Note: It appears (the documentation is blatantly absence in this matter) that specifying a CommandName of Delete causes OnDeleteCommand to be run instead of OnItemCommand. Empirical testing If handlers are supplied for both OnItemCommand and OnDeleteCommand then the OnItemCommand handler is called first followed by the OnDeleteCommand handler.

HyperLink Columns

A hyperlink column can be defined within the <Columns> tag as follows:

    <Columns>
        ...
        <asp:HyperLinkColumn Text="Buy!" NavigateUrl="anotherpage.htm" />
        ...
    <Columns>

A frame can be specified for the new page using the Target property.

Dynamic hyperlinks can be generated using:

• DataNavigateUrlField
  Specifies the data portion of the URL. For example: DataNavigateUrlField="au_id"
   
• DataNavigateUrlFormatString
  Specifies the template into which DataNavigateUrlField is placed. For example: DataNavigateUrlFormatString="myResultForm.aspx?record={0}
   
• DataTextField / DataTextFormatString
  Similar to the above, but specifies the link captions dynamically

As per grids, this controls are primarily configured using the DataSource property and the DataBind() function. Unlike DataGrid, this control contain no formatting or layout information themselfs but instead rely on <ItemTemplate> sub-tags to enclose a batch of layout data that is to be repeated once per record. Header and footer information can be included within DataList / Repeater control, but it only displayed once. For example:

<asp:Repeater id="myRepeater" runat="server">
    <HeaderTemplate>Hello, this is some plain text in the header</HeaderTemplate>
    <ItemTemplate>
        This is a repeated line for <%# DataBinder.Eval(Container.DataItem, "au_lname") + "," + "DataBinder.Eval(Container.DataItem, "au_fname") %><br>
    </ItemTemplate>
    <FooterTemplate>Some text in the footer</FooterTemplate>
</asp:Repeater>

asp:Repeater supports OnItemCommand, but none of the other On...Commands that were supported by grids.

As stated earlier, we can bind to anything supporting one of several interfaces. The exact type of Container.DataItem will be dependent on the collection (or whatever) we binded to. For example, it could be DataRowView or Person (for a fictional ArrayList of Person objects).

Other sub-tags of interest include:

• AlternatingItemTemplate
  The template to be used for every other item in place of <ItemTemplate>
   
• SeparatorTemplate
  The separator to be displayed between lines

Very similar to the Repeater control except control of formatting outside of the current "box" is allowed. For example, if you want to display multiple columns (where each "row" in each column is a different record) or to use the <div> tag, you need to use the DataList control.

<asp:DataList runat=server RepeatColumns="2" RepeatDirection="Horizontal">
    <ItemTemplate>
        This is a repeated line for <%# "DataBinder.Eval(Container.DataItem, "au_fname") %><br>
    </ItemTemplate>
</asp:DataList>

DataList supports the <EditItemTemplate> sub-tag that allows another template to be used when in edit mode.

DataList supports the On...Commands for Select, Edit, Update, Item and Cancel.