Retrieve VARBINARY from SQL Server table using FileStream to save file in VB.Net

Question

I have stored data in a SQL Server table and I need to retrieve it through FileStream to save it back as a png picture as it was before.

I can't understand or find a way to do this, I read many suggestions and examples but none worked. Any ideas?

I just need an easy working solution, all the understanding can come later. Also, is it possible to do it with OleDb instead of T-SQL?

What I did to store the data is:

    Dim connection As New OleDbConnection(MyConnString)
    Dim command As New OleDbCommand("UPDATE Prodotti SET Immagine = ? WHERE Oggetto = 'Monitor'", connection)
    Using picture As Image = Image.FromFile("C:\Users\user\Desktop\Nuova cartella\galar 1 mainland.png")
        Using stream As New MemoryStream
            picture.Save(stream, Imaging.ImageFormat.Png)
            command.Parameters.Add("@Picture", OleDbType.VarBinary).Value = stream.GetBuffer()
            connection.Open()
            command.ExecuteNonQuery()
            connection.Close()
        End Using
    End Using

What I got is in fact the Cell field as a long byte value.

Answer 1

You don't even need an Image and MemoryStream here, because you are just getting it straight from a file. Just use a FileStream directly.

You should also pass -1 as the length (ie a max value), otherwise you could end up with a different query plan for every execution.

Using
  connection As New SqlConnection(MyConnString),
  command As New SqlCommand("UPDATE Prodotti SET Immagine = @Picture WHERE Oggetto = 'Monitor'", connection),
  stream As FileStream = File.Open("C:\Users\user\Desktop\Nuova cartella\galar 1 mainland.png")
    command.Parameters.Add("@Picture", SqlDbType.VarBinary, -1).Value = stream
    connection.Open()
    command.ExecuteNonQuery()
End Using

Use SqlCommand etc rather than OleDb, as it is designed for SQL Server. If you need to support multiple DBMSs then use the DbConnection base type as an abstraction.

To do the same thing in reverse, use a reader. Since you only have one value, you can just do a single Read and then use GetStream

Using
  connection As New SqlConnection(MyConnString),
  command As New SqlCommand("SELECT Immagine FROM Prodotti WHERE Oggetto = 'Monitor'", connection),
  filestream As FileStream = File.OpenWrite("C:\Users\user\Desktop\Nuova cartella\galar 1 mainland.png")
    connection.Open()
    Using reader As SqlDataReader = command.ExecuteReader()
        If Not reader.Read() Then Return

        Using stream As Stream = reader.GetStream(0)
            stream.CopyTo(fileStream)
        End Using
    End Using
End Using

Answer 2

Actually, I don't see much of a reason to use a file stream here.

So, to insert the file, then this code can work: (and I 100% agree that you should try and consider to move from the oleDB provider over to the SQL provider. However, existing code and existing systems are often not our choice as a developer).

So, to save the file into database, then this code:

Private Sub cmdSave_Click(sender As Object, e As EventArgs) Handles cmdSave.Click

    Dim sFile As String =
        "C:\Users\user\Desktop\Nuova cartella\galar 1 mainland.png"

    Using conn = New OleDbConnection(My.Settings.TEST4)

        Dim strSQL As String =
            "UPDATE Prodotti Set Immagine = @Image 
            WHERE Oggetto = 'Monitor'"

        Using cmdSQL = New OleDbCommand(strSQL, conn)

            Dim RawFile As Byte() = File.ReadAllBytes(sFile)

            cmdSQL.Parameters.Add("@Image", OleDbType.VarBinary).Value = RawFile

            conn.Open()
            cmdSQL.ExecuteNonQuery()

        End Using

    End Using

End Sub

And to pull the file, and save to disk from the database, then this code should suffice:

Sub WriteFile()
    Dim sFile As String =
        "C:\Users\user\Desktop\Nuova cartella\galar 1 mainland2.png"

    Using conn = New OleDbConnection(My.Settings.TEST4)

        Dim strSQL As String =
            "SELECT Immagine FROM Prodotti 
            WHERE Oggetto = 'Monitor'"

        Using cmdSQL = New OleDbCommand(strSQL, conn)

            conn.Open()
            Dim RawFile As Byte() = cmdSQL.ExecuteScalar
            File.WriteAllBytes(sFile, RawFile)

        End Using

    End Using

End Sub

Keep in mind that if you overwriting a existing file, then the file size does not reduce, and thus you might consider deleting the file before you WriteAllBytes out to such a file, else it will continue to show the old previous file size.

The above code is "file type" neutral, and it thus don't matter if you writing out a PDF file, or some image file