Nested declarative data binding in ASP.NET 2.0

UPDATE [employee] SET [fname] = @fname, [minit] = @minit, [lname] = @lname, [job_id] = @job_id, [job_lvl] = @job_lvl, [pub_id] = @pub_id, [hire_date] = @hire_date WHERE [emp_id] = @emp_id

 

Here is a sample SqlDataSource that encapsulates these two commands (using just Select and Update to keep things simple):

 

<asp:SqlDataSource ID="_employeeDataSource" runat="server"

        ConnectionString="<%$ ConnectionStrings:pubsConnectionString1 %>"

        SelectCommand="SELECT e.emp_id, e.fname, e.minit, e.lname, e.job_id, e.job_lvl, e.pub_id, e.hire_date, job_desc, pub_name FROM employee AS e INNER JOIN jobs AS j ON e.job_id=j.job_id INNER JOIN publishers AS p ON e.pub_id=p.pub_id"

        UpdateCommand="UPDATE [employee] SET [fname] = @fname, [minit] = @minit, [lname] = @lname, [job_id] = @job_id, [job_lvl] = @job_lvl, [pub_id] = @pub_id, [hire_date] = @hire_date WHERE [emp_id] = @emp_id">

  <UpdateParameters>

    <asp:Parameter Name="fname" Type="String" />

    <asp:Parameter Name="minit" Type="String" />

    <asp:Parameter Name="lname" Type="String" />

    <asp:Parameter Name="job_id" Type="Int16" />

    <asp:Parameter Name="job_lvl" Type="Byte" />

    <asp:Parameter Name="pub_id" Type="String" />

    <asp:Parameter Name="hire_date" Type="DateTime" />

    <asp:Parameter Name="emp_id" Type="String" />

  </UpdateParameters>

</asp:SqlDataSource>

 

Now, if you attach a GridView to this data source you typically will want to give the user a drop-down list in update mode so that she can select from the proper list of publishers and jobs. This is where declarative data sources shine, because you can create a template column for the job and publisher columns, and in their UpdateItemTemplates, specify a DropDownList with an associated DataSourceID attribute pointing to another declarative data source prepared to retrieve all of the jobs and publishers separately in a nested databind. Futhermore, you can use a databinding expression to set the selected element of the dropdown to the currently selected value for that column in the current row. Even better, since these lookup tables are unlikely to change very often, you can enable caching on their data sources and keep them in memory, all through properties of the data source control. Plus, because of Control state, all of this works even with ViewState disabled (as I've done here). So here is an example of a GridView pointing to the DataSource listed above, with two nested data binds when rendered in Update mode, grabbing table from a pair of lookup tables cached in memory (after the first access) for 200 seconds.

 

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"

                    EnableViewState="false" DataKeyNames="emp_id" DataSourceID="_employeeDataSource"

                    EmptyDataText="There are no data records to display.">

  <Columns>

    <asp:CommandField ShowEditButton="True" />

    <asp:BoundField DataField="emp_id" HeaderText="emp_id" ReadOnly="True"

                          SortExpression="emp_id" Visible="False" />

    <asp:BoundField DataField="fname" HeaderText="fname" SortExpression="fname" />

    <asp:BoundField DataField="minit" HeaderText="minit" SortExpression="minit" />

    <asp:BoundField DataField="lname" HeaderText="lname" SortExpression="lname" />

    <asp:TemplateField HeaderText="Job" SortExpression="job_id">

    <EditItemTemplate>

      <asp:DropDownList runat="server" ID="_jobDropDown" DataSourceID="_jobDataSource"

                                EnableViewState="false"

                                AppendDataBoundItems="false" DataTextField="job_desc"

                                DataValueField="job_id" SelectedValue='<%# Bind("job_id") %>'>

        <asp:ListItem Selected="true" Text="[Select a job]" Value="-1" />

      </asp:DropDownList>

    </EditItemTemplate>

    <ItemTemplate>

      <asp:Label ID="Label1" runat="server" Text='<%# Bind("job_desc") %>' />

    </ItemTemplate>

  </asp:TemplateField>

  <asp:BoundField DataField="job_lvl" HeaderText="job_lvl" SortExpression="job_lvl" />

  <asp:TemplateField HeaderText="Publisher" SortExpression="pub_id">

    <EditItemTemplate>

      <asp:DropDownList runat="server" ID="_publishersDropDown" DataSourceID="_publishersDataSource"

                                EnableViewState="false" AppendDataBoundItems="false" DataTextField="pub_name"

                                DataValueField="pub_id" SelectedValue='<%# Bind("pub_id") %>'>

        <asp:ListItem Selected="true" Text="[Select a publisher]" Value="-1" />

      </asp:DropDownList>

    </EditItemTemplate>

    <ItemTemplate>

      <asp:Label ID="Label2" runat="server" Text='<%# Bind("pub_name") %>'></asp:Label>

    </ItemTemplate>

  </asp:TemplateField>

  <asp:BoundField DataField="hire_date" DataFormatString="{0:d}" HeaderText="hire_date"        

                        HtmlEncode="False" SortExpression="hire_date" />

  </Columns>

</asp:GridView>

 

<asp:SqlDataSource ID="_jobDataSource" runat="server"

                            SelectCommand="SELECT job_id, job_desc FROM jobs"

                            EnableCaching="true" CacheDuration="200"

                            ConnectionString="<%$ ConnectionStrings:pubsConnectionString1 %>" />

<asp:SqlDataSource ID="_publishersDataSource" runat="server"

                            SelectCommand="SELECT pub_id, pub_name FROM publishers"

                            EnableCaching="true" CacheDuration="200"

                            ConnectionString="<%$ ConnectionStrings:pubsConnectionString1 %>" />

---

Posted Oct 31 2005, 09:30 AM by fritz-onion