Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
page 4 of 11
by Scott Guthrie
Average Rating: 
Views (Total / Last 10 Days): 52554/ 145

Tutorial 3: Creating our First Table Adapter

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>")

Notice 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:



<%@ Page Language="VB" AutoEventWireup="false" 
EnableViewState="false" CodeFile="SupplierTest2.aspx.vb" Inherits="SuppliersTest2" %>
    <title>Simple Category Listing</title>
    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
    <form id="form1" runat="server">
        <div class="datatable">
            <h1>Simple Category Listing</h1>
            <asp:GridView ID="GridView1" runat="server">


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()
    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.

View Entire Article

User Comments

Title: mn   
Name: Manpreet
Date: 2009-02-03 7:02:09 AM
Very Good, It helped me
Title: NVP   
Name: NVP
Date: 2008-09-02 12:24:25 AM
Nice one, Thanx a lot.
Title: ASP.NET   
Name: Muhamamd Ismail
Date: 2008-08-22 12:54:01 AM
Dear I am design website in Dreamwear now I want upload the result of studnets in website using any one can help me how I can make its please and visit website gave me your suggestion how I make it in and my emailaddress
Title: ASP.NET   
Name: Muhamamd Ismail
Date: 2008-08-22 12:50:49 AM
Dear I am new User of ASP.NET now working in I haveing that problem of how work with in C# any one can help me in kindly send me
Title: ASP.NET   
Name: Muhammad Ismail
Date: 2008-08-22 12:47:13 AM
I am new user of its very useful for all level I hope its is successful task .and
Title: Pain is just me....   
Name: Mohammad Javed
Date: 2008-06-18 6:27:59 AM
Using Insert, Update, and Delete DBDirect Commands on TableAdapters is very good articles fo us...
Title: Saeeedaa Khannnnnnnn   
Name: Mohammad Javed
Date: 2008-06-18 6:24:56 AM
I've been using ASP for 5yrs and felt the need to become current in my coding practices. I Spent 2 weeks trying to find a suitable method to work with Access DB so I could update my code and then update my DB to SQL. MS built in functionaliy is not very flexible. This functionality is so much better than RecordSet in ASP and your Demo actually Works. Good Job! Now I can get something done.
Title: Badal Verma   
Name: Badal Verma
Date: 2008-06-18 6:23:29 AM
I think this is very helpfull articles for all person beginner as well as professional.
Title: Very Good   
Name: Badal Verma
Date: 2008-06-18 6:22:06 AM
Very good articles..
Title: Excellent Articles   
Name: Mohammad Javed
Date: 2008-06-18 6:21:06 AM
Nice Article.
Title: Good   
Name: John
Date: 2007-11-27 2:39:07 AM
nice Article
Title: Good article   
Name: Basir
Date: 2007-09-27 1:12:37 PM
I found the information you have provided very useful and thorough. Thanks.
Title: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0   
Name: William
Date: 2007-07-19 11:43:06 PM
I've been using ASP for 5yrs and felt the need to become current in my coding practices. I Spent 2 weeks trying to find a suitable method to work with Access DB so I could update my code and then update my DB to SQL. MS built in functionaliy is not very flexible. This functionality is so much better than RecordSet in ASP and your Demo actually Works. Good Job! Now I can get something done.
Title: MR   
Name: Baktash Ahmed
Date: 2007-02-22 4:38:16 AM
This a very helptful and detailed explanation of how to create a data source and and modify and the table adapters. It has certainly helped me alot on my project thanks alot Scott for good job and keep it up
Title: This is willl help a great deal   
Name: tope Fatayo
Date: 2007-02-17 6:29:50 AM
This is a wonderful article. this should meet my data access need in my final year project
Title: nice explanation   
Name: B.D
Date: 2006-11-09 3:26:56 AM
Clear Explanation with a good examples!!!!!!!!!!!

thankz a lot
Title: perfect !!   
Name: Jan
Date: 2006-10-04 4:17:05 PM
Thank you
Title: Many thanks   
Name: Rolly
Date: 2006-09-08 10:09:08 AM
Many thanks for taking the time to put this together. It is the first time i use a Tableadapter and the blinkers are off my eyes. Cheers
Title: Well-done   
Name: Susantha Soysa
Date: 2006-08-22 8:42:21 AM
This opened my eyes to use partial classes effectively. Many thanks for your time.
Title: Great Article BUT...   
Name: Chester West
Date: 2006-07-12 7:29:43 PM
This is a great article telling the user how to setup a datasource HOWEVER it doesn't mention using the source in a web service (Microsoft got smart...they realized that the most time consuming part of the web service is getting data...therefore in the .NET 2.0 version they made it hard to create/use datasets in order to discourage anybody from using the web service)

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2021  |  Page Processed at 2021-10-18 2:30:38 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search