3

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.

Abhash786
  • 881
  • 2
  • 24
  • 53
  • why not to read the data into a class and return this class? The purpose of a `DataReader` is transport data from database layer, not to be used as a service contract. – Ricardo Pontual Nov 21 '16 at 12:33
  • Pls see updated question – Abhash786 Nov 22 '16 at 03:52
  • `Datareader` works connected to database, it will read data one-by-one, there's no way you can use a `Datareader` throught a wcf service since you need to loop the reader to get all data. When you do this, you must pass the data returned to some object, that generally is a class, and you should return this class or a stream of this class. – Ricardo Pontual Nov 22 '16 at 10:48

2 Answers2

1

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.

Mohammad
  • 2,724
  • 6
  • 29
  • 55
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.

Marc Selis
  • 833
  • 12
  • 17
  • I think this idea is correct as @Marc-Selis pointed. Again, you cannot stream or do anything else with a `Datareader` prior to loop and get all data. – Ricardo Pontual Nov 22 '16 at 10:50
  • What you are trying to do: create a single method that is able to query anything in your database is considered really bad design. WCF is a communication framework that relies on contracts. And that contract is to be considered quite literal: an agreement between client and server on what data will be sent over the wire, both in requests to the server and the responses to the client. In your question the server can return whatever it wants. In other words WCF is not a good technology for this. – Marc Selis Nov 23 '16 at 16:44
  • If you really want you'll probably get it to work though, but you'll have to use DbCommand.ExecuteDataSet instead of ExecuteDataReader and use the method @Mohammed provided to serialize the dataset to a stream. – Marc Selis Nov 23 '16 at 16:53