The “Create TableAdapter” wizard will first prompt me for
the database to use, and provide a list of all database connections currently
registered in Visual Web Developer’s server-explorer window (note: you can also
create new connections from this wizard if you want).
After I choose the Northwind database I want to use, it will
prompt me for where to store the database connection-string. By default it
will avoid hard-coding it within your code, and will instead save it within the
new <connectionStrings> section of your web.config file (or app.config
file if you are in a Class Library Project). Note that with .NET 2.0 you can
now optionally
encrypt configuration file values to keep them secure, as well as use the
new ASP.NET 2.0 Property Page within the IIS GUI Admin Tool to change it later
(ideal for administrators). Pick a name for what you want to call it:
Figure 6
You can then choose how you want to configure the
TableAdapter – you can use either SQL Statements that you embed within your DAL
layer, or stored procedures (SPROCs) that you call from it:
Figure 7
For our first table adapter we’ll use a SQL Statement. You
can either type this in directly within the next window:
Figure 8
Or alternatively launch the query-builder from that wizard
step to graphically construct it (one handy feature with it is the ability to
execute test queries to validate results):
Figure 9
Once we’ve finished building and testing our query, we will
return back to the “Enter a SQL Statement” page. Before moving to the next
page, we’ll want to click the “Advanced Options” button to verify what type of
operations we want the designer to generate:
Figure 10
The above dialog shows the default settings that are set
when you run the “Create TableAdapter” wizard from within a VS 2005 Web Site
Project (these are the most common data patterns we typically see developer’s
use with stateless web applications). When you run the wizard from within a
Class Library Project or from a Windows Client Project, the “Use Optimistic
Concurrency” checkbox will be selected by default as well.
For the purposes of this DAL walkthrough, we want to turn
off optimistic concurrency (note: one of my later blog postings on optimistic
concurrency will change that setting – but I will be showing straight updates
first).
When we click next on the wizard again, we’ll be prompted
for the method names we want to create in our DAL to run our Category SQL
query. There are two patterns of data usage that this data wizard can generate
– one is the “Fill” pattern used with DataSets, that will generate a method
that accepts a DataSet or DataTable parameter that we want to add category
information to. The second pattern of data usage is one where we will generate
a method that will simply return a new DataTable containing our category
information. This later approach is the one we’ll be primarily using in all of
our later tutorials, and is more typical with stateless web-applications.
In the wizard I’m going to name this DataTable method
“GetAllSuppliers”. I will also keep the “GenerateDBDirectMethods” checkbox to
automatically create default Insert, Update and Delete methods based off of my
Select query on the component:
Figure 11
The wizard will then list all of the DAL methods it will
create as part of this wizard process for, and when I hit finish add them to
our data designer:
Figure 12
What I now have is a strongly-typed DataAdapter class (by
default named “NorthwindTableAdapters.SuppliersTableAdapter”) with a
“GetAllSuppliers” method that I can use to execute a SQL query and obtain back
a strongly-typed DataTable results class (by default named
“Northwind.SuppliersDataTable”).
I could use these objects to easily obtain all of the
Suppliers information from the Northwind database and output it within a page
like so:
Dim suppliersAdapter As NewNorthwindTableAdapters.SuppliersTableAdapter
Dim suppliers As Northwind.SuppliersDataTable
Dim supplier As Northwind.SuppliersRow
suppliers = suppliersAdapter.GetAllSuppliers()
For Each supplier In suppliers
Response.Write("Supplier: " &supplier.CompanyName & "<br>")
NextNotice that I don’t need to write any manual ADO.NET code,
construct a SQL query, or manage connections at all in the above code – all of
that is encapsulated by the SuppliersDataTable and SuppliersTableAdapter that
we now have in our project. Notice also how the SuppliersTableAdapter class is
strongly typed – meaning I get intellisense and compilation checking on the
“suppliersAdapter.GetSuppliers()” method. I can also access each property
returned in the Suppliers result in a strongly typed fashion (for example:
supplier.CompanyName or supplier.SupplierID).
I could alternatively write the below .aspx page and
associated .aspx.vb code-behind file to easily databind and output the results
in a grid:
SuppliersTest2.aspx:
<%@ Page Language="VB" AutoEventWireup="false"
EnableViewState="false" CodeFile="SupplierTest2.aspx.vb" Inherits="SuppliersTest2" %>
<html>
<head>
<title>Simple Category Listing</title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div class="datatable">
<h1>Simple Category Listing</h1>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
SuppliersTest2.aspx.vb:
Imports NorthwindTableAdapters
Partial Class SuppliersTest2
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim suppliersAdapter As New SuppliersTableAdapter
GridView1.DataSource = suppliersAdapter.GetAllSuppliers()
GridView1.DataBind()
End Sub
End Class
Which then generates the below output at runtime:
Figure 13
Note that when using the new ASP.NET ObjectDataSource
control we will be able to eliminate the need to write any code for the scenario
above (which even now only has three lines), as well as to automatically enable
paging, sorting and editing on the GridView without any page code needed. I’ll
cover how to-do this in a later blog posting on using this DAL. My goal with
the sample above was just to show how you could procedurally databind the
GridView using techniques you might already be familiar with in ASP.NET 1.1.
