Returning a SqlDataReader

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

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

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