Insert image in a database in VB.NET

ADO.NET offers a comprehensive and robust framework for establishing seamless database connectivity between both relational and non-relational systems. This connectivity is achieved through a cohesive and unified set of components that facilitate data access and manipulation across diverse data sources.

Within the scope of SQL Server, a widely used and powerful database management system, a range of system data types is provided. These data types serve as the foundation for defining and representing various kinds of data that can be utilized within SQL Server. They encompass a wide spectrum of data categories, enabling developers to effectively store, retrieve, and manipulate data in a structured manner.

SQL Server Image data type

Among the system data types supported by SQL Server is the Image data type. This specific data type is specifically designed to handle variable length binary data. It empowers developers to store and manage vast amounts of binary data, ranging from 0 bytes up to a staggering 2A31-1 (2,147,483,647) bytes in size. This capability proves immensely useful when dealing with images, multimedia files, or other types of binary data within the database.

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.

Full Source VB.NET
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