Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Store and Retrieve pdf/txt/doc/Images in Sql server database
Posted Date:
Total Responses: 0
Posted By: Viji RAJKUMAR Member Level: Diamond 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: kumar | Member Level: Bronze | Revenue Score:  | hi,,i am unable to run this project ..i attached the error screen shot..
 | | Author: udit | Member Level: Bronze | Revenue Score: |
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: udit | Member Level: Bronze | Revenue Score: | I can not load large images and even .txt files
please help
| | Author: Viji RAJKUMAR | Member Level: Diamond | Revenue Score: | 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.
|
|
|
|
|