C# Code Snippet - Get Image from sql server

C# Code Snippet - Get Image from sql server

This .Net C# 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.

public System.Drawing.Image ScalarToImage(
        ref System.Data.SqlClient.SqlConnection _SqlConnection,
        string _SQL)
{
    object _SqlRetVal = null;
    System.Drawing.Image _Image = null;

    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
                = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

        _SqlRetVal = _SqlCommand.ExecuteScalar();

        // Dispose command
        _SqlCommand.Dispose();
        _SqlCommand = null;
    }
    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 null;
    }

    // convert object to image
    try
    {
        // get image from object
        byte[] _ImageData = new byte[0];
        _ImageData = (byte[])_SqlRetVal;
        System.IO.MemoryStream _MemoryStream = new 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 null;
    }

    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 = new 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 != null && _SqlConnection.State == ConnectionState.Open)
{
    // get image data from database
    System.Drawing.Image _Image = ScalarToImage(
        // Pass open database connection to function
        ref _SqlConnection,
        // Pass SQL statement to get image data
        "SELECT image FROM sampletable WHERE productid = 14");

    if (_Image != null)
    {
        // Lets show this image
        pictureBox1.Image = _Image;
    }
    else
    {
        // Failed to get image data from database
        pictureBox1.Image = null;
    }
                
    // close database connection
    _SqlConnection.Close();
}

C# Keywords Used:

• byte
• 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# (C-Sharp)

External Resources:

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

sanju :: January 03-2011 :: 07:24 AM

Hi,
I tried to run the above code but i am getting error in  line no 5(near = symboll) and  12(near catch) in part2
i have directly written this code into my load funtion

Leave a comment