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

Tutorial 7: Putting the Northwinds DAL Together

The above tutorials hopefully provide the basic background needed to create DAL’s using the DataSet designer and then use them from code.

Using the above knowledge, you should be able to go off and quickly create the following strongly-typed TableAdapters with associated data methods pretty easily (note that the designer and query builder can be used to avoid having to manually write any of the SQL queries below – and it will auto-suggest subsequent queries for each adapter after you add the first one).

SuppliersTableAdapter:

GetAllSuppliers:

SELECT         SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM            Suppliers

GetSuppliersByCountry:

SELECT         SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM            Suppliers

WHERE          Country=@Country

GetSupplierBySupplierId

SELECT         SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM            Suppliers

WHERE          SupplierId=@SupplierID

GetUniqueSupplierCountries

SELECT         DISTINCT Country

FROM            Suppliers

CategoriesTableAdapter:

GetAllCategories:

 

SELECT         CategoryID, CategoryName, Description

FROM  Categories

GetCategoryByCategoryId

SELECT         CategoryID, CategoryName, Description

FROM  Categories

WHERE          CategoryId=@CategoryId 

EmployeesTableAdapter:

GetAllEmployees:

SELECT         EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM  Employees

GetEmployeeByEmployeeID

SELECT         EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM  Employees

WHERE          EmployeeID=@EmployeeID

GetEmployeesByManager:

SELECT         EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM            Employees

WHERE          ReportsTo = @ReportsTo

ProductsTableAdapter:

GetAllProducts:

SELECT         ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM            Products

GetProductsBySupplierId:

SELECT         ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM            Products

WHERE SupplierID=@SupplierID

GetProductsByCategoryId:

SELECT         ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM            Products

WHERE CategoryID=@ CategoryID

Most of the above TableAdapters are pretty straight-forward and simple.  The one that is a little more advanced is the ProductsTableAdapter.  Specifically, there in addition to retrieving the Products table columns I’m also retrieving the CompanyName from the Suppliers table that maps to the Product’s SupplierId column (it has a foreign key relationship to the Suppliers table).  The above SQL statement will add this as a read-only “SupplierName” column on our ProductsDataTable.

This will allow me to be more efficient from a database perspective when I want to build a list UI on top of my Products table, and save me from having to hit the database an extra time per-row to retrieve this value when displaying a product list on the site. 

The good news is that the data designer can still infer enough from the product adapter’s SQL statements to still automatically generate the correct INSERT, UPDATE and DELETE commands for the ProductsTableAdapter.  In cases where you are doing more advanced JOINS across multiple tables and merging results from multiple places, the data designer might not be able to generate these automatically.  The good news is that you can still define these manually within the designer (just click on the table-adapter, and then within its property grid choose to create a new “Insert” (or update or delete) command and define your own logic).

When finished, my Northwinds data design-surface looks like this (note how the designer will automatically map and detail foreign-key relationships):

Figure 27

When I hit save on the Northwinds.xsd file (where all of the adapters, definitions and relations are declaratively stored in an XML file), I’ll be able to program and data-bind against any of the objects defined within it.


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 2:48:18 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search