使用ADO.Net分块读写数据库BLOB数据-技术开发专区

使用ADO.Net分块读写数据库BLOB数据

作者：ITPUB论坛编辑： nancy 2008-05-29 00:00

【IT168技术文档】

在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我至今都没有找到实现的方法。
下面就给出它们分别的实现代码:

读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

关注我们