Click here to Skip to main content
15,907,497 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello!
Good Day to all,

I have a VB.Net windows form project with MS SQL Server as back end database. I am storing images to database from the application. This is required by client, for example when a user posts a Payment Voucher then he needs to store some manual documents as supporting proofs of the transaction. Everything is going fine. Images are stored and retrieved perfectly. But due to this the size of the database is increasing much more than expected.
Now i want to know how can i do the same task occupying the database storage as minimum as possible.

If anyone can help in this regard, I will be very grateful.

Thanks and regards.

What I have tried:

Firstly i insert a picture in PictureBox control by windows browsing using OpenFileDialog.
Public Sub Select_Picture()
     Try
         OpenFile.ShowDialog()
         Dim PicPath As String = OpenFile.FileName
         MyPicBox.ImageLocation = PicPath
     Catch ex As Exception
         MessageBox.Show(ex.Message)
     End Try
End Sub


Then to store the image i use the following code.

  Public Sub SaveImage_()
        Dim SQL_Connection As New SqlConnection
        Try
            Dim MyImage As Image
            MyImage = MyPicBox.Image
            SQL_Connection.ConnectionString = MyConnectionString
            SQL_Connection.Open()
            Dim MyQuery As String = "insert into tbl_Setup_Companies Values(@Image_)"
            Dim MyCommand As SqlCommand
            MyCommand = New SqlCommand(MyQuery, SQL_Connection)
            MyCommand.Parameters.Add(New SqlParameter("@Image_", SqlDbType.Image))
            MyCommand.Parameters("@Image_").Value = ConvertImage(MyImage)
            MyCommand.ExecuteNonQuery()
            SQL_Connection.Close()
Catch ex As Exception
  MessageBox.Show(ex.Message)
End Try
End Sub


To convert the Image into Byte, I wrote a following function named "ConvertImate"

Public Function ConvertImage(ByVal myImage As Image) As Byte()
        Dim mstream As New MemoryStream
        myImage.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
        Dim myBytes(mstream.Length - 1) As Byte
        mstream.Position = 0
        mstream.Read(myBytes, 0, mstream.Length)
        Return myBytes
End Function
Posted
Comments
0x01AA 30-Dec-23 7:38am    
That would mayber help: FILESTREAM compatibility - SQL Server | Microsoft Learn[^]

Old, but for a first idea ok: How Do I: Use SQL File Stream[^]
vblover Programmer 30-Dec-23 14:27pm    
Public Sub Select_Picture()
       If  OpenFile.ShowDialog() = Cancel Then exit Sub
       MyPicBox.ImageLocation = OpenFile.FileName
End Sub


or

Public Sub Select_Picture()
      Dim OFD AS New OpenFileDialog With {.Filter = "Image Files(*.jpg;*.jpeg;*.bmp;*.png)|*.jpg;*.jpeg;*.bmp;*.png"}
       with OFD 
       If  .ShowDialog() = Cancel Then exit Sub
       MyPicBox.ImageLocation = .FileName
       End With
End Sub
vblover Programmer 30-Dec-23 14:36pm    
I don't know this is good way for your db or not, this is for local db:

Add File Bytes to database Algorithm Flowchart....

Storing images directly in a database can lead to increased database size, as you've noticed. To minimize database storage, a common approach is to store the images as files on the file system and store references (relative path) in the database. This way, only the paths are stored in the database, reducing the overall database size.
 
Share this answer
 
Could you not just give the image a unique name, save it to the server as a file and then store only a reference to the file in the database ?
 
Share this answer
 
Comments
Maciej Los 30-Dec-23 12:03pm    
Can a question be an answer?
CHill60 3-Jan-24 8:25am    
I think a rhetorical question can :-)
Maciej Los 3-Jan-24 13:47pm    
OK.
M-Badger 6-Jan-24 6:24am    
I had assumed that the point was that I could have added that as a comment to the OP rather than as an answer, which wasn't unreasonable.
But thank you :-)
Maciej Los 3-Jan-24 13:47pm    
Then... 5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900