For a limited time, if you watch 2 Labcasts by Microsoft (90 minutes each), you can get a free licensed version of Visual Studio Standard Edition.  Offer only applies in USA and expires June 30, 2007.  Click here for more details.

Related Reading:

Head First Java, 2nd Edition

Pro ASP.NET MVC 2 Framework, Second Edition (Expert's Voice in .NET)

Big Java: Compatible with Java 5, 6 and 7

SqlDataReader can be used if you want to keep an open connection to the database and use the rows as they come to you (forward only stream of data). 
When you use SqlHelper.ExecuteDataSet, what happens is it serializes the data to XML and returns it to you at the end of
the GET operation.  However, with SqlDataReader, you get the rows as they come, which can be handy if there is a lot of
rows (say, millions), or if you have some sort of parallel processing you want to do while the data is still coming.  

Here is an example of how to use SqlDataReader.  Take a look below for an example, uses both SqlCommand and then
the Microsoft Application Blocks version

Download the source code for the below example

While Microsoft provides you with the Microsoft ApplicationBlock class to make your database-driven application development easier, occasionally, you may have to use .NET’s built-in SqlReader class to connect to a database. 

Using SqlReader is quite simple–you feed it a connection string and a query, then pull fields and use readerInstance.Read() to advance row-by-row. 

First, set up your application with the appropriate settings, including the connection to the database: 

string connectionString = "...";

        string query = "..."; 

        SqlConnection conn = new SqlConnection(connectionString); 

        try

        {

            conn.Open();

        }

        catch (Exception ex)

        {

            MessageBox.Show("Couldn't open a connection to the database.  The exception is " + ex.ToString());

            conn.Close();

        }

(Fill in the appropriate query and connection string information–which can be from your configuration, decrypted from some value, etc.) 

Next, create your SqlReader instance: 

SqlDataReader reader;

        using (reader = new SqlCommand(query, conn).ExecuteReader())

        {

            if (reader.HasRows)

            {

(We use using so that the reader is automatically disposed of when we’re done with it.)  It’s important to check if you have any rows of data, so do that before you pull anything out. 

Then, advance row-by-row with reader.Read(), like so: 

while (reader.Read())

{

    string id = reader.GetString(reader.GetOrdinal("Id"));

    string firstName = reader.GetString(reader.GetOrdinal("FirstName"));

    string lastName = reader.GetString(reader.GetOrdinal("LastName"));

    // Do something interesting, albeit trivial

    MessageBox.Show(string.Format("[{0}] {1} {2}", id, firstName, lastName));

    // ... more data-processing code ...

}

SqlReader has several methods you can use to extract different kinds of fields, such as GUIDs, Integers, Strings, Dates, etc.  The methods are called (predictably) GetGuid, GetInt32, GetString, GetDate, etc.  The only thing to note is that all these methods require a column number–so in order to decouple your code from the database (i.e. make sure it doesn’t rely on the order of data), use GetOrdinal(columnName) to get the column ID. 

Finally, close your reader and connection when you’re finished, via reader.Close() and conn.Close(). 

And that’s all there is to SqlReader!

By Ashiq Alibhai, Edited by Sameer

It is interesting to note that SqlHelper.ExecuteDataSource(…) is implemented with a SqlDataReader internally, but has a lot of other bulky stuff in there.  So if efficiency is what you care about, you probably want to use a SqlDataReader, but otherwise, if you want a quick solution to get your DataSet, go for the ExecuteDataSet.

When not to use Sql Data Reader

Update – April 3, 2008

Don’t use Sql Data Reader when you don’t need to unecessarily keep a database connection open.   It’s more efficient to use a disconnected data source model, meaning that you fetch all the data that is required at the beginning, and then close the connection.  This is already implemented for you in SqlHelper.ExecuteDataSet().. If you are doing a big loop and outside the loop it has while (dr.read()) you will start to use up all your database connections pretty fast if your site gets moderately busy.  In most cases you won’t need this, so just use ExecuteDataSet instead. 

If you do need to grab the data as you are going along and use it, for example when your data source is very large, or when you want to do asynchronous work on the data, then its a good idea to use SqlDataReader so you can process the data as it comes (rather than waiting until the entire data results are available)

As well, DON’T FORGET, you need to explicitly close your Database connections, best to have a try { } catch { } and in the finally { } block you can put conn.Close(), otherwise if there is an exception you will have a connection that is going to stay open until .NET decides to dispose of it (in its own sweet time…..)

More discussion to come!

References:

1. SqlDataReader Class (System.Data.SqlClient)
   

Related Reading:

Head First Java, 2nd Edition

Pro ASP.NET MVC 2 Framework, Second Edition (Expert's Voice in .NET)

Big Java: Compatible with Java 5, 6 and 7

The C# keyword global:: (also known as Global.) allows .NET to differentiate between two namespaces.

Say you have a namespace called Common, and you have a class called Setup

In that case, to refer to a function inside it called GetUsersOnline(), you would write Common.Setup.GetUsersOnline()

 However, say that you are currently working within the Accounting namespace, and the accounting namespace had a class called Common.  If you tried to type Common.GetUsersOnline(), .NET would say,… hey!  Accounting.Common exists, but there is no GetUsersOnline() in there.. So.. crash

So in order to help .NET solve this mystery, append global:: to the beginning of the namespace, and all will be fine.

global::Common.GetUsersOnline();

Related Reading:

Head First Java, 2nd Edition

Pro ASP.NET MVC 2 Framework, Second Edition (Expert's Voice in .NET)

Big Java: Compatible with Java 5, 6 and 7

The runtime complexity of DataColumnCollection.Contains is O(1) for case sensitive lookup ,and O(n) for case insensitive lookup. O(1) means constant time, i.e. it is done same speed regardless of whether the dataset size is 10, or 10,000,000 records, and O(n) means it will run in a speed to the size proportional to the data you are running it on.

When would you use DataColumnCollection.Contains?  When you have a DataTable and you want to know if it contains a column, before deleting it, you can run the following (C# example):

DataTable dt = Users.GetBusinessObject.GetData();
if (dt.Columns.Contains("SupplementaryColumn"))
{
    dt.Columns.Remove("SupplementaryColumn"));
}

How do we know this runs in constant time? i.e. how do we determine this?  Download Reflector and when you run it on our DataTable class, this is what we find.  Our column names are stored as a Hashtable, which has O(1) lookup speed.

private readonly Hashtable columnFromName;

When we call Contains, it runs the following code:

public bool Contains(string name)
{
return ((this.columnFromName[name] is DataColumn) || (this.IndexOfCaseInsensitive(name) >= 0));
}

 Breaking this down, it first does a case sensitive search for the name on the hashtable, and if not, it calls the case insensitive search which is O(n) complexity.

Related Reading:

Head First Java, 2nd Edition

Pro ASP.NET MVC 2 Framework, Second Edition (Expert's Voice in .NET)

Big Java: Compatible with Java 5, 6 and 7