資料庫中取Clob類型欄位出現亂碼

來源:互聯網
上載者:User
資料|資料庫 DataReader 的預設行為是在整個資料行可用時立即以行的形式載入傳入資料。但是,對於二進位大對象 (BLOB) 則需要進行不同的處理,因為它們可能包含數十億位元組的資料,而單個行中無法包含如此多的資料。Command.ExecuteReader 方法具有一個重載,它將採用 CommandBehavior 參數來修改 DataReader 的預設行為。您可以將 CommandBehavior.SequentialAccess 傳遞到 ExecuteReader 方法來修改 DataReader 的預設行為,以便讓 DataReader 按照順序在接收到資料時立即將其載入,而不是載入資料行。這是載入 BLOB 或其他大資料結構的理想方案。請注意,該行為可能會因資料來源的不同而不同。例如,從 Microsoft Access 中返回 BLOB 將導致整個 BLOB 載入到記憶體中,而不是按接收資料的順序載入資料。

在將 DataReader 設定為使用 SequentialAccess 時,務必要注意訪問所返回欄位的順序。DataReader 的預設行為是在整個行可用時立即載入該行,這使您能夠在讀取下一行之前按任何順序訪問所返回的欄位。但是,當使用 SequentialAccess 時,必須按順序訪問由 DataReader 返回的不同欄位。例如,如果查詢返回三個列,其中第三列是 BLOB,則必須在訪問第三個欄位中的 BLOB 資料之前返回第一個和第二個欄位的值。如果在訪問第一個或第二個欄位之前訪問第三個欄位,則第一個和第二個欄位值將不再可用。這是因為 SequentialAccess 已修改 DataReader,使其按順序返回資料,當 DataReader 已經讀取超過特定資料時,該資料將不可用。

在訪問 BLOB 欄位中的資料時,請使用 DataReader 的 GetBytes 或 GetChars 類型化訪問器,它們將用資料來填充數組。還可以對字元資料使用 GetString,但是為了節省系統資源,您可能不希望將整個 BLOB 值載入到單個字串變數中。您可以指定要返回的特定資料緩衝區大小,以及從返回的資料中讀取的第一個位元組或字元的起始位置。GetBytes 和 GetChars 將返回一個 long 值,它表示返回的位元組或字元數。如果將一個空數組傳遞給 GetBytes 或 GetChars,則返回的長值將是 BLOB 中字元或字元的總數。您可以選擇將數組中的某個索引指定為所讀取資料的起始位置。

以下樣本從 Microsoft SQL Server 中的 pubs 樣本資料庫中返回傳行者 ID 和徽標。發行者 ID (pub_id) 是字元欄位,而徽標則是圖形,即 BLOB。由於 logo 欄位是位元影像,因此該樣本使用 GetBytes 返回位元據。請注意,由於必須按順序訪問欄位,所以將在訪問徽標之前訪問當前資料行的發行者 ID。

[Visual Basic]
Dim pubsConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;")
Dim logoCMD As SqlCommand = New SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn)

Dim fs As FileStream ' Writes the BLOB to a file (*.bmp).
Dim bw As BinaryWriter ' Streams the binary data to the FileStream object.

Dim bufferSize As Integer = 100 ' The size of the BLOB buffer.
Dim outbyte(bufferSize - 1) As Byte ' The BLOB byte() buffer to be filled by GetBytes.
Dim retval As Long ' The bytes returned from GetBytes.
Dim startIndex As Long = 0 ' The starting position in the BLOB output.

Dim pub_id As String = "" ' The publisher id to use in the file name.

' Open the connection and read data into the DataReader.
pubsConn.Open()
Dim myReader As SqlDataReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess)

Do While myReader.Read()
' Get the publisher id, which must occur before getting the logo.
pub_id = myReader.GetString(0)

' Create a file to hold the output.
fs = New FileStream("logo" & pub_id & ".bmp", FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)

' Reset the starting byte for a new BLOB.
startIndex = 0

' Read 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.
Do While retval = bufferSize
bw.Write(outbyte)
bw.Flush()

' 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)
Loop

' Write the remaining buffer.
bw.Write(outbyte, 0 , retval - 1)
bw.Flush()

' Close the output file.
bw.Close()
fs.Close()
Loop

' Close the reader and the connection.
myReader.Close()
pubsConn.Close()

[C#]
SqlConnection pubsConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;");
SqlCommand logoCMD = new SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn);

FileStream fs; // Writes the BLOB to a file (*.bmp).
BinaryWriter bw; // Streams the BLOB to the FileStream object.

int bufferSize = 100; // Size of the BLOB buffer.
byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.
long retval; // The bytes returned from GetBytes.
long startIndex = 0; // The starting position in the BLOB output.

string pub_id = ""; // The publisher id to use in the file name.

// Open the connection and read data into the DataReader.
pubsConn.Open();
SqlDataReader myReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())
{
// Get the publisher id, which must occur before getting the logo.
pub_id = myReader.GetString(0);

// Create a file to hold the output.
fs = new FileStream("logo" + pub_id + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
bw = new BinaryWriter(fs);

// Reset the starting byte for the new BLOB.
startIndex = 0;

// 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)
{
bw.Write(outbyte);
bw.Flush();

// 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);
}

// Write the remaining buffer.
bw.Write(outbyte, 0, (int)retval - 1);
bw.Flush();

// Close the output file.
bw.Close();
fs.Close();
}

// Close the reader and the connection.
myReader.Close();
pubsConn.Close();



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。