在ADO中处理大数据量的文件与数据库间的交互用GetChunk和AppendChunk方法，但是在ADO.NET的DataReader 列、DataSet 列、或Command 参数中这两个方法已不可用，本文就来阐述一下ADO.Net对该问题是如何处理的。
首先需要指出的是, ADO.Net对SQL和Oracle支持的很好, 对Access就弱一些, 可以用SQL语句ReadText和WriteText对SQL进行BLOB读写, 用System.Data.OracleClient.OracleLob中的OracleLob.Read和OracleLob.Write对Oracle进行BLOB读写，用于Access不支持ReadText和WriteText语句，在ADO.Net中只能实现从Access中读BLOB，写BLOB我至今都没有找到实现的方法。
下面就给出它们分别的实现代码:
1、读SQL BLOB（两种方法）VB.Net源码：
''' <summary>
''' SqlConnection方法
''' </summary>
''' <param name="DestFilePath">文件路径</param>
''' <remarks></remarks>
Private Sub SqlChunkBLOB2File(ByVal DestFilePath As String)
Dim PictureCol As Integer = 0
Dim BUFFER_LENGTH As Integer = 32768
Dim cn As New SqlConnection("Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
Dim cmdGetPointer As New SqlCommand("SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn)
Dim PointerOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
Dim LengthOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Length", SqlDbType.Int)
LengthOutParam.Direction = ParameterDirection.Output
cn.Open()
cmdGetPointer.ExecuteNonQuery()
If PointerOutParam.Value Is DBNull.Value Then
cn.Close()
Exit Sub
End If
Dim cmdReadBinary As New SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cn)
Dim PointerParam As SqlParameter = cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
Dim OffsetParam As SqlParameter = cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int)
Dim SizeParam As SqlParameter = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int)
Dim dr As SqlDataReader
Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
Dim Offset As Integer = 0
OffsetParam.Value = Offset
Dim Buffer(BUFFER_LENGTH - 1) As Byte
Do
PointerParam.Value = PointerOutParam.Value
If Offset + BUFFER_LENGTH >= LengthOutParam.Value Then
SizeParam.Value = LengthOutParam.Value - Offset
Else
SizeParam.Value = BUFFER_LENGTH
End If
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
dr.Read()
dr.GetBytes(PictureCol, 0, Buffer, 0, SizeParam.Value)
dr.Close()
fs.Write(Buffer, 0, SizeParam.Value)
Offset += SizeParam.Value
OffsetParam.Value = Offset
Loop Until Offset >= LengthOutParam.Value
fs.Close()
cn.Close()
End Sub
''' <summary>
''' OleDb方法
''' </summary>
''' <param name="DestFilePath">文件路径</param>
''' <remarks></remarks>
Private Sub OleDbChunkBLOB2File(ByVal DestFilePath As String)
Dim PictureCol As Integer = 0
Dim BUFFER_LENGTH As Integer = 32768
Dim cn As New OleDbConnection("Provider=SQLOLEDB;Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
Dim cmdGetPointer As New OleDbCommand("SELECT ?=TEXTPTR(Picture), ?=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn)
Dim PointerOutParam As OleDbParameter = cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
Dim LengthOutParam As OleDbParameter = cmdGetPointer.Parameters.Add("@Length", OleDbType.Integer)
LengthOutParam.Direction = ParameterDirection.Output
cn.Open()
cmdGetPointer.ExecuteNonQuery()
If PointerOutParam.Value Is DBNull.Value Then
cn.Close()
Exit Sub
End If
Dim cmdReadBinary As New OleDbCommand("READTEXT Categories.Picture ? ? ? HOLDLOCK", cn)
Dim PointerParam As OleDbParameter = cmdReadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16)
Dim OffsetParam As OleDbParameter = cmdReadBinary.Parameters.Add("@Offset", OleDbType.Integer)
Dim SizeParam As OleDbParameter = cmdReadBinary.Parameters.Add("@Size", OleDbType.Integer)
Dim dr As OleDbDataReader
Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
Dim Offset As Integer = 0
OffsetParam.Value = Offset
Dim Buffer(BUFFER_LENGTH - 1) As Byte
Do
PointerParam.Value = PointerOutParam.Value
If Offset + BUFFER_LENGTH >= LengthOutParam.Value Then
SizeParam.Value = LengthOutParam.Value - Offset
Else
SizeParam.Value = BUFFER_LENGTH
End If
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
dr.Read()
dr.GetBytes(PictureCol, 0, Buffer, 0, SizeParam.Value)
dr.Close()
fs.Write(Buffer, 0, SizeParam.Value)
Offset += SizeParam.Value
OffsetParam.Value = Offset
Loop Until Offset >= LengthOutParam.Value
fs.Close()
cn.Close()
End Sub
2、读SQL BLOB（两种方法）C＃源码：
/// <summary>
/// SQL方法
/// </summary>
/// <param name="DestFilePath">文件路径</param>
public void SqlChunkBlob2File(string DestFilePath)
{
try
{
int PictureCol = 0;
int BUFFER_LENGTH = 32768;
SqlConnection cn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
SqlCommand cmdGetPointer = new SqlCommand("SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn);
SqlParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
PointerOutParam.Direction = ParameterDirection.Output;
SqlParameter LengthOutParam = cmdGetPointer.Parameters.Add("@Length", SqlDbType.Int);
LengthOutParam.Direction = ParameterDirection.Output;
cn.Open();
cmdGetPointer.ExecuteNonQuery();
if (PointerOutParam.Value == null)
{
cn.Close();
return;
}
SqlCommand cmdReadBinary = new SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cn);
SqlParameter PointerParam = cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
SqlParameter OffsetParam = cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int);
SqlParameter SizeParam = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int);
SqlDataReader dr;
System.IO.FileStream fs = new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
int Offset = 0;
OffsetParam.Value = Offset;
Byte[] Buffer = new Byte[BUFFER_LENGTH];
do
{
PointerParam.Value = PointerOutParam.Value;
if ((Offset + BUFFER_LENGTH) >= System.Convert.ToInt32(LengthOutParam.Value))
SizeParam.Value = System.Convert.ToInt32(LengthOutParam.Value) - Offset;
else SizeParam.Value = BUFFER_LENGTH;
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
dr.Read();
dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
dr.Close();
fs.Write(Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
Offset += System.Convert.ToInt32(SizeParam.Value);
OffsetParam.Value = Offset;
} while (Offset < System.Convert.ToInt32(LengthOutParam.Value));
fs.Close();
cn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// OleDb方法
/// </summary>
/// <param name="DestFilePath">文件路径</param>
public void OleDbChunkBlob2File(string DestFilePath)
{
try
{
int PictureCol = 0;
int BUFFER_LENGTH = 32768;
OleDbConnection cn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
OleDbCommand cmdGetPointer = new OleDbCommand("SELECT ?=TEXTPTR(Picture), ?=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn);
OleDbParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100);
PointerOutParam.Direction = ParameterDirection.Output;
OleDbParameter LengthOutParam = cmdGetPointer.Parameters.Add("@Length", OleDbType.Integer);
LengthOutParam.Direction = ParameterDirection.Output;
cn.Open();
cmdGetPointer.ExecuteNonQuery();
if (PointerOutParam.Value == DBNull.Value)
{
cn.Close();
return;
}
OleDbCommand cmdReadBinary = new OleDbCommand("READTEXT Categories.Picture ? ? ? HOLDLOCK", cn);
OleDbParameter PointerParam = cmdReadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16);
OleDbParameter OffsetParam = cmdReadBinary.Parameters.Add("@Offset", OleDbType.Integer);
OleDbParameter SizeParam = cmdReadBinary.Parameters.Add("@Size", OleDbType.Integer);
OleDbDataReader dr;
System.IO.FileStream fs = new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
int Offset = 0;
OffsetParam.Value = Offset;
Byte[] Buffer = new Byte[BUFFER_LENGTH];
do
{
PointerParam.Value = PointerOutParam.Value;
if ((Offset + BUFFER_LENGTH) >= System.Convert.ToInt32(LengthOutParam.Value))
SizeParam.Value = System.Convert.ToInt32(LengthOutParam.Value) - Offset;
else SizeParam.Value = BUFFER_LENGTH;
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
dr.Read();
dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
dr.Close();
fs.Write(Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
Offset += System.Convert.ToInt32(SizeParam.Value);
OffsetParam.Value = Offset;
} while (Offset < System.Convert.ToInt32(LengthOutParam.Value));
fs.Close();
cn.Close();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
}
3、写SQL BLOB（VB.Net源码）
''' <summary>
''' SQL方法
''' </summary>
''' <param name="SourceFilepath">文件路径</param>
''' <remarks></remarks>
Private Sub ChunkFile2SqlBLOB(ByVal SourceFilepath As String)
Dim BUFFER_LENGTH As Integer = 32768
Dim cn As New SqlConnection("Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
Dim cmdGetPointer As New SqlCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" & _
"SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn)
Dim PointerOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
cn.Open()
cmdGetPointer.ExecuteNonQuery()
Dim cmdUploadBinary As New SqlCommand("UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn)
Dim PointerParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
Dim OffsetParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Offset", SqlDbType.Int)
Dim DeleteParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Delete", SqlDbType.Int)
DeleteParam.Value = 1 ' delete 0x0 character
Dim BytesParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH)
Dim fs As New IO.FileStream(SourceFilepath, IO.FileMode.Open, IO.FileAccess.Read)
Dim br As New IO.BinaryReader(fs)
Dim Offset As Integer = 0
OffsetParam.Value = Offset
Dim Buffer() As Byte = br.ReadBytes(BUFFER_LENGTH)
Do While Buffer.Length > 0
PointerParam.Value = PointerOutParam.Value
BytesParam.Value = Buffer
cmdUploadBinary.ExecuteNonQuery()
DeleteParam.Value = 0
Offset += Buffer.Length
OffsetParam.Value = Offset
Buffer = br.ReadBytes(BUFFER_LENGTH)
Loop
br.Close()
fs.Close()
cn.Close()
End Sub
''' <summary>
''' OleDb方法
''' </summary>
''' <param name="SourceFilePath">文件路径</param>
''' <remarks></remarks>
Private Sub ChunkFile2OleDbBLOB(ByVal SourceFilePath As String)
Dim BUFFER_LENGTH As Integer = 32768
Dim cn As New OleDbConnection("Provider=SQLOLEDB;Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
Dim cmdGetPointer As New OleDbCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" & _
"SELECT ?=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn)
Dim PointerOutParam As OleDbParameter = cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
cn.Open()
cmdGetPointer.ExecuteNonQuery()
Dim cmdUploadBinary As New OleDbCommand("UPDATETEXT Categories.Picture ? ? ? WITH LOG ?", cn)
Dim PointerParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16)
Dim OffsetParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Offset", OleDbType.Integer)
Dim DeleteParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Delete", OleDbType.Integer)
DeleteParam.Value = 1
Dim BytesParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Bytes", OleDbType.Binary, BUFFER_LENGTH)
Dim fs As New IO.FileStream(SourceFilePath, IO.FileMode.Open, IO.FileAccess.Read)
Dim br As New IO.BinaryReader(fs)
Dim Offset As Integer = 0
OffsetParam.Value = Offset
Dim Buffer() As Byte = br.ReadBytes(BUFFER_LENGTH)
Do While Buffer.Length > 0
PointerParam.Value = PointerOutParam.Value
BytesParam.Value = Buffer
cmdUploadBinary.ExecuteNonQuery()
DeleteParam.Value = 0
Offset += Buffer.Length
OffsetParam.Value = Offset
Buffer = br.ReadBytes(BUFFER_LENGTH)
Loop
br.Close()
fs.Close()
cn.Close()
End Sub
4、写SQL BLOB（C#源码）
/// <summary>
/// SQL方法
/// </summary>
/// <param name="SourceFilePath">文件路径</param>
private void ChunkFile2SqlBlob(string SourceFilePath)
{
try
{
int BUFFER_LENGTH = 32768;
SqlConnection cn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
SqlCommand cmdGetPointer = new SqlCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
"SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn);
SqlParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
PointerOutParam.Direction = ParameterDirection.Output;
cn.Open();
cmdGetPointer.ExecuteNonQuery();
SqlCommand cmdUploadBinary = new SqlCommand("UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn);
SqlParameter PointerParam = cmdUploadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
SqlParameter OffsetParam = cmdUploadBinary.Parameters.Add("@Offset", SqlDbType.Int);
SqlParameter DeleteParam = cmdUploadBinary.Parameters.Add("@Delete", SqlDbType.Int);
DeleteParam.Value = 1; // delete 0x0 character
SqlParameter BytesParam = cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH);
System.IO.FileStream fs = new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
int Offset = 0;
OffsetParam.Value = Offset;
Byte[] Buffer = br.ReadBytes(BUFFER_LENGTH);
while (Buffer.Length > 0)
{
PointerParam.Value = PointerOutParam.Value;
BytesParam.Value = Buffer;
cmdUploadBinary.ExecuteNonQuery();
DeleteParam.Value = 0;
Offset += Buffer.Length;
OffsetParam.Value = Offset;
Buffer = br.ReadBytes(BUFFER_LENGTH);
}
br.Close();
fs.Close();
cn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// OleDB方法
/// </summary>
/// <param name="SourceFilePath">文件路径</param>
public void ChunkFile2OleDbBlob(string SourceFilePath)
{
try
{
int BUFFER_LENGTH = 32768;
OleDbConnection cn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
OleDbCommand cmdGetPointer = new OleDbCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
"SELECT ?=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn);
OleDbParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100);
PointerOutParam.Direction = ParameterDirection.Output;
cn.Open();
cmdGetPointer.ExecuteNonQuery();
OleDbCommand cmdUploadBinary = new OleDbCommand("UPDATETEXT Categories.Picture ? ? ? WITH LOG ?", cn);
OleDbParameter PointerParam = cmdUploadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16);
OleDbParameter OffsetParam = cmdUploadBinary.Parameters.Add("@Offset", OleDbType.Integer);
OleDbParameter DeleteParam = cmdUploadBinary.Parameters.Add("@Delete", OleDbType.Integer);
DeleteParam.Value = 1;
OleDbParameter BytesParam = cmdUploadBinary.Parameters.Add("@Bytes", OleDbType.Binary, BUFFER_LENGTH);
System.IO.FileStream fs = new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
int Offset = 0;
OffsetParam.Value = Offset;
Byte[] Buffer = br.ReadBytes(BUFFER_LENGTH);
while (Buffer.Length > 0)
{
PointerParam.Value = PointerOutParam.Value;
BytesParam.Value = Buffer;
cmdUploadBinary.ExecuteNonQuery();
DeleteParam.Value = 0;
Offset += Buffer.Length;
OffsetParam.Value = Offset;
Buffer = br.ReadBytes(BUFFER_LENGTH);
}
br.Close();
fs.Close();
cn.Close();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
}
5、读Access BLOB(VB.Net源码),建议用ADO处理Access数据库，我有一篇随笔写了ADO中GetChunk和AppendChunk两个函数的用法(http://www.cnblogs.com/wall/archive/2008/02/26/1081903.html)
''' <summary>
''' OleDb方法从Access中分块读Blob
''' </summary>
''' <param name="DestFilePath">文件路径</param>
''' <remarks></remarks>
Private Sub OleDbChunkBlob2FileFromAccess(ByVal DestFilePath As String)
Try
Dim PictureCol As Integer = 0
Dim BUFFER_LENGTH As Integer = 500
Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\2.mdb")
Dim cmdGetPointer As OleDbCommand = New OleDbCommand("SELECT Data FROM Test1 WHERE ID=1", cn)
cn.Open()
Dim dr As OleDbDataReader
Dim fs As System.IO.FileStream = New System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim Buffer(BUFFER_LENGTH - 1) As Byte
dr = cmdGetPointer.ExecuteReader(CommandBehavior.SingleResult)
dr.Read()
Dim ChunkNum As Integer = Int(dr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) / BUFFER_LENGTH)
Dim j As Integer
For j = 0 To ChunkNum - 1
dr.GetBytes(PictureCol, 0 + j * Buffer.Length, Buffer, 0, Buffer.Length)
fs.Write(Buffer, 0, Buffer.Length)
Next
Dim pLastChunkSize As Integer = dr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) Mod BUFFER_LENGTH
Dim pLastChunkBuffer(pLastChunkSize - 1) As Byte
Try
dr.GetBytes(PictureCol, ChunkNum * Buffer.Length, pLastChunkBuffer, 0, pLastChunkBuffer.Length)
dr.Close()
fs.Write(pLastChunkBuffer, 0, pLastChunkBuffer.Length)
Catch ex As Exception
End Try
fs.Close()
cn.Close()
Catch ex As OleDbException
MessageBox.Show(ex.Message)
End Try
End Sub
附上英文资料，这篇英文对ADO.Net处理大数据量BLOB字段介绍的很清楚，很有借鉴价值。
Binary Large Objects
A binary large object (BLOB) is a binary resource such as an image, a sound or video clip, or a document. Storing BLOBs in a database can cause significant resource pressure. For example, large BLOBs can consume large amounts of memory, CPU, and networking resources on both the client and the server.
You can choose to handle BLOBs as a whole or handle them in chunks.
Handling BLOBs as a whole is useful when the BLOB is not very large in size, and you require the complete BLOB to be in memory before you perform the operation. This approach tends to put excessive memory pressure on the server as well as on network bandwidth.
Compared to handling BLOBs as a whole, chunking does cause more round trips, but chunking creates less load on the server and reduces network bandwidth use. The network bandwidth is not excessively consumed because you transfer the data in chunks rather than passing the BLOB all at one time. Therefore, the server only has to take care of the immediate buffer passed to it. The server can either store the buffer to the disk or redirect it as an output stream to the client.
ADO.NET data providers do not provide the GetChunk and AppendChunk methods in the same way that Data Access Objects (DAO) and ActiveX Data Objects (ADO) do with Recordset objects. However, this section describes the alternate options that exist.
Consider the following when you are working with BLOBs:
- Use CommandBehavior.SequentialAccess and GetBytes to read data.
- Use READTEXT to read from SQL Server 2000.
- Use OracleLob.Read to read from Oracle databases.
- Use UpdateText to write to SQL Server databases.
- Use OracleLob.Write to write to Oracle databases.
- Avoid moving binary large objects repeatedly.
Use CommandBehavior.SequentialAccess and GetBytes to Read Data
The default behavior of the DataReader is to read an entire row into memory. All columns are accessible in any order until the next row is read.
If you retrieve large BLOBs, reading the whole BLOB into memory may cause excessive memory consumption. Using CommandBehavior.SequentialAccess enables you to stream the data or to send the data in chunks from the column containing the BLOB by using the GetBytes, GetChars, or GetString methods.
The following code fragment shows how to use the SequentialAccess and GetBytes methods.
Copy Code
// Allocate a buffer to hold a BLOB chunk.
int bufferSize = 100; // the size of the buffer to hold interim chunks of the BLOB
byte[] outbyte = new byte[bufferSize]; // The buffer to hold the BLOB
SqlDataReader myReader = empCmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (myReader.Read())
{
// The BLOB data is in column two. Must get the first column
// before the BLOB data.
empID = myReader.GetInt32(0); // First column
// Read the bytes into outbyte[] and retain the number of bytes returned.
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
// Continue reading and writing while there are bytes beyond the
// Size of the buffer.
while (retval == bufferSize)
{
// Write data to a file or to a Web page (omitted for brevity).
. . .
// Reposition the start index to the end of the last buffer
// and fill the buffer.
startIndex += bufferSize;
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
}
}
Note When you use CommandBehavior.SequentialAccess, you must retrieve columns in sequence. For example, if you have three columns, and the BLOB data is in the third column, you must retrieve the data from the first and second columns, before you retrieve the data from the third column.
More Information
For more information, see "Obtaining BLOB Values from a Database" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp.
Use READTEXT to Read from SQL Server 2000
The READTEXT command reads text, ntext, or image values from a text, ntext, or image column. The READTEXT command starts reading from a specified offset and reads the specified number of bytes. This command is available in SQL Server 2000 and later. This command enables you to read data in chunks by sending a fixed set of bytes over the network for each iteration. The following are the steps you must follow to use the READTEXT command:
- Obtain a pointer to the BLOB by using the TEXTPTR command.
- Read the BLOB, by using the READTEXT command, in the required chunk size, with the help of the pointer that you obtained in step 1.
- Send the data to the client.
- Read the data on the client, and then store it in a buffer or a stream.
The following code fragment shows how to use the READTEXT command.
Copy Code
int BUFFER_LENGTH = 32768; // chunk size
// Obtain a pointer to the BLOB using TEXTPTR.
SqlCommand cmdGetPointer = new SqlCommand(
"SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture)" +
"FROM Categories WHERE CategoryName='Test'", conn);
// Set up the parameters.
SqlParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
// Run the query.
// Set up the READTEXT command to read the BLOB by passing the following
// parameters: @Pointer – pointer to blob, @Offset – number of bytes to
// skip before starting the read, @Size – number of bytes to read.
SqlCommand cmdReadBinary = new SqlCommand(
"READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", conn);
// Set up the parameters for the command.
SqlParameter SizeParam = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int);
SqlDataReader dr;
int Offset= 0;
Byte []Buffer = new Byte[BUFFER_LENGTH ];
// Read buffer full of data.
do {
// Add code for calculating the buffer size - may be less than
// BUFFER LENGTH for the last block.
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
dr.Read();
dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
Offset += System.Convert.ToInt32(SizeParam.Value);
OffsetParam.Value = Offset;
} while( //Check for the offset until it reaches the maximum size.);
More Information
For more information about the READTEXT command, see Knowledge Base article 317043, "HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;317043.
Use OracleLob.Read to Read from Oracle Databases
To read BLOBs from an Oracle database, use the .NET Framework Data Provider for Oracle. This data provider provides the System.Data.OracleClient.OracleLob class that can read BLOBs. The following code fragment shows how the OracleLob.Read method enables you to read the data in chunks.
Copy Code
byte[] buffer = new byte[100];
while((actual = blob.Read(buffer, 0/*buffer offset*/,
buffer.Length/*count*/)) >0)
{ //write the buffer to some stream
}
More Information
For more information about OracleLob.Read, see "OracleLob.Read Method" in .NET Framework Class Library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclelobclassreadtopic.asp.
Use UpdateText to Write to SQL Server Databases
If you are using SQL Server, you can use the UpdateText function to write the data in chunks, as shown in the following code fragment.
Copy Code
int BUFFER_LENGTH = 32768; // Chunk size.
// Set the existing BLOB to null and
// Obtain a pointer to the BLOB using TEXTPTR
SqlCommand cmdGetPointer = new SqlCommand(
"SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
"SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'",
cn);
// Set up the parameters.
// Run the query.
// Set up the UPDATETEXT command to read the BLOB by passing the following
// parameters: @Pointer – pointer to blob, @Offset – number of bytes to
// skip before starting the read, @Size – number of bytes to read.
SqlCommand cmdUploadBinary = new SqlCommand(
"UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn);
// Set up the parameters.
// Read buffer full of data and then run the UPDATETEXT statement.
Byte [] Buffer = br.ReadBytes(BUFFER_LENGTH);
while(Buffer.Length > 0)
{
PointerParam.Value = PointerOutParam.Value;
BytesParam.Value = Buffer;
cmdUploadBinary.ExecuteNonQuery();
DeleteParam.Value = 0; //Do not delete any other data.
Offset += Buffer.Length;
OffsetParam.Value = Offset;
Buffer = br.ReadBytes(BUFFER_LENGTH);
}
More Information
For more information about writing BLOB data to SQL Server, see "Conserving Resources When Writing BLOB Values to SQL Server" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconservingresourceswhenwritingblobvaluestosqlserver.asp.
Or, see Knowledge Base article 317043, "HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;317043.
Use OracleLob.Write to Write to Oracle Databases
You can write BLOBs to an Oracle database by using the .NET Framework data provider for Oracle. This data provider permits the System.Data.OracleClient.OracleLob class to write BLOBs. The OracleLob.Write method enables you to write data in chunks.
More Information
For more information, see "OracleLob.Write Method," in .NET Framework Class Library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclelobclasswritetopic.asp.
Avoid Moving Binary Large Objects Repeatedly
Avoid moving BLOB data more than one time. For example, if you build a Web application that serves images, store the images on the file system and the file names in the database instead of storing the images as BLOBs in the database.
Storing the images as BLOBs in the database means that you must read the BLOB from the database to the Web server and then send the image from the Web server to the browser. Reading the file name from the database and having the Web server send the image to the browser reduces the load on the database server. It also reduces the data that is sent between the database and the Web server. This can significantly affect performance and scalability.
posted on 2008-05-27 17:11
王者之魂 阅读(1066)
评论(2) 编辑 收藏