How to convert IDataReader into Stream in C#

Question

In my WCF service, I am retrieving data from SQL server using Command.ExecuteReader() method. The data size is very large (around 1+ GB) and transferring this data to client over netTcp binding.

I am planning to implement stream mode instead of buffered mode in WCF. Can anyone point me to any article or document to do the same.

In simple words, my objective is to convert IDataReader to stream object that will transfer to client and client side, want to convert this stream back to dataset/datatable or anything that can be bind with Grid.

I cannot convert IdataReader to IEnumerable as data is coming through SP and no of columns in output set keep changing (I don;t want to add no of column limitation in code).

Ultimately, final communication will be done on dataset from WCF service to client app. If any solution like converting Dataset to stream, sent it to client and at client, convert stream back to dataset will also solve my problem.

Answer 1

You should not try to convert the IDataReader to a stream, but let your data access method return an IEnumerable of a type representing a single row of the query result like this:

public IEnumerable<Order> GetOrders()
{
   IDbCommand cmd = ...  <<build your command here>> ...
   using(var rdr = cmd.ExecuteDataReader())
   {
      while(rdr.Read())
      {
          Order order = new Order {Id=rdr.GetDecimal(1), Name=rdr.GetString(2)};
          yield return order;
      }
   }
}

Next you can serialize the result of this method to a stream (as shown by @Mohamed, for example). This way you can send a list of objects to the client without needing the complete resultset to be loaded in memory. And you are still sure the datareader is disposed when the reader has reached the end of the result.

Answer 2

you can convert anything to stream like this:

var stream = new MemoryStream(Encoding.UTF8.GetBytes(Newtonsoft.Json.JsonConvert.SerializeObject(datareader)));

but its not a best practice. you should create an array of objects with your datareader and then return it.