Read / Write BLOBs from / to SQL Server using C# .NET DataReader

More Information on installing the .Net Framework click here.
Download full Visual Studio C# .NET Examples from this Article.

Obtaining BLOB Values from a Database

The default behavior of the DataReader is to load incoming data as a row as soon as an entire row of data is available. Binary large objects (BLOBs) need to be treated differently, however, because they can contain gigabytes of data that cannot be contained in a single row. The Command.ExecuteReader method has an overload which will take a CommandBehavior argument to modify the default behavior of the DataReader. You can pass CommandBehavior.SequentialAccess to the ExecuteReader method to modify the default behavior of the DataReader so that instead of loading rows of data, it will load data sequentially as it is received. This is ideal for loading BLOBs or other large data structures. Note that this behavior may differ depending on your data source.

Provides a way for the DataReader to handle rows that contain columns with large binary values BLOBs. Rather than loading the entire row, SequentialAccess enables the DataReader to load data as a stream. When setting the DataReader to use SequentialAccess, it is important to note the sequence in which you access the fields returned. The default behavior of the DataReader, which loads an entire row as soon as it is available, allows you to access the fields returned in any order until the next row is read. When using SequentialAccess however, you must access the different fields returned by the DataReader in order. For example, if your query returns three columns, the third of which is a BLOB, you must return the values of the first and second fields before accessing the BLOB data in the third field. If you access the third field before the first or second fields, the first and second field values will no longer be available. This is because SequentialAccess has modified the DataReader to return data in sequence and the data will not be available after the DataReader has read past it.

When accessing the data in the BLOB field, use the GetBytes or GetChars typed accessors of the DataReader, which fill an array with data. You can also use GetString for character data, however to conserve system resources you may not want to load an entire BLOB value into a single string variable. You can specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. GetBytes and GetChars will return a long value, which represents the number of bytes or characters returned. If you pass a null array to GetBytes or GetChars, the long value returned will be the total number of bytes or characters in the BLOB. You can optionally specify an index in the array as a starting position for the data being read.

Writing BLOB Values to a Database

You can write a binary large object (BLOB) to a database as either binary or character data, depending on the type of field at your data source. To write a BLOB value to your database, issue the appropriate INSERT or UPDATE statement and pass the BLOB value as an input parameter. If your BLOB is stored as text, such as a SQL Server text field, you can pass the BLOB as a string parameter. If the BLOB is stored in binary format, such as a SQL Server image field, you can pass an array of type byte as a binary parameter.

Example

The following code example adds employee information to the Employees table in the Northwind database. A photo of the employee is read from a file and added to the Photo field in the table, which is an image field. The Photo field is then read using GetBytes. Notice that the employee id is accessed for the current row of data before the Photo, because the fields must be accessed sequentially.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Akadia.ReadWriteBlob
{
    // Read and Write BLOB to/from Filesystem and Database
    public class BlobSample : System.Windows.Forms.Form
    {
        private System.Windows.Forms.PictureBox pbxPhoto;
        private System.ComponentModel.Container components = null;
        private System.Windows.Forms.Button btnLoadPhoto;
        private System.Windows.Forms.Button btnAddEmp;
        private System.Windows.Forms.Button btnGetEmp;
        private System.Windows.Forms.StatusBar statusBar;

        // My own private Variables
        private String _fname = null;
        private SqlConnection _conn;

        // Constructor
        public BlobSample()
        {
            // Initialize GUI
            InitializeComponent();

            // Get Database Connection
            _conn = new SqlConnection("data source=XEON;"+
                "initial catalog=Northwind;"+
                "user id=sa;password=manager;");
        }

        // **** Read Image from Filesystem and add it to the Database.
        public void AddEmployee(
            string plastName,
            string pfirstName,
            string ptitle,
            DateTime phireDate,
            int preportsTo,
            string photoFilePath)
        {

            // Read Image into Byte Array from Filesystem
            byte[] photo = GetPhoto(photoFilePath);

            // Construct INSERT Command
            SqlCommand addEmp = new SqlCommand(
                "INSERT INTO Employees ("+
                "LastName,FirstName,Title,HireDate,ReportsTo,Photo) "+
                "VALUES(@LastName,@FirstName,@Title,@HireDate,@ReportsTo,@Photo)",_conn);

            addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = plastName;
            addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = pfirstName;
            addEmp.Parameters.Add("@Title",     SqlDbType.NVarChar, 30).Value = ptitle;
            addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value     = phireDate;
            addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value          = preportsTo;
            addEmp.Parameters.Add("@Photo",     SqlDbType.Image, photo.Length).Value = photo;

            // Open the Connection and INSERT the BLOB into the Database
            _conn.Open();
            addEmp.ExecuteNonQuery();
            _conn.Close();
        }

        // **** Read Image into Byte Array from Filesystem
        public static byte[] GetPhoto(string filePath)
        {
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            BinaryReader br = new BinaryReader(fs);

            byte[] photo = br.ReadBytes((int)fs.Length);

            br.Close();
            fs.Close();

            return photo;
        }

        // **** Read BLOB from the Database and save it on the Filesystem
        public void GetEmployee(string plastName,string pfirstName)
        {
            SqlCommand getEmp = new SqlCommand(
                "SELECT EmployeeID, Photo "+
                "FROM Employees "+
                "WHERE LastName = @lastName "+
                "AND FirstName = @firstName", _conn);

            getEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = plastName;
            getEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = pfirstName;

            FileStream fs;                          // Writes the BLOB to a file (*.bmp).
            BinaryWriter bw;                        // Streams the BLOB to the FileStream object.
            int bufferSize = 100;                   // Size of the BLOB buffer.
            byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.
            long retval;                            // The bytes returned from GetBytes.
            long startIndex = 0;                    // The starting position in the BLOB output.
            string emp_id = "";                     // The employee id to use in the file name.

            // Open the connection and read data into the DataReader.
            _conn.Open();
            SqlDataReader myReader = getEmp.ExecuteReader(CommandBehavior.SequentialAccess);

            while (myReader.Read())
            {
                // Get the employee id, which must occur before getting the employee.
                emp_id = myReader.GetInt32(0).ToString();

                // Create a file to hold the output.
                fs = new FileStream("employee" + emp_id + ".bmp",
                                    FileMode.OpenOrCreate, FileAccess.Write);
                bw = new BinaryWriter(fs);

                // Reset the starting byte for the new BLOB.
                startIndex = 0;

                // Read the bytes into outbyte[] and retain the number of bytes returned.
                retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

                // Continue reading and writing while there are bytes beyond the size of the buffer.
                while (retval == bufferSize)
                {
                    bw.Write(outbyte);
                    bw.Flush();

                    // Reposition the start index to the end of the last buffer and fill the buffer.
                    startIndex += bufferSize;
                    retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
                }

                // Write the remaining buffer.
                bw.Write(outbyte, 0, (int)retval);
                bw.Flush();

                // Close the output file.
                bw.Close();
                fs.Close();
            }

            // Close the reader and the connection.
            myReader.Close();
            _conn.Close();
        }

        private void btnAddEmp_Click(object sender, System.EventArgs e)
        {
            DateTime hireDate = DateTime.Parse("2003.05.03");
            AddEmployee("Mary","Jones","Software Engineer",hireDate,5,_fname);
            statusBar.Text = "Employee added to the Database";
        }

        private void btnGetEmp_Click(object sender, System.EventArgs e)
        {
            GetEmployee("Mary","Jones");
            statusBar.Text = "Employee saved to Filesystem";
        }

        private void btnLoadPhoto_Click(object sender, System.EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Title = "Open Photo";
            dlg.Filter = "Windows Bitmap Files (*.bmp)|*.bmp"
                + "|All files (*.*)|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    pbxPhoto.Image = new Bitmap(dlg.OpenFile());
                    _fname = dlg.FileName;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Unable to load file: " + ex.Message);
                }
            }

            dlg.Dispose();
        }

        .....
        .....

        // The main entry point for the application.
        [STAThread]
        static void Main()
        {
            Application.Run(new BlobSample());
        }
    }
}