none
Save image file text in SQL Varbinary column RRS feed

  • Question

  • Hi Experts,

    I am trying to save all the file contents in SQL Server. Facing issue only when image file with below text is not been able to save in Varbinary column. Any idea how can i store this?

    ���� JFIF  H H  �� C   



                 �� C

                                                     �� ]� ��              �� _ 
    
      !1"2AQ�RSaq���#34BTs����5Ubru��$%Ct�����678DV��c����&���Ee���            �� G     !12AQRq��"3ar���4B���DSb#%5C�$E�cT������   ? ��5��]�q%Xb�2a"
    ���n�

    I am using following code to save files to SQL. When file is directly accessible then using OPENROWSET-

    If bFilePath Then
                    sSql = "INSERT INTO [dbo].[ResultsContent] ([FileContent],[Extension],[MD5])"
                    sSql &= " SELECT BULKCOLUMN,'" & sExtension & "','" & sMD5 & "'"
                    sSql &= " FROM OPENROWSET(BULK '" + sFileContent + "', SINGLE_BLOB) AS T"
                Else
                    sSql = "INSERT INTO [dbo].[ResultsContent] ([Extension], [MD5], [FileContent]) SELECT '" & sExtension.Replace("'", "''") & "','" & sMD5 & "', CAST('" & sFileContent.Replace("'", "''") & "' AS VARBINARY(MAX))"
                End If

    But if file is getting read from Zip file (drive:\downloads\modernuicharts\wiki\downloadWiki.zip\\docs\Home_Screenshot_Silverlight_preview.jpg) I am extracting content of Image file. But error i got is as below-

    System.Data.OleDb.OleDbException: Incorrect syntax near '����'." & vbCrLf & "Unclosed quotation mark after the character string '����'." & vbCrLf & "  

    Is there a way that i can store this as plain text in Varbinary column?


    Abhijeet Khopade

    Thursday, December 12, 2019 1:34 PM

Answers

  • Here is an example from a C# code sample I wrote, below is the code that will insert one or more files selected from an OpenDialog.

    Public Class Form1
        Private Sub SelectFilesButton_Click(sender As Object, e As EventArgs) _
            Handles SelectFilesButton.Click
    
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                Dim operations As New DataOperations
                operations.InsertFiles(OpenFileDialog1.FileNames.ToList())
            End If
    
        End Sub
    End Class

    Simple method in a class used by the code above.

    Imports System.Data.SqlClient
    Imports System.IO
    
    Public Class DataOperations
        Public Function InsertFiles(ByVal files As List(Of String)) As Boolean
    
            Const statement As String =
                      "INSERT INTO Table1 (FileContents,FileName)" &
                      " VALUES (@FileContents,@FileName);" &
                      "SELECT CAST(scope_identity() AS int);"
    
    
            Using cn = New SqlConnection() With {.ConnectionString = "TODO"}
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = statement}
                    cn.Open()
    
                    cmd.Parameters.Add("@FileContents", SqlDbType.VarBinary)
                    cmd.Parameters.Add("@FileName", SqlDbType.VarChar)
    
                    For Each fileName In files
                        Dim fileByes = GetFileBytes(fileName)
                        cmd.Parameters("@FileContents").Size = fileByes.Length
                        cmd.Parameters("@FileContents").Value = fileByes
                        cmd.Parameters("@FileName").Value = Path.GetFileName(fileName)
                    Next
    
                End Using
    
            End Using
    
            Return True
        End Function
        Private Function GetFileBytes(ByVal fileName As String) As Byte()
            Using stream = New FileStream(fileName, FileMode.Open, FileAccess.Read)
                Using reader = New BinaryReader(stream)
                    Return reader.ReadBytes(CInt(stream.Length))
                End Using
            End Using
        End Function
    End Class

    Table structure

    CREATE TABLE [dbo].[EventAttachments](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[EventId] [int] NULL,
    	[FileContent] [varbinary](max) NULL,
    	[FileExtention] [nchar](10) NULL,
    	[FileBaseName] [nvarchar](max) NULL,
     CONSTRAINT [PK_EventAttachments] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Note: This is strange, System.Data.OleDb.OleDbException as for SQL-Server you should be using the namespace System.Data.SqlClient. If your database is not SQL-Server but MS-Access here is a start code sample.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, December 12, 2019 2:27 PM
    Moderator
  • First off the code works in regards to the table schema I provided. In regards to searching in that column, no, that is not the purpose for this column type.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, December 12, 2019 3:26 PM
    Moderator

All replies

  • Here is an example from a C# code sample I wrote, below is the code that will insert one or more files selected from an OpenDialog.

    Public Class Form1
        Private Sub SelectFilesButton_Click(sender As Object, e As EventArgs) _
            Handles SelectFilesButton.Click
    
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                Dim operations As New DataOperations
                operations.InsertFiles(OpenFileDialog1.FileNames.ToList())
            End If
    
        End Sub
    End Class

    Simple method in a class used by the code above.

    Imports System.Data.SqlClient
    Imports System.IO
    
    Public Class DataOperations
        Public Function InsertFiles(ByVal files As List(Of String)) As Boolean
    
            Const statement As String =
                      "INSERT INTO Table1 (FileContents,FileName)" &
                      " VALUES (@FileContents,@FileName);" &
                      "SELECT CAST(scope_identity() AS int);"
    
    
            Using cn = New SqlConnection() With {.ConnectionString = "TODO"}
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = statement}
                    cn.Open()
    
                    cmd.Parameters.Add("@FileContents", SqlDbType.VarBinary)
                    cmd.Parameters.Add("@FileName", SqlDbType.VarChar)
    
                    For Each fileName In files
                        Dim fileByes = GetFileBytes(fileName)
                        cmd.Parameters("@FileContents").Size = fileByes.Length
                        cmd.Parameters("@FileContents").Value = fileByes
                        cmd.Parameters("@FileName").Value = Path.GetFileName(fileName)
                    Next
    
                End Using
    
            End Using
    
            Return True
        End Function
        Private Function GetFileBytes(ByVal fileName As String) As Byte()
            Using stream = New FileStream(fileName, FileMode.Open, FileAccess.Read)
                Using reader = New BinaryReader(stream)
                    Return reader.ReadBytes(CInt(stream.Length))
                End Using
            End Using
        End Function
    End Class

    Table structure

    CREATE TABLE [dbo].[EventAttachments](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[EventId] [int] NULL,
    	[FileContent] [varbinary](max) NULL,
    	[FileExtention] [nchar](10) NULL,
    	[FileBaseName] [nvarchar](max) NULL,
     CONSTRAINT [PK_EventAttachments] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Note: This is strange, System.Data.OleDb.OleDbException as for SQL-Server you should be using the namespace System.Data.SqlClient. If your database is not SQL-Server but MS-Access here is a start code sample.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, December 12, 2019 2:27 PM
    Moderator
  • Hi Karen,

    Thanks for this code and explanation. I have exact same code (Not implemented but commented) but my doubt iswill it take Byte[] to Varbinary column? Will the store content searchable for SQL Query for this particular file?

    I will try to implement the code you have provided and will get back to you.

    Thanks again.


    Abhijeet Khopade

    Thursday, December 12, 2019 3:12 PM
  • First off the code works in regards to the table schema I provided. In regards to searching in that column, no, that is not the purpose for this column type.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, December 12, 2019 3:26 PM
    Moderator
  • Hi Abhijeet Khopade, 

    Thank you for posting here.

    For your question, you want to save image file in SQL varbinary column.

    I make a test on my side and you can refer to the following code.

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using openFileDialog As OpenFileDialog = New OpenFileDialog
                openFileDialog.InitialDirectory = "D:\"
                openFileDialog.Filter = "Image files (*.jpg, *.jpeg, *.jpe, *.jfif, *.png) | *.jpg; *.jpeg; *.jpe; *.jfif; *.png"
                openFileDialog.FilterIndex = 2
                openFileDialog.RestoreDirectory = True
    
                If openFileDialog.ShowDialog = DialogResult.OK Then
                    Dim filePath As String = openFileDialog.FileName
                    Dim bt = imageToByteArray(Image.FromFile(filePath))
                    Dim connString = "your connect string"
    
                    Using Conn As SqlConnection = New SqlConnection(connString)
                        Conn.Open()
                        Dim cmdText = "INSERT INTO TableImage(Name,imagebinary) VALUES (@Name,@imagebinary)"
                        Dim cmd As SqlCommand = New SqlCommand(cmdText, Conn)
                        Dim param As SqlParameter = cmd.Parameters.Add("@imagebinary", SqlDbType.VarBinary)
                        param.Value = bt
                        Dim param1 As SqlParameter = cmd.Parameters.Add("@Name", SqlDbType.NVarChar)
                        param1.Value = openFileDialog.SafeFileName
                        cmd.ExecuteNonQuery()
                    End Using
                End If
            End Using
        End Sub
    
        Public Function imageToByteArray(ByVal imageIn As System.Drawing.Image) As Byte()
            Dim ms = New MemoryStream
            imageIn.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
            Return ms.ToArray
        End Function
    

    My database.

    Result:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 13, 2019 5:25 AM
  • Probably your script will work too if the extracted image is in a file and you specify the filename instead of contents. Something like this:

    … OPENROWSET(BULK ‘C:\MyFile.jpg’, SINGLE_BLOB) …

    This is convenient if your extraction method uses intermediate files which are also accessible by SQL server.

     


    • Edited by Viorel_MVP Friday, December 13, 2019 6:57 AM
    Friday, December 13, 2019 6:54 AM
  • Hi Abhijeet Khopade,

    Did you solve your problem? If your question has been answered then please click the "Mark as Answer" Link at the bottom of the correct post(s), so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Xingyu Zhao 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 24, 2019 7:58 AM