Visual Basic Code Snippet - Insert/Update Image To SQL Server

Visual Basic Code Snippet - Insert/Update Image To SQL Server

This .Net Visual Basic 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 Function InsertUpdateImage(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String, ByVal _Image As System.Drawing.Image, ByVal _ImageFieldName As String, ByVal _ImageFormat As System.Drawing.Imaging.ImageFormat) As Integer
    Dim _SqlRetVal As Integer = 0

    Try
        ' lets add this record to database
        Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)

        ' Convert image to memory stream
        Dim _MemoryStream As New System.IO.MemoryStream()
        _Image.Save(_MemoryStream, _ImageFormat)

        ' Add image as SQL parameter
        Dim _SqlParameter As 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 = Nothing
    Catch _Exception As 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
    End Try

    Return _SqlRetVal
End Function

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
Dim _SqlConnection As 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 As Exception
    ' Error occurred while trying to connect to database
    Console.WriteLine(_Exception.Message)
End Try

' Check for valid open database connection before query database
If _SqlConnection IsNot Nothing AndAlso _SqlConnection.State = ConnectionState.Open Then
    Dim _RecCount As Integer = 0

    ' load image from file
    Dim _Image As System.Drawing.Image = System.Drawing.Image.FromFile("C:\SampleImage.JPG")

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

    If _RecCount > 0 Then
        ' 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")
    End If

    ' close database connection
    _SqlConnection.Close()
End If

VB Keywords Used:

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

Code Snippet Information:

• Applies To: .Net, VB, Visual Basic, CLI, SQL, SqlCommand, ExecuteNonQuery, MemoryStream, Image, ImageFormat, SqlDbType, SQL Server, SQL Client, Connection String, Database Connection, SQL Server Binary Data
• Programming Language : Visual Basic (VB)

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