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 :
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.
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