Managed C++: Read Image Data Using the ADO.NET DataReader

One of the first tasks I encountered when I started working with Managed C++ and ADO.NET was that of reading image data from a SQL Server database and then either saving or displaying the images. As an example, the standard SQL Server Northwind database has a table (Employees) that includes a Binary Large Object (BLOB) column that contains the image data for each employee. This article first lists the steps for reading this data using the .NET DataReader class. It then presents a generic function for reading an image from a SQL Server database and writing that data to a disk file.

If you want to learn more about ADO.NET and the classes presented here, my book Extending MFC Applications with the .NET Framework has three chapters devoted to ADO.NET, including such advanced subjects as disconnected data, working with hierarchical data, and dealing with issues of concurrency.

Reading Image Data from a SQL Server Database

When using ODBC, your code interfaces with the ODBC manager that directs calls to the ODBC Driver associated with the target data source. This function seam provides a layer of abstraction between your code and the driver, such that each part doesn’t need to be too intimately coupled with the other. Likewise, companies wanting to provide for ADO.NET access to specific data source types provide what are called managed providers. To that end, there are a number of managed providers for accessing just about any type of data source. This includes generic providers as well as providers that are database-specific such as Oracle and SQL Server.

Most of these managed providers define a data reader that enables the forward-only reading from a data source. The following steps use classes specific to SQL Server—SqlConnection, SqlCommand, and SqlDataReader—but you should be able to easily modify the following code snippets to use other managed providers.

  1. Specify the necessary DLL references and namespaces.
    Regardless of the provider you use, you must include a reference for your project to the system.data.dll module. However, the namespace(s) you specify depends on the provider classes that your code uses. As an example, because this article refers specifically to SQL Server, the code specifies the SqlClient namespace. For Oracle, you would specify the OracleClient namespace and so on:

    #using <system.data.dll>
    using namespace System::Data;
    using namespace System::Data::SqlClient;
    
  2. Create a Connection object and open the connection to the database.
    The first step to working with a data reader is to connect to a data store using an instance of one of the connection classes, such as SqlConnection. Here, I create and open a connection to the SQL Server NorthWind demo database:

    SqlConnection* conn =
      new SqlConnection(S"Server=localhost;"
                        S"Database=NorthWind;"
                        S"Integrated Security=true");
    conn->Open();
    

    The “integrated security” setting specifies that the user’s login authentication information will be used instead of a database-specific user ID and password.

  3. Create the Command object.
    Once the connection with the database has been opened, construct a Command object where you can specify the query to run against the data store in the command class’s constructor:

    SqlCommand* cmd = new SqlCommand(S"SELECT * FROM Employees", conn);
  4. Obtain a DataReader object.
    Once you’ve executed a command that yields a result set, you can obtain the reader for that command by calling the command class’s ExecuteReader method:

    SqlDataReader* reader = cmd->ExecuteReader();
  5. Enumerate the data reader.
    To move the cursor through the result set, you simply call the reader’s Read method. This method returns a boolean value indicating whether more records exist to be read, so it works nicely in a while loop:

    while (reader->Read())
    {
    ...
    }
    
  6. Read and convert the image data.
    You can retrieve data from a data reader by using the Item property, where you specify a column name or index. The data for the result set’s current row and the specified column are then returned in a generic Object.

    For a finer level of control, you can use the various data-type-specific methods, such as GetBoolean, GetGuid, or GetChars. For purposes of reading image data, you use the GetBytes method. By using GetBytes, you specify the column index, the starting index of the data to read (0 would mean to read from the beginning of the data), the buffer into which to read the data, the starting index of the buffer into which to read the data, and how many bytes to read. The return value is the number of bytes read.

    The trick here is that you need to call this method twice. The first time you call it, you specify a null value for the array; you don’t know the size of the value and therefore can’t allocate the proper amount of memory. Because this call will result in the method returning the amount of data stored in the column, you can use this call to allocate the array. A second call to GetBytes then fills the array. Here’s an example of that (where columnIndex is the zero-based index of the result set column that contains the image data):

    Byte image[] =
      __gc new Byte[Convert::ToInt32((reader->GetBytes(columnIndex,
                                                       0, 0, 0,
                                                       Int32::MaxValue)))];
    reader->GetBytes(columnIndex, 0, image, 0, image->Length);
    
    // At this point, the image data is in the buffer for you to use
    
  7. Close the connection.
    Once you’ve finished using the data reader, simply close the connection via the Close method.

    conn->Close();

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read