Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0By Joseph Chancellor
"Never put off until run time what can be done at compile time."
David Gries, Compiler Construction for Digital Computers
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!
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:
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
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:
In the second example, we have a strongly-typed row, and we know the
FirstName property is of type
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:
- Open Visual Studio, and create a new ASP.NET Website.
- 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_Codefolder, which you should allow it to do for you.
AdventureWorks.xsdwill 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.
- 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.)
- Drag the
SalesOrderDetailtables 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.
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):
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
GridView1. Go to the ASP.NET page's source code and import the
AdventureWorksTableAdaptersnamespace at top of the file (in C#, the syntax is
using AdventureWorksTableAdapters;). Finally, add the following code to the
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,
TypeName property to
AdventureWorksTableAdapters.SalesOrderHeaderTableAdapter and its
|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
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)!
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.