MemoryStream Memory Intensive? newbie

[Jim Bayers]

I'm opening a connection to our sql server and puting it in a
memorystream so that, later on, a user can save the data as an xml file.

Our question is, how hard is this on our server? What if we have a meg
of data? Will that take up a meg of ram?

Can this bring the server to its knees? How easily?

Function getMemStream() As MemoryStream
'MemoryStream ReturnStream = new MemoryStream();
'Create the return memorystream object that will hold
'the buffered data.
Dim ReturnStream As New MemoryStream()

' set up the stream
Dim sw As New StreamWriter(ReturnStream)

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader

Try
'Get the sql string
Dim strSql As New StringBuilder()
strSql.Append("SELECT DISTINCT ISNULL blah, blah)
' connect to db
conn = New SqlConnection(ConnectionString)
conn.Open()
cmd = New SqlCommand(strSql.ToString, conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

' get row names
Dim strTmp As String = ""
Dim counter As Integer
If dr.Read Then
For counter = 0 To dr.FieldCount - 1
If Not dr.IsDBNull(counter) Then
strTmp += dr.GetName(counter) & vbTab
Else
strTmp += "" & vbTab
End If
Next
End If
sw.WriteLine(strTmp)

' get the data
While dr.Read
strTmp = ""
For counter = 0 To dr.FieldCount - 1
If Not dr.IsDBNull(counter) Then
strTmp += dr.GetValue(counter) & vbTab
Else
strTmp += "" & vbTab
End If
Next
sw.WriteLine(strTmp)
End While
Catch exc As SqlException
Response.Write("SQL Error Occured: " & exc.ToString)

Catch exc As Exception
Response.Write("Error Occured: " & exc.ToString)

Finally
If Not dr Is Nothing Then
dr.Close()
End If
conn.Close()
'close up shop
sw.Flush()
sw.Close()
conn.Close()
End Try
' return link to stream
Return ReturnStream
End Function

 

[Cor Ligthert]

Hi Jim,

And what is the sense of this, why not direct to an XML file or more simple
just create a dataset?

Cor

 

[Cor Ligthert]

Hi Jim,

Not offended or something like that meant just I can not get the idea why
you do it. Reading it over I saw it was not writen in the way it was
intended.

Cor

 

[Herfried K. Wagner [MVP]]

I'm opening a connection to our sql server and puting it in a
memorystream so that, later on, a user can save the data as an xml file.

Our question is, how hard is this on our server? What if we have a meg
of data? Will that take up a meg of ram?

Yes, except the data is written into virtual memory on disk if there is
no more physical RAM free.

 

[Jay B. Harlow [MVP - Outlook]]

Jim,
As Cor asks, why a MemoryStream instead of a DataSet, with a DataSet all you
need is:

Dim strSql As New StringBuilder()
strSql.Append("SELECT DISTINCT ISNULL blah, blah)
' connect to db
conn = New SqlConnection(ConnectionString)
conn.Open()
cmd = New SqlCommand(strSql.ToString, conn)

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet
da.Fill(ds)

Also with a DataSet you can access the information easily now & later,
however with a MemoryStream you will need to convert it from a MemoryStream
into something useful...

Also, I find it odd that you are using a StringBuilder to build a constant
string for your SQL select statement, however when you are building the
results of your query you are using string concatenation. (read
inconsistency in your code)

I would define strSql As String constant!

Const strSql As String = "SELECT DISTINCT ISNULL blah, blah)
' connect to db
conn = New SqlConnection(ConnectionString)
conn.Open()
cmd = New SqlCommand(strSql.ToString, conn)

Unless the "blah blah" is you dynamically building the SELECT statement in
which case I would still define it as a String Const & use Parameters to
avoid SQL Injection Attacks!

Within your loop I would use the StreamWriter you defined earlier (sw) or I
would skip the MemoryStream & StreamWriter entirely or use StringWriter.
Note your "if dr.Read" will skip the first record.

Something like:
Dim dr As System.Data.SqlClient.SqlDataReader

' set up the stream
Dim sw As New StringWriter

' get row names
For index As Integer = 0 To dr.FieldCount - 1
sw.Write(dr.GetName(index))
sw.Write(ControlChars.Tab)
Next
sw.WriteLine()

' get the data
While dr.Read
For index As Integer = 0 To dr.FieldCount - 1
If Not dr.IsDBNull(indext) Then
sw.Write(dr.GetValue(index))
End If
sw.Write(ControlChars.Tab)
Next
sw.WriteLine()
End While

Note ControlChars.Tab is the same as vbTab

Hope this helps
Jay

 

[Jim Bayers]

Oops..

I'm creating an excel spreadsheet from the info, an xls file, not an xml
file. Users fill in a couple of text boxes and I use that for to create
the where statement. After I create the stream, I send it the user with
this:

Sub sendData()
' //Create and populate a memorystream with the contents
of the '//database table
'System.IO.MemoryStream mstream = GetData();
Dim mstream As MemoryStream = getMemStream()

'//Convert the memorystream to an array of bytes.
'byte[] byteArray = mstream.ToArray();

Dim byteArray As Byte() = mstream.ToArray
'//Clean up the memory stream
'mstream.Flush();
'mstream.Close();

mstream.Flush()
mstream.Close()

'// Clear all content output from the buffer stream
'Response.Clear();
Response.Clear()
'// Add a HTTP header to the output stream that specifies the
default filename '// for the browser's download dialog
'Response.AddHeader("Content-Disposition", "attachment;
filename=foo.xls"); Response.AddHeader("Content-Disposition",
"attachment; filename=foo.xls") '// Add a HTTP header to the
output stream that contains the '// content length(File Size).
This lets the browser know how much data is being transfered
'Response.AddHeader("Content-Length",
byteArray.Length.ToString());
Response.AddHeader("Content-Length",
byteArray.Length.ToString()) '// Set the HTTP MIME type of the
output stream 'Response.ContentType =
"application/octet-stream"; Response.ContentType =
"application/octet-stream" '// Write the data out to the client.
'Response.BinaryWrite(byteArray);
Response.BinaryWrite(byteArray)


End Sub


Thanks for the help!

 

[Jay B. Harlow [MVP - Outlook]]

Jim,

Users fill in a couple of text boxes and I use that for to create
the where statement.

Danger! Danger! building a where statement from text boxes that users type
in opens you up for SQL Injection attacks!! See the August 2004 issues of
SQL Server Magazine for complete details.


The comments in the second half of my original reply still applies.

Your use of the strTmp variable will cause numerous temporary string
objects, if you write directly to the StreamWriter, as my code demonstrated
you will eliminate all of these temporary string objects. The temporary
string objects cause extra load on the GC which may slow down ASP.NET.

The following articles provide information on writing .NET code that
performs well.

http://msdn.microsoft.com/architecture/default.aspx?pull=/library/en-us/dnpag/html/scalenet.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/fastmanagedcode.asp

http://msdn.microsoft.com/library/d...y/en-us/dndotnet/html/highperfmanagedapps.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/vbnstrcatn.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vbtchperfopt.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/dotnetperftechs.asp

Hope this helps
Jay

Oops..

I'm creating an excel spreadsheet from the info, an xls file, not an xml
file. Users fill in a couple of text boxes and I use that for to create
the where statement. After I create the stream, I send it the user with
this:

Sub sendData()
' //Create and populate a memorystream with the contents
of the '//database table
'System.IO.MemoryStream mstream = GetData();
Dim mstream As MemoryStream = getMemStream()

'//Convert the memorystream to an array of bytes.
'byte[] byteArray = mstream.ToArray();

Dim byteArray As Byte() = mstream.ToArray
'//Clean up the memory stream
'mstream.Flush();
'mstream.Close();

mstream.Flush()
mstream.Close()

'// Clear all content output from the buffer stream
'Response.Clear();
Response.Clear()
'// Add a HTTP header to the output stream that specifies the
default filename '// for the browser's download dialog
'Response.AddHeader("Content-Disposition", "attachment;
filename=foo.xls"); Response.AddHeader("Content-Disposition",
"attachment; filename=foo.xls") '// Add a HTTP header to the
output stream that contains the '// content length(File Size).
This lets the browser know how much data is being transfered
'Response.AddHeader("Content-Length",
byteArray.Length.ToString());
Response.AddHeader("Content-Length",
byteArray.Length.ToString()) '// Set the HTTP MIME type of the
output stream 'Response.ContentType =
"application/octet-stream"; Response.ContentType =
"application/octet-stream" '// Write the data out to the client.
'Response.BinaryWrite(byteArray);
Response.BinaryWrite(byteArray)


End Sub


Thanks for the help!

I'm opening a connection to our sql server and puting it in a
memorystream so that, later on, a user can save the data as an xml
file.

Our question is, how hard is this on our server? What if we have a
meg of data? Will that take up a meg of ram?

Can this bring the server to its knees? How easily?

Function getMemStream() As MemoryStream
'MemoryStream ReturnStream = new MemoryStream();
'Create the return memorystream object that will hold
'the buffered data.
Dim ReturnStream As New MemoryStream()

' set up the stream
Dim sw As New StreamWriter(ReturnStream)

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader

Try
'Get the sql string
Dim strSql As New StringBuilder()
strSql.Append("SELECT DISTINCT ISNULL blah, blah)
' connect to db
conn = New SqlConnection(ConnectionString)
conn.Open()
cmd = New SqlCommand(strSql.ToString, conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

' get row names
Dim strTmp As String = ""
Dim counter As Integer
If dr.Read Then
For counter = 0 To dr.FieldCount - 1
If Not dr.IsDBNull(counter) Then
strTmp += dr.GetName(counter) & vbTab
Else
strTmp += "" & vbTab
End If
Next
End If
sw.WriteLine(strTmp)

' get the data
While dr.Read
strTmp = ""
For counter = 0 To dr.FieldCount - 1
If Not dr.IsDBNull(counter) Then
strTmp += dr.GetValue(counter) & vbTab
Else
strTmp += "" & vbTab
End If
Next
sw.WriteLine(strTmp)
End While
Catch exc As SqlException
Response.Write("SQL Error Occured: " & exc.ToString)

Catch exc As Exception
Response.Write("Error Occured: " & exc.ToString)

Finally
If Not dr Is Nothing Then
dr.Close()
End If
conn.Close()
'close up shop
sw.Flush()
sw.Close()
conn.Close()
End Try
' return link to stream
Return ReturnStream
End Function