WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
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:
- 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;
- 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();
- 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.
- 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();
- 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);
- 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());
- Close the connection.
Once you've finished, simply close the connection via the Close method: