Understanding Data in .NET

I have seen quite a few posts lately about the data objects in .NET. One of the latest ones was this:

Is it correct to say that when you use ASP.NET you use SqlDataSource but
when you use Windows forms you use SqlDataAdapter.

Time for a primer.

Data Basics

In order to work with data in any paradigm, you need a few things.

  1. A means of connecting to the data source
  2. A means of querying the data source
  3. A container object to hold the result(s) from the data source query

In ADO.NET, as with ADO before it, #1 is solved with a connection object and #2 is solved with a Command object. The final part can be solved with a wide variety of objects. Here are some examples:

  • DataSet
  • Entity Framework objects
  • LINQ objects
  • Custom objects (generally filled with something like a DataReader)

So, connection, command and a container. Pretty simple, right? But what about DataSource objects or DataReaders? And where do Transaction objects fit in? In order to answer this question,  let’s first look at storing and moving data, no matter what its form is.

Streams and Endpoints

In applications, we are generally concerned with two aspects: Behavior and State. If we over-generalize, state is the information we are working with, while behavior is how me alter state to fulfill the goals of the application. With data, however, we tend to focus on state and allow Microsoft to worry about behavior. Let’s take a look at streams and endpoints for a second before we drop down into data specifics.

The Endpoint:
The most obvious endpoint in any application that works with data is the data source. In general, this is a database, but we can also store in flat files, XML files, etc. The important point about this particular endpoint is it is designed to persist between sessions, so we can find the information the next time a user wants to access it.

There are other types of endpoints, however. Any time a piece of data is held for any length of time, we have an endpoint. This means our objects serve as a type of “in memory” endpoint.

A stream is a means of getting objects from one endpoint to another. Some streams are obvious, like the MemoryStream. Some semi-obvious, like the StreamReader or StreamWriter used for text files. Others are not so obvious. The DataReader, for example, is a stream type of object. It acts as a “firehose” cursor (think one direction and very fast) for data.

If we wanted to picture endpoints and streams visually, it might look like the diagram below. Notice how the DataReader works as a Stream from a Database to a DataSet or custom objects.


The takeaway here is endpoints are where we store “data”, either temporarily (use in our current session) or persistent (use across sessions). This is an oversimplification, of course, but it works to get us to .NET data objects.

Working with Data in ADO.NET

You will notice ADO.NET spans a couple of namespaces. There is a general namespaces System.Data and specific namespaces, like System.Data.SqlClient, System.Data.OleDb and System.Data.Odbc. If you move to LINQ or EF, there are additional namespaces that, while not directly ADO.NET, have a lot of ties into ADO.NET. I am only going to focus on the namespaces with System.Data in this section. Let’s map this out


  • Common enumerations
  • Common exceptions
  • DataSet “container” classes – endpoints
  • Interfaces for data classes (this may be the most important part of System.Data)


  • Base classes for specific implementations. We are mostly concerned with the following:
    • connection
    • command
    • adapter


For our purposes, {Specific} will mean one of the following namespaces, which contain specific implementations for the base classes in System.Data.Common:

  • System.Data.Odbc
  • System.Data.OleDb
  • System.Data.OracleClient
  • System.Data.SqlClient

And what about System.Data.Linq? LINQ is a bit of a special cases, as it is a wrapper for the underlying ADO.NET goodness. We will touch wrapper classes when we get to {Type}DataSource in the ASP.NET discussion. Unfortunately, I will have to get into specifics for how LINQ wraps ADO.NET in another entry.

The DataReader

I will cover the Data Reader first, as it is one of the simplest to understand. We will assume we are filling a collection of custom objects, but we could be filling rows in a DataSet (this will be important in a bit). Here is a diagram of our retrieval and storage in memory:


Let’s make sure we understand this:

  • We need a connection to get to the database
  • We need a command to run
  • The database is one endpoint
  • The collection is another
  • The DataReader serves as a stream from the database (endpoint 1) to the collection (end point 2)

Here is how it looks in code (overly simplistic, of course):

public class Customer
    public string FName { get; set; }
    public string LName { get; set; }

    public Customer()

public class CustomerCollection : Dictionary<string,Customer>

Now some data access code so we can see the Reader in action:

CustomerCollection customers = new CustomerCollection();
string connectionString = GetConnectionString();
string commandString = "Select TOP 2 FName, LName From Customer";

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(commandString, connection);

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
        Customer customer = new Customer();
        customer.FName = reader["FName"];
        customer.LName = reader["LName"];
        customers.Add(customer.LName + ", " + customer.FName
            , customer);


Pretty simple. And we can see that we need to read (records, lines, bytes) from a stream object (in this case records from the DataReader) to get them from one endpoint to another. I could get a bit more complex and show what is going on underneath the hood, but this is enough to get the concept.


The DataSet seems much simpler, as we don’t worry about the stream, right? Let’s flip this one over and look at the code. Note that I am

CustomerDataSet customers = new CustomerDataSet();
string connectionString = GetConnectionString();
string commandString = "GetCustomersWithOrders";

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(commandString, connection);
command.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adapter = new SqlCommand(command);
adapter.TableMappings.Add("Table", "Customer");
adapter.TablesMappings.Add("Table1", "Order");


It appears there is no stream involved, but there is still a DataReader. It is just hidden from us (hidden objects with dotted lines):


This “hiding” becomes very important as you get deeper into ASP.NET and how it interacts with ADO.NET.

DataSource Controls

Here is where things get really nasty, as far as understanding goes. The DataSource controls, like SqlDataSource, are wrappers. The way you use them is you drag a DataSource control on a page, set some properties and then bind the DataSource to a control, like a Grid. What it looks like is this:


Look at all those hidden objects. In addition, the SqlDataSource is a bit hidden, as you manipulate it in one of two ways.

  • Change the tag in the ASP.NET page (ASPX)
  • Click on the context menu (chevron in upper right hand corner) and go through the dialogs

Some of the underlying objects are exposed when you use this object programmatically. Others are partially exposed, meaning you can change some properties of the object.

Answering the Question

Now for the short answer to this question:

Is it correct to say that when you use ASP.NET you use SqlDataSource but
when you use Windows forms you use SqlDataAdapter.

No, it is not correct. Technically, if you want to include the correct libraries, you can use the SqlDataSource with a windows forms library, as well. What is true is you see the SqlDataSource used in many drag and drop examples. It makes things easier, if you can fit in the box, as these are the steps.

  1. Drag a GridView on the page
  2. Drag a SqlDataSource on the Page
  3. Configure the SqlDataSource using the wizard
    1. Add a connection string
    2. Add a command
  4. Configure the GridView
    1. Set the SqlDataSource as the source
    2. Configure for empty result set to be safe
    3. (optionally) Add formatting to make the GridView look better

You don’t have to write any code, but a lot of code is working to make this magic work, as illustrated in the last section. This is fine as long as you don’t need to completely control the underlying objects.

This is a good first article and should show how we build layers on top of layers, constantly hiding things. This makes things easier for the newbie developer and can work in many cases. Eventually, however, you will find an exercise that requires you get out of this box.

Peace and Grace,

Twitter: @gbworld


3 Responses to Understanding Data in .NET

  1. J says:

    Well explained! Many thanx!

  2. Sandra says:

    Great article, thank you very much! When are we going to read a second part? 🙂

  3. Javier says:

    Very useful. I understood very well the diference between DataReader and DataSet.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: