Web Service streaming sql data

Question

0.00/5 (No votes)

See more:

I have to write a web service method that will pass back many rows of data, I was thinking about steaming the data back from a sqldatareader but when I sent up to stream I get the error cannot convert to io.stream. Is there a way I can maybe wrap a memorystream around the sqldatareader and pass that back?

Here is the simple method:

[WebMethod]
public Stream RetrieveFile()
{
SqlConnection connection = new SqlConnection(Properties.Settings.Default.ConnectionString);

SqlCommand command = new SqlCommand("select * from tblcase_casedetail", connection);

connection.Open();

SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

reader.Read();

return reader;

}

the error returned
Cannot implicitly convert type 'System.Data.SqlClient.SqlDataReader' to 'System.IO.Stream'

I don't want to buffer since there is a chance of a out of memory error, I want to be able to just stream rows of data to a client that will insert the rows into a similar data table.

I'm working in VS2012 and SQL Server 20108 R2

Posted 6-Nov-15 9:33am

Glagace_7763

Updated 8-Nov-15 3:36am

v4

Add a Solution

Comments

ZurdoDev 6-Nov-15 15:42pm

I suggest you return that much data on flash drive. ;)

Suvendu Shekhar Giri 6-Nov-15 15:59pm

:D

Glagace_7763 6-Nov-15 16:40pm

funny not helpful but funny

ZurdoDev 6-Nov-15 18:46pm

Agreed.

George Jonsson 6-Nov-15 20:54pm

To get better help you should provide more information.
What is the code you have done so far (relevant to the problem, of course), what error do you get and where does it occur.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2015-11-08T03:53:00

Solution 1

C#

public Stream RetrieveFile()
{
    // ...
    SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
    reader.Read();
    
    return reader;
    }

You cannot declare a method to say that it returns a Stream object, and then try to return a SqlDataReader from it. You need to convert the result of the SQL query into a stream, or some other object that the caller can send from the web service.

Posted 8-Nov-15 3:53am

Richard MacCutchan

Comments

Glagace_7763 8-Nov-15 13:58pm

Thank you, you have clarified my question, the SqlDataReader is a streaming object, it will pass a single row at a time, my question was how to I convert the sqldatareader output to a stream type that I can pass from the web service to a client.

Richard MacCutchan 9-Nov-15 4:28am

You cannot convert it. You will most likely need to create your own class based on the Stream class, that reads the reader and passes the data on to who or what is reading the stream.