Net-informations.com
SiteMap  | About    

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.




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






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.