Insert image in a database

ADO.NET provides database connectivity between relational and non relational systems through a common set of components . SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. Image is a Datatype in SQL Server that stores variable length binary data from 0 through 2A31-1 (2,147,483,647) bytes. The following VB.NET program shows how to insert an Image in SQL Server.

First you have to create a table that contain an image column . The following sql script help you to create a table with Image Datatype column :

 
CREATE TABLE [dbo].[imgtable](
    [id] [int] NULL,
  [img] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The above scrip will create a table named as imgtable and adding two column , first column is id ,an integer datatype and second column is image, an image(img) datatype.

The following VB.NET source code read the image from physical path D:\picfile.jpg and stores it to a byte array and then insert it into database.

Next : Retrieve image from database

Download Source Code

Print Source Code

Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
	Dim fName As String

	Dim cnn As SqlConnection
	Dim connectionString As String

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		connectionString = "Data Source=servername; Initial Catalog=databasename; User ID=sa; Password=password"
		cnn = New SqlConnection(connectionString)
		fName = "D:\picfile.jpg"
		If File.Exists(fName) Then
			Dim id As Integer = 1
			Dim content As Byte() = ImageToStream(fName)
			cnn.Open()

			Dim cmd As New SqlCommand("insert into imgtable (id,img) values ( @id,@img)", cnn)
			cmd.Parameters.AddWithValue("@id", id)
			cmd.Parameters.AddWithValue("@img", content)
			cmd.ExecuteNonQuery()

			cnn.Close()
			MsgBox("Image inserted")
		Else
			MsgBox(fName & " not found ")
		End If
	End Sub

	Private Function ImageToStream(ByVal fileName As String) As Byte()
		Dim stream As New MemoryStream()
tryagain:
		Try
			Dim image As New Bitmap(fileName)
			image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg)
		Catch ex As Exception
			GoTo tryagain
		End Try

		Return Stream.ToArray()
	End Function
End Class

Retrieve image from database