Typed DataSet and Sorting Filtering and Searching a DataTable in ADO.NET - Custom Expressions - ShoppingCart DataSet
by David Hayden ( .NET Developer )
I mentioned some ultra cool new features in the DataTable in ADO.NET 2.0 in a previous post:
However, the DataTable has always been cool, because it allows you to sort, filter, and search its contents much like using queries and stored procedures in a database. And, if you choose to work with DataTables and DataSets in your web and windows applications, you might as well create a Typed DataSet and reap all its benefits to provide type safety and make your code that much easier to use and understand.
Shopping Cart DataSet
I created a simple Typed DataSet in Visual Studio 2005 by add a new dataset item to the project and then manually building a couple of DataTables, Cart and Item, and adding a foreign key DataRelation between the two on CartID.
One of the nice things about the DataTable is that you can add columns that represent an expression based on other columns. Here I created an ItemTotal Column and set its expression to “Quantity * Price”. This saves me the work of doing it myself and makes it a bindable property.
Shopping Cart Data
I've loaded the CartDataSet with some test data so that I can show off the functionality of the DataTable. Here is a snapshot of the items in the shopping cart using the DataSet Debug Visualizer:
Finding a Specific Item in the Shopping Cart By Primary Key - DataTable.Find
If you have a typed DataTable, you will find a very useful FindBy method on the table to help you find a row in the DataTable via the primary key. In this case, ItemID is the primary key of the Item DataTable, so I can find ItemID = 2 via the following code:
CartDataSet.ItemDataTable items = dataset.Item;
CartDataSet.ItemRow item = items.FindByItemID(2);
If you are using a typed DataTable which is the case in this example, you get a nifty FindBy<PK> method built for you that takes an Int32 datatype ( the datatype of ItemID ).
Selecting Multiple Rows in a DataTable - DataTable.Select
If you want to find all rows in the DataTable that have a “Quantity > 1”, you can query the table similar to how you would query it in a database with the Select Method:
DataRow[] multipleItems = items.Select("Quantity > 1");
The Select Method returns a collection of DataRow ( in this case 2 ). If you also wanted to sort those shopping cart items by Title, you could include that in the Select Method as well:
DataRow[] multipleItems =
items.Select("Quantity > 1", "Title");
Calculating SubTotal using DataTable.Compute
If you want to calculate the subtotal of the shopping cart, we can take advantage of the DataTable.Compute method as well as the cool ItemTotal Column:
decimal subTotal = Convert.ToDecimal
(items.Compute("Sum(ItemTotal)", string.Empty));
Conclusion
The DataTable has a lot of cool things that make it an absolute joy to work with when building applications. Before you spend time building custom classes and collections in your web and winfom applications, look at all the searching, sorting, and filtering capabilities you get for free in the DataTable. And when possible, enjoy the type safety, user friendliness, and functionality you get from Typed DataSets and DataTables in ADO.NET.
Written by David Hayden ( .NET Developer )
ADO.NET 2.0 Tutorials