Returning a SqlDataReader

Question

I have this code:

public static SqlDataReader GetGeneralInformation ( int RecID )
{
    using ( var conn = new SqlConnection( GetConnectionString() ) )
    using ( var cmd = conn.CreateCommand() )
    {
        conn.Open();
        cmd.CommandText =
        @"SELECT cs.Status, cs.Completed
          FROM NC_Steps s
          INNER JOIN NC_ClientSteps cs
              ON cs.RecID = s.RecID
          WHERE cs.ClientID = 162
          AND s.RecID = @value";
        cmd.Parameters.AddWithValue( "@value", RecID );
        using ( var reader = cmd.ExecuteReader() )
        {
            if ( reader.Read() )
            {
                return reader;
            }
            return null;
        }
    }
}

How do I reference this?

I tried this but it does not work.

SqlDataReader reader = GeneralFunctions.GetGeneralInformation();

Also how would I read from the reader?

Reader.GetString( reader.GetOrdinal( "Status" ) )

Edit:

I am now getting the following error:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Here is the updated code:

public static IEnumerable<IDataRecord> GetGeneralInformation ( int ClientID )
{
    using ( var conn = new SqlConnection( GetConnectionString() ) )
    using ( var cmd = conn.CreateCommand() )
    {
        conn.Open();
        cmd.CommandText =
        @"SELECT i.GoLiveDate, i.FirstBonusRun, i.TechFName, i.TechLName, i.TechEmail, i.TechPhone, i.WebISPFName, i.WebISPLName, 
          i.WebISPEmail, i.WebISPPhone, i.FullFillFName, i.FullFillLName, i.FullFillEmail, i.FullFillPhone, d.FName,
          d.LName, d.HomePhone, d.Email
          FROM NC_Information i
          INNER JOIN Distributor d
            ON d.DistID = i.ClientID
          WHERE clientID = @value";
        cmd.Parameters.AddWithValue( "@value", ClientID );
        using ( var reader = cmd.ExecuteReader() )
        {
            while ( reader.Read() )
            {
                yield return reader;
            }
            yield return null;
        }
    }
}

protected void Page_Load(object sender, EventArgs e)
{
    IEnumerable<IDataRecord> reader = GeneralFunctions.GetGeneralInformation( (int)Session[ "DistID" ] );

    //string result = GeneralFunctions.GetGeneralInformation( Globals.GeneralInformation ).First()[ "Status" ].ToString();
}

Answer 1

The problem is that leaving the function (via the return statement) kicks you out of the using blocks, and so the SqlDataReader and SqlConnections you are using are disposed. To get around the problem, try changing the function signature like this:

public static IEnumerable<IDataRecord> GetGeneralInformation ( int RecID )

and then update the middle of the function like this:

using ( var reader = cmd.ExecuteReader() )
{
    while ( reader.Read() )
    {
        yield return reader;
    }
}

For the final "How do I read from it?" part, it might look like this:

int RecID = 12345;
string result = GetGeneralInformation(RecID).First()["Status"].ToString();