How to read and write a file to or from a BLOB column by using ADO.NET and Visual C# .NET (317016)

SUMMARY

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'

back to the top

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

back to the top

Create the project and add the code

Open SQL Query Analyzer.
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:
```
string destfilepath;
string sourcefilepath;
					
```

Add the following code under the Form Load event:

destfilepath = @"c:\mytest.bmp";
sourcefilepath = @"c:\windows\coffee bean.bmp";

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.

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

back to the top

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

back to the top

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.

back to the top

How to read and write a file to or from a BLOB column by using ADO.NET and Visual C# .NET (317016)

IN THIS TASK

SUMMARY

Requirements

Create the project and add the code

Read from a BLOB column

Write to a BLOB column

Troubleshooting

REFERENCES