DAL DataSets vs. Collections

• Download source code - 115 KB

Introduction

In this article, I'll step you through the creation of an example web app that will print a list of data from the database. Instead of using datasets, we'll use a strongly typed collection that will leverage Generics. It's amazing how simple this more advanced method of data handling is to implement.

Background

We're in a refactoring heaven right now (I consider it a heaven at least, he he). While my peers at work and I have been brainstorming what pieces of the app we don't like and what we would ideally like to do instead, the subject came up that collections could be more worth while for our system than datasets. I did some research, and stumbled across the following article regarding this very topic: MSDN: DataSets vs. Collections. Let me save you some reading. Basically, if you got the know how, your company can afford the time investment, and are routinely serving back 1000+ rows per most SQL transactions, you should be doing things along these lines.

That's general rule. Where I work, we've used datasets for everything in the past. They come with a lot of overhead, which we're honestly not leveraging more than 15% of the time. This is a good alternative for us that will run leaner and give us the least amount of functionality that we need.

Also, you'll notice I went to town on the OOP in this project. Normally, I wouldn't use it on something so small as it is superfluous. This, however, is an example of how you might go about doing this in your day to day code working on your average sized project. Feel free to trim this down to your liking.

Using the Code

So, first things first. You're going to need Microsoft's sample AdventureWorks database. You can download it here: AdventureWorks Sample Database.

To further simplify things, we're going to leave the Test Driven Development alone for the time being.

Let's start out viewing the desired end product and work our way backwards.

etc...

Simple, ugly, functional. We're just going to make a table that relates the LoginID to the EmployeeID. What use would this have in the real world? I don't know... Auditing? Anyway, it's a simple example that will teach you everything you need to know (for the time being) about lists, Generics, and databinding a GridView to a collection based datasource.

So now that we know where we are heading to, let me take you one step back and let's take a look just at the code within the web page (we'll get to the class objects and DAL in a few minutes). I like to include a list of all the namespaces I use since I hate having to hunt those down when following code tutorials.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using HR;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Employees AllEmployees = new Employees();
        this.EmployeesGridView.DataSource = AllEmployees;
        this.EmployeesGridView.DataBind();
    }
}

That's all it takes to use the objects I've created. We have an Employees class. This is actually our collection of data. Upon instantiating the collection, its constructor queries the database and pulls back a list of all employees, as you'll see in the next example. I'm going to list the namespaces in this first example once again, since it's our first time using this particular namespace.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text;

using DbDAL;

namespace HR
{
    public class Employees : List<IEmployee>
    {
        public Employees()
        {
            //This should not be hard coded but it's
            //an example. Oh well!  ;)
            string SqlQuery = "select * from HumanResources.Employee";
            IEmployee AnEmployee;
            SqlDataReader MySqlReader = DBHelper.ExecuteQuery(SqlQuery);

            while (MySqlReader.Read())
            {
                AnEmployee = new Employee();
                AnEmployee.EmployeeID = (int)MySqlReader["EmployeeID"];
                AnEmployee.LoginID = (string)MySqlReader["LoginID"];
                this.Add(AnEmployee);
            }

            MySqlReader.Close();
        }
    }

OK. More complex, but still pretty compact. The general overview is this: Employees is basically a list that grabs its own data and populates itself. It is also a strongly typed list so you don't have to perform any casting. Whenever you cast, you take two major risks: runtime errors, since you can't be 100% certain of the data type your casting from, and a performance hit. We use Generics (<iemployee>) to make the list a strongly typed collection of IEmployee. When we want to add an item to our collection, we just have to reference the "this" object and invoke the Add method.

Next up, read from the database into a struct that is defined in this namespace. This struct is designed for this recordset only. I favor this because it basically makes our method self-describing. If I ever need to know what kind of data this code is expecting, I have but to examine the struct associated with my data access method and I'll know.

Now, for each new row of data, we create a new struct to hold it and then place that struct into our collection ("this" in this context) and repeat. You will notice I do have to do an initial cast from my DataReader to my struct. I don't know of a way to circumvent this. Besides that, if each item only has to be cast once, I think we'll be doing better than most code.

First, I declare my struct. Notice, I'm programming to the interface here. It's a very small tutorial, but since we are practicing for enterprise level projects, I'd rather keep these habits intact. Next, I get my query via hard coding. Yeah, I know that's bad. Don't do as I do! I use my DBHelper object to run the query and return a DataReader that I can cycle through to populate my list. You can read the following article if you're curious about the reasons why I chose a DataReader over a DataSet: DataReader vs. DataSet. Suffice it to say that for pulling data from the database directly into a data structure, nothing is more efficient than the DataReader.

public struct Employee
{
    private int _employeeID;
    private string _loginID;

    public int EmployeeID
    {
        get
        {
            return _employeeID;
        }
        set
        {
            _employeeID = value;
        }
    }
    public string LoginID
    {
        get
        {
            return _loginID;
        }
        set
        {
            _loginID = value;
        }
    }
}

This is the interface for the Employee struct. I made an interface for the struct because the code at work supports .NET remoting. Setting the struct up with an interface in this case allows anyone who can interface with my code to know what data should be expected. This is an ideal example of self documenting code to me. I have attached all of the sample code if you'd like to take a better look at it.

Points of Interest

Some things to keep in mind here are try to look out for what you plan to do with your data. If you're going to be doing filters and sorts, you may be better off just using a dataset. If, however, the majority of your DAL is like the one's I've seen, you'll probably be just fine returning all of your results as objects.

History

• I believe the code can be marked as ISerializable to enable it to be used via Web Services.
• Placed article in the .NET Programming section instead of .NET Databases.
• Made some edits. Removed the interface and replaced it with just the struct.