How to insert images into SQL Server database table

Question

I have created a new SQL Server local database with a table called drink. I use Microsoft Visual Studio 2008.

Inside the table I defined the following columns:

id [int], kind [varchar], year [datatime], image [image]

I would like to insert images into the image column but I don't know how to do.

I need this column, because I want to display all data in DataGridView using VB.NET

Thanks for help!

If you can change the format of the table you should really use Varbinary(Max) as Image is deprecated. http://stackoverflow.com/questions/444072/varbinary-vs-image-sql-server-data-type-to-store-binary-data — Ciarán, Apr 03 '15 at 22:02
`image` data type will be removed in a future version of SQL Server. Avoid using this data types in new development work, and plan to modify applications that currently use it. Use `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, Apr 04 '15 at 06:44

score 3 · Answer 1 · answered Apr 03 '15 at 20:51

3

Using openrowset you can insert image into database:

insert into tableName (id,kind,ImageColumn) 
SELECT 1,'JPEG',BulkColumn 
FROM Openrowset( Bulk '<Path of the image>', Single_Blob) as img

answered Apr 03 '15 at 20:51

knkarthick24

3,106
15
21

score 2 · Accepted Answer · answered Apr 03 '15 at 22:39

This worked for me...

Imports System.Data.Sql
Imports System.IO
Imports System.drawing

Module Module1

    Private Const _ConnectString As String = "your connection string here"

     Sub Main()
        Dim MyImage As Image = Image.FromFile("RandomImage.jpg")
        Dim Id As Long = 1
        SaveDrinkImage(MyImage, Id)
     End Sub

    Sub SaveDrinkImage(MyImage As Image, Id As Long)

        Dim ImageBytes(0) As Byte
        Using mStream As New MemoryStream()
                 MyImage.Save(mStream, MyImage.RawFormat)
                 ImageBytes = mStream.ToArray()
        End Using

        Dim adoConnect = New SqlClient.SqlConnection(_ConnectString)
        Dim adoCommand = New SqlClient.SqlCommand("UPDATE [drink] SET [image]=@MyNewImage WHERE [id]=@id", adoConnect)

        With adoCommand.Parameters.Add("@MyNewImage", SqlDbType.Image)
            .Value = ImageBytes
            .Size = ImageBytes.Length
        End With
        With adoCommand.Parameters.Add("@id", SqlDbType.BigInt)
            .Value = Id
        End With

        adoConnect.Open()
        adoCommand.ExecuteNonQuery()
        adoConnect.close()

    End Sub

End Module

score 0 · Answer 3 · edited May 23 '17 at 11:54

0

There are two approaches. You can store the image as a blob in the database or you can store the path (string) to the image file. There is an answer that discusses this: Storing images in SQL Server?

edited May 23 '17 at 11:54

Community

1
1

answered Apr 03 '15 at 20:55

Infojam

175
1
9

score 0 · Answer 4 · answered Apr 03 '15 at 23:29

Try this:

Sub SaveDrinkToDB(name As String, imageFilePath As String)
    Using cn = New SqlConnection("connection string here")
        cn.Open()
        Using cmd = New SqlCommand("INSERT INTO Drink (Name, Image) Values(@Name,@Image)", cn)
            cmd.Parameters.AddWithValue("@name", name)
            cmd.Parameters.AddWithValue("@Image", File.ReadAllBytes(imageFilePath))
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

Usage:

    SaveDrinkToDB("Milk", "c:\drink.png")

score 0 · Answer 5 · edited Jan 10 '19 at 07:55

0

USE [database_name]
GO
INSERT INTO [schema].[tablename]([property1],[property2],[Property3],[Image])
     VALUES
           ('value','value','value','C:\pathname.jpg')
GO

edited Jan 10 '19 at 07:55

slavoo

5,798
64
37
39

answered Jan 10 '19 at 07:04

tushar mathur

29
5

2

Don't add only code to answer, explanation is also necessary – Harsh Patel Jan 10 '19 at 07:07

How to insert images into SQL Server database table

5 Answers5

Linked