How to download large files, stored in database...fluent nhibernate

Question

Using: C#, .NET 3.5 web forms application, fluent nhibernate 1.1.0.685, SQL Server 2008R2

I have a web app that allows users to upload files and attach them to "cases" they are working. The files are stored in the database as varbinary(MAX).

Here is the code I'm currently using to download the files:

...

if (!SiteUserService.HasPermission(Domain.SiteUserService.SitePermissions.ModifyCase, CurrentUser))
    this.AccessDenied();

string attachmentId = Request.QueryString["f"].ToString();
DownloadFileResponse response = CaseService.RetrieveAttachment(attachmentId, CurrentUser.Id);
DownloadAttachment(response.Attachment.ContentType, response.Attachment.FileName, response.FileBytes);

...

protected void DownloadAttachment(string mimeType, string fileName, byte[] file)
{
    Response.Clear();
    Response.ContentType = mimeType;
    Response.AddHeader("content-disposition", string.Format("attachment;filename=\"{0}\"", fileName));
    Response.BinaryWrite(file);
    Response.Flush();
    Response.End();
}

This works just fine for smaller files. A couple files were uploaded yesterday, sizes (in bytes): 2230165, 2104051, 1024274, and 2202318. When the users try to download these files they receive a "Request Timed Out" error. The majority of the files are around 45572 bytes.

The application in question resides in the DMZ, so we are using WCF services for data access, no direct SQL calls, so a solution like this won't work.

I don't have a physical file in the file system, so I don't think this code (below) will work:

System.IO.Stream stream = null;
byte[] buffer = new Byte[10000];
int length;
long dataToRead;

string filePath = context.Request.PhysicalPath;
string fileName  = System.IO.Path.GetFileName(filePath);

try
{
    stream = new System.IO.FileStream(filePath, System.IO.FileMode.Open,
    System.IO.FileAccess.Read,System.IO.FileShare.Read);

    dataToRead = stream 

    context.Response.ContentType = "application/octet-stream";
    context.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);

    while(dataToRead > 0)
    {
        if(context.Response.IsClientConnected)
        {
            length = stream (buffer, 0, 10000);
            context.Response.OutputStream.Write(buffer, 0, length);

            context.Response.Flush();

            buffer= new Byte[10000];
            dataToRead = dataToRead - length;
        }
        else
        {
            dataToRead = -1;
        }
    }
}
catch(Exception ex)
{
    throw(ex);
}
finally
{
    if(stream != null)
    {
        stream ();
    }
}

Any suggestions for how I can allow files to be downloaded in "chunks"?

Edit: I also looked at this solution, but don't know how I would implement it with just a byte[] from the DB.

Have a look at this answer: http://stackoverflow.com/questions/608480/best-way-to-stream-files-in-asp-net/609151#609151 — epitka, Jul 25 '13 at 14:15
Thank you. I saw that one yesterday; forgot to include in my original post. My issue is I don't know how to accomplish this with a byte[] from the database. I don't have a file to open or a stream object to work with. — CDR12, Jul 25 '13 at 17:26
You have some type, so you need to then convert it to byte[]. See here how to convert any type: http://stackoverflow.com/questions/1068541/how-to-convert-a-value-type-to-byte-in-c — epitka, Jul 25 '13 at 17:30

score 0 · Answer 1 · edited May 23 '17 at 12:18

0

you have to use the Streaming api of sql server which can be wrapped into a Stream. Then use it like this

var sqlConn = (SqlConnection)session.Connection;
BlobStream blob = new BlobStream(sqlConn, sqlConn.BeginTransaction(), "dbo", "Uploads", "FileData", "Id", id);
BufferedStream bufferedBlob = new BufferedStream(blob, 8040);

// use bufferedBlob to stream from and to context stream

edited May 23 '17 at 12:18

Community

1
1

answered Aug 01 '13 at 09:19

Firo

30,626
4
55
94

I cannot access SQL Server directly, this application is in the DMZ. I use WCF Services for data access. – CDR12 Aug 06 '13 at 14:14
wcf also allows streaming of data so you can stream it from database and directly into wcf. You need to put transfer mode = "Streamed" (possible for basicHttpBinding and netTcpBinding) in wcf service – Firo Aug 07 '13 at 05:45

score 0 · Accepted Answer · answered Aug 27 '13 at 14:09

Here's what I ended up doing:

public class DownloadHandler : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
        // send the file in 10k chunks -- should help with mem consumption
        Stream stream = null;
        byte[] buffer = new Byte[10000];
        // Length of the file:
        int length;
        // Total bytes to read:
        long dataToRead;

        try
        {
            CaseService svc = new CaseService();

            // Retrieve the attachment
            DownloadFileResponse response = svc.RetrieveAttachment(context.Request["f"].ToString(), context.Request["u"].ToString());
            AttachmentContract file = response.Attachment;

            stream = new MemoryStream(response.FileBytes);

            // Total bytes to read:
            dataToRead = Convert.ToInt64(file.FileSize);

            context.Response.ContentType = "application/octet-stream";
            context.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.FileName);

            // Read the bytes.
            while (dataToRead > 0)
            {
                // Verify that the client is connected.
                if (context.Response.IsClientConnected)
                {
                    // Read the data in buffer.
                    length = stream.Read(buffer, 0, 10000);

                    // Write the data to the current output stream.
                    context.Response.OutputStream.Write(buffer, 0, length);

                    // Flush the data to the HTML output.
                    context.Response.Flush();

                    buffer = new Byte[10000];
                    dataToRead = dataToRead - length;
                }
                else
                {
                    //prevent infinite loop if user disconnects
                    dataToRead = -1;
                }
            }
        }
        catch (Exception)
        {
            // Trap the error, if any.
            throw;
        }
        finally
        {
            if (stream != null)
            {
                //Close the file.
                stream.Close();
            }
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Web.config:

<system.web>
    ...
    <httpRuntime maxRequestLength="51200" />
    ...
</system.web>
<system.serviceModel>
    ...
    <bindings configSource="Config\Wcf\bindings.config" />
    ...
</system.serviceModel>

bindings.config:

<binding name="reliableBinding" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:10:00" allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard" maxBufferPoolSize="1048576" maxReceivedMessageSize="4194304" messageEncoding="Text" textEncoding="utf-8" useDefaultWebProxy="true">
    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="4194304" maxBytesPerRead="51200" maxNameTableCharCount="4194304" />
    <reliableSession ordered="true" inactivityTimeout="00:10:00" enabled="true" />
    <security mode="None">
        <transport clientCredentialType="None" proxyCredentialType="None" realm="" />
        <message clientCredentialType="UserName" algorithmSuite="Default" />
    </security>
</binding>

How to download large files, stored in database...fluent nhibernate

2 Answers2