One of my favorite features of Visual Studio 2005 is the easy to use DataSet designer. If you haven’t tried using the DataSet designer to create your data access layer try it out. Brian Noyes has written a nice tutorial here.

However, one issue I have with the DataSet designer is that it doesn’t give you the ability to create a SqlDataReader from the design interface. Fortunately there is a way around this limitation. Using partial classes you can extend the DataSet designer’s built in functionality to add your own methods. Brian Noyes touches on this in his tutorial I referenced above.

I have a little cheat to share that makes adding a SqlDataReader method to your TableAdapter a snap.

First off, you need to use the designer to create a TableAdapter. Have it create a GetData method for you.

Next you need to create a new class file for your project. I recommend naming it something like ‘<the name of your DataSet xsd file>Custom.cs’. That way it’s easy to identify.

Now you need to extend the namespace of your DataSet, and create a partial class for the TableAdapter.

Here comes the cheat.

Highlight the name of your TableAdapter you just created a partial class for. Right-click on it, and click on “Go to Definition.” This will take you to the Microsoft generated code for your TableAdapter. Below the definition code you should be able to find the ‘GetData’ method. Copy that entire method block and retrofit it into the partial class you created for your SqlDataReader. Now simply replace the section of Microsoft generated code that creates and returns a DataTable with code that returns a SqlDataReader.

When you’re done you’ll end up with a class file that looks something like the following.

using System;
using System.Data;
using System.ComponentModel;
using System.Data.SqlClient;

namespace DataSetDirectTouchTableAdapters
{
    public partial class displayResponseQuestionTableAdapter : Component
    {

        public virtual SqlDataReader GetReader(System.Nullable ResponseSet, System.Nullable Question)
        {
            this.Adapter.SelectCommand = this.CommandCollection[0];
            if ((ResponseSet.HasValue == true))
            {
                this.Adapter.SelectCommand.Parameters[1].Value = ((int)(ResponseSet.Value));
            }
            else
            {
                this.Adapter.SelectCommand.Parameters[1].Value = System.DBNull.Value;
            }
            if ((Question.HasValue == true))
            {
                this.Adapter.SelectCommand.Parameters[2].Value = ((int)(Question.Value));
            }
            else
            {
                this.Adapter.SelectCommand.Parameters[2].Value = System.DBNull.Value;
            }
            this._connection.Open();
            return this.Adapter.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}

After you rebuild your project you can access the GetReader method via Intellisense! Good times. 🙂