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'
Requirements
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:
| Microsoft Visual Studio .NET |
| ADO.NET fundamentals and syntax |
Create the project and add the code
| 1. | Open SQL Query Analyzer. |
| 2. | Change the default database to Northwind. Run the following
command, and then press F5: use Northwind |
| 3. | 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. |
| 4. | Open Visual Studio .NET. |
| 5. | Create a new Visual C# .NET Windows application. |
| 6. | 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; |
| 7. | Add four buttons to Form1, and then change the Text property of each of the buttons as follows:
| SQLBlob2File | | OlDbBlob2File | | File2OleDbBlob | | File2SqlBlob |
|
| 8. | Add the following string variable declarations under public
class Form1:
string destfilepath;
string sourcefilepath;
|
| 9. | Add the following code under the Form Load event:
destfilepath = @"c:\mytest.bmp";
sourcefilepath = @"c:\windows\coffee bean.bmp";
|
| 10. | 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); |
|
| 11. | Paste the following functions in Form1:
| SqlBlob2File | | OleDbBlob2File | | File2SqlBlob | | File2OleDbBlob |
|
| 12. | 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. |
Read from a BLOB column
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);
}
}
Write to a BLOB column
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);
}
}
Troubleshooting
| You may have to modify the code that is described in this
article to use the code on LongVarChar columns or on LongVarWChar columns. |
| Modify the connection string and SQL statements to suit
your own server. |
| Add error checking, in case your query returns no records.
|
For more information about working with BLOBs in ADO.NET,
see the following MSDN library documentation:
