Solved

C#: Reading Data in chunks from DB

Posted on 2009-04-02
5
1,614 Views
Last Modified: 2012-05-06
Hi,

I have number of large binary files stored in a MS SQL Database and have to retrieve them in order to offer them to users of a website for download. While this works fine using the usual Response.BinaryWrite() for files for up about 100 MB in size, anything larger then that eventually runs into an OutOfMemory Exception on the webserver of course.

The natural solution to this is retrieving and serving up the files in chunks instead methinks. I have thus used the code below to achieve this (that is just a snippet of course), but the files become corrupted during transfer. That means I can successfully download the files in their full size, but they are no longer openable. I am a novice at serving up such hugs files from the database, but due to the architecture of the project, I am unable to save them in the filesystem instead. My suspicion is that I am just dropping bytes in the method below, or possibly I am not catching the last chunk fully.

I have read this article explaining how to read from a file system, but am unsure if this is applicable to my problem here: http://www.developerfusion.com/code/4696/reading-binary-data-in-c/

At a loss here, any hints would be much appreciated - feeling a bit stupid for asking what might seem like an obvious question to some experts.

Thanks in advance!

//execute command and get file itself
                SqlDataReader drFile = myCommand.ExecuteReader(CommandBehavior.SequentialAccess);
                while (drFile.Read())
                {
                    Byte[] fileBytes = new Byte[32768];
                    MemoryStream ms = new MemoryStream();
                    try
                    {
                        int index = 0;
                        while (true)
                        {
                            long count = drFile.GetBytes(drFile.GetOrdinal("File"), index, fileBytes, 0, fileBytes.Length);
                            if (count == 0)
                            {
                                break;
                            }
                            else
                            {
                                index = index + (int)count;
                                ms.Write(fileBytes, 0, (int)count);
                                Response.BinaryWrite(ms.ToArray());
                                Response.Flush();
                            }
                        }
 
                    }
                    catch (Exception ex)
                    {
                        Trace.Warn("Failed to get file:", ex.ToString());
                    }
                    fileBytes = null;
                    ms.Dispose();
                }
                //close connections
                drFile.Close();
                drFile.Dispose();
                myConnection.Close();
                Response.End();

Open in new window

0
Comment
Question by:mikevoss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
vbwizardry earned 250 total points
ID: 24053191
Try this
const int BUFFER_SIZE = 1024;
long currPos = 0;
int fIndex = drFile.GetOrdinal("File")
long blobSize = reader.GetBytes(0, 0, null, 0, 0);
byte[] buffer = new byte[(BUFFER_SIZE>blobSize?blobSize:BUFFER_SIZE)];
while (currPos < blobSize) {
    currPos += reader.GetBytes(fIndex, currPos, buffer, 0, buffer.Length);
    Response.BinaryWrite(ms.ToArray());
    Response.Flush();
}

Open in new window

0
 
LVL 7

Assisted Solution

by:vbwizardry
vbwizardry earned 250 total points
ID: 24053197
Small fix

fix this long blobSize = reader.GetBytes(fIndex, 0, null, 0, 0);

Open in new window

0
 

Author Comment

by:mikevoss
ID: 24057914
Hi vbwizardry,

Thank you for your quick response! I have taken your code and applied it. It doesnt work however, because the bytes never get written to the memory stream from what I can tell. Am I missing a line?

Here is the full code including the datareader read instruction, stripped of the try catch finally code for database operations.

Thanks!
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString);
myConnection.Open();
new SqlCommand myCommand = new SqlCommand("usp_get_GeoDirectory_CustomerFiles", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandTimeout = 240;
 
//execute command and get file
SqlDataReader drFile = myCommand.ExecuteReader(CommandBehavior.SequentialAccess);
while (drFile.Read())
{
    MemoryStream ms = new MemoryStream();
    const int BUFFER_SIZE = 1024;
    long currPos = 0;
    int fIndex = drFile.GetOrdinal("File");
    long blobSize = drFile.GetBytes(fIndex, 0, null, 0, 0);
    byte[] buffer = new byte[(BUFFER_SIZE>blobSize?blobSize:BUFFER_SIZE)];
    while (currPos < blobSize) {
        currPos += drFile.GetBytes(fIndex, currPos, buffer, 0, buffer.Length);
        Response.BinaryWrite(ms.ToArray());
        Response.Flush();
    }
 
//close connections
drFile.Close();
drFile.Dispose();
myConnection.Close();
Response.End();

Open in new window

0
 
LVL 7

Assisted Solution

by:vbwizardry
vbwizardry earned 250 total points
ID: 24060581
Thats because i forgot to replace the ms.ToArray() with buffer.
Response.BinaryWrite(buffer);

Open in new window

0
 

Author Closing Comment

by:mikevoss
ID: 31565919
Great Stuff, thanks a lot for your help - merely graded it as good rather than excellent, because it didnt work the first time round, otherwise very happy with fast response and working solution.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dictionary and array of [N] size - performance tuned 12 51
Good forum hosting with good privacy policy 3 29
Nested forach loop to linq 3 41
denied execute as 13 55
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question