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