下载大文件，保存在数据库 [英] Downloading large files, saved in Database

问题描述

我们有很多的文件，保存在我们的SQL Server数据库二进制文件。
我已作出ashx的文件，它提供这些文件，给用户。
不幸的是，当文件变得相当大，它会失败，并出现以下错误：

  

在算术运算溢出或下溢

我以为它运行的内存，因为我二进制加载到一个byte []。

所以，我的问题是，我怎么能做出这样的功能，在成批读（也许？），当它从数据库表？它也好像Response.TransmitFile（）是一个不错的选择，但同样的，怎么会与数据库这项工作？

的DB.GetReposFile（），在下方的code，从数据库获取该文件。有各种领域，为入境：
文件名，则contentType，日戳和FileContent为varbinary。

这是我的功能，为客户提供文件：

  context.Response.Clear（）;
尝试
{
    如果（！String.IsNullOrEmpty（context.Request.QueryString [ID]））
    {
        INT ID = Int32.Parse（context.Request.QueryString [ID]）;
        数据表DTBL = DB.GetReposFile（ID）;
        。字符串文件名= dtbl.Rows [0] [文件名]的ToString（）;
        字符串扩展= FileName.Substring（FileName.LastIndexOf（）'。'）ToLower将（）。
        context.Response.ContentType = ReturnExtension（扩展）;
        context.Response.AddHeader（内容处置，附件;文件名=+文件名）;        字节[]缓冲=（字节[]）dtbl.Rows [0] [FileContent];
        context.Response.OutputStream.Write（缓冲液，0，buffer.Length）;
    }
    其他
    {
        context.Response.ContentType =text / html的;
        context.Response.Write（＆LT; P＆GT;需要有效的身份证件和LT; / P＆gt;中）;
    }
}
赶上（异常前）
{
    context.Response.ContentType =text / html的;
    context.Response.Write（＆所述; P＆gt;中+ ex.ToString（）+＆下; / P＆gt;中）;
}
 

更新：
我结束了该功能，是下列之一。
DB.GetReposFileSize（）简单地获取内容DATALENGTH，蒂姆提到。
我把这种功能，在原来的code，而不是这两行：

 字节[]缓冲=（字节[]）dtbl.Rows [0] [FileContent];
context.Response.OutputStream.Write（缓冲液，0，buffer.Length）;
 

新的下载功能：

 私人无效GetFileInChunks（HttpContext的背景下，INT ID）
    {
        //字符串路径= @C：\\ somefile.txt
        // FileInfo的文件=新的FileInfo（路径）;
        INT LEN = DB.GetReposFileSize（ID）;
        context.Response.AppendHeader（内容长度，len.ToString（））;
        context.Response.Buffer = FALSE;
        //流outStream =（流）context.Response.OutputStream;        康涅狄格州的SqlConnection = NULL;
        字符串STRSQL =从LM_FileUploads其中ID = @ ID选择FileContent
        尝试
        {
            DB.OpenDB（REF康涅狄格州，DB.DatabaseConnection.PDM）;
            CMD的SqlCommand =新的SqlCommand（STRSQL，康涅狄格州）;
            cmd.Parameters.AddWithValue（@ ID中，ID）;            SqlDataReader的读卡器= cmd.ExecuteReader（CommandBehavior.SequentialAccess）;
            reader.Read（）;
            字节[]缓冲区=新的字节[1024];
            诠释字节;
            长偏移= 0;            而（（字节=（int）的reader.GetBytes（0，偏移，缓冲液，0，buffer.Length））大于0）
            {
                // TODO：从`buffer`做`bytes`字节的东西
                context.Response.OutputStream.Write（缓冲液，0，buffer.Length）;                胶印+ =字节;
            }
        }        赶上（异常前）
        {
            扔恩;
        }
        最后
        {
            DB.CloseDB（REF康恩）;
        }
    }
 

解决方案

您可以使用的 DATALENGTH 获得 VARBINARY的大小，并用<$ C $流呢例如C> SqlDataReader的和它的的 读 -or的 的ReadBytes - 方法。

有一个看看这个答案，看一个实现：<一href=\"http://stackoverflow.com/questions/608480/best-way-to-stream-files-in-asp-net/609151#609151\">Best方式流于ASP.NET文件

We have a lot of files, saved as binary in our SQL Server database. I have made an .ashx file, that delivers these files, to the users. Unfortunately, when the files become rather large, it will fail, with the following error:

Overflow or underflow in the arithmetic operation

I assume it runs out of memory, as I load the binary into a byte[].

So, my question is, how can I make this functionality, read in chunks (maybe?), when it is from a database table? It also seems like Response.TransmitFile() is a good option, but again, how would this work with a database?

The DB.GetReposFile(), in the code beneath, gets the file from the database. There are various fields, for the entry: Filename, ContentType, datestamps and the FileContent as varbinary.

This is my function, to deliver the file:

context.Response.Clear();
try
{
    if (!String.IsNullOrEmpty(context.Request.QueryString["id"]))
    {
        int id = Int32.Parse(context.Request.QueryString["id"]);
        DataTable dtbl = DB.GetReposFile(id);
        string FileName = dtbl.Rows[0]["FileName"].ToString();
        string Extension = FileName.Substring(FileName.LastIndexOf('.')).ToLower();
        context.Response.ContentType = ReturnExtension(Extension);
        context.Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName);

        byte[] buffer = (byte[])dtbl.Rows[0]["FileContent"];
        context.Response.OutputStream.Write(buffer, 0, buffer.Length);
    }
    else
    {
        context.Response.ContentType = "text/html";
        context.Response.Write("<p>Need a valid id</p>");
    }
}
catch (Exception ex)
{
    context.Response.ContentType = "text/html";
    context.Response.Write("<p>" + ex.ToString() + "</p>");
}

Update: The function I ended up with, is the one listed below. DB.GetReposFileSize() simply gets the content Datalength, as Tim mentions. I call this function, in the original code, instead of these two lines:

byte[] buffer = (byte[])dtbl.Rows[0]["FileContent"];
context.Response.OutputStream.Write(buffer, 0, buffer.Length);

New download function:

private void GetFileInChunks(HttpContext context, int ID)
    {
        //string path = @"c:\somefile.txt";
        //FileInfo file = new FileInfo(path);
        int len = DB.GetReposFileSize(ID);
        context.Response.AppendHeader("content-length", len.ToString());
        context.Response.Buffer = false;


        //Stream outStream = (Stream)context.Response.OutputStream;

        SqlConnection conn = null;
        string strSQL = "select FileContent from LM_FileUploads where ID=@ID";
        try
        {
            DB.OpenDB(ref conn, DB.DatabaseConnection.PDM);
            SqlCommand cmd = new SqlCommand(strSQL, conn);
            cmd.Parameters.AddWithValue("@ID", ID);

            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
            reader.Read();
            byte[] buffer = new byte[1024];
            int bytes;
            long offset = 0;

            while ((bytes = (int)reader.GetBytes(0, offset, buffer, 0, buffer.Length)) > 0)
            {
                // TODO: do something with `bytes` bytes from `buffer`
                context.Response.OutputStream.Write(buffer, 0, buffer.Length);

                offset += bytes;
            }
        }

        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            DB.CloseDB(ref conn);
        }
    }

解决方案

You can use DATALENGTH to get the size of the VARBINARY and stream it for instance with a SqldataReader and it's Read-or ReadBytes-Method.

Have a look at this answer to see an implementation: Best way to stream files in ASP.NET

这篇关于下载大文件，保存在数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！