下载大文件，保存在数据库

本文介绍了下载大文件，保存在数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我们有很多的文件，保存在我们的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的大小，并用 SqlDataReader的和它的的 读 -or的 的ReadBytes - 方法。

有一个看看这个答案，看一个实现：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