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

Tutorial 4: Adding Parameterized Methods within Table Adapters

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 ObjectByVal 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.


View Entire Article

User Comments

Title: mn   
Name: Manpreet
Date: 2009-02-03 7:02:09 AM
Comment:
Very Good, It helped me
thnX
Title: NVP   
Name: NVP
Date: 2008-09-02 12:24:25 AM
Comment:
Nice one, Thanx a lot.
Title: ASP.NET   
Name: Muhamamd Ismail
Date: 2008-08-22 12:54:01 AM
Comment:
Dear I am design website in Dreamwear now I want upload the result of studnets in website using asp.net any one can help me how I can make its please and visit website gave me your suggestion how I make it in asp.net www.apnakhuzdar.com and my emailaddress ismardoi2001@hotmail.com
Title: ASP.NET   
Name: Muhamamd Ismail
Date: 2008-08-22 12:50:49 AM
Comment:
Dear I am new User of ASP.NET now working in Asp.net I haveing that problem of how work with ado.net in C# any one can help me in asp.net kindly send me ismardoi2001@yahoo.com
Title: ASP.NET   
Name: Muhammad Ismail
Date: 2008-08-22 12:47:13 AM
Comment:
I am new user of asp.net 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
Comment:
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
Comment:
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
Comment:
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
Comment:
Very good articles..
Title: Excellent Articles   
Name: Mohammad Javed
Date: 2008-06-18 6:21:06 AM
Comment:
Nice Article.
Title: Good   
Name: John
Date: 2007-11-27 2:39:07 AM
Comment:
nice Article
Title: Good article   
Name: Basir
Date: 2007-09-27 1:12:37 PM
Comment:
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
Comment:
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
Comment:
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
Comment:
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
Comment:
Clear Explanation with a good examples!!!!!!!!!!!

thankz a lot
Title: perfect !!   
Name: Jan
Date: 2006-10-04 4:17:05 PM
Comment:
Thank you
Title: Many thanks   
Name: Rolly
Date: 2006-09-08 10:09:08 AM
Comment:
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
Comment:
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
Comment:
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 ASPAlliance.com  |  Page Processed at 2021-10-18 3:07:24 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search