Bujar Ademi

Bujar Ademi

Windows Programmer

Visual Basic 2005 – Add file to sql table

During a project that I’m doing now, I saw that many programmers have their photos or any document to keep them in the database and whenever they need to open it. In the project I’m working for my company we need to save our documents (images, word documents etc.) in a database where they can after access documents any time they need.

The idea was good and easy to make. First, I will create table on database where I will save my documents.

SQL Table

As mention early, I need first to create table where I will save documents, also because I am lover of stored procedures I will create a stored procedure to insert document in the table.

CREATE TABLE tblDocuments(
DocumentID bigint PRIMARY KEY IDENTITY(1,1),
Bytes varbinary(MAX),
NameDocument varchar(255));

 

This table contains only three column. First column is primary key, this is needed when we make SELECT from table. Second column is the column that contain document itself, ofcourse in array of binary format and the last column is the name of the document.Insertion in table I am doing with sql stored procedure.

CREATE PROCEDURE InsUpdDocument
@DocumentID bigint,
@Bytes varbinary(MAX),
@NameDocument varchar(255),
@ReturnVal bigint OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @count int
SET @count = 0
SELECT @count=COUNT(*) FROM tblDocuments WHERE DocumentID = @DocumentID
IF(@count=0)
BEGIN
INSERT INTO tblDocuments(Bytes, NameDocument) VALUES (@Bytes, @NameDocument)
SELECT @ReturnVal = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT @ReturnVal = @DocumentID
END
END
GO

 

Following stored procedure gets 4 parameters and return an output parameter with primary key. This stored procedure checks if documentid exists in the table and if it doesn’t exists then it inserts the document.

Visual Basic 2005

We are doing nothing if we don’t have from where to insert the document or from where to get the document. SQL is only for storing data, until with Visual Basic 2005 we will make a class which class contains all interaction with tblDocuments table.

On this part I will show some part of the code. What is most interesting is the idea of accessing document and converting to array of bytes.

For this purpose I have created a class in Visual Basic 2005 and three constructors for this class. Default constructor is only initialize variables, until second variable as parameter takes a string variable (path of the file) and converts the file to bytes of array. Let’s see the code:

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Class Attach

'Declare variable for database connection
Private Const strCon As String = "Data Source=.\SQLEXPRESS;Initial Catalog=dbBlogSamples;Integrated Security=True"
Protected Connection As SqlConnection
'Declare variables as columns in tblDocuments
Private _DocumentID As Integer
Private _Bytes As Byte()
Public FilePath As String
Private _NameDocument As String

Public Property DocumentID() As Integer
Get
Return Me._DocumentID
End Get
Set(ByVal value As Integer)
Me._DocumentID = value
End Set
End Property

Public Property Bytes() As Byte()
Get
Return Me._Bytes
End Get
Set(ByVal value As Byte())
Me._Bytes = value
End Set
End Property

Public Property NameDocument() As String
Get
Return Me._NameDocument
End Get
Set(ByVal value As String)
Me._NameDocument = value
End Set
End Property

'Declare default constructor
Sub New()
Me.Connection = New SqlConnection(strCon)
Me.DocumentID = 0
Me.Bytes = New Byte() {}
Me.FilePath = ""
Me.NameDocument = ""
End Sub

'Declare constructor which takes string parameter, filepath of the document
Sub New(ByVal File As String)
Me.New()
Me.FilePath = File
Dim FInfo As FileInfo = New FileInfo(Me.FilePath)
Dim NrBytes As Long = FInfo.Length
Dim fs As FileStream = New FileStream(Me.FilePath, FileMode.Open, FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Me.Bytes = br.ReadBytes(Convert.ToInt32(NrBytes))
Me.NameDocument = FInfo.Name
End Sub

'Declare constructor which takes integer parameter, when calling from database
Sub New(ByVal DocID As Integer)
Me.New()
If Me.Connection.State <> ConnectionState.Open Then
Me.Connection.Open()
End If
Dim params As SqlParameter() = New SqlParameter() {New SqlParameter("@DocumentID", DocID)}
Dim dt As DataTable = Me.RunDataTable("LoadDokument", params)
If dt.Rows.Count = 0 Then
Return
End If

With dt.Rows(0)
Me.DokcumentID = CInt(.Item("DocumentID"))
Me.Bytes = DirectCast(.Item("Bytes"), Byte())
Me.NameDocument = .Item("NameDocument")
Me.FilePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\" & Me.NameDocument
End With
End Sub

Public Sub Insert()
'This will be method for calling stored procedure that already was created
End Sub

Public Sub Show()
If IO.File.Exists(Me.FilePath) = True Then
Process.Start(Me.FilePath)
Return
End If
Dim fs As FileStream = Nothing
fs = New FileStream(Me.FilePath, FileMode.OpenOrCreate, FileAccess.Write)
Dim bw As BinaryWriter = New BinaryWriter(fs)
bw.Write(Me.Bytes)
bw.Flush()
bw.Close()
fs.Close()
bw = Nothing
Process.Start(Me.FilePath)
End Sub
End Class

 

Usage of the Attach class

Using the class is very simple, depending if you are getting document from your local computer or from your sql database table.

Following example shows how to get document from your local computer and store in the sql table.

'Example of Inserting Document into the database
Dim myFile As String = "C:\File1.pdf"
Dim ClassInstance As Attach = New Attach(myFile)
ClassInstance.Insert()
'Example of Getting Document from Database and showing
Dim DatabaseID As Integer = 1
Dim SecondInstance As Attach = New Attach(DatabaseID)
SecondInstance.Show()