-----------------------------------------------------------
New Message on MumbaiUserGroup
-----------------------------------------------------------
From: SlimmerSwamymca
Message 2 in Discussion
Nested declarative data binding in ASP.NET 2.0
I'm becoming more and more enamored with the declarative databinding model
of ASP.NET 2.0 the more that I use it. One issue that it deals with rather
nicely is the asymmetric nature of join queries and their corresponding
update statements, which I find to be one of the most common queries used in
Web applications since you are often presenting data from a table that
contains foreign-key references to other tables containing the complete name
and description (or whatever the extra data is).
To show what I mean, take the employees table in the pubs database (sample
database that comes with SQL server). The employees table has two foreign
key columns in it, job_id and pub_id, referencing the publishers and jobs
tables respectively. To properly present the data, you might build a query
with two inner joins to retrieve the names of the jobs and publishers:
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
However, if you are performing an update or an insert into the table, you
need to specify the foreign key id fields directly:
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 %>" />
_________________________________________________________________
Sexy, sultry, sensuous. - see why Bipasha Basu is all that and more. Try MSN
Search http://server1.msn.co.in/Profile/bipashabasu.asp
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/mumbaiusergroup/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
