none
VB.NET 2015: Open a filestream stores in a SQL Server 2008 r2 Database RRS feed

  • Question

  • Hi. Firstrly thank's for your time.

    I created a method (on VB.NET 2015) to download from the database (SQL Server 2008 r2) a file (stored like binary file) and save it on a location of the hard disk. This method work correctly, but i would like open the file directly. 
    Currently, when the user clickes on a datagridview,  the program solicites to the user a location to save the file, i would like that when the user click on the datagridview, the file  opens directly.
    I tried some alternatives but I could not make it work.
    I hope tha you can help me.
    Regards.

    My Code:

    		    If SaveFileDialog1.FileName <> Nothing Then
                            LocationToSave= SaveFileDialog1.FileName
                        End If
    
                        Dim id As Integer = dg_files.SelectedRows(0).Cells("col_ArchID").Value
                        Dim conexion As New CConexion
                        Dim datos As New CDatos
                        datos.ID_file = id
                        Dim tb_file = conexion.SearchFileOnBase(datos).Copy
                        If tb_file.Rows.Count = 1 Then
                            Dim fila As DataRow = tb_file.Rows(0)
                            Dim MyData() As Byte
                            MyData = fila.Item("datos")
                            Dim K As Long
                            K = UBound(MyData)
                            Dim MyFileStreams New FileStream(LocationToSave, FileMode.OpenOrCreate, FileAccess.Write)
    
                            MyFileStream=.Write(MyData, 0, K)
                            MyFileStream=.Close()
                            MyFileStream= Nothing
                        End If

    Friday, September 29, 2017 12:09 PM

Answers

  • Hello,

    This was done from my MSDN code sample I did, modified to suit your question. In short you pass in the primary key for the record that has the image to save to disk and open in the default app for images on the machine the app is running on using Process.Start (which you could easily override and select another program to open the image).

    The image is read via a Stored Procedure e.g. (of course you could forego the SP and do it in code)

    CREATE PROC [dbo].[ReadImageWithFileName] @imgId INT
    AS
        SELECT  ImageData ,
                FileName
        FROM    ImageData
        WHERE   ImageID = @imgId; 
    
    
    GO

    In the method below, the connection string is not shown, it' a property of the class (DatabaseImageOperations.vb) the method is in

    A connection and command object are used along with a DataTable (could also simple use a SqlDataReader too) to get the image by it's primary key. Success is an Enum used rather than a boolean (members are Okay for all worked and OhSnap for something went wrong) the class also has a property ErrorMessage to allow the caller to get the error message if OhSnap is returned

    Public Function ImageToFile(ByVal Identifier As Integer) As Success
        Dim dtResults As New DataTable
        Dim SuccessType As Success
    
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With
                {
                    .Connection = cn,
                    .CommandText = "ReadImageWithFileName",
                    .CommandType = CommandType.StoredProcedure
                }
    
                cmd.Parameters.Add("@imgId", SqlDbType.Int).Value = Identifier
    
                Try
    
                    cn.Open()
                    dtResults.Load(cmd.ExecuteReader)
    
    
                    If dtResults.Rows.Count = 1 Then
                        Dim ms As New MemoryStream(CType(dtResults.Rows(0)("ImageData"), Byte()))
    
                        Dim inBoundImage = Image.FromStream(ms)
                        Dim fileName As String = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dtResults.Rows(0).Field(Of String)("FileName"))
                        inBoundImage.Save(fileName)
                        Process.Start(fileName)
                        SuccessType = Success.Okay
                    Else
                        HasError = True
                        ErrorMessage = $"{Identifier} not located"
                        SuccessType = Success.OhSnap
                    End If
                Catch ex As Exception
                    HasError = True
                    ErrorMessage = ex.Message
                    SuccessType = Success.OhSnap
                End Try
            End Using
        End Using
    
        Return SuccessType
    
    End Function
    

    Table structure

    CREATE TABLE [dbo].[ImageData](
    	[ImageID] [INT] IDENTITY(1,1) NOT NULL,
    	[ImageData] [IMAGE] NULL,
    	[Description] [NVARCHAR](MAX) NULL,
    	[FileName] [NVARCHAR](MAX) NULL,
     CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED 
    (
    	[ImageID] 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]

    From my MSDN code sample (which did not have the code above)


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by CM16 Friday, September 29, 2017 1:54 PM
    Friday, September 29, 2017 1:37 PM
    Moderator

All replies

  • Hello,

    This was done from my MSDN code sample I did, modified to suit your question. In short you pass in the primary key for the record that has the image to save to disk and open in the default app for images on the machine the app is running on using Process.Start (which you could easily override and select another program to open the image).

    The image is read via a Stored Procedure e.g. (of course you could forego the SP and do it in code)

    CREATE PROC [dbo].[ReadImageWithFileName] @imgId INT
    AS
        SELECT  ImageData ,
                FileName
        FROM    ImageData
        WHERE   ImageID = @imgId; 
    
    
    GO

    In the method below, the connection string is not shown, it' a property of the class (DatabaseImageOperations.vb) the method is in

    A connection and command object are used along with a DataTable (could also simple use a SqlDataReader too) to get the image by it's primary key. Success is an Enum used rather than a boolean (members are Okay for all worked and OhSnap for something went wrong) the class also has a property ErrorMessage to allow the caller to get the error message if OhSnap is returned

    Public Function ImageToFile(ByVal Identifier As Integer) As Success
        Dim dtResults As New DataTable
        Dim SuccessType As Success
    
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With
                {
                    .Connection = cn,
                    .CommandText = "ReadImageWithFileName",
                    .CommandType = CommandType.StoredProcedure
                }
    
                cmd.Parameters.Add("@imgId", SqlDbType.Int).Value = Identifier
    
                Try
    
                    cn.Open()
                    dtResults.Load(cmd.ExecuteReader)
    
    
                    If dtResults.Rows.Count = 1 Then
                        Dim ms As New MemoryStream(CType(dtResults.Rows(0)("ImageData"), Byte()))
    
                        Dim inBoundImage = Image.FromStream(ms)
                        Dim fileName As String = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dtResults.Rows(0).Field(Of String)("FileName"))
                        inBoundImage.Save(fileName)
                        Process.Start(fileName)
                        SuccessType = Success.Okay
                    Else
                        HasError = True
                        ErrorMessage = $"{Identifier} not located"
                        SuccessType = Success.OhSnap
                    End If
                Catch ex As Exception
                    HasError = True
                    ErrorMessage = ex.Message
                    SuccessType = Success.OhSnap
                End Try
            End Using
        End Using
    
        Return SuccessType
    
    End Function
    

    Table structure

    CREATE TABLE [dbo].[ImageData](
    	[ImageID] [INT] IDENTITY(1,1) NOT NULL,
    	[ImageData] [IMAGE] NULL,
    	[Description] [NVARCHAR](MAX) NULL,
    	[FileName] [NVARCHAR](MAX) NULL,
     CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED 
    (
    	[ImageID] 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]

    From my MSDN code sample (which did not have the code above)


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by CM16 Friday, September 29, 2017 1:54 PM
    Friday, September 29, 2017 1:37 PM
    Moderator
  • Amazing karen, exactly that i wanted. Thank you so mutch.

    Regards.

    Friday, September 29, 2017 1:54 PM