This article describes how to read and write data from LongVarBinary BLOB columns in a database table.
This article contains examples of both the SqlDataAdapter class and the OleDbDataAdapter class. The only differences between the two classes, other than the class names, are the
connection strings and the declaration of SQL parameters; the fundamental
technique for retrieving the BLOB data is the same.
The sample code in this article uses a sample record that
is added to the Categories table of the Northwind database. After you use this
sample, you may want to remove this record: Enter the following command in SQL
Query Analyzer, and then press F5:
use Northwind
delete from Categories where CategoryName = 'Test'
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
One of the following: Microsoft Windows 2000 Professional,
Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or
Microsoft Windows NT 4.0 Server
Microsoft Visual Studio .NET
Microsoft SQL Server 7.0 or later
This
article assumes that you are familiar with the following topics:
Change the default database to Northwind. Run the following
command, and then press F5:
use Northwind
Insert a new record in the Categories table of the
Northwind database. Run the following command, and then press F5:
Insert into categories(categoryname) values ('Test')
Add this additional record to the Categories table
only to try this example; you do not have to modify any part of the existing
data in this table.
Open Visual Studio .NET.
Create a new Visual C# .NET Windows application.
Add references to your project for System.Data.SQLClient and System.Data.OleDb. Do this by adding the following two lines to the top of your
Form1.cs file:
using System.Data.SqlClient;
using System.Data.OleDb;
Add four buttons to Form1, and then change the Text property of each of the buttons as follows:
SQLBlob2File
OlDbBlob2File
File2OleDbBlob
File2SqlBlob
Add the following string variable declarations under public
class Form1:
In the Click event of each of buttons, call the following procedures as
appropriate to each button:
Click event for button that is labeled SqlBlob2File:
SqlBlob2File(destfilepath);
Click event for button that is labeled OLDbBlob2File:
OlDbBlob2File(destfilepath);
Click event for button that is labeled File2OleDbBlob:
File2OleDbBlob(sourcefilepath);
Click event for button that is labeled File2SqlBlob:
File2SqlBlob(sourcefilepath);
Paste the following functions in Form1:
SqlBlob2File
OleDbBlob2File
File2SqlBlob
File2OleDbBlob
Before you try to write to the .bmp file on disk, click the
File2OleDbBlob button to load an image in the SQL Server
database, and then press F5 to run the code.
The functions in the following sample code use the DataReader class to retrieve the BLOB value and to assign the BLOB value to
a byte array. Because the BLOB is already completely in memory, the data does
not have to be chunked, and the BLOB is assigned to a Byte array.
There are two calls to the GetBytes method:
The first call obtains the length of the BLOB in bytes and
is used to allocate the Byte array.
The second call retrieves the data. The FileStream object is used to write the Byte array to disk.
public void SqlBlob2File(string DestFilePath)
{
try
{
int PictureCol = 0; // the column # of the BLOB field
SqlConnection cn = new SqlConnection("server=localhost;integrated security=yes;database=NorthWind");
SqlCommand cmd = new SqlCommand("SELECT Picture FROM Categories WHERE CategoryName='Test'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
Byte[] b = new Byte[(dr.GetBytes(PictureCol, 0, null, 0, int.MaxValue))];
dr.GetBytes(PictureCol, 0, b, 0, b.Length);
dr.Close();
cn.Close();
System.IO.FileStream fs =
new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
fs.Write(b, 0, b.Length);
fs.Close();
MessageBox.Show("Image written to file successfully");
}
catch(SqlException ex)
{
MessageBox.Show (ex.Message);
}
}
public void OleDbBlob2File(string DestFilePath)
{
try
{
int PictureCol = 0; // the column # of the BLOB field
OleDbConnection cn = new OleDbConnection("provider=sqloledb;server=localhost;" +
"user id=uid;password=password;database=NorthWind");
OleDbCommand cmd = new OleDbCommand("SELECT Picture FROM Categories " +
"WHERE CategoryName='Test'", cn);
cn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
dr.Read();
Byte[] b = new Byte[(dr.GetBytes(PictureCol, 0, null, 0, int.MaxValue))];
dr.GetBytes(PictureCol, 0, b, 0, b.Length);
dr.Close();
cn.Close();
System.IO.FileStream fs =
new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
fs.Write(b, 0, b.Length);
fs.Close();
MessageBox.Show("Image written to file successfully");
}
catch(OleDbException ex)
{
MessageBox.Show (ex.Message);
}
}
The functions in the following sample code use the Command object and the Parameter object to write data from a Byte array to a BLOB column. The
following technique reads the file completely into memory. Therefore, this
technique does not have to chunk the data when data is written to the server. The Byte
array and its Length parameter are passed to the Parameter constructor.
private void File2SqlBlob(string SourceFilePath)
{
try
{
SqlConnection cn = new SqlConnection("server=localhost;integrated security=yes;database=NorthWind");
SqlCommand cmd = new SqlCommand("UPDATE Categories SET Picture=@Picture " +
"WHERE CategoryName='Test'", cn);
System.IO.FileStream fs =
new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
Byte[] b = new Byte[fs.Length];
fs.Read(b, 0, b.Length);
fs.Close();
SqlParameter P = new SqlParameter("@Picture", SqlDbType.VarBinary, b.Length,
ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, b);
cmd.Parameters.Add(P);
cn.Open();
if (cmd.ExecuteNonQuery() == 1)
MessageBox.Show("Your images stored successfully");
cn.Close();
}
catch(SqlException ex)
{
MessageBox.Show (ex.Message);
}
}
public void File2OleDbBlob(string SourceFilePath)
{
try
{
OleDbConnection cn = new OleDbConnection("provider=sqloledb;server=localhost;" +
"user id=uid;password=password;initial catalog=NorthWind");
OleDbCommand cmd = new OleDbCommand("UPDATE Categories SET Picture=? WHERE CategoryName='Test'", cn);
System.IO.FileStream fs =
new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
Byte[] b = new Byte[fs.Length];
fs.Read(b, 0, b.Length);
fs.Close();
OleDbParameter P = new OleDbParameter("@Picture", OleDbType.VarBinary, b.Length,
ParameterDirection.Input, false, 0, 0, null,DataRowVersion.Current, b);
cmd.Parameters.Add(P);
cn.Open();
if (cmd.ExecuteNonQuery() == 1)
MessageBox.Show("Your images stored successfully");
cn.Close();
}
catch(OleDbException ex)
{
MessageBox.Show (ex.Message);
}
}
Back to the top
