Visual Basic 1002005 [A Developers Notebook] [Electronic resources] نسخه متنی

4.3. Bind to Data Without Writing Code

Most serious web applications
need to retrieve records from a database. In .NET, the database
access framework of choice is ADO.NET. However, writing the code to
perform common database operations with ADO.NET, such as opening a
connection and fetching a table of results, can be tedious and
repetitive, which is not what VB programmers have come to expect. To
simplify such tasks, ASP.NET 2.0 introduces several new
data source controls that greatly simplify the
task of retrieving data and binding it to a web page.

Note: With the new ASP.NET data provider controls, you can
generate and bind all your database code at design time, without
writing a single line of code.

4.3.1. How do I do that?

To use a new ASP.NET 2.0 data source control, all you need to do is
to drag it from the Visual Studio toolbox to a web page, configure a
few of its properties, and then bind it to other controls that
display the data it exposes. When you run the web page, the data
source control performs the heavy lifting, contacting your database
and extracting the rows you need.

ASP.NET ships with several data source controls, and more are
planned. Although the list has changed from build to build, the
latest release includes:

SqlDataSource

Interacts with a SQL Server database
(Version 7.0 or later).

XmlDataSource

Interacts with XML data from a file or
some other data source.

ObjectDataSource

Interacts with a custom object that you
create. The next lab, "Bind Web Controls to a Custom
Class," provides more details about this technique.

To try out no-code data binding, drag a new
SqlDataSource onto the design surface of a web
page from the Data section of the toolbox. Then, click the
control's smart tag and choose Configure Data
Source. Visual Studio will walk you through a short wizard in which
you specify the connection string for your database (which is then
set in the ConnectionString property) and the
query you want to perform (which is then set in the
SelectCommand property). Find your database
server, and select the Northwind database. Although you can build the
query dynamically by selecting the columns in a table, for this
example just specify the SQL string "SELECT
ContactName FROM Customers".

Note: Other data sources are planned to allow easy retrieval of
everything from directory listings to web service data.

When you're finished, Visual Studio will have added
a SqlDataSource control tag to your web page,
which looks something like this:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT ContactName FROM Customers"
ConnectionString=
"Data Source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=Northwind">
</asp:SqlDataSource>

This data source defines a connection to the Northwind database, and
a Select operation that retrieves a list of all contact names in the
Customers table.

Note: Remember, to modify any ASP. NET control, you have two
choices. You can select it and make changes in the Properties window,
or you can switch to Source view and edit the control tag.

Binding a control to your data source is easy. To try this out, drag
a BulletedList control onto your web page, which
you can use to show the list of contact names from the Customers
table. Click the smart tag, and select Connect to Data Source. You
can then choose the data source you want to use (which is the data
source created in the last step) and the field you want to display
(which is ContactName).

Here's the finished tag:

<asp:BulletedList ID="BulletedList1" Runat="server" 
DataSourceID="SqlDataSource1"
DataTextField="ContactName">
</asp:BulletedList>

Amazingly enough, these two control declarations are all you need to
create this data-bound page. When you run the page, the
BulletedList will request data from the
SqlDataSource, which will fetch it from the
database using the query you've defined. You
don't need to write a line of code.

For a little more sophistication, you could use another control to
filter the list of contacts by some other piece of criteria, like
country of residence. This raises a new problemnamely, how can
you update the query in the
SqlDataSource.SelectCommand according to the value
entered in the other control?

ASP.NET solves this problem neatly with parameters. To try it out,
start by adding a new data source that fetches a list of customer
countries from the database. Here's an example that
works in this case:

<asp:SqlDataSource ID="Countries" Runat="server" ConnectionString="..."
SelectCommand="SELECT DISTINCT Country FROM Customers">
</asp:SqlDataSource>

Next, add a DropDownList control named
lstCountries to expose the country list. You can
use the same approach as when you wired up the
BulletedList, or you can type the tag in by hand.
Here's the completed tag you need:

 <asp:DropDownList ID="lstCountries" Runat="server" 
DataValueField="Country" DataTextField="Country"
DataSourceID="Countries" AutoPostBack="True">
</asp:DropDownList>

Now you can modify the query that creates the customer list. First,
you insert a named parameter into your query. Remember to place an
@ symbol at the beginning of the parameter name so
SqlDataSource can recognize it. In this example,
use @Country. (The @ denotes a
named parameter when using the SQL Server provider.)

Here's what the revised data source tag should look
like:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT ContactName FROM Customers WHERE Country='@Country'
...
</asp:SqlDataSource>

Next, you add a definition that links the parameter to the
appropriate control. Once again, you can configure this information
in Visual Studio or by hand. In Visual Studio, select the
SqlDataSource control and click the ellipses next
to the SelectQuery property in the Properties
window. (Truthfully, there is no real SelectQuery.
That's just the way Visual Studio exposes the
SelectCommand and
SelectParameters properties to make it easier to
edit them as a single unit at design time.) In this case, you need to
create a new control parameter that retrieves the
SelectedValue property of the
lstCountries control.

Here's the revised data source tag once
you've added the parameter definition:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT ContactName FROM Customers WHERE Country=@Country"
ConnectionString=
"Data Source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=Northwind"
<SelectParameters>
<asp:ControlParameter Name="Country" 
ControlID="lstCountries" PropertyName="SelectedValue">
</asp:ControlParameter>
</SelectParameters>
</asp:SqlDataSource>

Note that the name of the control parameter matches the name of the
parameter in the SQL expression, with one minor quirk: the leading
@ symbol is always left out.

Figure 4-5 shows the completed page. When you
select a country from the drop-down list, the bulleted customer list
is refreshed with the matching customers automatically. You now have
a fair bit of functionality, and still have not written any code.

Figure 4-5. Linked data-bound controls with no code

This example should already suggest possibilities where you can use
multiple data source controls. For example, imagine you want to
provide a master-detail view of orders by customer. You could use one
data source to fill a listbox with customers. When the user selects a
customer, you could then use your other data source to perform a
query for the linked orders and show it in a different control.

4.3.2. What about...

...reasons not to use the new code-free
data-binding controls? Many right-thinking developers steer clear of
data-binding techniques because they embed database details into the
user-interface code. In fact, that's exactly what
this example does, which has negative consequences for
maintainability, optimization, and debugging. Quite simply, with
database details strewn everywhere in a large site,
it's hard to stay consistent.

ASP.NET developers
haven't forgotten about this side of things. With a
little care, you can use the data source providers and still
centralize your database logic. One of the best ways to do so is to
use the ObjectDataSource control, which allows you
to link to a custom class that you've created with
data access code. The next lab, "Bind Web Controls
to a Custom Class," demonstrates this technique.

Data sources also
provide a useful place to add more advanced functionality. One of the
most interesting examples is caching. If you set
EnableCaching to
true, the data source control will automatically
insert the retrieved data into the ASP.NET cache and reuse it in
future requests, potentially reducing your database load
dramatically. You can configure the amount of time an item is cached
by setting the
CacheDuration and
CacheExpirationPolicy properties.

4.3.3. Where can I learn more?

For more on caching and other advanced scenarios, look up the index
entry "data source controls" in the
MSDN help library.