Chapter 8

Transcript Chapter 8

Chapter 8
Working With Databases in ASP .NET
Listing 8.1 – ShowListControls
• Uses The SqlDataSource control for estabishing
database connectivity and query/update
• Includes all the list controls:
– BulletedList, CheckBoxList, DropDownList, ListBox, and
RadioButtonList
• Each List control has these properties for data
binding using an SqlDataSource
– DataSourceId – the ID of the SqlDataSource object
– DataTextField – the name of the column from the SQL
command that should be displayed
Listing 8.1 ShowListControls.aspx
Each of these are
subclasses of the
ListControl class.
ListControl is an
abstract class.
Listing 8.1 ShowListControls.aspx
The SQLDataSource control
allows the .aspx page to set
both database connections and
default queries.
 With this approach,
database connectivity and
query is part of the markup,
not the code-behind
SqlDataSource Control
• Represents an SQL database to data-bound controls
• Used to establish connection to a data source and
perform queries and updates to the data source.
• Some Properties
–
–
–
–
–
ConnectionString
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand
ConnectionString Property
<asp:SqlDataSource
id="srcMovies"
ConnectionString="Data Source=.\SQLExpress;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;
Integrated Security=True;User Instance=True"
SelectCommand="SELECT Title FROM Movies"
Runat="server" />
The ConnectionString property’s value depends on the database server and
host’s configurations. Above is the connection string from the book. For the
Lab computers use the following:
ConnectionString="Server=localhost;Database=MyDatabase;
Trusted_Connection=Yes;"
SelectCommand
<asp:SqlDataSource
id="srcMovies"
ConnectionString="Data Source=.\SQLExpress;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;
Integrated Security=True;User Instance=True"
SelectCommand="SELECT Title FROM Movies"
Runat="server" />
The SelectCommand property’s is a SQL SELECT statement.
Other properties include InsertCommand, UpdateCommand, and
DeleteCommand.
List Control Data Binding Properties
<asp:BulletedList
id="BulletedList1"
DataSourceId="srcMovies"
DataTextField="Title"
Runat="server" />
All List controls (databound) include these two properties:
• DataSourceId – the ID of the SqlDataSource object
• DataTextField – the name of the column from the SQL
SelectCommand that should be displayed
Browser’s rendering of HTML code.
Note, each list contains the result of the SQL
SELECT query of the associated SqlDataSource, with
the Title column being displayed.
Listing 8.6 BoundGridView.aspx
GridView is a databound control that renders an HTML table
based on a query.
Listing 8.2 ShowTabularDataBound.aspx
These six Web controls are
more complex and can
display multiple separate
columns from a query.
Browser’s rendering of HTML code.
Tabular DataBound Controls
Subclasses of
DataBoundControl
(newer)
– GridView – HTML table, one record per
displayed row
– DataList – HTML table, multiple records
can display on a row, requires templates
– Repeater – does not render an HTML table,
requires templates
– ListView – displayed via template; allows
sorting, paging, editing
• Single Data Record
– DetailsView – HTML Table of a single
record
– FormView – Single record displayed via
templates, allows paging
http://msdn.microsoft.com/en-us/library/ms228214.aspx
Not descended from
DataBoundControl
(older)
Subclasses of
CompositeDataBoundControl
• Multiple Data Records
Using Item Templates
<asp:FormView
id="FormView1"
DataSourceId="srcMovies"
AllowPaging="true"
Runat="server">
<ItemTemplate>
<%#Eval("Title")%>
directed by
<%#Eval("Director")%>
</ItemTemplate>
</asp:FormView>
<asp:Repeater
id="Repeater1"
DataSourceId="srcMovies"
Runat="server">
<ItemTemplate>
<%#Eval("Title")%>
directed by
<%#Eval("Director")%>
</ItemTemplate>
</asp:Repeater>
ItemTemplates allow you to
specify, using html and “old
ASP”, specific content to
display.
ItemTemplate is a sub-element
of the data bound Web control.
Old ASP
<asp:FormView
id="FormView1"
DataSourceId="srcMovies"
AllowPaging="true"
Runat="server">
<ItemTemplate>
<%#Eval("Title")%>
directed by
<%#Eval("Director")%>
</ItemTemplate>
</asp:FormView>
<asp:Repeater
id="Repeater1"
DataSourceId="srcMovies"
Runat="server">
<ItemTemplate>
<%#Eval("Title")%>
directed by
<%#Eval("Director")%>
</ItemTemplate>
</asp:Repeater>
Before .NET, ASP tags looked
like this:
<%#
%>
server-side code here
The Eval function call returns
the value of the field identified
in its argument.
Listing 8.10 ShowLinks.aspx
ItemTemplates can contain
other <asp:> tags as subelements.
Listing 8.13 ShowFormView.aspx
EditItemTemplates can contain other <asp:>
tags as sub-elements, and allow for Buttons to
submit data to the server.
The UpdateCommand includes @FldName. This is
called a placeholder.
This allows you to retrieve the data from the
associated TextBox controls in the form (and
DataKeyName for the primary key).
Listing 8.7
ShowControlParameter.aspx
This example illustrates
the use of control
parameters to modify
queries based on user
selection from a
DropDownList
Database Tables for this Example
Two tables, with one-to-many relationship.
Listing 8.7
ShowControlParameter.aspx
A DropDownList databound to a
SqlDataSource for the dominant table in the
relationship.
Listing 8.7
ShowControlParameter.aspx
Clicking the button simply performs
the submit to the server.
Listing 8.7
ShowControlParameter.aspx
Based on the userselected value from the
DropDownList, the other
SqlDataSource can
customize a query. This is
done through the control
parameter.
Listing 8.7
ShowControlParameter.aspx
A control parameter links a particular
control to an item in a SelectCommand
(or Update, Insert, Delete).
Note: there are other types of
parameters. The SelectParameters subelement represents a collection of
parameters to be used for the
SelectCommand. Each SelectParameter
will be associated with a placeholder in
the query.
Listing 8.7
ShowControlParameter.aspx
ControlParameter Properties:
• Name – identifies the placeholder to replace
in the query
• ControlID – identifies the control that
contains the value to put into the placeholder