ASP.NET上传大文件（几百兆）到oracle数据库Blob字段中，内存溢出，该怎么解决？

[Elva_Zhao]

HttpPostedFile upFile = FileUpload1.PostedFile;
int FileLength = upFile.ContentLength;
Byte[] FileByteArray = new Byte[FileLength];
Stream StreamObject = upFile.InputStream;
int pos = upFile.FileName.LastIndexOf("\\");
string ImgFName = upFile.FileName.Substring(pos + 1);
StreamObject.Read(FileByteArray, 0, FileLength);
代码如上

[qldsrx]

几百兆你还敢存数据库？你是不是发高烧了。
对于文件，数据库里只要有文件的路径存放，实际文件到磁盘上读取。

[普世编程技术]

Byte[] FileByteArray = new Byte[FileLength];

这句话了不得，申请了文件大小的内存。

应该以4K大小的数据块为单位，循环读取存入数据库、

[天下如山]

说得对 几百兆你还存数据库 存路径 实际文件存放在硬盘

[Elva_Zhao]

没办法，客户要求不能改

[大葡萄八块一斤]

web.config进行配置

<configuration>
<system.web>
<httpRuntime maxRequestLength="10240" requestLengthDiskThreshold="100"/>
</system.web>
</configuration>

这是最大10M，缓存阈值为100KB，最大几百M的话你自己改吧，这么大的存数据库里，我也没试过

[Elva_Zhao]

不是这问题，这个配置已经改了，是上传的时候报内存已满或不够的错误

[大葡萄八块一斤]

[Quote=引用 6 楼 的回复:]

不是这问题，这个配置已经改了，是上传的时候报内存已满或不够的错误
[/Quote]

那就是跟字段类型有关，不够存放，你的字段类型是什么？

[qldsrx]

[Quote=引用 7 楼 的回复:]

引用 6 楼 的回复:

不是这问题，这个配置已经改了，是上传的时候报内存已满或不够的错误


那就是跟字段类型有关，不够存放，你的字段类型是什么？
[/Quote]
我看是服务器内存不够了。

[普世编程技术]

http://msdn.microsoft.com/zh-cn/library/cydxhzhz(v=vs.80).aspx

使用 Oracle LOB
.NET Framework 2.0 其他版本 2（共 8）对本文的评价是有帮助 - 评价此主题
Oracle .NET Framework 数据提供程序包括 OracleLob 类，该类用于使用 Oracle LOB 数据类型。

OracleLob 可能是下列 OracleType 数据类型之一：

数据类型 说明
Blob
包含二进制数据的 Oracle BLOB 数据类型，其最大大小为 4 GB。此数据类型映射到 Byte 类型的 Array。
Clob
包含字符数据的 Oracle CLOB 数据类型，根据服务器的默认字符集，其最大大小为 4 GB。此数据类型映射到 String。
NClob
包含字符数据的 Oracle NCLOB 数据类型，根据服务器的区域字符集，其最大大小为 4G 字节。此数据类型映射到 String。
OracleLob 与 OracleBFile 的区别在于前者的数据存储在服务器上而不是存储在操作系统的物理文件中。它也可以是一个读写对象，这一点与 OracleBFile 不同（后者始终为只读）。

创建、检索和写入 LOB
以下 C# 示例演示如何在 Oracle 表中创建 LOB，然后以 OracleLob 对象的形式检索并写入。该示例演示如何使用 OracleDataReader 对象以及 OracleLob Read 和 Write 方法。该示例使用 Oracle BLOB、CLOB 和 NCLOB 数据类型。

[C#]

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.OracleClient;

// LobExample
public class LobExample
{
public static int Main(string[] args)
{
//Create a connection.
OracleConnection conn = new OracleConnection(
"Data Source=Oracle8i;Integrated Security=yes");
using(conn)
{
//Open a connection.
conn.Open();
OracleCommand cmd = conn.CreateCommand();

//Create the table and schema.
CreateTable(cmd);

//Read example.
ReadLobExample(cmd);

//Write example
WriteLobExample(cmd);
}

return 1;
}

// ReadLobExample
public static void ReadLobExample(OracleCommand cmd)
{
int actual = 0;

// Table Schema:
// "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
// "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
// Select some data.
cmd.CommandText = "SELECT * FROM tablewithlobs";
OracleDataReader reader = cmd.ExecuteReader();
using(reader)
{
//Obtain the first row of data.
reader.Read();

//Obtain the LOBs (all 3 varieties).
OracleLob blob = reader.GetOracleLob(1);
OracleLob clob = reader.GetOracleLob(2);
OracleLob nclob = reader.GetOracleLob(3);

//Example - Reading binary data (in chunks).
byte[] buffer = new byte[100];
while((actual = blob.Read(buffer, 0, buffer.Length)) >0)
Console.WriteLine(blob.LobType + ".Read(" + buffer + ", " +
buffer.Length + ") => " + actual);

// Example - Reading CLOB/NCLOB data (in chunks).
// Note: You can read character data as raw Unicode bytes
// (using OracleLob.Read as in the above example).
// However, because the OracleLob object inherits directly
// from the .Net stream object,
// all the existing classes that manipluate streams can
// also be used. For example, the
// .Net StreamReader makes it easier to convert the raw bytes
// into actual characters.
StreamReader streamreader =
new StreamReader(clob, Encoding.Unicode);
char[] cbuffer = new char[100];
while((actual = streamreader.Read(cbuffer,
0, cbuffer.Length)) >0)
Console.WriteLine(clob.LobType + ".Read(
" + new string(cbuffer, 0, actual) + ", " +
cbuffer.Length + ") => " + actual);

// Example - Reading data (all at once).
// You could use StreamReader.ReadToEnd to obtain
// all the string data, or simply
// call OracleLob.Value to obtain a contiguous allocation
// of all the data.
Console.WriteLine(nclob.LobType + ".Value => " + nclob.Value);
}
}

// WriteLobExample
public static void WriteLobExample(OracleCommand cmd)
{
//Note: Updating LOB data requires a transaction.
cmd.Transaction = cmd.Connection.BeginTransaction();

// Select some data.
// Table Schema:
// "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
// "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
cmd.CommandText = "SELECT * FROM tablewithlobs FOR UPDATE";
OracleDataReader reader = cmd.ExecuteReader();
using(reader)
{
// Obtain the first row of data.
reader.Read();

// Obtain a LOB.
OracleLob blob = reader.GetOracleLob(1/*0:based ordinal*/);

// Perform any desired operations on the LOB
// (read, position, and so on).

// Example - Writing binary data (directly to the backend).
// To write, you can use any of the stream classes, or write
// raw binary data using
// the OracleLob write method. Writing character vs. binary
// is the same;
// however note that character is always in terms of
// Unicode byte counts
// (for example, even number of bytes - 2 bytes for every
// Unicode character).
byte[] buffer = new byte[100];
buffer[0] = 0xCC;
buffer[1] = 0xDD;
blob.Write(buffer, 0, 2);
blob.Position = 0;
Console.WriteLine(blob.LobType + ".Write(
" + buffer + ", 0, 2) => " + blob.Value);

// Example - Obtaining a temp LOB and copying data
// into it from another LOB.
OracleLob templob = CreateTempLob(cmd, blob.LobType);
long actual = blob.CopyTo(templob);
Console.WriteLine(blob.LobType + ".CopyTo(
" + templob.Value + ") => " + actual);

// Commit the transaction now that everything succeeded.
// Note: On error, Transaction.Dispose is called
// (from the using statement)
// and will automatically roll back the pending transaction.
cmd.Transaction.Commit();
}
}

// CreateTempLob
public static OracleLob CreateTempLob(
OracleCommand cmd, OracleType lobtype)
{
//Oracle server syntax to obtain a temporary LOB.
cmd.CommandText = "DECLARE A " + lobtype + "; "+
"BEGIN "+
"DBMS_LOB.CREATETEMPORARY(A, FALSE); "+
":LOC := A; "+
"END;";

//Bind the LOB as an output parameter.
OracleParameter p = cmd.Parameters.Add("LOC", lobtype);
p.Direction = ParameterDirection.Output;

//Execute (to receive the output temporary LOB).
cmd.ExecuteNonQuery();

//Return the temporary LOB.
return (OracleLob)p.Value;
}

// CreateTable
public static void CreateTable(OracleCommand cmd)
{
// Table Schema:
// "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
// "INSERT INTO tablewithlobs VALUES (1, 'AA', 'AAA', N'AAAA')";
try
{
cmd.CommandText = "DROP TABLE tablewithlobs";
cmd.ExecuteNonQuery();
}
catch(Exception)
{
}

cmd.CommandText =
"CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
cmd.ExecuteNonQuery();
cmd.CommandText =
"INSERT INTO tablewithlobs VALUES (1, 'AA', 'AAA', N'AAAA')";
cmd.ExecuteNonQuery();
}
}
创建临时 LOB
以下 C# 示例演示如何创建临时 LOB。

[C#]

OracleConnection conn = new OracleConnection(
"server=test8172; integrated security=yes;");
conn.Open();

OracleTransaction tx = conn.BeginTransaction();

OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText =
"declare xx blob; begin dbms_lob.createtemporary(
xx, false, 0); :tempblob := xx; end;";
cmd.Parameters.Add(new OracleParameter("tempblob",
OracleType.Blob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(tempbuff,0,tempbuff.Length);
tempLob.EndBatch();
cmd.Parameters.Clear();
cmd.CommandText = "myTable.myProc";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter(
"ImportDoc", OracleType.Blob)).Value = tempLob;
cmd.ExecuteNonQuery();

tx.Commit();