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

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 BlobDataan imagefrom 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.

Select Image Button.Click

Opens a file dialog to allow an image to be selected by the user . The image is retrieved using a FileStream and loaded into the PictureBox using the Image.FromStream( ) method.

Clear Image Button.Click

Clears the selected image from the PictureBox on the form.

Update Button.Click

Gets the ID of the current record from the BindingManager and builds a SQL statement to update the image in the field BlobData. A connection is created and a stored procedure command is created to update the image. A parameter for the image is added to the stored procedure command. The ExecuteNonquery( ) method of the Command object is used to update the image in the database. The DataAdapter is used to update the other datathe Description field in this sample.

<< (move first) Button.Click

Moves to the first record by setting the Position property of the BindingManager to 0.

< (move previous) Button.Click

Moves to the previous record by setting the Position property of the BindingManager to one less than the current value.

> (move next ) Button.Click

Moves to the next record by setting the Position property of the BindingManager to one more than the current value.

>> (move last) Button.Click

Moves to the last record by setting the Position property of the BindingManager to one less than the number of records.

The C# code is shown in Example 9-14.

Example 9-14. File: BinaryDataForm.cs

// Namespaces, variables, and constants using System; 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. da = new SqlDataAdapter(selectCommand, ConfigurationSettings.AppSettings["Sql_ConnectString"]); da.UpdateCommand = new SqlCommand(updateCommand, da.SelectCommand.Connection); da.UpdateCommand.CommandType = CommandType.Text; da.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id"); da.UpdateCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description"); // Fill the schema and the data from the table. da.FillSchema(ds, SchemaType.Source, TABLENAME); da.Fill(ds, TABLENAME); // Bind all of the controls to the DataSet. idTextBox.DataBindings.Add("Text", ds, TABLENAME + ".Id"); descriptionTextBox.DataBindings.Add("Text", ds, TABLENAME + ".Description"); // Get the binding manager base for the parent table. bm = BindingContext[ds, TABLENAME]; // Handler to update the correct image in response to // each record reposition bm.PositionChanged += new EventHandler(bm_PositionChanged); // Update the display for the first record. bm_PositionChanged(null, null); } private void bm_PositionChanged(Object sender, EventArgs e) { // Handler for the binding manager record change // 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)) { // Create the memory stream to hold the output. MemoryStream ms = new MemoryStream( ); // Read the bytes into outbyte. retVal = dr.GetBytes(0, startIndex, outbyte, 0, bufferSize); // Keep reading while there are more bytes // beyond the buffer. while (retVal == bufferSize) { // Write the bytes to the memory stream. ms.Write(outbyte, 0, outbyte.Length); // Update the start index and // fill the buffer again. startIndex += bufferSize; retVal = dr.GetBytes(0, startIndex, outbyte, 0, bufferSize); } // Write the bytes remaining in the buffer. ms.Write(outbyte, 0, (int)retVal - 1); // Transfer the memory stream to the image. image = ms.ToArray( ); } } catch (System.InvalidCastException) { // Image is null or invalid in the database. Ignore. } finally { conn.Close( ); } if (image != null) { // Load the image into a stream. MemoryStream ms = new MemoryStream(image); try { // Set the PictureBox image to the value of the stream. imagePictureBox.Image = Image.FromStream(ms); } catch(Exception ex) { MessageBox.Show(ex.Message); } // Close the stream. ms.Close( ); } } private void selectImageButton_Click(object sender, System.EventArgs e) { // Create the file dialog to select image. OpenFileDialog ofd = new OpenFileDialog( ); ofd.InitialDirectory = System.IO.Path.GetTempPath( ); ofd.Filter = "Bitmap Files (*.bmp)*.bmpJPEG files (*.jpg)*.jpg" + "All files (*.*)*.*"; ofd.FilterIndex = 2; if (ofd.ShowDialog( ) == DialogResult.OK) { // Read image into file stream, and from there into Byte array. FileStream fs = new FileStream(ofd.FileName, FileMode.Open, FileAccess.Read); image = new Byte[fs.Length]; fs.Read(image, 0, image.Length); try { // Set the PictureBox image from the stream. imagePictureBox.Image = Image.FromStream(fs); } catch (Exception ex) { MessageBox.Show(ex.Message); image = null; } fs.Close( ); } } private void clearImageButton_Click(object sender, System.EventArgs e) { // Clear the image and picture box. image = null; imagePictureBox.Image = null; } private void updateButton_Click(object sender, System.EventArgs e) { // Update the data and image to the database. // Get the ID for the record from the binding manager. int Id = (int)ds.Tables[TABLENAME].Rows[bm.Position]["ID"]; String sqlWrite = "UPDATE " + TABLENAME + " SET BlobData = @BlobData WHERE ID = " + Id; // Create the connection and command. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmdWrite = new SqlCommand(sqlWrite, conn); // Create parameter for insert command. SqlParameter prm; if(image != null) { // Add a parameter for the image binary data. prm = new SqlParameter("@BlobData", SqlDbType.VarBinary, image.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, image); } else { // Add a parameter for a null image. prm = new SqlParameter("@BlobData", SqlDbType.VarBinary, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, System.DBNull.Value); } // Add the parameter to the command. cmdWrite.Parameters.Add(prm); // Execute the command to update the image in the database. conn.Open( ); cmdWrite.ExecuteNonQuery( ); conn.Close( ); // End the binding manager edit. bm.EndCurrentEdit( ); // Use the DataAdapter to update the table data. da.Update(ds.Tables[TABLENAME]); } private void moveFirstButton_Click(object sender, System.EventArgs e) { bm.Position = 0; } private void movePreviousButton_Click(object sender, System.EventArgs e) { bm.Position -= 1; } private void moveNextButton_Click(object sender, System.EventArgs e) { bm.Position += 1; } private void moveLastButton_Click(object sender, System.EventArgs e) { bm.Position = bm.Count - 1; }

Discussion

You can write a BLOB to a data source using several techniques:

• Issue a SQL INSERT or UPDATE statement and pass in the BLOB value as an input parameter as shown in the solution code. With SQL Server, if the BLOB contains character data, use a SqlDbType.Text or SqlDbType.NText parameter and pass in the BLOB as a String ; if the BLOB contains binary data, use a SqlDbType.Image parameter and pass in the BLOB as a Byte array.
• Create a DataRow and define its schema for the binary types as described previously or retrieve the schema from the data source using the FillSchema( ) method of the DataAdapter . Add the row to a DataTable and use the Update( ) method of the DataAdapter to update the data source. Recipe 9.13 shows an example of this technique.

You can read a BLOB from a data source using several techniques:

• Use a DataReader as shown in the solution code. While this approach is more complicated than using the ExecuteScalar( ) solution, it is more flexible and capable of dealing with very large BLOB data. Large BLOB data needs to be treated differently than other data when reading with a DataReader since the data cannot be contained in a single row. The ExecuteReader( ) method of the Command object that is used to create the DataReader has an overload that takes an argument from the CommandBehavior enumeration.

  Passing the value CommandBehavior.SequentialAccess causes the DataReader to load the data sequentially as it is received rather than the default behavior of loading one row of data at a time. Some data sources do not behave in this wayfor example, Microsoft Access reads the entire BLOB into memory rather than loading it sequentially as it is received. The default behavior of the DataReader allows data to be read from fields in the row in any order. When using SequentialAccess , the fields must be read in the order that they are retrieved and once a field is read, the previous fields in that row are no longer available.

• Use the GetBytes( ) typed accessor method to read the binary data from the BLOB into a Byte array. This method allows you to read the data as a sequence of smaller pieces of a defined number of bytes ( chunks ) to reduce system resources required when with dealing with large files. The solution demonstrates this using a buffer with an arbitrary size of 100 bytes.
• Use the GetChars( ) typed accessor method to read character BLOB data into a Char array or the GetString( ) typed accessor method to read the data into a String variable. Check for null values when using typed accessor methods , if necessary.
• Use the ExecuteScalar( ) method of the Command object with a SQL SELECT statement that returns the BLOB . Cast the result of the ExecuteScalar( ) method to a Byte array if it contains binary data or to a String if it contains character data. The following code demonstrates this technique:

  image = (Byte[])cmd.ExecuteScalar( );

A BLOB can be quite large and may require a lot of system memory to be written to a data source as a single value. In addition to reading BLOBs in chunks, some data sources allow you to write a BLOB to the data source in chunks. For more information consult the MSDN Library and the documentation for your .NET data provider.