C# Code Snippet - Insert/Update Image To SQL Server

C# Code Snippet - Insert/Update Image To SQL Server

This .Net C# code snippet connects to SQL server and executes SQL statement and update/insert binary image data in database table. To use this function simply provide open database connection, insert/update SQL statement, binary image and image format. This function uses SqlClient name space to update data using SqlCommand. Modify the exception handling section to as your project requirements.

public int InsertUpdateImage(
        ref System.Data.SqlClient.SqlConnection _SqlConnection,
        string _SQL,
        System.Drawing.Image _Image,
        string _ImageFieldName,
        System.Drawing.Imaging.ImageFormat _ImageFormat)
{
    int _SqlRetVal = 0;

    try
    {
        // lets add this record to database
        System.Data.SqlClient.SqlCommand _SqlCommand
            = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

        // Convert image to memory stream
        System.IO.MemoryStream _MemoryStream = new System.IO.MemoryStream();
        _Image.Save(_MemoryStream, _ImageFormat);

        // Add image as SQL parameter
        System.Data.SqlClient.SqlParameter _SqlParameter 
            = new System.Data.SqlClient.SqlParameter("@" + _ImageFieldName, SqlDbType.Image);
       
        _SqlParameter.Value = _MemoryStream.ToArray();
        _SqlCommand.Parameters.Add(_SqlParameter);

        // Executes a Transact-SQL statement against the connection 
        // and returns the number of rows affected.
        _SqlRetVal = _SqlCommand.ExecuteNonQuery();

        // 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 0;
    }

    return _SqlRetVal;
}

Here is a simple example showing how to use above function (InsertUpdateImage) to load new image from file and insert into database table.

// 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)
{
    int _RecCount = 0;

	// load image from file
    System.Drawing.Image _Image = System.Drawing.Image.FromFile("C:\\SampleImage.JPG");

    // add new record to database table and get the autoincrement product id
    _RecCount = InsertUpdateImage(
        // Pass open database connection to function
         ref _SqlConnection,
        // Pass SQL statement to insert new record
        "INSERT INTO sampletable (name, price, image) VALUES ('sample product name', 22.75, @image)",
        // pass image
        _Image,
        // image field name
        "image",
        // image format
        System.Drawing.Imaging.ImageFormat.Jpeg);

    if (_RecCount > 0)
    {
        // Record successfully insert into database
        Console.WriteLine("Image Record added to database table");
    }
    else
    {
        // Record failed to insert into database
        Console.WriteLine("Failed to add new record to database table");
    }

    // close database connection
    _SqlConnection.Close();
}

C# Keywords Used:

• byte
• Image
• ImageFormat
• SqlDbType.Image
• MemoryStream
• ExecuteNonQuery
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

• Applies To: .Net, C#, CLI, SQL, SqlCommand, ExecuteNonQuery, MemoryStream, Image, ImageFormat, SqlDbType, SQL Server, SQL Client, Connection String, Database Connection, SQL Server Binary Data
• Programming Language : C# (C-Sharp)

External Resources:

• SqlCommand.ExecuteNonQuery Method
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class
• MemoryStream Class
• Image Class
• ImageFormat Class
• SqlDbType Enumeration

Leave a comment