Vb.net – How to store and retrieve images in sql server database with vb.net using picturebox

vb.net

Can someone please tell me the easiest technique to store and retrieve image in a SQL Server database with vb.net.

A very important consideration requirement:

Well, the application in vb.net seems to have a database which can accessed by the LAN computers so the SQL Server 2005 database allows remote computers to share its database once the other computer application requests database from the server.

So, every other computer has to store and retrieve images in SQL Server 2005. For example, comp1 has to store images taken into the server and retrieve it like tableadapter.

I am using bindings and tableadapters to store the details in database and retrieving information from db. Please if you have code or anything which is working for me, please post it here…

Please share with me how to store image and retrieve it SQL Server through vb.net provided there's LAN computers who are also sharing the SQL Server database.

Please, if anyone has a knowledge about this stuff, I would really appreciate it.

With all my heart, I request you consider my issue, please.

Best Regards,

Hosseinel

Best Answer

Let's have a table containing images (instead of max you can use desired size):

CREATE TABLE dbo.STORED_IMAGES
     (
     ID int NOT NULL  IDENTITY (1, 1),
     Img varbinary(MAX) NULL
     )

Storing image:

Dim CMD As New SqlClient.SqlCommand("insert into STORED_IMAGES(Img) values(@Img)",cn)
CMD.Parameters.Add("@Img", SqlDbType.VarBinary, Integer.MaxValue).Value = ReadFile(Path)

Function for reading file:

Private Function ReadFile(sPath As String) As Byte()
        Dim data As Byte() = Nothing
        Dim fInfo As New IO.FileInfo(sPath)
        Dim numBytes As Long = fInfo.Length
        Using fStream As New IO.FileStream(sPath, IO.FileMode.Open, IO.FileAccess.Read)
            Dim br As New IO.BinaryReader(fStream)
            data = br.ReadBytes(CInt(numBytes))
        End Using
        Return data
    End Function

Getting file from DB:

Dim fcmd As New SqlCommand("select Img from STORED_IMAGES where ID=@ID", cn)
fcmd.Parameters.Add("@ID", SqlDbType.Int).Value = 3 'e.g.
Dim bytes As Byte() = fcmd.ExecuteScalar

I suppose that with tableadapters your datatable will contain column with type Byte().

Related Solutions

.net – how to import MS access database table in sql server 2005 in vb.net code

1. put your MS Access database in "App_Data" file in your vb.net . now, suppose that your MS Access database table name is 'customer' and you have id ,name and address.

2. in your pageName.aspx for example:

TextBox: txtid

Button: Find

AccessDataSource: AccessDataSource1

GridView: GridView1

a) in AccessDataSource1 press the arrow > then press "Configure Data Source..." . press on Browse ,select your MS Access database from the "App_Data" file and click Next. Select "Specify a custom SQL statment or stored procedure" and click Next. Put this in SQL statment box SELECT ID, Name, address FROM customer WHERE (ID = ?) and click next. Select 'Control' in 'Parameter Source:', txtid in 'ControlID' and click Next. Press Finish.

b) in GridView1 press the arrow > then select 'AccessDataSource1' in 'Choose Data Source:'.

b)double click on find to put the vb code:

Imports System.Data
Partial Class pageName
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        AccessDataSource1.DataSourceMode = SqlDataSourceMode.DataReader
        Dim reader As IDataReader = CType(AccessDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader)
        Dim flag As Integer
        flag = 0
        If reader.Read Then
            txtid.Text = reader("id")
            txtname.Text = reader("name")
            txtaddress.Text = reader("address")
                            flag = 1
        End If
        If flag = 0 Then
            MsgBox("invalid customer id ... !")
        End If
    End Sub

      End Class

Vb.net – display/retrieve image from sql database in vb.net

You need both a command object and a data reader. However, you are putting them in the wrong page.

If you check out the properties of the Image control you will see that it doesn't have any Image property, so you can't load an image and put in the control. The reason for that is that you can't send both the page and the image in the same response, instead the browser has to request the image separately when the page loads.

To get the image from the database and show in the web page you need a separate proxy page that you can use to get only the image data from the database. In the ImageUrl property of the Image control you would put something like "GetImage.ashx". Then you make an HTTP handler with that name that will just get the image data from the database and write to the response stream.

Example:

Private Sub HandleRequest(context as HttpContext)
  Dim SqlCnn As SqlConnection = Nothing, sql As String
  Dim emp_id As Integer
  emp_id = Int32.Parse(context.Request.QueryString("id"))
  ConnectDB(SqlCnn)
  Try
    sql = "SELECT image FROM employees (NOLOCK) WHERE ID =" & emp_id
    sqlcmd = New SqlCommand(sqlstr, SqlCnn)
    Dim imageData As Byte() = DirectCast(sqlcmd.ExecuteScalar(), Byte())
    context.Response.ContentType = "image/jpeg"
    context.Response.BinaryWrite(imageData)
  Catch ex As Exception
    ReportError(ex)
  Finally
    CloseDB(SqlCnn)
  End Try
End Sub

Best Answer

Related Solutions

.net – how to import MS access database table in sql server 2005 in vb.net code

Vb.net – display/retrieve image from sql database in vb.net

Related Topic