Answered by:
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?
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