locked
Retrieving Files From SQL Server 2005. RRS feed

  • 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?

    Saturday, August 7, 2010 2:24 PM

Answers

  • 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
    • Proposed as answer by WaywardMage Sunday, August 8, 2010 6:04 AM
    • Marked as answer by A MADI Sunday, August 8, 2010 6:08 AM
    Sunday, August 8, 2010 2:37 AM

All replies

  • 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
    • Proposed as answer by WaywardMage Sunday, August 8, 2010 6:04 AM
    • Marked as answer by A MADI Sunday, August 8, 2010 6:08 AM
    Sunday, August 8, 2010 2:37 AM
  • Thank you very much! This had rectified the issue.
    Sunday, August 8, 2010 6:10 AM