C++/CLI Code Snippet - Get Image from sql server

C++/CLI Code Snippet - Get Image from sql server

This .Net C++/CLI code snippet connects to SQL server and executes SQL statement and returns the binary image data from database. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlCommand. Modify the exception handling section to as your project requirements.

System::Drawing::Image ^ScalarToImage(System::Data::SqlClient::SqlConnection ^%_SqlConnection, System::String ^_SQL)
{
    System::Object ^_SqlRetVal = nullptr;
    System::Drawing::Image ^_Image = nullptr;

    try
    {
        // Executes the query, and returns the first column of the first row in the result 
        // set returned by the query. Additional columns or rows are ignored.
        System::Data::SqlClient::SqlCommand ^_SqlCommand = gcnew System::Data::SqlClient::SqlCommand(_SQL, _SqlConnection);

        _SqlRetVal = _SqlCommand->ExecuteScalar();

        // Dispose command
        delete _SqlCommand;
        _SqlCommand = nullptr;
    }
    catch (Exception ^_Exception)
    {
        // Error occurred while trying to execute reader
        // send error message to console (change below line to customize error handling)
        Console::WriteLine(_Exception->Message);

        return nullptr;
    }

    // convert object to image
    try
    {
        // get image from object
        array<System::Byte> ^_ImageData = gcnew array<System::Byte>(0);
        _ImageData = safe_cast<array<System::Byte>^>(_SqlRetVal);
        System::IO::MemoryStream ^_MemoryStream = gcnew System::IO::MemoryStream(_ImageData);
        _Image = System::Drawing::Image::FromStream(_MemoryStream);
    }
    catch (Exception ^_Exception)
    {
        // Error occurred while trying to create image
        // send error message to console (change below line to customize error handling)
        Console::WriteLine(_Exception->Message);

        return nullptr;
    }

    return _Image;
}

Here is a simple example showing how to use above function (ScalarToImage) to connect to SQL database and get image data and show it in a picturebox.

// set temporary variable for database connection
System::Data::SqlClient::SqlConnection ^_SqlConnection = gcnew System::Data::SqlClient::SqlConnection();

// assign database connection string
_SqlConnection->ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;";

// Connect to database
try
{
    _SqlConnection->Open();
}
catch (Exception ^_Exception)
{
    // Error occurred while trying to connect to database
    Console::WriteLine(_Exception->Message);
}

// Check for valid open database connection before query database
if (_SqlConnection != nullptr && _SqlConnection->State == ConnectionState::Open)
{
    // get image data from database
        // Pass open database connection to function
        // Pass SQL statement get image data
    System::Drawing::Image ^_Image = ScalarToImage(_SqlConnection, "SELECT image FROM sampletable WHERE productid = 14");

    if (_Image != nullptr)
    {
        // Lets show this image
        pictureBox1->Image = _Image;
    }
    else
    {
        // Failed to get image data from database
        pictureBox1->Image = nullptr;
    }

    // close database connection
    _SqlConnection->Close();
}

C++/CLI Keywords Used:

• Byte
• safe_cast
• Image
• MemoryStream
• FromStream
• ExecuteScalar
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

• Applies To: .Net, C++, CLI, SQL, SqlCommand, ExecuteScalar, MemoryStream, Image, SQL Server, SQL Client, Connection String, Database Connection, SQL Server Binary Data
• Programming Language : C++/CLI

External Resources:

• SqlCommand.ExecuteScalar Method
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class
• MemoryStream Class
• Image Class
• safe_cast

Leave a comment