It is usually pretty rare in web applications to want to
request all of the data within a database table. More typically you’ll want to
use SQL queries to retrieve only the subset of data that you need. The data
designer makes it super easy to create multiple strongly-typed data-methods
within a DAL to help with the parameterized SQL operations.
To add one to our Suppliers object, right-click on it and
choose “Add Query”:
Figure 14
Once again we can choose either a stored procedure, or a SQL
statement. For the SQL statement you can choose to return multiple rows, or
just a single value (useful for things like SELECT Count(*) commands, or to
retrieve a single value):
Figure 15
For this parameterized query I’m going to return multiple
rows, and will be filtering the data by country value. I can express a parameter
value using a “@parametername” syntax within my SQL statement like so (note:
there can be any number of parameters in the SQL statement):
Figure 16
I can then name this parameterized data method
“GetSuppliersByCountry”:
Figure 17
And now I have two methods I can use within my
SuppliersTableAdapter to get Supplier data (GetAllSuppliers and
GetSuppliersByCountry). Note that the designer is fully re-entrant, meaning
you can at any point re-configure a data method (for example: to change the SQL
statement, add/remove parameters, etc). Just right click and select the
“configure” method to-do so:
Figure 18
You can also use the designer to run any of the data
methods. Just right-click and select “Preview Data” to execute them and see
the results (note how any parameters can be specified as part of this
operation):
Figure 19
And in code I can now easily build a page that uses this new
method like so:
SuppliersByCountry.aspx:
<%@ Page Language="VB" AutoEventWireup="false" EnableViewState="false"
CodeFile="SuppliersByCountry.aspx.vb" Inherits="SuppliersByCountry" %>
<html>
<head runat="server">
<title>Suppliers By Country Sample</title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<h1>Suppliers By Country:</h1>
<div>
Select a country: <asp:TextBox ID="CountryTxt" runat="server" />
<asp:Button ID="SearchBtn" Text="Search" runat="Server"/>
</div>
<div class="datatable">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView> </div>
</form>
</body>
</html>
SuppliersByCountry.aspx.vb:
Imports NorthwindTableAdapters
Partial Class SuppliersByCountry
Inherits System.Web.UI.Page
Protected Sub SearchBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles SearchBtn.Click
Dim suppliersAdapter As New SuppliersTableAdapter
GridView1.DataSource = suppliersAdapter.GetSuppliersByCountry(CountryTxt.Text)
GridView1.DataBind()
End Sub
End Class
Which will generate a Suppliers search page:
Figure 20
Note that I did not have to write any ADO.NET code, manually
create parameter collections, manage connection objects etc. I only had to
write 3 lines of code total to build the above sample from scratch using the
Visual Web Developer data and page designers.
