none
Compressing and decompressing files and images to and from an SQL Server Database table RRS feed

  • Question

  • I have an application that uses merge replication for mobile devices to sync to an SQL server.  Being that database sizes are limited in the SQL Express version, I was wondering if someone could post some sample code that would allow me to zip up images/photos and files to an SQL database to hopefully reduce the size of the mobile database and then be able to extract the image or file from the zip file so it can be put in a grid and selected to be opened for the user to view.    Any help would be appreciated.  I'm inserting an image of the Files table layout.

    Monday, October 28, 2019 8:46 PM

Answers

  • Look at

    https://docs.microsoft.com/en-us/dotnet/api/system.io.compression?view=netframework-4.8

    With code place images into a temp folder, use ZipFile.CreateFromDirectory to compress the images, delete the images in the temp folder. Add zip file to a field in the table.

    Use ZipFile.ExtractToDirectory to decompress the files.

    Look at the following for base code samples to insert binary files into a SQL-Server database table.

    https://code.msdn.microsoft.com/SQL-Server-insert-binary-0de8aef3?redir=0


    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

    • Marked as answer by mrbill65 Wednesday, November 13, 2019 8:28 PM
    Monday, October 28, 2019 9:36 PM
    Moderator
  • Hi mrbill65,

    Thank you for posting here.

    Based on your description, you want to zip up files and store in the database.

    First, you could try to install nugetpackage-> DotNetZip.

    Then, you could try the following code to compress file to zip file and store them in binary in the database.

    Code:

     private void button1_Click(object sender, EventArgs e)
            {
                string frompath = "D:\\1.xml";
                string fileName = Path.GetFileName(frompath);
                string zippath = "output.zip";
                using (ZipFile zip = new ZipFile())
                {
                    zip.AddFile(frompath);
                    zip.Save(zippath);
                }
                byte[] b = File.ReadAllBytes(zippath);
                string connstring = @"";
                SqlConnection connection = new SqlConnection(connstring);
                connection.Open();
                string sql = "insert into FileCompress(FileName,FilePath,FileData) values(@FileName,@FilePath,@FileData)";
                SqlCommand command = new SqlCommand(sql,connection);
                command.Parameters.AddWithValue("@FileName", SqlDbType.NVarChar).Value=frompath;
                command.Parameters.AddWithValue("@FilePath", SqlDbType.NVarChar).Value = fileName;
                command.Parameters.AddWithValue("@FileData", SqlDbType.VarBinary).Value = b;
                command.ExecuteNonQuery();
                connection.Close();
                File.Delete(zippath);
                MessageBox.Show("success");
    
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                string connstring = @"";
                SqlConnection connection = new SqlConnection(connstring);
                connection.Open();
                string sql = "select FileData from FileCompress";
                SqlCommand cmd = new SqlCommand(sql,connection);
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    byte[] test =(byte[]) reader[0];
                    File.WriteAllBytes("d:\\test.zip", test);
                }
                MessageBox.Show("success");
            }

    Hope this could help you.
    Best Regards,

    Jack


    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.

    • Marked as answer by mrbill65 Wednesday, November 13, 2019 8:28 PM
    Tuesday, October 29, 2019 9:22 AM
    Moderator

All replies

  • Look at

    https://docs.microsoft.com/en-us/dotnet/api/system.io.compression?view=netframework-4.8

    With code place images into a temp folder, use ZipFile.CreateFromDirectory to compress the images, delete the images in the temp folder. Add zip file to a field in the table.

    Use ZipFile.ExtractToDirectory to decompress the files.

    Look at the following for base code samples to insert binary files into a SQL-Server database table.

    https://code.msdn.microsoft.com/SQL-Server-insert-binary-0de8aef3?redir=0


    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

    • Marked as answer by mrbill65 Wednesday, November 13, 2019 8:28 PM
    Monday, October 28, 2019 9:36 PM
    Moderator
  • Hi mrbill65,

    Thank you for posting here.

    Based on your description, you want to zip up files and store in the database.

    First, you could try to install nugetpackage-> DotNetZip.

    Then, you could try the following code to compress file to zip file and store them in binary in the database.

    Code:

     private void button1_Click(object sender, EventArgs e)
            {
                string frompath = "D:\\1.xml";
                string fileName = Path.GetFileName(frompath);
                string zippath = "output.zip";
                using (ZipFile zip = new ZipFile())
                {
                    zip.AddFile(frompath);
                    zip.Save(zippath);
                }
                byte[] b = File.ReadAllBytes(zippath);
                string connstring = @"";
                SqlConnection connection = new SqlConnection(connstring);
                connection.Open();
                string sql = "insert into FileCompress(FileName,FilePath,FileData) values(@FileName,@FilePath,@FileData)";
                SqlCommand command = new SqlCommand(sql,connection);
                command.Parameters.AddWithValue("@FileName", SqlDbType.NVarChar).Value=frompath;
                command.Parameters.AddWithValue("@FilePath", SqlDbType.NVarChar).Value = fileName;
                command.Parameters.AddWithValue("@FileData", SqlDbType.VarBinary).Value = b;
                command.ExecuteNonQuery();
                connection.Close();
                File.Delete(zippath);
                MessageBox.Show("success");
    
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                string connstring = @"";
                SqlConnection connection = new SqlConnection(connstring);
                connection.Open();
                string sql = "select FileData from FileCompress";
                SqlCommand cmd = new SqlCommand(sql,connection);
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    byte[] test =(byte[]) reader[0];
                    File.WriteAllBytes("d:\\test.zip", test);
                }
                MessageBox.Show("success");
            }

    Hope this could help you.
    Best Regards,

    Jack


    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.

    • Marked as answer by mrbill65 Wednesday, November 13, 2019 8:28 PM
    Tuesday, October 29, 2019 9:22 AM
    Moderator
  • I've added code to compress and decompress.  My code to compress is working and I'm saving the file as a GZip.  However, I'm running into an issue when decompressing. My Code is below:

            Dim oTable As DataTable = Nothing
            Dim command As System.Data.OleDb.OleDbCommand
            Dim oCon As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(GetConnectionString)
            Dim buffer As Byte()
            Dim oFileStream As System.IO.FileStream
            Dim writer As BinaryWriter
            Dim bufferSize As Integer = 100
            ' The BLOB byte() buffer to be filled by GetBytes.
            Dim outByte(bufferSize - 1) As Byte
            Dim FinaloutByte() As Byte
            ' The bytes returned from GetBytes.
            Dim retval As Long
            ' The starting position in the BLOB output.
            Dim startIndex As Long = 0
            Try
                'get image data from database
                'oTable = eData.GetDataTable2(GetConnectionString, "Select FileName, FilePath, Linked, FileData FROM Files WHERE FileId = '" & FileId & "'")
                command = New System.Data.OleDb.OleDbCommand("Select FileName, FilePath, Linked, FileData FROM Files WHERE FileId = '" & FileId & "'", oCon)
                'convert image data from db field to byte array
                oCon.Open()
                Dim reader As OleDb.OleDbDataReader = command.ExecuteReader(CommandBehavior.SequentialAccess)
                'buffer = CType(oTable.Rows(0).Item("FileData"), Byte())
                If reader.HasRows AndAlso reader.Read() Then
                    Dim strFileName As String = reader.GetString(0)
                    Dim strFilePath As String
                    Try
                        strFilePath = reader.GetString(1)
                    Catch ex As Exception
                        strFilePath = TempFileDirectory & strFileName
                    End Try
                    Dim blnLinked As Boolean = reader.GetBoolean(2)
                    If Not blnLinked Then
                        If Not System.IO.Directory.Exists(TempFileDirectory) Then
                            System.IO.Directory.CreateDirectory(TempFileDirectory)
                        End If
                        Dim oFileInfo As New FileInfo(strFilePath & strFileName)
                        Dim fileToDecompress As FileInfo
                        fileToDecompress = oFileInfo
                        Dim newFilePath As String
                        Using originalFileStream As FileStream = fileToDecompress.OpenRead()
                            Dim currentFileName As String = fileToDecompress.FullName
                            newFilePath = currentFileName.Remove(currentFileName.Length - fileToDecompress.Extension.Length)
                            newFileName = Strings.Right(newFilePath, newFilePath.Length - InStrRev(newFilePath, "\"))
                            Using decompressedFileStream As FileStream = File.Create(newFileName)
                                Using decompressionStream As GZipStream = New GZipStream(originalFileStream, CompressionMode.Decompress)
                                    decompressionStream.CopyTo(decompressedFileStream)
                                 End Using
                            End Using
                        End Using
                        oFileStream = New System.IO.FileStream(TempFileDirectory & NewFileName, IO.FileMode.OpenOrCreate, FileAccess.Write)
                        writer = New BinaryWriter(oFileStream)

                        ' Reset the starting byte for a new BLOB.
                        startIndex = 0
                        ' Read bytes into outByte() and retain the number of bytes returned.
                        retval = reader.GetBytes(3, startIndex, outByte, 0, bufferSize)
                        ' Continue while there are bytes beyond the size of the buffer.
                        Do While retval = bufferSize
                            writer.Write(outByte)
       
                            ' Reposition start index to end of the last buffer and fill buffer.
                            startIndex += bufferSize
                            retval = reader.GetBytes(3, startIndex, outByte, 0, bufferSize)
                            If retval < 100 AndAlso retval > 0 Then
                                ReDim FinaloutByte(retval - 1)
                                retval = reader.GetBytes(3, startIndex, FinaloutByte, 0, retval)
                            End If
                        Loop
                        ' Write the remaining buffer.
                        If retval > 0 Then
                            writer.Write(FinaloutByte) ', 0, retval)
                        End If
                        writer.Flush()
                        ' Close the output file.
                        writer.Close()
                        oFileStream.Close()

                          ShellFile(TempFileDirectory & newFileName, bAsync:=Not PreviewOnly)

    When I get to the shell file I get an error opening the .pdf file I had decompressed.  I've attached it's pic.  

    I also noticed the .pdf file's size that was decompressed looks like it has an issue between it's size and size on disk as well in the pic below:

    Any help would be appreciated.

    Friday, November 8, 2019 9:30 PM
  • Hi Jack,

    What would the code look like to unzip the file from the database?

    Friday, November 8, 2019 10:22 PM
  • Unless I'm missing something, it's above starting with the lines:

    Dim fileToDecompress As FileInfo
                        fileToDecompress = oFileInfo
                        Dim newFilePath As String
                        Using originalFileStream As FileStream = fileToDecompress.OpenRead()
                            Dim currentFileName As String = fileToDecompress.FullName
                            newFilePath = currentFileName.Remove(currentFileName.Length - fileToDecompress.Extension.Length)
                            newFileName = Strings.Right(newFilePath, newFilePath.Length - InStrRev(newFilePath, "\"))
                            Using decompressedFileStream As FileStream = File.Create(newFileName)
                                Using decompressionStream As GZipStream = New GZipStream(originalFileStream, CompressionMode.Decompress)
                                    decompressionStream.CopyTo(decompressedFileStream)
                                 End Using
                            End Using
                        End Using
                        oFileStream = New System.IO.FileStream(TempFileDirectory & NewFileName, IO.FileMode.OpenOrCreate, FileAccess.Write)
                        writer = New BinaryWriter(oFileStream)

    Monday, November 11, 2019 2:04 PM
  • Hi mrbill65,

    Thanks for the feedback.

    It seems that you want to solve the problem by using VB.net. Therefore, I suggest that you could ask your question in Visual Basic Forum.

    The Visual C# forum discusses and asks questions about the C# programming language, IDE, libraries, samples, and tools.

    Best Regards,

    Jack


    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, November 12, 2019 2:27 AM
    Moderator