Retrieving Files From SQL Server 2005.

Question

I have stored an excel file within an SQL Server Table having the corresponding column data type as varbinary (MAX) through a VB .NET windows application form.

Can anyone help how can I retrieve and open the stored excel file for manipulation?

Answer 1

The following example reads an varbinary(MAX) field that contains an Excel 2010 file and writes it to disk in a temporary file and then launches the file.

  Private Sub ReadOpenExcel(ByVal intID As Integer)

    Dim con As SqlConnection = Nothing

    Try

      Dim blnWroteFile As Boolean = False

      con = New SqlConnection("Data Source=LDJLAPTOP;Initial Catalog=test;Integrated Security=True")
      con.Open()

      Dim strFileName = Path.GetFileNameWithoutExtension(Path.GetTempFileName) & ".xlsx"
      Dim cmdFile As New SqlCommand("SELECT [ExcelFile] FROM [dbo].[Files] WHERE ID = " & intID.ToString, con)

      Using reader As SqlDataReader = cmdFile.ExecuteReader(CommandBehavior.CloseConnection)

        If reader.HasRows Then
          reader.Read()
          Dim fsWrite As FileStream = New IO.FileStream(strFileName, IO.FileMode.Create)
          Dim bRead() As Byte = reader.Item("ExcelFile")

          fsWrite.Write(bRead, 0, bRead.Length)
          fsWrite.Close()

          blnWroteFile = True

        End If

      End Using

      If blnWroteFile Then
        Process.Start(strFileName)
      End If

    Catch ex As Exception

    Finally

      If con IsNot Nothing Then
        con.Close()
      End If

    End Try

  End Sub

 

 

---

www.sqlquerybuilder.com

Answer 2

Thank you very much! This had rectified the issue.