[ Team LiB ]Recipe 9.11 Reading and Writing Binary Data with SQL Server
Problem
You need to read and write binary data from and to a SQL Server 2000 database.
Solution
Use the techniques from the following example.
The schema of table TBL0911 used in this solution is shown in Table 9-3
.
Table 9-3. TBL0911 schema
Column name Data type Length Allow nulls?
Id int 4 No
Description nvarchar 50 Yes
BlobData image 16 Yes
The sample code contains nine event handlers:
Form.Load
Creates a DataAdapter to read and update the Id and Description fields from table
TBL0911. A TextBox is bound to the Id column and another TextBox is bound to
the Description field. A DataSet is filled with all records from TBL0911. The
BindingManager is retrieved for the table in the DataSet. A handler is attached to
the BindingManager.PositionChanged event. Finally, the display is updated for the
current record in the table.
BindingManagerBase.PositionChanged
Clears the image displayed in the PictureBox on the form. The ID of the current
record is retrieved. A connection is created to select the field BlobData—an
image—from TBL0911 corresponding to the current record. The image is
retrieved using a DataReader. A MemoryStream is created from the image
retrieved and the MemoryStream is loaded into the PictureBox using the
Image.FromStream( ) method passing the image in the MemoryStream as an
argument.

using System.Configuration;
using System.Drawing;
using System.Windows.Forms;
using System.IO;

using System.Data;
using System.Data.SqlClient;

private const String TABLENAME = "TBL0911";

private DataSet ds;
private SqlDataAdapter da;

private BindingManagerBase bm;
private Byte[] image;

// . . .

private void BinaryDataForm_Load(object sender, System.EventArgs e)
{
// Create the DataSet.
ds = new DataSet( );

// Define select and update commands for the DataAdapter.
String selectCommand = "SELECT Id, Description FROM " + TABLENAME;
String updateCommand = "UPDATE " + TABLENAME + " " +
"SET Description = @Description " +
"WHERE Id = @Id";

// Create the DataAdapter.

// Clear the image and picture box.
image = null;
imagePictureBox.Image = null;

// Get the ID for the record from the binding manager.
int Id = (int)ds.Tables[TABLENAME].Rows[bm.Position]["ID"];

// Create the connection.
SqlConnection conn = new SqlConnection(
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
// Create the command to retrieve the image from the database.
String sqlText = "SELECT BlobData FROM " + TABLENAME +
" WHERE Id = " + Id;
SqlCommand cmd = new SqlCommand(sqlText, conn);

// Retrieve the image to a stream.
conn.Open( );
try
{
int bufferSize = 100;
byte[] outbyte = new byte[bufferSize];
long retVal = 0;
long startIndex = 0;

SqlDataReader
dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
dr.Read( );

// Check to see if the field is DBNull.
if (!dr.IsDBNull(0))

// Image is null or invalid in the database. Ignore.
}
finally
{
conn.Close( );
}

if (image != null)