Solved

MySql blob to File - vb.net

Posted on 2016-08-22
8
43 Views
Last Modified: 2016-10-17
How do I retrieve a blob from mysql and save it to a file using vb.net
Blob can either be a jpg or pdf.
0
Comment
Question by:WelsfordAlan
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:it_saige
ID: 41766175
Assuming that you have at least the filename and file (as a binary blob), it really is just as simple as:
Imports System.IO

Sub SaveFiles(Optional ByVal path As String = "")
	Dim cmd As New MySqlCommand
	Dim reader As New MySqlDataReader
	If String.IsNullOrEmpty(path) Then path = Path.GetDirectoryName(Application.ExecutablePath)
	Try
		' Making the assumption that you already have your connection object configured
		If conn.State <> ConnectionState.Open Then conn.Open()
		cmd.Connection = conn
		' Name is filename and data is filedata in binary/blob form.
		cmd.CommandText = "SELECT name, data from files"
		reader = cmd.ExecuteReader
		While reader.Read()
			Dim data As Byte() = DirectCast(reader.GetValue("data"), Byte())
			Using stream As New FileStream(Path.Combine(path, reader.GetString("name")), FileMode.Create, FileAccess.ReadWrite)
				Using writer As New BinaryWriter(stream)
					writer.Write(data)
					writer.Close()
				End Using
			End Using
		End While
		reader.Close()
	Catch ex As Exception
		' Log your exceptions here
	Finally
		If conn IsNot Nothing AndAlso conn.State = ConnectionState.Open Then conn.Close()
		If cmd IsNot Nothing Then 
			cmd.Dispose()
			cmd = Nothing
		End If
	End Try
End Sub

Open in new window


-saige-
0
 

Author Comment

by:WelsfordAlan
ID: 41770885
Hi Saige

Thank you for the reply, below is the code to test if it works, I am getting an error on the line:
Dim data As Byte() = DirectCast(myReader.GetValue("DocBin"), Byte())
. The error is
Conversion from string "DocBin" to type 'Integer' is not valid.
     
I would be most grateful if you could help me solve this problem

Thanks in advance
Alan Welsford


Private Sub ExpToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExpToolStripMenuItem.Click
        Dim cmd As New MySqlCommand
        Dim myConnection As MySqlConnection
        Dim pathx = "c:\temp\test.jpg"
        Dim DocName As String
        Dim sql As String
        DocName = MDIParent1.Organisation + "-CompanyLogo"
        Sql = "Select docbin from DocEmbed where DocName='" + DocName + "'"
        myConnection = New MySqlConnection(MDIParent1.MyDbConn)
        Dim myReader As MySqlDataReader
        Try
            If myConnection.State <> ConnectionState.Open Then myConnection.Open()
            cmd.Connection = myConnection
            cmd.CommandText = sql
            myReader = cmd.ExecuteReader
            While myReader.Read()
                Dim data As Byte() = DirectCast(myReader.GetValue("DocBin"), Byte())
                Using stream As New FileStream(pathx, FileMode.Create, FileAccess.ReadWrite)
                    Using writer As New BinaryWriter(stream)
                        writer.Write(data)
                        writer.Close()
                    End Using
                End Using
            End While
            myReader.Close()
        Catch ex As Exception
            ' Log your exceptions here
            MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If myConnection IsNot Nothing AndAlso myConnection.State = ConnectionState.Open Then myConnection.Close()
            If cmd IsNot Nothing Then
                cmd.Dispose()
                cmd = Nothing
            End If
        End Try
End Sub
0
 
LVL 32

Expert Comment

by:it_saige
ID: 41770985
How are you saving to DocBin and what is the Field type for DocBin in the database?

-saige-
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:WelsfordAlan
ID: 41771211
Hi Saige

Below is my upload code....
The field is a blob.

MySql Table
 Private Sub CompanyLogoToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CompanyLogoToolStripMenuItem.Click
        Dim DialogResult As Integer
        Dim ImportFile As String
        'Dim q As Int16 = 1
        Dim i As Integer
   
        Dim ExNonQuery As String = "Err"
        Dim myConnection As MySqlConnection
        Dim myCommand As MySqlCommand

        'Dim record As Integer = 0
        Dim DocName As String
        Dim sql As String
        Dim sqlreturn

        Dim FileSize As UInt32
        Dim rawData() As Byte
        Dim fs As FileStream

        DocName = MDIParent1.Organisation + "-CompanyLogo"
        DialogResult = MsgBox("Bitmap file 102 x 366 to be used as Company logo", MsgBoxStyle.OkCancel)
        If DialogResult <> 1 Then Exit Sub
        DialogResult = OpenFileDialog1.ShowDialog(Me)


        If DialogResult = 1 Then

            ImportFile = OpenFileDialog1.FileName
            sql = "select ts from DocEmbed where organisation='" + MDIParent1.Organisation + "' and DocName='" + DocName + "'"
            sqlreturn = ExQuery(sql, "S")
            Try
                fs = New FileStream(ImportFile, FileMode.Open, FileAccess.Read)
                FileSize = fs.Length

                rawData = New Byte(FileSize) {}
                fs.Read(rawData, 0, FileSize)
                fs.Close()

                myConnection = New MySqlConnection(MDIParent1.MyDbConn)
                If sqlreturn = "" Then
                    sql = "Insert DocEmbed set doctype='bmp',docsize='" + Format(FileSize) + "',organisation='" + MDIParent1.Organisation + "', DocName='" + DocName + "', DocPath='', DocBin=@image_data "
                Else
                    sql = "Update DocEmbed set doctype='bmp',docsize='" + Format(FileSize) + "',DocBin=@image_data , ts=null where organisation='" + MDIParent1.Organisation + "' and DocName='" + DocName + "'"
                End If
                myCommand = New MySqlCommand(sql, myConnection)
                myCommand.Parameters.AddWithValue("@image_data", rawData)
                myCommand.Connection.Open()

                i = myCommand.ExecuteNonQuery()
                myCommand.Connection.Close()
            Catch ex As Exception
                MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            If i = 1 Then
                MessageBox.Show("The image has been uploaded", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("The image could not be uploaded", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End If

       


    End Sub
0
 
LVL 32

Accepted Solution

by:
it_saige earned 500 total points
ID: 41775343
Your methods look fine.  I just whipped up a quick little application to test.  See if any of this code provides additional assitance:

MySQL Table Structure -
CREATE TABLE `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `data` longblob NOT NULL,
  `size` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

Open in new window

Capture.JPGForm1.vb -
Imports System.ComponentModel
Imports System.IO

Public Class Form1
	Private frmDocument As DocumentForm
	Private documents As New BindingList(Of Document)

	Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
		LoadDocuments()
		Binder.DataSource = documents
		DocumentGrid.DataSource = Binder
	End Sub

	Private Sub OnRowContextMenuStripNeeded(sender As Object, e As DataGridViewRowContextMenuStripNeededEventArgs) Handles DocumentGrid.RowContextMenuStripNeeded
		If TypeOf sender Is DataGridView Then
			Dim dgv = DirectCast(sender, DataGridView)
			If Not dgv.SelectedRows.Contains(dgv.Rows(e.RowIndex)) Then dgv.Rows(e.RowIndex).Selected = True
			e.ContextMenuStrip = RowContext
		End If
	End Sub

	Private Sub OnClick(sender As Object, e As EventArgs) Handles AddDocumentToolStripMenuItem.Click, OpenDocumentToolStripMenuItem.Click, SaveDocumentToolStripMenuItem.Click, DeleteDocumentToolStripMenuItem.Click, EditDocumentDatabaseRowToolStripMenuItem.Click
		If TypeOf sender Is ToolStripMenuItem Then
			If frmDocument IsNot Nothing Then frmDocument.Dispose()

			Dim menu = DirectCast(sender, ToolStripMenuItem)
			Dim current = If(DocumentGrid.SelectedRows.Count = 1, DirectCast(DocumentGrid.SelectedRows(0).DataBoundItem, Document), New Document())
			If menu.Equals(AddDocumentToolStripMenuItem) Then
				frmDocument = New DocumentForm(New Document())
				If frmDocument.ShowDialog() = DialogResult.OK Then documents.Add(frmDocument.[Document])
			ElseIf menu.Equals(DeleteDocumentToolStripMenuItem) Then
				DeleteDocument(current)
			ElseIf menu.Equals(EditDocumentDatabaseRowToolStripMenuItem) Then
				frmDocument = New DocumentForm(current)
				frmDocument.ShowDialog()
			ElseIf menu.Equals(SaveDocumentToolStripMenuItem) Then
				Using browser = New SaveFileDialog() With {.CheckFileExists = True, .CheckPathExists = True, .FileName = current.Name}
					If browser.ShowDialog() = Windows.Forms.DialogResult.OK Then
						current.Data.ToFile(browser.FileName)
					End If
				End Using
			ElseIf menu.Equals(OpenDocumentToolStripMenuItem) Then
				Dim _file = Path.Combine(Path.GetTempPath, current.Name)
				current.Data.ToFile(_file)
				Using p As Process = New Process() With {.StartInfo = New ProcessStartInfo(_file)}
					p.Start()
					p.WaitForExit()
				End Using
				File.Delete(_file)
			End If
		End If
	End Sub

	Private Sub LoadDocuments()
		If documents IsNot Nothing Then documents = New BindingList(Of Document)
		Try
			Connection.Open()
			Using _ds As New DataSet()
				_ds.Tables.Add(New DataTable("Documents"))

				DocumentAdapter.Fill(_ds.Tables("Documents"))
				For Each row As DataRow In _ds.Tables(0).Rows()
					documents.Add(New Document() With {.ID = CType(row("ID"), Integer), .Name = CType(row("NAME"), String), .Data = CType(row("DATA"), Byte()), .Size = CType(row("SIZE"), Integer)})
				Next
			End Using
		Catch ex As Exception
		Finally
			Connection.Close()
		End Try
	End Sub

	Private Sub DeleteDocument(current As Document)
		If current IsNot Nothing Then
			Try
				Connection.Open()
				If current.ID <> -1 Then
					DocumentAdapter.DeleteCommand.Parameters("@ID").Value = current.ID
					DocumentAdapter.DeleteCommand.ExecuteNonQuery()
				End If

				For Each [person] In documents.Reverse()
					If [person].ID.Equals(current.ID) Then
						documents.Remove([person])
					End If
				Next
			Catch ex As Exception
				MessageBox.Show(ex.Message)
			Finally
				Connection.Close()
			End Try
		End If
	End Sub

	Private Sub OnCellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DocumentGrid.CellFormatting
		If TypeOf sender Is DataGridView Then
			Dim grid = DirectCast(sender, DataGridView)
			If grid.Rows.Count > 0 Then
				If grid.Columns(e.ColumnIndex).Name.Equals("birthdate", StringComparison.OrdinalIgnoreCase) Then
					e.Value = CType(e.Value, DateTime).ToShortDateString()
					e.FormattingApplied = True
				ElseIf grid.Columns(e.ColumnIndex).Name.Equals("signature", StringComparison.OrdinalIgnoreCase) Then

				End If
			End If
		End If
	End Sub
End Class

Open in new window

Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
	Inherits System.Windows.Forms.Form

	'Form overrides dispose to clean up the component list.
	<System.Diagnostics.DebuggerNonUserCode()> _
	Protected Overrides Sub Dispose(ByVal disposing As Boolean)
		Try
			If disposing AndAlso components IsNot Nothing Then
				components.Dispose()
			End If
		Finally
			MyBase.Dispose(disposing)
		End Try
	End Sub

	'Required by the Windows Form Designer
	Private components As System.ComponentModel.IContainer

	'NOTE: The following procedure is required by the Windows Form Designer
	'It can be modified using the Windows Form Designer.  
	'Do not modify it using the code editor.
	<System.Diagnostics.DebuggerStepThrough()> _
	Private Sub InitializeComponent()
		Me.components = New System.ComponentModel.Container()
		Me.DocumentGrid = New System.Windows.Forms.DataGridView()
		Me.GridContext = New System.Windows.Forms.ContextMenuStrip(Me.components)
		Me.AddDocumentToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
		Me.RowContext = New System.Windows.Forms.ContextMenuStrip(Me.components)
		Me.OpenDocumentToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
		Me.SaveDocumentToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
		Me.Binder = New System.Windows.Forms.BindingSource(Me.components)
		Me.DeleteDocumentToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
		Me.EditDocumentDatabaseRowToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
		CType(Me.DocumentGrid, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.GridContext.SuspendLayout()
		Me.RowContext.SuspendLayout()
		CType(Me.Binder, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.SuspendLayout()
		'
		'DocumentGrid
		'
		Me.DocumentGrid.AllowUserToAddRows = False
		Me.DocumentGrid.AllowUserToDeleteRows = False
		Me.DocumentGrid.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
		Me.DocumentGrid.ContextMenuStrip = Me.GridContext
		Me.DocumentGrid.Dock = System.Windows.Forms.DockStyle.Fill
		Me.DocumentGrid.Location = New System.Drawing.Point(0, 0)
		Me.DocumentGrid.MultiSelect = False
		Me.DocumentGrid.Name = "DocumentGrid"
		Me.DocumentGrid.ReadOnly = True
		Me.DocumentGrid.RowHeadersVisible = False
		Me.DocumentGrid.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
		Me.DocumentGrid.Size = New System.Drawing.Size(501, 261)
		Me.DocumentGrid.TabIndex = 0
		'
		'GridContext
		'
		Me.GridContext.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.AddDocumentToolStripMenuItem})
		Me.GridContext.Name = "GridContext"
		Me.GridContext.Size = New System.Drawing.Size(156, 26)
		'
		'AddDocumentToolStripMenuItem
		'
		Me.AddDocumentToolStripMenuItem.Name = "AddDocumentToolStripMenuItem"
		Me.AddDocumentToolStripMenuItem.Size = New System.Drawing.Size(155, 22)
		Me.AddDocumentToolStripMenuItem.Text = "Add Document"
		'
		'RowContext
		'
		Me.RowContext.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.OpenDocumentToolStripMenuItem, Me.SaveDocumentToolStripMenuItem, Me.EditDocumentDatabaseRowToolStripMenuItem, Me.DeleteDocumentToolStripMenuItem})
		Me.RowContext.Name = "RowContext"
		Me.RowContext.Size = New System.Drawing.Size(281, 114)
		'
		'OpenDocumentToolStripMenuItem
		'
		Me.OpenDocumentToolStripMenuItem.Name = "OpenDocumentToolStripMenuItem"
		Me.OpenDocumentToolStripMenuItem.Size = New System.Drawing.Size(280, 22)
		Me.OpenDocumentToolStripMenuItem.Text = "Open Document in Default Application"
		'
		'SaveDocumentToolStripMenuItem
		'
		Me.SaveDocumentToolStripMenuItem.Name = "SaveDocumentToolStripMenuItem"
		Me.SaveDocumentToolStripMenuItem.Size = New System.Drawing.Size(198, 22)
		Me.SaveDocumentToolStripMenuItem.Text = "Save Document To Disk"
		'
		'DeleteDocumentToolStripMenuItem
		'
		Me.DeleteDocumentToolStripMenuItem.Name = "DeleteDocumentToolStripMenuItem"
		Me.DeleteDocumentToolStripMenuItem.Size = New System.Drawing.Size(280, 22)
		Me.DeleteDocumentToolStripMenuItem.Text = "Delete Document Database Row"
		'
		'EditDocumentDatabaseRowToolStripMenuItem
		'
		Me.EditDocumentDatabaseRowToolStripMenuItem.Name = "EditDocumentDatabaseRowToolStripMenuItem"
		Me.EditDocumentDatabaseRowToolStripMenuItem.Size = New System.Drawing.Size(280, 22)
		Me.EditDocumentDatabaseRowToolStripMenuItem.Text = "Edit Document Database Row"
		'
		'Form1
		'
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.ClientSize = New System.Drawing.Size(501, 261)
		Me.Controls.Add(Me.DocumentGrid)
		Me.Name = "Form1"
		Me.Text = "Form1"
		CType(Me.DocumentGrid, System.ComponentModel.ISupportInitialize).EndInit()
		Me.GridContext.ResumeLayout(False)
		Me.RowContext.ResumeLayout(False)
		CType(Me.Binder, System.ComponentModel.ISupportInitialize).EndInit()
		Me.ResumeLayout(False)

	End Sub
	Friend WithEvents DocumentGrid As System.Windows.Forms.DataGridView
	Friend WithEvents GridContext As System.Windows.Forms.ContextMenuStrip
	Friend WithEvents AddDocumentToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
	Friend WithEvents RowContext As System.Windows.Forms.ContextMenuStrip
	Friend WithEvents OpenDocumentToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
	Friend WithEvents SaveDocumentToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
	Friend WithEvents Binder As System.Windows.Forms.BindingSource
	Friend WithEvents DeleteDocumentToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
	Friend WithEvents EditDocumentDatabaseRowToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem

End Class

Open in new window

DocumentForm.vb -
Imports System.IO

Public Class DocumentForm
	Private ReadOnly _document As Document

	Public ReadOnly Property [Document]() As Document
		Get
			Return _document
		End Get
	End Property

	Public Sub New(p As Document)
		InitializeComponent()
		_document = If(p IsNot Nothing, p, New Document())
	End Sub

	Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
		If _document.ID <> -1 Then
			tbName.Text = _document.Name
			tbFileType.Text = Convert.ToBase64String(_document.Data)
			tbSize.Text = _document.Size
		End If
	End Sub

	Private Sub OnClick(sender As Object, e As EventArgs) Handles btnOK.Click, btnCancel.Click, btnSelectFile.Click
		If TypeOf sender Is Button Then
			Dim btn = DirectCast(sender, Button)
			If btn.Equals(btnCancel) Then
				DialogResult = DialogResult.Cancel
				Close()
			ElseIf btn.Equals(btnOK) Then
				Try
					[Connection].Open()
					InsertOrUpdateDocumnet()
					DialogResult = DialogResult.OK
					Close()
				Catch ex As Exception
					MessageBox.Show(ex.Message)
				Finally
					[Connection].Close()
				End Try
			ElseIf btn.Equals(btnSelectFile) Then
				Using browser = New OpenFileDialog() With {.CheckFileExists = True, .CheckPathExists = True, .Multiselect = False}
					If (browser.ShowDialog() = Windows.Forms.DialogResult.OK) Then
						Dim _file = New FileInfo(browser.FileName)
						tbName.Text = _file.Name
						_document.Data = File.ReadAllBytes(_file.FullName)
						tbFileType.Text = _document.FileType
						tbSize.Text = _document.Data.Length
					End If
				End Using
			End If
		End If
	End Sub

	Private Sub InsertOrUpdateDocumnet()
		If [Connection].State = ConnectionState.Open AndAlso _document.Data IsNot Nothing Then
			_document.Name = tbName.Text
			_document.Size = Convert.ToInt32(tbSize.Text)

			If _document.ID = -1 Then
				DocumentAdapter.InsertCommand.Parameters("@NAME").Value = _document.Name
				DocumentAdapter.InsertCommand.Parameters("@DATA").Value = _document.Data
				DocumentAdapter.InsertCommand.Parameters("@SIZE").Value = _document.Size
				_document.ID = CType(DocumentAdapter.InsertCommand().ExecuteScalar(), Integer)
			Else
				DocumentAdapter.UpdateCommand.Parameters("@NAME").Value = _document.Name
				DocumentAdapter.UpdateCommand.Parameters("@DATA").Value = _document.Data
				DocumentAdapter.UpdateCommand.Parameters("@SIZE").Value = _document.Size
				DocumentAdapter.UpdateCommand.Parameters("@ID").Value = _document.ID
				DocumentAdapter.UpdateCommand().ExecuteNonQuery()
			End If
		End If
	End Sub
End Class

Open in new window

DocumentForm.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class DocumentForm
	Inherits System.Windows.Forms.Form

	'Form overrides dispose to clean up the component list.
	<System.Diagnostics.DebuggerNonUserCode()> _
	Protected Overrides Sub Dispose(ByVal disposing As Boolean)
		Try
			If disposing AndAlso components IsNot Nothing Then
				components.Dispose()
			End If
		Finally
			MyBase.Dispose(disposing)
		End Try
	End Sub

	'Required by the Windows Form Designer
	Private components As System.ComponentModel.IContainer

	'NOTE: The following procedure is required by the Windows Form Designer
	'It can be modified using the Windows Form Designer.  
	'Do not modify it using the code editor.
	<System.Diagnostics.DebuggerStepThrough()> _
	Private Sub InitializeComponent()
		Me.lblName = New System.Windows.Forms.Label()
		Me.tbName = New System.Windows.Forms.TextBox()
		Me.lblData = New System.Windows.Forms.Label()
		Me.tbFileType = New System.Windows.Forms.TextBox()
		Me.btnOK = New System.Windows.Forms.Button()
		Me.btnCancel = New System.Windows.Forms.Button()
		Me.btnSelectFile = New System.Windows.Forms.Button()
		Me.tbSize = New System.Windows.Forms.TextBox()
		Me.lblSize = New System.Windows.Forms.Label()
		Me.SuspendLayout()
		'
		'lblName
		'
		Me.lblName.AutoSize = True
		Me.lblName.Location = New System.Drawing.Point(9, 11)
		Me.lblName.Name = "lblName"
		Me.lblName.Size = New System.Drawing.Size(38, 13)
		Me.lblName.TabIndex = 0
		Me.lblName.Text = "Name:"
		'
		'tbName
		'
		Me.tbName.Location = New System.Drawing.Point(66, 8)
		Me.tbName.Name = "tbName"
		Me.tbName.ReadOnly = True
		Me.tbName.Size = New System.Drawing.Size(206, 20)
		Me.tbName.TabIndex = 1
		'
		'lblData
		'
		Me.lblData.AutoSize = True
		Me.lblData.Location = New System.Drawing.Point(9, 38)
		Me.lblData.Name = "lblData"
		Me.lblData.Size = New System.Drawing.Size(86, 13)
		Me.lblData.TabIndex = 2
		Me.lblData.Text = "Document Type:"
		'
		'tbData
		'
		Me.tbFileType.Location = New System.Drawing.Point(106, 35)
		Me.tbFileType.Name = "tbData"
		Me.tbFileType.ReadOnly = True
		Me.tbFileType.Size = New System.Drawing.Size(166, 20)
		Me.tbFileType.TabIndex = 3
		'
		'btnOK
		'
		Me.btnOK.Location = New System.Drawing.Point(116, 87)
		Me.btnOK.Name = "btnOK"
		Me.btnOK.Size = New System.Drawing.Size(75, 23)
		Me.btnOK.TabIndex = 5
		Me.btnOK.Text = "OK"
		Me.btnOK.UseVisualStyleBackColor = True
		'
		'btnCancel
		'
		Me.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel
		Me.btnCancel.Location = New System.Drawing.Point(197, 87)
		Me.btnCancel.Name = "btnCancel"
		Me.btnCancel.Size = New System.Drawing.Size(75, 23)
		Me.btnCancel.TabIndex = 6
		Me.btnCancel.Text = "Cancel"
		Me.btnCancel.UseVisualStyleBackColor = True
		'
		'btnSelectFile
		'
		Me.btnSelectFile.Location = New System.Drawing.Point(12, 87)
		Me.btnSelectFile.Name = "btnSelectFile"
		Me.btnSelectFile.Size = New System.Drawing.Size(75, 23)
		Me.btnSelectFile.TabIndex = 8
		Me.btnSelectFile.Text = "Select File"
		Me.btnSelectFile.UseVisualStyleBackColor = True
		'
		'tbSize
		'
		Me.tbSize.Location = New System.Drawing.Point(66, 61)
		Me.tbSize.Name = "tbSize"
		Me.tbSize.ReadOnly = True
		Me.tbSize.Size = New System.Drawing.Size(206, 20)
		Me.tbSize.TabIndex = 10
		'
		'lblSize
		'
		Me.lblSize.AutoSize = True
		Me.lblSize.Location = New System.Drawing.Point(9, 64)
		Me.lblSize.Name = "lblSize"
		Me.lblSize.Size = New System.Drawing.Size(30, 13)
		Me.lblSize.TabIndex = 9
		Me.lblSize.Text = "Size:"
		'
		'DocumentForm
		'
		Me.AcceptButton = Me.btnOK
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.CancelButton = Me.btnCancel
		Me.ClientSize = New System.Drawing.Size(284, 116)
		Me.Controls.Add(Me.tbSize)
		Me.Controls.Add(Me.lblSize)
		Me.Controls.Add(Me.btnSelectFile)
		Me.Controls.Add(Me.btnCancel)
		Me.Controls.Add(Me.btnOK)
		Me.Controls.Add(Me.tbFileType)
		Me.Controls.Add(Me.lblData)
		Me.Controls.Add(Me.tbName)
		Me.Controls.Add(Me.lblName)
		Me.Name = "DocumentForm"
		Me.Text = "Document"
		Me.ResumeLayout(False)
		Me.PerformLayout()

	End Sub
	Friend WithEvents lblName As System.Windows.Forms.Label
	Friend WithEvents tbName As System.Windows.Forms.TextBox
	Friend WithEvents lblData As System.Windows.Forms.Label
	Friend WithEvents tbFileType As System.Windows.Forms.TextBox
	Friend WithEvents btnOK As System.Windows.Forms.Button
	Friend WithEvents btnCancel As System.Windows.Forms.Button
	Friend WithEvents btnSelectFile As System.Windows.Forms.Button
	Friend WithEvents tbSize As System.Windows.Forms.TextBox
	Friend WithEvents lblSize As System.Windows.Forms.Label
End Class

Open in new window

SupportingObjects.vb -
Imports MySql.Data.MySqlClient
Imports System.ComponentModel
Imports System.IO
Imports System.Runtime.CompilerServices
Imports System.Runtime.InteropServices

Module Extensions
	<Extension()> _
	Public Function ToFile([data] As Byte(), [path] As String) As Boolean
		Try
			If [data] IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace([path]) Then
				Using [stream] As FileStream = New FileStream([path], FileMode.CreateNew, FileAccess.ReadWrite, FileShare.ReadWrite)
					[stream].Write([data], 0, [data].Length)
				End Using
				ToFile = True
			End If
		Catch ex As Exception
			ToFile = False
		End Try
		Return ToFile
	End Function
End Module

Module Globals
	Private _documentAdapter As MySqlDataAdapter
	Private ReadOnly _connection As MySqlConnection = New MySqlConnection("place your mysql connection string here")

	Public ReadOnly Property [Connection]() As MySqlConnection
		Get
			Return _connection
		End Get
	End Property

	Public ReadOnly Property [DocumentAdapter]() As MySqlDataAdapter
		Get
			If _documentAdapter Is Nothing Then ConfigureDocumentAdapter()
			Return _documentAdapter
		End Get
	End Property

	Private Sub ConfigureDocumentAdapter()
		_documentAdapter = New MySqlDataAdapter("SELECT * FROM DOCUMENTS", [Connection])
		_documentAdapter.DeleteCommand = New MySqlCommand("DELETE FROM DOCUMENTS WHERE ID = @ID", [Connection])
		_documentAdapter.InsertCommand = New MySqlCommand("INSERT INTO DOCUMENTS (NAME, DATA, SIZE) VALUES (@NAME, @DATA, @SIZE); SELECT LAST_INSERT_ID();", [Connection])
		_documentAdapter.UpdateCommand = New MySqlCommand("UPDATE DOCUMENTS SET NAME = @NAME, DATA = @DATA, SIZE = @SIZE WHERE ID = @ID", [Connection])

		'' Configure the command parameters
		'' Delete Command
		_documentAdapter.DeleteCommand.Parameters.Add("@ID", MySqlDbType.Int32)

		'' Update Command
		_documentAdapter.UpdateCommand.Parameters.AddRange(New MySqlParameter() {New MySqlParameter("@NAME", MySqlDbType.VarChar), New MySqlParameter("@DATA", MySqlDbType.Blob), New MySqlParameter("@SIZE", MySqlDbType.Int32), New MySqlParameter("@ID", MySqlDbType.Int32)})

		'' Insert Command
		_documentAdapter.InsertCommand.Parameters.AddRange(New MySqlParameter() {New MySqlParameter("@NAME", MySqlDbType.VarChar), New MySqlParameter("@DATA", MySqlDbType.Blob), New MySqlParameter("@SIZE", MySqlDbType.Int32), New MySqlParameter("@ID", MySqlDbType.Int32, 4) With {.Direction = ParameterDirection.Output, .DbType = DbType.Int32}})
	End Sub
End Module

Module NativeMethods
	Public MimeSampleSize As Integer = 256
	Public DefaultMimeType As String = "application/octet-stream"

	<Flags()> _
	Public Enum MIMEFlags As UInteger
		''' <summary>No flags specified. Use default behavior for the function.</summary>
		[Default] = &H0
		''' <summary>Treat the specified pwzUrl as a file name. </summary>
		URLAsFileName = &H1
		''' <summary>Internet Explorer 6 for Windows XP SP2 and later. Use MIME-type detection even if FEATURE_MIME_SNIFFING is detected. Usually, this feature control key would disable MIME-type detection.</summary>
		EnableMIMESniffing = &H2
		''' <summary>Internet Explorer 6 for Windows XP SP2 and later. Perform MIME-type detection if "text/plain" is proposed, even if data sniffing is otherwise disabled. Plain text may be converted to text/html if HTML tags are detected. </summary>
		IgnoreMIMETextPlain = &H4
		''' <summary>Internet Explorer 8. Use the authoritative MIME type specified in pwzMimeProposed. Unless <see cref="MIMEFlags.IgnoreMIMETextPlain "/> is specified, no data sniffing is performed.</summary>
		ServerMIME = &H8
		''' <summary>Internet Explorer 9. Do not perform detection if "text/plain" is specified in pwzMimeProposed.</summary>
		RespectTextPlain = &H10
		''' <summary>Internet Explorer 9. Returns image/png and image/jpeg instead of image/x-png and image/pjpeg. </summary>
		ReturnUpdatedImgMIMEs = &H20
	End Enum

	<DllImport("urlmon.dll", CharSet:=CharSet.Auto)> _
	Public Function FindMimeFromData(pBC As IntPtr, <MarshalAs(UnmanagedType.LPWStr)> pwzUrl As String, <MarshalAs(UnmanagedType.LPArray)> pBuffer As Byte(), _
							    cbSize As UInteger, <MarshalAs(UnmanagedType.LPWStr)> pwzMimeProposed As String, dwMimeFlags As UInteger, _
							    ByRef ppwzMimeOut As IntPtr, dwReserved As UInteger) As UInteger
	End Function
End Module

Public Class Document
		Private _id As Integer = -1

	Public Property ID() As Integer
		Get
			Return _id
		End Get
		Set(ByVal value As Integer)
			_id = value
		End Set
	End Property

	Public Property Name() As String
	Public ReadOnly Property FileType() As String
		Get
			Try
				Dim [mimePointer] As IntPtr
				FindMimeFromData(IntPtr.Zero, Nothing, Data, MimeSampleSize, Nothing, MIMEFlags.Default, mimePointer, 0)
				Dim [mime] = Marshal.PtrToStringUni(mimePointer)
				Marshal.FreeCoTaskMem(mimePointer)
				Return If(mime IsNot Nothing, mime, DefaultMimeType)
			Catch ex As Exception
				Return DefaultMimeType
			End Try
		End Get
	End Property
	<Browsable(False)> _
	Public Property Data() As Byte()
	Public Property Size() As Integer
End Class

Open in new window

Provides the following output -

Initial load (I already added some files to the database):Capture.JPGSelecting to add a new document:Capture.JPGRight-clicking on an inserted row:Capture.JPGThe 'Open Document in Default Application' item, saves the file to the disk into the temporary folder of the user and starts the file using the associated application defined as the files default.
The 'Save Document To Disk' item, saves the file to the location specified by the user.

-saige-
0
 

Author Comment

by:WelsfordAlan
ID: 41786896
Hi Saige

I will only be able to try this out over the weekend... thanks so much for you info. Will respond during the weekend.
0
 
LVL 32

Expert Comment

by:it_saige
ID: 41837042
This comment; https:#a41775343; provides an adequate and testable solution to the authors question.

-saige-
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Wpf develop 5 35
C# Error - Add Failed 12 53
Events in static methods 3 31
Recommendation vb6 to vb.net or others 14 44
I. Introduction In a previous article (http://www.experts-exchange.com/Web_Development/Document_Imaging/A_6537-PaperPort-Upgrade-How-to-download-and-install-updated-versions-of-PaperPort-11-and-12.html) (now deprecated), I discussed how to upgrad…
This article provides the solution to a question (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28674207.html) posed here at Experts Exchange. The asker of the question has many JPG images in many folders, and all of t…
In this first video of the three-part Xpdf series, we introduce and describe Xpdf, a library containing nine command line utilities that perform various functions on PDF files. We show where the library is located and how to download it, discuss its…
This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now