Returning a SqlDataReader

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();
}

Solution 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();

Solution 2:

Add your connection string to AppSettings section in app.config or web.config.

   public string GetSqlConnection()
    {
        return  System.Configuration.ConfigurationManager.AppSettings["SqlConnectionString"];
    }

//Function to return SqlDataReader results

    public SqlDataReader executeReader(string sql, SqlParameter[] parameters=null)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = GetSqlConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        if (parameters != null)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter p in parameters)
            {
                cmd.Parameters.Add(p);
            }
        }
        else
        {
            cmd.CommandType = CommandType.Text;
        }
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }

To use the function:

        string query = @"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";
       //you can add more parameters by adding commas
       var parameters = new SqlParameter[] {
            new SqlParameter("@value", RecID )
           };

        SqlDataReader dr = executeReader(query, parameters);
        while (dr.Read())
        {
            //fill your controls with data 
        }
        dr.Close();

Solution 3:

I believe this StackOverflow answer deserves mentioning. A very simple and pleasant-to-use solution.