Managed C++: Write Image Data Using the ADO.NET Command and Parameter Objects

My previous article covered how to use the .NET SqlConnection, SqlCommand, and SqlDataReader classes to read image data from a database into a byte array and then how to save that information to disk. This article shows the reverse scenario of reading an image file from disk and saving it to a database column by utilizing the SqlCommand and SqlParameter classes.

Combining the Command and Parameter Objects

With the various command classes (for example, SqlCommand, OracleCommand, OdbcCommand, and so forth) you easily can insert or modify database records by passing a SQL statement to the command class's constructor (or setting a command object's CommandText property) and then calling one of the command object's "execute" methods, such as ExecuteReader.

However, things are a bit trickier when dealing with binary data, such as image data. There's no way to specify the binary data within a SQL statement. For situations like these, you can use one of the managed provider's parameter classes. The following steps illustrate how to do this by using the SQL Server managed provider's SqlParameter class:

  1. Specify the necessary DLL references and namespaces.
    This demonstration uses SQL Server (whose managed provider classes are defined in the SqlClient namespace). You'll need to include the namespace(s) specific to your managed provider:
    #using <>
    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. This demonstration creates and opens a connection to the SQL Server NorthWind demo database. The "integrated security" setting specifies that the user's login authentication information is used instead of a database-specific user ID and password:
    // Construct a connection object specifying the database
    // and any necessary authentication information
    SqlConnection* conn = 
      new SqlConnection(S"Server=localhost;"
                        S"Integrated Security=true");
    // Open the connection
  3. Create the Command object.
    As mentioned, you'll use parameters to insert image data into the database. The parameter class is used in situations where either the data cannot be passed in the SQL (as in this case) or in situations where the data to be passed won't be resolved until after the command has been constructed. For such cases, ADO.NET allows you to specify a placeholder for the value. This placeholder is a name preceded by the "at" sign, as in @MyParam.

    The following command object basically states that the Photo column of the Employees table will be updated by a value that will be specified at a later time, and the value will be identified by a parameter named @Photo:
    // Specify the parameter placeholder in the command's SQL
    SqlCommand* cmd = new SqlCommand("UPDATE Employees
                                      SET Photo=@Photo",
    Although common, it is not necessary to create the parameter name by prefixing the column name with the special @ character.

  4. Define and fill a byte array with the image data.
    As an example, this following command reads a file called c:\test.jpg into a byte array using the FileStream and Convert classes:
    // Read image into array
    FileStream* stream = new FileStream(S"c:\\test.jpg",
    // Determine the size of the image
    int size = Convert::ToInt32(stream->Length);
    // Allocate the byte array
    Byte image[] = __gc new  Byte[size];
    // Read the data into the byte array
    stream->Read(image, 0, size);
    // Close the stream
  5. Construct the Parameter object.
    All managed providers define a parameter class. For the SQL Server provider, this class is called SqlParameter. To construct a SqlParameter object, pass the parameter name, data type, data length, parameter direction, and the data. Once you've created the parameter, you can add it to the command object's parameter collection via the Add method:
    // Create a parameter called Photo of type VarBinary
    SqlParameter* param = new SqlParameter("@Photo",
                                           0, 0, 0,
    // Add the parameter to the command's parameter collection
  6. Update the table with the new image data.
    As this command will not return any data, you can use the ExecuteNonQuery method, which upon completion returns the number of rows that were updated:
    // Execute the query
    int rowsUpdated = Convert::ToInt32(cmd->ExecuteNonQuery());
  7. Close the connection.
    Once you've finished, simply close the connection via the Close method:

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.


  • 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

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • VMware vCloud® Government Service provided by Carpathia® is an enterprise-class hybrid cloud service that delivers the tried and tested VMware capabilities widely used by government organizations today, with the added security and compliance assurance of FedRAMP authorization. The hybrid cloud is becoming more and more prevalent – in fact, nearly three-fourths of large enterprises expect to have hybrid deployments by 2015, according to a recent Gartner analyst report. Learn about the benefits of …

Most Popular Programming Stories

More for Developers

RSS Feeds