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!
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
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
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?
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")
USE [database_name]
GO
INSERT INTO [schema].[tablename]([property1],[property2],[Property3],[Image])
VALUES
('value','value','value','C:\pathname.jpg')
GO
imagedata 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. Usevarbinary(max)instead. See details here