When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article
Jobs

ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
 
Print this Page!
Published: Tuesday, February 8, 2005

Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0

By Joseph Chancellor


"Never put off until run time what can be done at compile time."
David Gries, Compiler Construction for Digital Computers

Introduction


When we as developers are learning new techniques, examples can be our own worst enemies. Tutorials are designed to be easy to understand, but at the same time they often reinforce lazy, inefficient, and even dangerous coding practices. Nowhere is this more common than in ADO.NET samples. In this article we're going to take a look at what it means to strongly-type objects from your database, and why despite the lack of examples, you will almost always want to do so in your applications.

Specifically, we will see how to create and use strongly-typed DataSets in Visual Studio 2005. As this article explores, strongly-typed DataSets offer a number of advantages over alternative, loosely-typed data access techniques and, with Visual Studio 2005, creating and using strongly-typed DataSets has never been easier. Read on to learn more!

- continued -

The Basics and Benefits of Strongly-Typed Objects


To understand what it means to strongly type something, consider dating. If you are single, what type of person would you consider dating? You may have specific criteria (wealthy and attractive) or it might be short and vague (living and breathing). Whatever your qualifications are, you inevitably have a standard of some type that you use when you decide who to spend more time with. If you're wise, a thoughtful list will save you from unnecessarily painful emotional trauma. You may find, for example, that a serious relationship with an alcoholic is a recipe for instability and unpredictability. Because forcing someone to change is as painful as it is ineffective, wisdom would dictate that you stop the relationship before it even starts. Adding a non-alcoholic clause to your dating standard saves you heartache down the road, and allows you to focus your time and energy on better candidates.

You may wonder how this analogy applies to programming. Stay with me, reader! ADO.NET data access objects are designed for maximum flexibility. Unless you go to extra trouble when you read data from your database, you will be working with a lot of plain, un-typed objects - as generic as the .NET framework allows. Using our dating analogy, always treating your relational data as generic objects is a little like admitting, "I only date things composed of matter." Could you be less specific? You haven't even limited yourself to living creatures, much less humans! As your friend, I must implore you, "Have some standards! Narrow down your list a little!"

Just as neglecting to screen who you date can cause relational problems in the future, being loose with your objects can create errors in your code. In addition, because you let any old object waltz in and out of your subroutines, you're not going to know there's a problem until your application is executing at run time. In our dating analogy, catching errors at run time is like having a bitter and embarrassing argument with your date in the middle of a trendy Italian restaurant. Yes, you found the problem, but if you had planned ahead, you wouldn't end up with a crowd of diners staring at you and you wouldn't be wearing your cannelloni. If you simply applied some tighter standards to your code, you'd catch errors before your application starts - at compile time. For example, consider this code sample below:

string FirstName = myrow.("FirstName").ToString();

The DataRow in this example is untyped, and as a result, you have to access the value that you're looking for with the name of the column as a string (or, optionally, the index of the column in the collection of columns in the record). Hopefully, that column does indeed exist. The data type of a DataRow column is object; we're assuming that the underlying data type of the FirstName column is string, but we have to explicitly convert it to a string in order to use it. If the name of the column changes (to PersonFirstName, for instance), the compiler is helpless to notify you. Impressed? You shouldn't be. Your life would be much easier and your code more reliable, if your code looked more like this:

string FirstName = PersonRow.FirstName;

In the second example, we have a strongly-typed row, and we know the FirstName property is of type string. Here there are no messy column names as strings and no messy object conversions. The compiler is type checking for us, and we can move on to other tasks without worrying whether or not we typed the column name correctly.

All else being equal, you should never use a generic object when you can use a more specific one. But hold on for a moment. Where did that strongly-typed object come from? I wish I could tell you these objects are created for you automatically. However, just as having quality relationships takes time and energy, so also strongly typing your objects takes extra effort. The good news is that the extra time spent here is worth it and saves exponentially more time bug-hunting in the future.

There are several ways to do accomplish strongly typing and in the rest of this article we'll discuss how to create strongly-typed DataSets in Visual Studio 2005. We'll also look at the advantages and disadvantages of doing so, as well as some other common alternatives.

Creating Strongly-Typed DataSets in Visual Studio 2005


Strongly-typed DataSets are merely generic DataSets that have their columns and tables defined in advance so the compiler already knows what they will contain. Instead of wrapping your data as loosely as your hand might be wrapped by a mitten, a strongly-typed DataSet fits like a glove. Every consecutive version of Visual Studio makes the process of strongly typing a DataSet easier. In this example we'll use the AdventureWorks database from SQL Server 2005. Simply perform the following steps:
  1. Open Visual Studio, and create a new ASP.NET Website.
  2. In Solution Explorer, right-click to add a new item, and select DataSet. Give it the name AdventureWorks.xsd (view screen shot). Visual Studio will recommend placing the DataSet file inside the App_Code folder, which you should allow it to do for you.
  3. The AdventureWorks.xsd will open in design mode, and the TableAdapter Configuration Wizard will launch. For now, just click Cancel, as we will add tables by dragging them from the Server Explorer.
  4. Locate the Server Explorer Toolbox, navigate to your SQL Server 2005 database, and the AdventureWorks database. (If you do not have the AdventureWorks database installed, you can download it, plus other SQL Server 2005 examples, from Microsoft's SQL Server 2005 Samples and Sample Databases download page.)
  5. Drag the SalesOrderHeader and SalesOrderDetail tables to your DataSet Designer window. The window should now resemble the screen shot below. What are we looking at? For each table we added, Visual Studio created a strongly typed DataTable (the name is based on the original table) and a TableAdapter. The DataTable has each column defined for us. The table adapter is the object we will use to fill the table. By default we have a Fill() method that will find every row from that table.

This strongly-typed DataSet, as-is, will return all of the records in these two tables. Since the AdventureWorks database contains a lot of order information, why don't we create a more specific query? We can add methods to our TableAdapter object to retrieve a more specific subset of the records in the tables. Start by right-clicking on the SalesOrderHeaderTableAdapter, and select Add | Query. Pick "Use SQL statements" and click the Next button. Then, choose "SELECT which returns rows" and click Next. Finally, enter the following query in the window (or use the Query Builder to accomplish the same task):

SELECT
     SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, 
     Status, OnlineOrderFlag, SalesOrderNumber, PurchaseOrderNumber, 
     AccountNumber, CustomerID, ContactID, SalesPersonID, TerritoryID, 
     BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, 
     CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, 
     TotalDue, Comment, rowguid, ModifiedDate

FROM Sales.SalesOrderHeader
WHERE (OrderDate > @OrderDate)

This SQL query is a simple SELECT query with an @OrderDate parameter to narrow down the results. This will keep us from returning every order in the database. Leaving the "Fill a DataTable" and "Return a DataTable" checkboxes checked, click Finish. After adding this SELECT statement your designer should now have an extra query added to the SalesOrderHeaderTableAdapter, as shown in the screen shot below.

Using the Strongly-Typed DataSet in an ASP.NET Page


With the strongly-typed DataSet created, we can easily display this data in an ASP.NET page with just a few lines of code. Start by creating an ASP.NET page in your website and view it in the Design mode. Next, drop a GridView control on the surface, leaving the ID as GridView1. Go to the ASP.NET page's source code and import the AdventureWorksTableAdapters namespace at top of the file (in C#, the syntax is using AdventureWorksTableAdapters;). Finally, add the following code to the Page_Load event handler:

// Create the SalesOrderHeaderTableAdapter
SalesOrderHeaderTableAdapter salesAdapter = 
    new SalesOrderHeaderTableAdapter();

// Get orders that took place after July 1st, 2004
AdventureWorks.SalesOrderHeaderDataTable Orders = 
    salesAdapter.GetDataBy(new DateTime(2004, 7, 1));

// Bind the order results to the GridView
this.GridView1.DataSource = Orders;
this.GridView1.DataBind();     

This code is very simple. We create an instance of the SalesOrderHeaderTableAdapter, which we will use to fill our DataTable. Notice that instead of declaring a generic DataTable, we declare an object of type SalesOrderHeaderDataTable. To fill this DataTable, we call the GetDateBy() method and pass it a DateTime object. Also notice that even the retrieval command is strongly typed, as we have to pass a DateTime object, not just a generic object. The screen shot below illustrates the net result of the above code sample.

In addition to binding the results to the GridView through code, you could also use an ObjectDataSource, setting its TypeName property to AdventureWorksTableAdapters.SalesOrderHeaderTableAdapter and its SelectMethod to GetData or GetDataBy.

You Can Also Insert, Update, and Delete Data with Strongly-Typed DataSets
In this article we've just seen how to select data from a database using a strongly-typed DataSet. However, you can also insert, update, and delete underlying database data using these tools. For more information check out ASP.NET Team Member Scott Guthrie's blog entry: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0. Scott Mitchell has flushed out Scott Guthrie's DAL tutorials into an extensive tutorial series titled Working with Data in ASP.NET 2.0.

In addition to not having to write code to access the database, another great advantage using this stronly typed DataSet is that here are no strings of column names lurking in our code that the compiler cannot check. And we didn't have to perform any object conversions. If we ever change the schema of our database, once we update our AdventureWorks.xsd file, we'll notice all the breaking changes in our application during compilation.

Other Techniques for Generating Strongly-Typed Data-Access Applications


There are additional methods to accomplish strong typing in your applications outside of using strongly-typed DataSets. You can create custom classes that are more lightweight than DataSets and correspond exactly to your database. There are a few third-party software developers that make tools to automate this process. One in particular is LLBLGen Pro, which I like so much, I wrote a book about it: Rapid C# Windows Development: Visual Studio 2005, SQL Server 2005, and LLBLGen Pro. (You can read more than 1/3 of the book for free on my website.) Another popular tool is CodeSmith. Even Microsoft is working on a similar tool called DLINQ, which is still in beta and won't be out for at least another year.

If you use the Visual Studio strongly-typed DataSet method, you don't need to purchase any other software, which is a definite advantage. All these solutions have different features and benefits, but the main benefits you receive with strongly-typing your relational data is reliability, fewer mistakes, and less time spent debugging. It's also easier to examine the impact of database schema changes and implement them. Hopefully, you've come to appreciate the benefits of strong typing. Good luck developing (and dating)!

  • By Joseph Chancellor


    Attachments


  • Download the code examined in this article

    Further Reading


  • Working with Data in ASP.nET 2.0 (these tutorials illustrate creating a Data Access Layer using Typed DataSets)
  • Build a Data Access Layer with the Visual Studio 2005 DataSet Designer
  • Designing Data Tier Components and Passing Data Through Tiers
  • About the Author


    Joseph Chancellor is a C# developer in Southern California who has had his fair share of relational trauma. He appreciates all kinds of feedback and suggestions. Visit his blog or read the first five chapters of his book on Visual Studio 2005, SQL Server 2005, and LLBLGen Pro.



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article