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 <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. 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"Database=NorthWind;"
                        S"Integrated Security=true");
    
    // Open the connection
    conn->Open();
    
  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",
                                     conn);
    
    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",
                                        FileMode::Open,
                                        FileAccess::Read);
    
    // 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
    stream->Close();
    
  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",
                                           SqlDbType::VarBinary,
                                           image->Length,
                                           ParameterDirection::Input,
                                           false,
                                           0, 0, 0,
                                           DataRowVersion::Current,
                                           image);
    
    // Add the parameter to the command's parameter collection
    cmd->Parameters->Add(param);
    
  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:
    conn->Close();
    


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

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Live Event Date: November 13, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT APIs can be a great source of competitive advantage. The practice of exposing backend services as APIs has become pervasive, however their use varies widely across companies and industries. Some companies leverage APIs to create internal, operational and development efficiencies, while others use them to drive ancillary revenue channels. Many companies successfully support both public and private programs from the same API by varying levels …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds