Working with BLOBs

Saving and fetching binary data presents interesting problems that are typically not encountered when querying standard rows of data. The problems arise because you will probably not want to move the entire BLOB in one piece but will likely need to break it up into smaller portions. For example, consider having to move a large binary that is several megabytes in size. Loading the entire BLOB into a variable consumes a lot of memory and can seriously affect the performance of your application. Having to work with a table of these BLOBs, you can quickly see the dilemma.

The good thing is that the .NET Framework provides classes that are specifically designed for moving large amounts of binary data. Specifically, access to these classes—for example, the BinaryReader and BinaryWriter classes, the FileStream and MemoryStream classes, and so on—is enabled in the System.IO namespace. Although this lesson does not use all the available stream objects, it should provide enough of a starting point to understand the basics of saving and fetching binary data from a database.

BLOBs and the DataReader

In previous lessons, you have seen that the main ADO.NET object for accessing retrieved data is the DataReader. Although the DataReader provides an easy model for working with records where the number of columns and layout of the data are known,

(meaning you have been able to easily iterate through the reader and display the data), it also provides a means for returning BLOB data. By setting its CommandBehavior to SequentialAccess, you can then call the GetBytes method, which allows you to read the data in smaller, user-definable amounts. The bytes that make up a BLOB are transported in and out of the database to your application using byte arrays.

The following exercise demonstrates how to read and write binary data to the database, providing two distinctly different models. In the first model, you know how big your data is and you save it in one action. This is illustrated in the SaveBlobToDatabase method. In the FetchBlobFromDatabase method, you read the bits into a file, but you do it in small chunks defined by the BufferSize variable.

► Exercise 1: Saving and Retrieving BLOB Values

This sample application demonstrates several of the concepts explained in this chapter. In addition to just saving and fetching BLOB values, it also sets up some infrastructure for the application that uses Command objects to create a new table in the database (to hold the BLOB values) and executes parameterized queries to populate the list of available BLOBs and retrieve the BLOB value. The code has been compartmentalized, so it should be very easy to parse the routines that are important to you.

1.

Create a new Windows application and name it BLOBSample.

2.

Add

a ComboBox to the form and set its Name property to BlobList.

3.

Add

a button below the ComboBox and set the following properties

Name = RefreshBlobListButton

Text = Refresh List

4.

Add

a second button and set the following properties:

Name = SaveBlobButton

Text = Save BLOB to Database

5.

Add

a third button and set the following properties (see Figure 6-8):

Name = FetchBlobButton

Text = Fetch BLOB from Database

Is JilliliHffiWM ^JnJxj

Current BLOBs in database:

Refresh List

Save BLOB to Database

Fetch BLOB from Database

Figure 6-8 Form with controls in preparation for manipulating BLOB data

6. Double-click the form to create a Form_Load event handler.

Because this lesson's objective is to explain working with BLOB values, let's just add all the infrastructure code at once and get the form set up. You can analyze this code at your leisure!

7. Replace the Form 1 code with the following:

Imports System.Data Imports System.Data.SqlClient Imports System.IO

Public Class Form1

Private NorthwindConnection As New SqlConnection _

("Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True") Private CompleteFilePath As String = "" Private SavePath As String = "" Private Sub GetCompleteFilePath()

Dim OpenDialog As New OpenFileDialog OpenDialog.Title = "Select Document File to Save" OpenDial og.ShowDialog() CompleteFilePath = OpenDialog.FileName End Sub

Private Sub GetSavePath()

Dim SavePathDialog As New FolderBrowserDialog

SavePathDialog.Description = "Select a folder to restore BLOB file to" SavePathDial og.ShowDialog() SavePath = SavePathDialog.SelectedPath End Sub

' Create a table to hold our BLOB values Private Sub CreateDocumentStorageTable()

Dim CreateTableCommand As New SqlCommand

CreateTableCommand.Connection = NorthwindConnection CreateTableCommand.CommandType = CommandType.Text

CreateTableCommand.CommandText = "IF OBJECT_ID ( 'DocumentStorage' ) IS NOT NULL " & _

"DROP TABLE DocumentStorage; " & _ "CREATE TABLE DocumentStorage(" & _ "DocumentID int IDENTITY(1,1) NOT NULL, " & _ "FileName nvarchar(255) NOT NULL, " & _ "DocumentFile varbinary(max) NOT NULL)"

CreateTableCommand.Connection.Open() CreateTableCommand.ExecuteNonQuery() CreateTableCommand.Connection.Close() End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim response As DialogResult = MessageBox.Show("Create the Document Storage Table?"

Environment.NewLine & "Click Yes to create a new DocumentStorage table. Click No if you already have one!", _

"Create DocumentStorage table", MessageBoxButtons.YesNo, _ MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) Select Case response

Case Is = Windows.Forms.DialogResult.Yes

CreateDocumentSto rageTable() Case Is = Windows.Forms.DialogResult.No refreshBlobList() End Select

End Sub

Private Sub refreshBlobList()

Dim GetBlobListCommand As New SqlCommand _

("SELECT FileName FROM DocumentStorage", NorthwindConnection) Dim reader As SqlDataReader

GetBlobListCommand.Connection.Open() reader = GetBlobListCommand.ExecuteReader While reader.Read

BlobList.Items.Add(reader(0)) End While reader.Close()

GetBlobListCommand.Connection.Close()

BlobList.SelectedIndex = 0 End Sub End Class

usi usi usi usi usi ng System;

ng System.Collections.Generic; ng System.ComponentModel; ng System.Data; ng System.Drawing;

using System.Text;

using System.Windows.Forms; using System.Data.SqlClient; using System.IO;

namespace BlobSampleCS {

public partial class Form1 : Form {

InitializeComponent() ;

private SqlConnection NorthwindConnection = new SqlConnection ("Data Source=.\\sqlexpress;Initial Catalog=Northwind;Integrated Security=True"); private String CompleteFilePath = ""; private String SavePath = "";

private void GetCompleteFilePathO {

OpenFileDialog OpenDialog = new OpenFileDialog(); OpenDialog.Title = "Select Document to Save"; OpenDial og.ShowDialog(); CompleteFilePath = OpenDialog.FileName;

private void GetSavePath() {

FolderBrowserDialog SavePathDialog = new FolderBrowserDialog(); SavePathDialog.Description = "Select a folder to restore BLOB file to"; SavePathDialog.ShowDialogO; SavePath = SavePathDialog.SelectedPath;

// Create a table to hold our BLOB values.

private void CreateDocumentStorageTable() {

SqlCommand CreateTableCommand = new SqlCommand();

CreateTableCommand.Connection = NorthwindConnection;

CreateTableCommand.CommandType = CommandType.Text;

CreateTableCommand.CommandText =

"IF OBJECT_ID ( 'DocumentStorage' ) IS NOT NULL "DROP TABLE DocumentStorage; " + "CREATE TABLE DocumentStorage(" + "DocumentID int IDENTITY(1,1) NOT NULL, " + "FileName nvarchar(255) NOT NULL, " + "DocumentFile varbinary(max) NOT NULL)";

CreateTableCommand.Connection.Open(); CreateTableCommand.ExecuteNonQueryO; CreateTableCommand.Connection.Close();

private void Form1_Load(object sender, EventArgs e) {

DialogResult response = MessageBox.Show("Create the Document Storage Table?" + Environment.NewLine +

"Click Yes to create a new DocumentStorage table." + "Click No if you already have one!",

"Create DocumentStorage table", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);

switch (response) {

case DialogResult.Yes:

CreateDocumentStorageTable(); break; case DialogResult.No: RefreshBlobList(); break;

private void RefreshBlobListO {

SqlCommand GetBlobListCommand = new SqlCommand

("SELECT FileName FROM DocumentStorage", NorthwindConnection); SqlDataReader reader;

GetBlobListCommand.Connection.Open(); reader = GetBlobListCommand.ExecuteReader();

BlobList.Items.Add(reader[0]);

reader.Close();

GetBlobListCommand.Connection.Close(); BlobList.SelectedIndex = 0;

8. Add the following code to save the BLOB to the database:

Private Sub SaveBlobToDatabase()

' This call lets you select the ' binary file to save As a BLOB ' in the database. GetCompleteFilePath()

' The BLOB holds the byte array to save. Dim BLOB() As Byte

' The FileStream is the stream of bytes ' that represent the binary file. Dim FileStream As New IO.FileStream _

(CompleteFilePath, IO.FileMode.Open, IO.FileAccess.Read) ' The reader reads the binary data from the FileStream. Dim reader As New IO.BinaryReader(FileStream)

' The BLOB is asigned the bytes from the reader. ' The file length is passed to the ReadBytes method ' telling it how many bytes to read. BLOB=

reader.ReadBytes(CInt(My.Computer.FileSystem.GetFileInfo(CompleteFilePath).Length))

FileStream.Close() reader.Close()

' Create a command object to save ' the BLOB value.

Dim SaveDocCommand As New SqlCommand SaveDocCommand.Connection = NorthwindConnection SaveDocCommand.CommandText = "INSERT INTO DocumentStorage" & _

"(FileName, DocumentFile)" & _ "VALUES (@FileName, @DocumentFile)"

' Create parameters to store the filename and BLOB data.

Dim FileNameParameter As New SqlParameter("@FileName", SqlDbType.NChar)

Dim DocumentFileParameter As New SqlParameter("@DocumentFile", SqlDbType.Binary)

SaveDocCommand.Parameters.Add(FileNameParameter)

SaveDocCommand.Parameters.Add(DocumentFileParameter)

' Parse the filename out of the complete path ' and assign it to the parameter. FileNameParameter.Value = _

CompleteFilePath.Substring(CompleteFilePath.LastIndexOf("\") + 1)

' Set the DocumentFile parameteter to the BLOB Value. DocumentFileParameter.Value = BLOB

' Execute the command and save the BLOB to the database. Try

SaveDocCommand.Connection.Open() SaveDocCommand.ExecuteNonQueryO

MessageBox.Show(FileNameParameter.Value.ToString & _

" saved to database.", "BLOB Saved!", MessageBoxButtons.OK, _ MessageBoxIcon.Information) Catch ex As Exception

MessageBox.Show(ex.Message, "Save Failed", _ MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

SaveDocCommand.Connection.Close() End Try End Sub

private void SaveB1obToDatabase() {

// This call lets you select the // binary file to save As a BLOB // in the database. GetCompleteFilePathO;

// The BLOB holds the byte array to save. byte[] BLOB;

// The FileStream is the stream of bytes // that represent the binary file.

System.IO.FileStream FileStream = new System.IO.FileStream _

(CompleteFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

// The reader reads the binary data from the FileStream. System.IO.BinaryReader reader = new System.IO.BinaryReader(FileStream);

// The BLOB is asigned the bytes from the reader. // The file length is passed to the ReadBytes method // telling it how many bytes to read.

System.IO.FileInfo file = new FileInfo(CompleteFilePath);

BLOB = reader.ReadBytes((int)(file.Length));

FileStream.CloseO; reader.C1ose();

// Create a command object to save // the BLOB value.

SqlCommand SaveDocCommand = new Sq1Command(); SaveDocCommand.Connection = NorthwindConnection; SaveDocCommand.CommandText = "INSERT INTO DocumentStorage" + "(FileName, DocumentFile)" + "VALUES (©FileName, ©DocumentFile)";

// Create parameters to store the filename and BLOB data.

SqlParameter FileNameParameter = new Sq1Parameter("@Fi1eName", SqlDbType.NChar); SqlParameter DocumentFileParameter = new SqlParameter _

("©DocumentFile", SqlDbType.Binary); SaveDocCommand.Parameters.Add(FileNameParameter); SaveDocCommand.Parameters.Add(DocumentFileParameter);

// Parse the filename out of the complete path // and assign it to the parameter.

FileNameParameter.Value = CompleteFilePath.Substring _ (Comp1eteFi1ePath.LastIndexOf("\\")+ 1);

// Set the DocumentFile parameteter to the BLOB Value. DocumentFileParameter.Value = BLOB;

// Execute the command and save the BLOB to the database.

SaveDocCommand.Connection.Open(); SaveDocCommand.ExecuteNonQueryO;

MessageBox.Show(FileNameParameter.Value.ToString() + " saved to database.", _ "BLOB Saved!", MessageBoxButtons.OK, MessageBoxIcon.Information);

catch (Exception ex) {

MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK, _ MessageBoxIcon.Error);

finally {

SaveDocCommand.Connection.Close();

9. Add the following code to retrieve the BLOB from the database and write it back out as a file:

Private Sub FetchBlobFromDatabase()

' Verify there is a BLOB selected to retrieve. If BlobList.Text = "" Then

MessageBox.Show("Select a BLOB to fetch from the ComboBox") Exit Sub End If

' Get the path to save the BLOB to. GetSavePath()

' Create the Command object to fetch the selected BLOB.

Dim GetBlobCommand As New SqlCommand("SELECT FileName, DocumentFile " & _ "FROM DocumentStorage " & _

"WHERE FileName = @DocName", NorthwindConnection) GetBlobCommand.Parameters.Add("@DocName", SqlDbType.NVarChar).Value = _ BlobList.Text

' Current index to write the bytes to Dim CurrentIndex As Long = 0

' number of bytes to store in the BLOB. Dim BufferSize As Integer = 100

' Actual number of bytes returned when calling GetBytes. Dim BytesReturned As Long

' The Byte array used to hold the buffer. Dim Blob(BufferSize - 1) As Byte

GetBlobCommand.Connection.Open()

Dim reader As SqlDataReader = _

GetBlobCommand.ExecuteReader(CommandBehavior.SequentialAccess)

Do While reader.Read

' Create or open the selected file.

Dim FileStream As New IO.FileStream(SavePath & "\" & _

reader("FileName").ToString, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)

' Set the writer to write the BLOB to the file. Dim writer As New IO.BinaryWriter(FileStream)

' Reset the index to the beginning of the file. CurrentIndex = 0

' Set the BytesReturned to the actual number of bytes returned by the GetBytes call.

BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize)

' If the BytesReturned fills the buffer keep appending to the file. Do While BytesReturned = BufferSize writer.Write (Blob) writer.Flush()

CurrentIndex += BufferSize

BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize)

Loop

' When the BytesReturned no longer fills the buffer, write the remaining bytes. writer.Write(Blob, 0, CInt(BytesReturned - 1)) writer.Flush()

writer.Close() FileStream.Close()

Loop reader.Close()

GetBlobCommand.Connection.Close() End Sub

private void FetchBlobFromDatabase() {

// Verify there is a BLOB selected if (BlobList.Text == "") {

MessageBox.Show("Select a BLOB return;

to retrieve.

to fetch from the ComboBox");

GetSavePath();

// Create the Command object to fetch the selected BLOB.

SqlCommand GetBlobCommand = new SqlCommand("SELECT FileName, DocumentFile " + "FROM DocumentStorage " + "WHERE FileName = @DocName", NorthwindConnection);

GetBlobCommand.Parameters.Add("@DocName", SqlDbType.NVarChar).Value = BlobList.Text;

// Current index to write the bytes to. long CurrentIndex = 0;

// number of bytes to store in the BLOB. int BufferSize = 100;

// Actual number of bytes returned when calling GetBytes. long BytesReturned ;

// The Byte array used to hold the buffer. byte[] Blob = new byte[BufferSize];

GetBlobCommand.Connection.Open();

SqlDataReader reader = GetBlobCommand.ExecuteReader(CommandBehavior.SequentialAccess);

// Create or open the selected file.

System.IO.FileStream FileStream = new System.IO.FileStream(SavePath + "\\" + _ reader["FileName"].ToStringO, System.IO.FileMode.OpenOrCreate, _ System.IO.FileAccess.Write);

// Set the writer to write the BLOB to the file.

System.IO.BinaryWriter writer= new System.IO.BinaryWriter(FileStream);

// Reset the index to the beginning of the file. CurrentIndex = 0;

// Set the BytesReturned to the actual number // of bytes returned by the GetBytes call.

BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);

// If the BytesReturned fills the buffer keep appending to the file.

while (BytesReturned == BufferSize) {

writer.Write(Blob); writer.Flush();

CurrentIndex += BufferSize;

BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);

// When the BytesReturned no longer fills the buffer, write the remaining bytes. writer.Write(Blob, 0, (int)(BytesReturned)); writer.Flush();

writer.Close(); FileStream.Close();

reader.Close();

GetBlobCommand.Connection.Close();

10. Double-click each of the three buttons to create the button-click event handlers and add the following code to the form:

Private Sub SaveBlobButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)_

Handles SaveBlobButton.Click SaveBlobToDatabase()

End Sub

Private Sub FetchBlobButton_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs)_ Handles FetchBlobButton.Click FetchBlobFromDatabase ()

End Sub

Private Sub RefreshBlobListButton_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles RefreshBlobListButton.Click refreshBlobList()

private void RefreshBlobListButton_Click(object sender, EventArgs e) {

RefreshBlobList();

private void SaveBlobButton_Click(object sender, EventArgs e) {

SaveBlobToDatabase() ;

private void FetchBlobButton_Click(object sender, EventArgs e) {

FetchBlobFromDatabase();

11. Run the application. When the application starts, you have the option of creating the table that stores the BLOB values. If you select Yes, the table is created, replacing any existing DocumentStorage table with a new one.

CAUTION Existing DocumentStorage Table in your database

If you already have a DocumentStorage table in your database and select Yes to create one, the existing table is dropped along with any records it may contain.

12. Click the Save BLOB to Database button and navigate to any Microsoft Office Word document on your hard drive. As soon as you select a file, it is saved to the database and a confirmation message appears.

13. Click the Refresh List button, and the file you just saved appears in the combo box.

14. Now click the Fetch BLOB from Database button and select a folder to save the file (BLOB data) out to.

NOTE Save location

Select a different folder than the one containing the original file.

15. Click OK and the BLOB is retrieved from the database and written out to the file location specified. Navigate to the folder you selected and verify that the file is there.

NOTE Binary file types

Even though the sample indicates that you should save a document, you can actually select and save any binary file into the database.

Was this article helpful?

0 0

Post a comment