hello All

i am developing a windows appliation using VS 2005 (VB.NET)

how to store & Reteriving image from SQL Server

Please help me

Thanks In Advance

This is to add the image

Dim intImageSize As Int64
Dim strImageType As String
Dim ImageStream As Stream

' Gets the Size of the Image
intImageSize = PersonImage.PostedFile.ContentLength

' Gets the Image Type
strImageType = PersonImage.PostedFile.ContentType

' Reads the Image
ImageStream = PersonImage.PostedFile.InputStream

Dim ImageContent(intImageSize) As Byte
Dim intStatus As Integer
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)

' Create Instance of Connection and Command Object
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlCommand("sp_person_isp", con)


cmd.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)
prmPersonImage.Value = ImageContent
cmd.Parameters.Add(prmPersonImage)

Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 50)
prmPersonImageType.Value = strImageType
cmd.Parameters.Add(prmPersonImageType)

Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Response.Write("New person successfully added!")
Catch SQLexc As SqlException
Response.Write("Insert Failed. Error Details are: " & SQLexc.ToString())
End Try


code For stored procedure

Create Table Person

(
PersonID Int Identity,

PersonEmail Varchar(50),

PersonName Varchar(50),

PersonSex Char(1),

PersonDOB DateTime,

PersonImage Image,

PersonImageType Varchar(15)

)

Drop Proc sp_person_isp

Go

Create Proc sp_person_isp

@PersonEmail Varchar(50),

@PersonName Varchar(50),

@PersonSex Char(1),

@PersonDOB DateTime,

@PersonImage Image,

@PersonImageType Varchar(50)

As

Begin

Insert into Person

(PersonEmail, PersonName, PersonSex,

PersonDOB, PersonImage, PersonImageType)

Values

(@PersonEmail, @PersonName, @PersonSex,

@PersonDOB, @PersonImage, @PersonImageType)

End



To Retrieve the images from sqlserver

Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlCommand("Select * from Person", con)

Try
con.Open()
Dim dr as SqlDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Do While (dr.Read())
Response.ContentType = dr.Item("PersonImageType")
Response.BinaryWrite(dr.Item("PersonImage"))
Loop

con.Close()
Response.Write("Person info successfully retrieved!")
Catch SQLexc As SqlException
Response.Write("Read Failed : " & SQLexc.ToString())
End Try

Thank u

Baba

This is to add the image

Dim intImageSize As Int64
Dim strImageType As String
Dim ImageStream As Stream

' Gets the Size of the Image
intImageSize = PersonImage.PostedFile.ContentLength

' Gets the Image Type
strImageType = PersonImage.PostedFile.ContentType

' Reads the Image
ImageStream = PersonImage.PostedFile.InputStream

Dim ImageContent(intImageSize) As Byte
Dim intStatus As Integer
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)

' Create Instance of Connection and Command Object
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlCommand("sp_person_isp", con)


cmd.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)
prmPersonImage.Value = ImageContent
cmd.Parameters.Add(prmPersonImage)

Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 50)
prmPersonImageType.Value = strImageType
cmd.Parameters.Add(prmPersonImageType)

Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Response.Write("New person successfully added!")
Catch SQLexc As SqlException
Response.Write("Insert Failed. Error Details are: " & SQLexc.ToString())
End Try


code For stored procedure

Create Table Person

(
PersonID Int Identity,

PersonEmail Varchar(50),

PersonName Varchar(50),

PersonSex Char(1),

PersonDOB DateTime,

PersonImage Image,

PersonImageType Varchar(15)

)

Drop Proc sp_person_isp

Go

Create Proc sp_person_isp

@PersonEmail Varchar(50),

@PersonName Varchar(50),

@PersonSex Char(1),

@PersonDOB DateTime,

@PersonImage Image,

@PersonImageType Varchar(50)

As

Begin

Insert into Person

(PersonEmail, PersonName, PersonSex,

PersonDOB, PersonImage, PersonImageType)

Values

(@PersonEmail, @PersonName, @PersonSex,

@PersonDOB, @PersonImage, @PersonImageType)

End



To Retrieve the images from sqlserver

Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlCommand("Select * from Person", con)

Try
con.Open()
Dim dr as SqlDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Do While (dr.Read())
Response.ContentType = dr.Item("PersonImageType")
Response.BinaryWrite(dr.Item("PersonImage"))
Loop

con.Close()
Response.Write("Person info successfully retrieved!")
Catch SQLexc As SqlException
Response.Write("Read Failed : " & SQLexc.ToString())
End Try

Thank u

Baba