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

Friday Feb 4th 2005 by Tom Archer - MSFT

Tom Archer illustrates in step-by-step fashion how to read an image file from disk and then save it to a SQL Server database by using the ADO.NET SqlCommand and SqlParameter 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:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved