Can I keep SqlDataReader alive after closing the connection?

Question

Is there any way to access SqlDataReaderafter closing the connection?

Or are there any equivalent objects SqlDataReaderso that I can store the reader in them and process the objects later?

I get a composite dataset from the server, so I can’t use regular classes to process such data, my model looks like this:

public class OneToNinetyNine
{
    public List<Cities> listCities;
    public string CityID;
    public DateTime DateFrom;
    public DateTime DateTo;
    // this is the reader that I attempt to pass to the views 
    public SqlDataReader SqlReader; 
}

Answer 1

You cannot use DataReaderafter closing the connection, since it needs to use the connection to retrieve data from the data source.

DataSet DataTable Load, .

Answer 2

SqlDataAdapter DataSet :

DataSet ds = new DataSet();
SqlCommand mycommand = new SqlCommand("sql statement");
using (SqlDataAdapter adapter = new SqlDataAdapter(mycommand))
{
    adapter.Fill(ds);
}

Answer 3

SqlDataReader ?

. , .

DataReader Connection ExecuteReader ExecuteReaderAsync CommandBehavior.CloseConnection. , Reader ( ).

CommandBehavior.CloseConnection , "--", , -. 2.

1. ,

, Connection, Create Command, Execute Reader :

public async Task<Foo> GetOneFoo(int idToFetch)
{
   using (var myConn = new SqlConnection(_connectionString))
   using (var cmd = new SqlCommand("SELECT Id, Col2, ... FROM Foo WHERE Id = @Id"))
   {
      await myConn.OpenAsync();
      cmd.Parameters.AddWithValue("@Id", idToFetch);
      using (var reader = await cmd.ExecuteReaderAsync())
      {
         var myFoo = new Foo
         {
            Id = Convert.ToInt32(reader["Id"]),
            ... etc
         }
         return myFoo; // We're done with our Sql data access here
      } // Reader Disposed here
   } // Command and Connection Disposed here
}

2. , Long Lived Reader

CommandBehavior.CloseConnection, , Reader .

, DataReaders, , yield return - , , (, , ) , .

public async Task<IEnumerable<Foo>> LazyQueryAllFoos()
{
   // NB : No `using` ... the reader controls the lifetime of the connection.
   var sqlConn = new SqlConnection(_connectionString);
   // Yes, it is OK to dispose of the Command https://stackoverflow.com/a/744307/314291
   using (var cmd = new SqlCommand(
        $"SELECT Col1, Col2, ... FROM LargeFoos", mySqlConn))
   {
      await mySqlConn.OpenAsync();
      var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
      // Return the IEnumerable, without actually materializing Foos yet.
      // Reader and Connection remain open until caller is done with the enumerable
      return GenerateFoos(reader);
   }
}

// Helper method to manage lifespan of foos
private static IEnumerable<Foo> GenerateFoos(IDataReader reader)
{
    using(reader)
    {
       while (reader.Read())
       {
          yield return new Foo
          {
              Id = Convert.ToInt32(reader["Id"]),
              ...
          };
       }
    } // Reader is Closed + Disposed here => Connection also Closed.
}

• # 6, async , , ( , , ).
• GenerateFoos , ( ) , , , .