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

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

Generic Method to Read Image Data

As promised, here's a generic method that takes as its parameters a data reader object pointer, a result set column index, and the name of a file. The function reads the data into memory and writes the image data to disk using the passed file name:

bool GetPictureValue(SqlDataReader* reader,
                     int columnIndex,
                     String* destination)
{
#pragma push_macro("new")
#undef new

  bool success = false;

  try
  {
    if (!reader->IsDBNull(columnIndex))
    {
      // Allocate a byte array
      Byte image[] = 
        __gc new Byte[Convert::ToInt32((reader->GetBytes(columnIndex,
                                                         0, 0, 0, 
                                                         Int32::MaxValue)))];

      // Read the binary data into the byte array
      reader->GetBytes(columnIndex, 0, image, 0, image->Length);

      // Open FileStream and write buffer to file.
      FileStream* stream = new FileStream(destination,
                                          FileMode::Create,
                                          FileAccess::Write);
      stream->Write(image, 0, image->Length);
      stream->Close();
    
      success = true;
    }
  }
  catch (Exception* e)
  {
    // handle exception
  }
  
  return success;
#pragma pop_macro("new")
}

Continuing with the Northwind Employees table example, you now can enumerate through every employee's photo data, writing the image data to disk with the following code:

SqlConnection* conn =
  new SqlConnection(S"Server=localhost;"
                    S"Database=NorthWind;"
                    S"Integrated Security=true");
SqlCommand* cmd = 
  new SqlCommand(S"SELECT EmployeeID, Photo FROM Employees", conn);

conn->Open();

SqlDataReader* reader = cmd->ExecuteReader();
while (reader->Read())
{
  String* fileName = String::Format(S"{0}.jpg", reader->Item[0]);
  GetPictureValue(reader, 1, fileName);
}
  
conn->Close();
Note: The only columns read are the EmployeeID (because it's used to determine the file name) and the Photo column (containing the image data).

Looking Ahead

This article illustrated how the ADO.NET data reader class enables you to easily read image data from a database, and it presented a sample generic function for reading this data and writing it to a disk file. The next article will show how to write image data from a disk file to a database via the SqlParameter class.



About the Author

Tom Archer - MSFT

I am a Program Manager and Content Strategist for the Microsoft MSDN Online team managing the Windows Vista and Visual C++ developer centers. Before being employed at Microsoft, I was awarded MVP status for the Visual C++ product. A 20+ year veteran of programming with various languages - C++, C, Assembler, RPG III/400, PL/I, etc. - I've also written many technical books (Inside C#, Extending MFC Applications with the .NET Framework, Visual C++.NET Bible, etc.) and 100+ online articles.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Confused by all the agile advice? Relax! With the Agile for Dummies eBook by your side you'll learn the fundamentals of agile and how to increase the productivity of your software teams while enabling them to produce higher-quality solutions that better fulfill customer needs much faster.

  • Live Event Date: April 22, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Database professionals — whether developers or DBAs — can often save valuable time by learning to get the most from their new or existing productivity tools. Whether you're responsible for managing database projects, performing database health checks and reporting, analyzing code, or measuring software engineering metrics, it's likely you're not taking advantage of some of the lesser-known features of Toad from Dell. Attend this live …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds