ADODB.Stream equivalent in C# for retreaving records in CSV format

[Fresh_Air_Rider]

Hi

In the "good old" Classic ASP days, I used to stream records from a SQL
Server database out to the user's browser in CSV format by using a
combination of COALESCE and the ADODB.Stream object.

The trouble is that I'm not really sure how to do this in C# 2005.

This was a very useful technique and if anyone could please show me how
to adapt the Classic ASP code to C# 2005, I'd be very grateful indeed.

The old Classic ASP method went something like the following and
setting the ADODB.Stream object's type to 1 (binary) was crucial

SQL Server Stored Procedure
--------------------------------------------

DECLARE @CRLF AS CHAR(2)
DECLARE @Delimiter AS CHAR(1)

SET @CRLF = CHAR(13) + CHAR(10)
SET @Delimiter = ','

SELECT
Title + @Delimiter +
Forename + @Delimiter +
Surname + @CRLF
FROM Users

Classic ASP code
---------------------------

Set oADOConn = Server.CreateObject("ADODB.Connection")
Set oADOStream = Server.CreateObject("ADODB.Stream")
Set oADOCmd = Server.CreateObject("ADODB.Command")

oADOStream.Charset = "windows-1252"

oADOConn.Open Application("ConnStr")
oADOCmd.CommandType = adCmdStoredProc
oADOCmd.ActiveConnection = oADOConn
oADOCmd.CommandText = "GetUsersInCSVFormat"

oADOStream.Open
oADOCmd.Properties("Output Stream") = oADOStream
oADOCmd.Execute , , adExecuteStream

Set oADOCmd = Nothing
Set oADOConn = Nothing

Response.AddHeader
"content-disposition","attachment;filename=Users.csv"
Response.ContentType = "application/csv"

'Set type to binary
oADOStream.Type = 1

If oADOStream.Size = 0 Then
Response.Write "No records available"
Else
'Write out the binary data
Response.BinaryWrite oADOStream.Read
End If

....
....
....

 

[Nicholas Paldino [.NET/C# MVP]]

All you have to do is use a SqlCommand set up to call this stored
procedure and then get a SqlDataReader or a fill a DataSet using a
SqlDataAdapter. With the reader, you will probably use less resources, with
the DataSet, you get the advantage of having the whole thing in memory.

Regardless, once you get either of these, you will iterate through them
(the SqlDataReader, or the DataSet), and just write your result set out row
by row. You will only have one column in each, and they will be in string
format. You just have to pass the string in each row to the Write method.
It should handle the encoding for you.

If you need a specific encoding, then you will have to do handle that
yourself, but it isn't too hard, using the Encoding class (combined with the
BinaryWrite method on the HttpResponse).

Hope this helps.

 

[Fresh_Air_Rider]

Hi Nicholas,

Many thanks for your reply.

I did try something like this without success, but I think that the
encoding that you mentioned could be the crucial bit, so I'll try that.

I know that with the old Classic ASP, it only worked when the
ADODB.Stream object's type was set to 1 (binary).

Thanks again for your prompt reply.

Best regards
David

All you have to do is use a SqlCommand set up to call this stored
procedure and then get a SqlDataReader or a fill a DataSet using a
SqlDataAdapter. With the reader, you will probably use less resources, with
the DataSet, you get the advantage of having the whole thing in memory.

Regardless, once you get either of these, you will iterate through them
(the SqlDataReader, or the DataSet), and just write your result set out row
by row. You will only have one column in each, and they will be in string
format. You just have to pass the string in each row to the Write method.
It should handle the encoding for you.

If you need a specific encoding, then you will have to do handle that
yourself, but it isn't too hard, using the Encoding class (combined with the
BinaryWrite method on the HttpResponse).

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi

In the "good old" Classic ASP days, I used to stream records from a SQL
Server database out to the user's browser in CSV format by using a
combination of COALESCE and the ADODB.Stream object.

The trouble is that I'm not really sure how to do this in C# 2005.

This was a very useful technique and if anyone could please show me how
to adapt the Classic ASP code to C# 2005, I'd be very grateful indeed.

The old Classic ASP method went something like the following and
setting the ADODB.Stream object's type to 1 (binary) was crucial

SQL Server Stored Procedure
--------------------------------------------

DECLARE @CRLF AS CHAR(2)
DECLARE @Delimiter AS CHAR(1)

SET @CRLF = CHAR(13) + CHAR(10)
SET @Delimiter = ','

SELECT
Title + @Delimiter +
Forename + @Delimiter +
Surname + @CRLF
FROM Users

Classic ASP code
---------------------------

Set oADOConn = Server.CreateObject("ADODB.Connection")
Set oADOStream = Server.CreateObject("ADODB.Stream")
Set oADOCmd = Server.CreateObject("ADODB.Command")

oADOStream.Charset = "windows-1252"

oADOConn.Open Application("ConnStr")
oADOCmd.CommandType = adCmdStoredProc
oADOCmd.ActiveConnection = oADOConn
oADOCmd.CommandText = "GetUsersInCSVFormat"

oADOStream.Open
oADOCmd.Properties("Output Stream") = oADOStream
oADOCmd.Execute , , adExecuteStream

Set oADOCmd = Nothing
Set oADOConn = Nothing

Response.AddHeader
"content-disposition","attachment;filename=Users.csv"
Response.ContentType = "application/csv"

'Set type to binary
oADOStream.Type = 1

If oADOStream.Size = 0 Then
Response.Write "No records available"
Else
'Write out the binary data
Response.BinaryWrite oADOStream.Read
End If

...
...
...

 

[Nicholas Paldino [.NET/C# MVP]]

David,

It worked not only because you set the type to 1, but because you set
the character set as well. This way, ADO knew how to encode the text to the
stream that you were outputting. And then you did a raw dump of those bytes
to the result stream.

You just have to do some work in encoding the text in the
dataset/datareader and you should be fine.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi Nicholas,

Many thanks for your reply.

I did try something like this without success, but I think that the
encoding that you mentioned could be the crucial bit, so I'll try that.

I know that with the old Classic ASP, it only worked when the
ADODB.Stream object's type was set to 1 (binary).

Thanks again for your prompt reply.

Best regards
David

All you have to do is use a SqlCommand set up to call this stored
procedure and then get a SqlDataReader or a fill a DataSet using a
SqlDataAdapter. With the reader, you will probably use less resources,
with
the DataSet, you get the advantage of having the whole thing in memory.

Regardless, once you get either of these, you will iterate through
them
(the SqlDataReader, or the DataSet), and just write your result set out
row
by row. You will only have one column in each, and they will be in
string
format. You just have to pass the string in each row to the Write
method.
It should handle the encoding for you.

If you need a specific encoding, then you will have to do handle that
yourself, but it isn't too hard, using the Encoding class (combined with
the
BinaryWrite method on the HttpResponse).

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi

In the "good old" Classic ASP days, I used to stream records from a SQL
Server database out to the user's browser in CSV format by using a
combination of COALESCE and the ADODB.Stream object.

The trouble is that I'm not really sure how to do this in C# 2005.

This was a very useful technique and if anyone could please show me how
to adapt the Classic ASP code to C# 2005, I'd be very grateful indeed.

The old Classic ASP method went something like the following and
setting the ADODB.Stream object's type to 1 (binary) was crucial

SQL Server Stored Procedure
--------------------------------------------

DECLARE @CRLF AS CHAR(2)
DECLARE @Delimiter AS CHAR(1)

SET @CRLF = CHAR(13) + CHAR(10)
SET @Delimiter = ','

SELECT
Title + @Delimiter +
Forename + @Delimiter +
Surname + @CRLF
FROM Users

Classic ASP code
---------------------------

Set oADOConn = Server.CreateObject("ADODB.Connection")
Set oADOStream = Server.CreateObject("ADODB.Stream")
Set oADOCmd = Server.CreateObject("ADODB.Command")

oADOStream.Charset = "windows-1252"

oADOConn.Open Application("ConnStr")
oADOCmd.CommandType = adCmdStoredProc
oADOCmd.ActiveConnection = oADOConn
oADOCmd.CommandText = "GetUsersInCSVFormat"

oADOStream.Open
oADOCmd.Properties("Output Stream") = oADOStream
oADOCmd.Execute , , adExecuteStream

Set oADOCmd = Nothing
Set oADOConn = Nothing

Response.AddHeader
"content-disposition","attachment;filename=Users.csv"
Response.ContentType = "application/csv"

'Set type to binary
oADOStream.Type = 1

If oADOStream.Size = 0 Then
Response.Write "No records available"
Else
'Write out the binary data
Response.BinaryWrite oADOStream.Read
End If

...
...
...

 

[Fresh_Air_Rider]

Hi Nicholas,

I followed your advice and it worked a treat.

I used SqlDataReader because forward only scrolling was fine for this
scenario and it uses less resources.

I didn't actually do anything to change the encoding of the text and it
still worked fine.

I'm really pleased that I've managed to implement this technique (with
your help) in C# 2005 as it is very useful.

Many thanks again to you for such a prompt and intelligent reply.

Best regards
David

David,

It worked not only because you set the type to 1, but because you set
the character set as well. This way, ADO knew how to encode the text to the
stream that you were outputting. And then you did a raw dump of those bytes
to the result stream.

You just have to do some work in encoding the text in the
dataset/datareader and you should be fine.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi Nicholas,

Many thanks for your reply.

I did try something like this without success, but I think that the
encoding that you mentioned could be the crucial bit, so I'll try that.

I know that with the old Classic ASP, it only worked when the
ADODB.Stream object's type was set to 1 (binary).

Thanks again for your prompt reply.

Best regards
David

All you have to do is use a SqlCommand set up to call this stored
procedure and then get a SqlDataReader or a fill a DataSet using a
SqlDataAdapter. With the reader, you will probably use less resources,
with
the DataSet, you get the advantage of having the whole thing in memory.

Regardless, once you get either of these, you will iterate through
them
(the SqlDataReader, or the DataSet), and just write your result set out
row
by row. You will only have one column in each, and they will be in
string
format. You just have to pass the string in each row to the Write
method.
It should handle the encoding for you.

If you need a specific encoding, then you will have to do handle that
yourself, but it isn't too hard, using the Encoding class (combined with
the
BinaryWrite method on the HttpResponse).

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi

In the "good old" Classic ASP days, I used to stream records from a SQL
Server database out to the user's browser in CSV format by using a
combination of COALESCE and the ADODB.Stream object.

The trouble is that I'm not really sure how to do this in C# 2005.

This was a very useful technique and if anyone could please show me how
to adapt the Classic ASP code to C# 2005, I'd be very grateful indeed.

The old Classic ASP method went something like the following and
setting the ADODB.Stream object's type to 1 (binary) was crucial

SQL Server Stored Procedure
--------------------------------------------

DECLARE @CRLF AS CHAR(2)
DECLARE @Delimiter AS CHAR(1)

SET @CRLF = CHAR(13) + CHAR(10)
SET @Delimiter = ','

SELECT
Title + @Delimiter +
Forename + @Delimiter +
Surname + @CRLF
FROM Users

Classic ASP code
---------------------------

Set oADOConn = Server.CreateObject("ADODB.Connection")
Set oADOStream = Server.CreateObject("ADODB.Stream")
Set oADOCmd = Server.CreateObject("ADODB.Command")

oADOStream.Charset = "windows-1252"

oADOConn.Open Application("ConnStr")
oADOCmd.CommandType = adCmdStoredProc
oADOCmd.ActiveConnection = oADOConn
oADOCmd.CommandText = "GetUsersInCSVFormat"

oADOStream.Open
oADOCmd.Properties("Output Stream") = oADOStream
oADOCmd.Execute , , adExecuteStream

Set oADOCmd = Nothing
Set oADOConn = Nothing

Response.AddHeader
"content-disposition","attachment;filename=Users.csv"
Response.ContentType = "application/csv"

'Set type to binary
oADOStream.Type = 1

If oADOStream.Size = 0 Then
Response.Write "No records available"
Else
'Write out the binary data
Response.BinaryWrite oADOStream.Read
End If

...
...
...