Store and Retrieve pdf/txt/doc/Images in Sql server database


Posted Date:     Total Responses: 0    Posted By: Viji RAJKUMAR   Member Level: Gold   Points/Cash: 1   


Technical Features:


• SQL Server 2000
• Microsoft .NET Version 3.5
• VB.NET (Windows Forms based application)



Functional Features:


• Uploading Images/Files into database
• Retrieval of Images/Files from database

Storing Images/Files:

1) Create a table in SQL Server 2000 database which has at least one field of type Image

Here is the Script I used:




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FileStore]
GO

CREATE TABLE [dbo].[FileStore] (
[FileId] [int] IDENTITY (1, 1) NOT NULL ,
[FileName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ImageData] [image] NOT NULL,
[FileType] [varchar](10) NOT NULL,
[Added On] [DateTime] NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO





The Image data type is used to store the binary content of the images/Files

2) I am using Open File Dialog to locate the file.


Using OpenFileDialog As OpenFileDialog = Me.GetOpenFileDialog()

If (OpenFileDialog.ShowDialog(Me) = DialogResult.OK) Then
txtFileToUpload.Text = OpenFileDialog.FileName

Else 'Cancel
Exit Sub
End If
End Using



3) I have used two methods , one to upload the image and another one to upload the files.



'Call Upload Images Or File
Dim sFileToUpload As String = ""

sFileToUpload = LTrim(RTrim(txtFileToUpload.Text))

Dim Extension As String = System.IO.Path.GetExtension(sFileToUpload)
upLoadImageOrFile(sFileToUpload, "Image")
upLoadImageOrFile(sFileToUpload, Extension)


4) Convert the file content into array of bytes using FileStream



'Initialize byte array with a null value initially.
Dim data As Byte() = Nothing

'Use FileInfo object to get file size.
Dim fInfo As New FileInfo(sPath)
Dim numBytes As Long = fInfo.Length

'Open FileStream to read file
Dim fStream As New FileStream(sPath, FileMode.Open, FileAccess.Read)

'Use BinaryReader to read file stream into byte array.
Dim br As New BinaryReader(fStream)

'When you use BinaryReader, you need to supply number of bytes to read from file.
'In this case we want to read entire file. So supplying total number of bytes.
data = br.ReadBytes(CInt(numBytes))



5) Saving byte array data to database

a) Create command text to insert record.

qry = "insert into FileStore (FileName,ImageData," & _
"FileType,[Added On]) values(@FileName, @ImageData," & _
"@FileType,@AddedOn)"


b) Create and provide value to the Parameters



'Initialize SqlCommand object for insert.
SqlCom = New SqlCommand(qry, connection)

'We are passing File Name and Image byte data as sql parameters.

SqlCom.Parameters.Add(New SqlParameter("@FileName", sFileName))
SqlCom.Parameters.Add(New SqlParameter("@ImageData", DirectCast(imageData, Object)))

SqlCom.Parameters.Add(New SqlParameter("@FileType", sFileType))
SqlCom.Parameters.Add(New SqlParameter("@AddedOn", Now()))



c) Execute the query to save the byte array to database



SqlCom.ExecuteNonQuery()

lblUploadStatus.Text = "File uploaded successfully"


d) Complete Code to save:


Private Sub upLoadImageOrFile(ByVal sFilePath As String, ByVal sFileType As String)
Dim SqlCom As SqlCommand
Dim imageData As Byte()
Dim sFileName As String
Dim qry As String

Try
'Read Image Bytes into a byte array

'Initialize SQL Server Connection
If connection.State = ConnectionState.Closed Then
connection.Open()
End If

imageData = ReadFile(sFilePath)
sFileName = System.IO.Path.GetFileName(sFilePath)

'Set insert query
qry = "insert into FileStore (FileName,ImageData," & _
"FileType,[Added On]) values(@FileName, @ImageData," & _
"@FileType,@AddedOn)"

'Initialize SqlCommand object for insert.
SqlCom = New SqlCommand(qry, connection)

'We are passing File Name and Image byte data as sql parameters.

SqlCom.Parameters.Add(New SqlParameter("@FileName", sFileName))
SqlCom.Parameters.Add(New SqlParameter("@ImageData", DirectCast(imageData, Object)))

SqlCom.Parameters.Add(New SqlParameter("@FileType", sFileType))
SqlCom.Parameters.Add(New SqlParameter("@AddedOn", Now()))

SqlCom.ExecuteNonQuery()
lblUploadStatus.Text = "File uploaded successfully"

Me.txtFileToUpload.Text = ""

Catch ex As Exception
MessageBox.Show(ex.ToString())
lblUploadStatus.Text = "File could not uploaded"
End Try

End Sub



6)
Retrieving Images/Files:

Retrieving images/files from the SQL database is the exact reverse process of saving the images/files to the SQL database. I have used DataGridView control to list the files/images stored with ViewFile button to view the file/image.


1) Populating the Gridview

Creating the query to list all the rows from FileStore database:



Dim strSql As String = "Select FileId,FileName," & _
"FileType,[Added On] from FileStore"

Fill the Adapter:

'Initialize SQL adapter.
Dim ADAP As New SqlDataAdapter(strSql, connection)

'Initialize Dataset.
Dim DS As New DataSet()

'Fill dataset with FileStore table.
ADAP.Fill(DS, "FileStore")

Assign the dataset to DataGridview:

'Fill Grid with dataset.
dbGridView.DataSource = DS.Tables("FileStore")
Add View File Button to the DataGridView:
Dim dgButtonColumn As New DataGridViewButtonColumn
dgButtonColumn.HeaderText = ""
dgButtonColumn.UseColumnTextForButtonValue = True
dgButtonColumn.Text = "View File"
dgButtonColumn.Name = "ViewFile"
dgButtonColumn.ToolTipText = "View File"

dbGridView.Columns.Add(dgButtonColumn)


2) Viewing the Image

When clicking on the View File button of the DataGridView row, it will display the image/file.

The dbGridView_CellContentClick handler does the trick.



If sender.Columns(e.ColumnIndex).Name = "ViewFile" Then
Select Case dbGridView.Rows(e.RowIndex).Cells("FileType").Value

Case "Image"
...
Case ".txt", ".pdf", ".doc"
...
Creating the query to retrieve the image from FileStore database based on FileId:

'For Image
strSql = "Select ImageData from FileStore WHERE FileId=" & dbGridView.Rows(e.RowIndex).Cells("FileId").Value



Convert the Image content into byte array:



Dim imageData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())
Convert the byte array to Image using Memory Stream

Dim newImage As Image = Nothing

Using ms As New MemoryStream(imageData, 0, imageData.Length)

ms.Write(imageData, 0, imageData.Length)

'Set image variable value using memory stream.
newImage = Image.FromStream(ms, True)

End Using



Display the image in picture box:

pictureBox1.Image = newImage


3) Viewing the File

• Creating the query to retrieve the File from FileStore database based on FileId:



strSql = "Select ImageData from FileStore WHERE FileId=" & iFileId


• Convert the Image content into byte array:


Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())


• Opening the Tempory File with the Stored File Name


Dim sTempFileName As String = Application.StartupPath & "\" & sFileName


• Convert the byte array to File Using File Stream


Using fs As New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)
fs.Write(fileData, 0, fileData.Length)
fs.Flush()
fs.Close()
End Using


• Opening the File


System.Diagnostics.Process.Start (sFileName)



Attachments:

1. Sql Script to create the table
2. Full VB.NET Project



Attachments

  • Store and Retrieve Images and Files(pdf,txt,doc etc) in Sql server database (512-51826-SQLScript.txt)
  • Store and Retrieve Images and Files(pdf,txt,doc etc) in Sql server database (512-51659-StoreImagesAndFiles.zip)

  • Project Feedbacks

    Author: Member Level: BronzeRevenue Score: 3 out of 53 out of 53 out of 5
    hi ..

    im freshman use vb.net..

    this project will upload a different files but when it comes with View files when i download a file there have some error that i encounter.. please help me for that error..

    this project i very useful for my project at my school..

    please help me. i need your respond as soon as possible please..

    i just hoping to help me.

    thanks godblees


    Author: Member Level: BronzeRevenue Score: 4 out of 54 out of 54 out of 54 out of 5
    hi ..

    im freshman use vb.net..

    this project will upload a different files but when it comes with View files when i download a file there have some error that i encounter.. please help me for that error..

    this project i very useful for my project at my school..

    please help me. i need your respond as soon as possible please..

    i just hoping to help me.

    thanks godblees


    Author: Member Level: BronzeRevenue Score: 4 out of 54 out of 54 out of 54 out of 5
    Hi..

    Can you pls provide me a c# code of your tutorial? this could help me alot if i'm using vb.net but unfortunately i'm using visual studio.net...
    Ur reponse will be a greate help to me..
    i'm using c# as my front end and sql server as my back end..
    hope u response immediately.. thans you so much in advance and godbless..


    Author: Member Level: BronzeRevenue Score: 5 out of 55 out of 55 out of 55 out of 55 out of 5
    Hi sachitanand here is the C# code, Iam sure this will work.

    byte[] imageData = (byte[])dtDoc.Rows[0]["File"];
    // File file;

    string sFileName = dtDoc.Rows[0]["path"].ToString();
    using (FileStream fs = new FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write))
    {
    fs.Write(imageData, 0, imageData.Length);
    fs.Flush();
    fs.Close();
    }

    System.Diagnostics.Process.Start(sFileName);


    Author: Member Level: BronzeRevenue Score: 5 out of 55 out of 55 out of 55 out of 55 out of 5
    Hi! Do you face any problem on storing big file size? with the method you use in your project?

    I had try to store a pdf file (Filesize: 466MB). - Successfull
    When i try to view the file it give me an error: - Exception of type 'System.OutOfMemoryException' was thrown.

    Im using your project, without changing anything.
    My PC Spec: 3GB RAM, I5 Processor 2nd Generation, SQL 2008 R2 & VB.NET 2010.

    I hope to get your reply. Thank!

    Regards
    Joe


    Author: Member Level: BronzeRevenue Score: 1 out of 5
    hi,,i am unable to run this project ..i attached the error screen shot..


    1.bmp
    Author: Member Level: BronzeRevenue Score: 5 out of 55 out of 55 out of 55 out of 55 out of 5
    I am trying to do a similar thing but only need pdf and I am using MS Access, can you help me please?

    This is your code, but I changed it a bit.

    Imports System.Threading
    Imports System.Runtime.InteropServices
    Imports System.Data.OleDb
    Imports System.Text
    Imports System.Security.Cryptography
    Imports System.IO
    Public Class frmpdfviewer
    Private Declare Function ShellEx Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hWnd As Integer, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Integer

    Dim bytImage() As Byte
    Dim rd As OleDbDataReader = Nothing
    Dim con As OleDbConnection = Nothing
    Dim cmd As OleDbCommand = Nothing
    'Dim dtable As DataTable
    'Dim dt As New DataTable
    Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\BCInventory.accdb;Persist Security Info=False;"
    Public Sub New()
    ' This call is required by the Windows Form Designer.
    InitializeComponent()
    Application.EnableVisualStyles()
    ' Add any initialization after the InitializeComponent() call
    Me.lblUploadStatus.Text = ""
    Me.WindowState = FormWindowState.Maximized
    End Sub
    Private Function GetOpenFileDialog() As OpenFileDialog
    Dim openFileDialog As New OpenFileDialog

    openFileDialog.CheckPathExists = True
    openFileDialog.CheckFileExists = True

    openFileDialog.Filter = "PDF files (*.pdf)|*.pdf"


    openFileDialog.Multiselect = False
    openFileDialog.AddExtension = True
    openFileDialog.ValidateNames = True
    openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)

    Return openFileDialog
    End Function
    Private Sub cmdBrowse_Click(sender As Object, e As EventArgs) Handles cmdBrowse.Click
    Using OpenFileDialog As OpenFileDialog = Me.GetOpenFileDialog()

    If (OpenFileDialog.ShowDialog(Me) = DialogResult.OK) Then
    txtFileToUpload.Text = OpenFileDialog.FileName

    Else 'Cancel
    Exit Sub
    End If
    End Using
    End Sub

    Private Sub cmdUpload_Click(sender As Object, e As EventArgs) Handles cmdUpload.Click
    Me.lblUploadStatus.Text = ""
    If LTrim(RTrim(txtFileToUpload.Text)) = "" Then
    lblUploadStatus.Text = "Enter File Name"
    txtFileToUpload.Focus()
    Exit Sub
    End If

    'Call Upload Images Or File
    Dim sFileToUpload As String = ""

    sFileToUpload = LTrim(RTrim(txtFileToUpload.Text))
    Dim Extension As String = System.IO.Path.GetExtension(sFileToUpload)
    upLoadImageOrFile(sFileToUpload, Extension)

    End Sub
    'Get table rows from sql server to be displayed in Datagrid.
    Private Sub GetImagesFromDatabase()
    Try
    'Initialize SQL Server Connection
    If con.State = ConnectionState.Closed Then
    con.Open()
    End If

    Dim strSql As String = "Select * from pdf"

    'Initialize SQL adapter.
    Dim ADP As OleDbDataAdapter

    'Initialize Dataset.
    Dim ds As DataSet

    'Fill dataset with FileStore table.
    ADP.Fill(ds, "pdf")

    'Fill Grid with dataset.
    dbGridView.DataSource = ds.Tables("pdf")

    'Add Button Starts

    Dim dgButtonColumn As New DataGridViewButtonColumn
    dgButtonColumn.HeaderText = ""
    dgButtonColumn.UseColumnTextForButtonValue = True
    dgButtonColumn.Text = "View File"
    dgButtonColumn.Name = "ViewFile"
    dgButtonColumn.ToolTipText = "View File"
    dgButtonColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCellsExceptHeader
    dgButtonColumn.FlatStyle = FlatStyle.System
    dgButtonColumn.DefaultCellStyle.BackColor = Color.Gray
    dgButtonColumn.DefaultCellStyle.ForeColor = Color.White
    dbGridView.Columns.Add(dgButtonColumn)


    Catch ex As Exception
    MessageBox.Show(ex.ToString())
    MessageBox.Show("Could not load the Image")
    End Try
    End Sub
    Private Sub dbGridView_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dbGridView.CellContentClick
    Dim strSql As String = ""
    Me.lblUploadStatus.Text = ""
    Try
    Select Case e.ColumnIndex
    Case Is > -1
    If sender.Columns(e.ColumnIndex).Name = "ViewFile" Then
    Select Case dbGridView.Rows(e.RowIndex).Cells("FileType").Value


    Case ".txt", ".pdf", ".doc"
    downLoadFile(dbGridView.Rows(e.RowIndex).Cells("FileId").Value, dbGridView.Rows(e.RowIndex).Cells("FileName").Value, dbGridView.Rows(e.RowIndex).Cells("FileType").Value)

    End Select

    End If

    End Select
    Catch ex As Exception
    MessageBox.Show(ex.ToString())
    End Try

    End Sub
    Private Sub downLoadFile(ByVal iFileId As Long, ByVal sFileName As String, ByVal sFileExtension As String)
    Dim strSql As String
    'For Document
    Try
    'Get image data from gridview column.
    strSql = "Select File from pdf WHERE PDFID=" & iFileId

    Dim sqlCmd As New OleDbCommand(strSql, con)

    'Get image data from DB
    Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())

    Dim sTempFileName As String = Application.StartupPath & "\" & sFileName

    If Not fileData Is Nothing Then
    'Read image data into a file stream
    Using fs As New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)
    fs.Write(fileData, 0, fileData.Length)
    'Set image variable value using memory stream.
    fs.Flush()
    fs.Close()
    End Using

    'Open File
    ' 10 = SW_SHOWDEFAULT
    ShellEx(Me.Handle, "Open", sFileName, "", "", 10)
    End If

    Catch ex As Exception
    MsgBox(ex.Message)
    End Try

    End Sub
    Private Sub upLoadImageOrFile(ByVal sFilePath As String, ByVal sFileType As String)
    Dim SqlCom As OleDbCommand
    Dim imageData As Byte()
    Dim sFileName As String
    Dim qry As String

    Try
    'Read Image Bytes into a byte array

    'Initialize SQL Server Connection
    If con.State = ConnectionState.Closed Then
    con.Open()
    End If

    imageData = ReadFile(sFilePath)
    sFileName = System.IO.Path.GetFileName(txtFileToUpload.Text)

    'Set insert query
    qry = "insert into pdf (PDFID,PR,Year,File) values(@pdfID,@pr,@year,@file)"

    'Initialize SqlCommand object for insert.
    SqlCom = New OleDbCommand(qry, con)

    'We are passing File Name and Image byte data as sql parameters.
    SqlCom.Parameters.Add(New OleDbParameter("@pdfID", sFileName))
    SqlCom.Parameters.Add(New OleDbParameter("@pr", pr.text))
    SqlCom.Parameters.Add(New OleDbParameter("@year", year.Text))
    SqlCom.Parameters.Add(New OleDbParameter("@file", DirectCast(imageData, Object)))

    SqlCom.ExecuteNonQuery()
    lblUploadStatus.Text = "File uploaded successfully"

    Me.txtFileToUpload.Text = ""

    Catch ex As Exception
    MessageBox.Show(ex.ToString())
    lblUploadStatus.Text = "File could not uploaded"
    End Try

    End Sub
    'Open file in to a filestream and read data in a byte array.
    Private Function ReadFile(ByVal sPath As String) As Byte()
    'Initialize byte array with a null value initially.
    Dim data As Byte() = Nothing

    'Use FileInfo object to get file size.
    Dim fInfo As New FileInfo(sPath)
    Dim numBytes As Long = fInfo.Length

    'Open FileStream to read file
    Dim fStream As New FileStream(sPath, FileMode.Open, FileAccess.Read)

    'Use BinaryReader to read file stream into byte array.
    Dim br As New BinaryReader(fStream)

    'When you use BinaryReader, you need to supply number of bytes to read from file.
    'In this case we want to read entire file. So supplying total number of bytes.
    data = br.ReadBytes(CInt(numBytes))

    Return data
    End Function
    Private Sub cmdView_Click(sender As Object, e As EventArgs) Handles cmdView.Click
    dbGridView.DataSource = Nothing
    dbGridView.Columns.Clear()

    GetImagesFromDatabase()
    End Sub
    Private Sub docViewerForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    Try
    frmMain.Enabled = True
    If con.State = ConnectionState.Open Then
    con.Close()
    con.Dispose()
    End If
    Catch ex As Exception

    End Try
    End Sub
    End Class



    Author: Member Level: BronzeRevenue Score: 1 out of 5

    I want to store .csv and large image files but
    cannot load large images and even .txt files
    please help me to load .csv and larges image files


    Author: Member Level: BronzeRevenue Score: 1 out of 5
    I can not load large images and even .txt files
    please help


    Author: Member Level: BronzeRevenue Score: 5 out of 55 out of 55 out of 55 out of 55 out of 5
    Sir i have created a web form in vb.net with sql server connectivity.
    The fields of the table are like last_name, First_name, bank_receipt, etc. Bank_receipt is the field where I want users to upload there pdf files. Currently i am able to insert all the fields into the database using vb.net but i don't know how to retrieve the pdf files stored in the bank_receipt field. Please help. The code behind and design files are attached below:





    Default.aspx.txt
    Author: Member Level: SilverRevenue Score: 1 out of 5
    It look great but can you provide me in c#


    Author: Member Level: GoldRevenue Score: 2 out of 52 out of 5
    Hi,
    Its really nice. I would like learn such type of things.

    It will be greatful for me to send the files.

    Thanks
    Siva Sreenath


    Author: Member Level: GoldRevenue Score: 5 out of 55 out of 55 out of 55 out of 55 out of 5
    Hi,

    This error message appears when a user is attempting to open a file through the Open from Source Control menu command and the file has not been added to source control through the Visual Studio IDE.

    Which Visual studio version you are having? This project is compiled in Visual studio 2008.

    To resolve this error, do the following:

    1. open VS.NET
    2. From the File menu, click Source Control, and then click change source control.
    3. All the other projects in the solution will show as Valid except for this
    solution.

    4. Select this project in the list and click on bind.

    5. Under server binding, it will automatically bind to the path that exists in VSS.

    6. But in the process it will ask you to check out the solution. Check the
    solution out. Wait until all the files get bound. Save the solution,
    check in the solution and close it. Open the solution again.


    Hope it should work.



    Post Feedback
    You must Sign In to post a feedback.
    Next Project: Sample Project
    Previous Project: Sample Ajax Web site

    Return to Project Index

    Post New Project


    Related Projects


    Top Contributors
    Today
      Last 7 Daysmore...

      Awards & Gifts

      Online Members

      Manigandan
      More...
       
      Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India