|
|
|
|
|
|
|
Page Views :
|
136601
|
|
Downloads :
|
3727
|
|
Rating :
|
Rate it
|
|
Level :
|
Intermediate
|
|
|
|
|
Download
Files:
|
|
|
|
|
|
|
|
|
|
|
Introduction
In order to provide your application with cool pictures you can employ two techniques (at least). One of them is that you can save the pictures in a folder and store the path to each one in a database or configuration file. The other one is to store the entire file into a database, along with its file name.
Each of them has its ups and downs:
- If you save your files to a folder, you might accidentally delete a file from that folder. If this happens, you will end up with a broken "link" in your database or configuration file. However, the hard disk storage space is cheap, so you can afford to store a lot of files.
- If you store your files into a database, you can enforce security by using the security settings of the database. Also, there are no broken links ever. However, the database storage space is more expensive. Another idea is that you can save a thumbnail of the image on the database for quick access and to save the actual picture on hard drive.
Of course, each application has its particularities and you have to choose which one you will use. Ok. Enough of the philosophical talk! Let's dive into the good stuff.
The application
The problem of uploading files to a database is not that difficult. You have to use, on the server, a certain data type when creating the table. This data type has to be capable of storing large amounts of binary data. When using Microsoft SQL Server, this data type is called image. For more information see BLOB (Binary Large OBject) for a short definition and Books Online for a complete reference.
The client has to obtain the data from the file in binary format - a byte array - and to call a procedure on the server with that array as a parameter.
In this presentation I assume that I have a database Pictures on the server with a table called Pictures. The structure of this table is as follows:
| Field Name |
Field Type |
| kFileName |
Long |
| Picture |
Image |
| FileName |
Varchar(250) |
I also have stored procedures for uploading, downloading and retrieving the list of uploaded files. These procedures are shown below:
| Procedure Name |
Procedure body |
| UploadFile |
CREATE PROCEDURE [dbo].[UploadFile]
(
@Picture image,
@FileName varchar(250),
@kFileName bigint output
)
AS
insert into Pictures (Picture, FileName)
values (@Picture,@FileName)
set @kFileName = @@IDENTITY
GO |
| DownloadFile |
CREATE PROCEDURE [dbo].[DownloadFile]
(
@kFileName bigint,
@FileName varchar(250) output
)
AS
select Picture, FileName
from Pictures
where kFileName=@kFileName
GO |
| getUploadedFiles |
CREATE PROCEDURE [dbo].[getUploadedFiles]
AS
select ltrim(str(kFileName)) + " - " + FileName as Name
from Pictures
GO |
The code presented below was written using VB.Net and was tested in Visual Studio .NET 2003. The client code that gets the byte array from the file and calls these procedures is shown below.
Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Data
Imports System.Text
Imports System.Data.SqlClient
'
'* Autor: Ghiondea Alexandru
'* Date: 08 october 2004
'* Description: Implements methods for uploading and downloading files
'* with MS SQL Server
'*
Namespace PicturesInSQLServer
''' <summary>
''' This class manages uploads and downloads to and from an SQL Server
''' </summary>
Public Class TransferPictures
''' <summary>
''' Gets from the server a list of uploaded files into a dataSet
''' </summary>
''' <param name="ds">The dataset</param>
''' <param name="table">The table in the dataset</param>
Public Sub GetUploadedFiles(ByRef ds As DataSet, ByVal table As String)
'
' The variables required for connecting to the server.
'
Dim conn As SqlConnection = Nothing
Dim cmd As SqlCommand = Nothing
Dim da As SqlDataAdapter = Nothing
' ----------------------------------------------
Try
'
' If the table already exists, cleares its content. Else adds a new table.
'
If ds.Tables.Contains(table) Then
ds.Tables(table).Clear()
Else
ds.Tables.Add(table)
End If
' ----------------------------------------------
'
' Creates a connection to the database and initilizes the command
'
conn = New SqlConnection(ConnectionString())
cmd = New SqlCommand("getUploadedFiles", conn)
cmd.CommandType = CommandType.StoredProcedure
' ----------------------------------------------
'
' Initializes the DataAdapter used for retrieving the data
'
da = New SqlDataAdapter(cmd)
' ----------------------------------------------
'
' Opens the connection and populates the dataset
'
conn.Open()
da.Fill(ds, table)
conn.Close()
' ----------------------------------------------
Catch e As Exception
'
' If an error occurs, we assign null to the result and display the error to the user,
' with information about the StackTrace for debugging purposes.
'
Console.WriteLine(e.Message & " - " & e.StackTrace)
End Try
End Sub
''' <summary>
''' Uploads a file to the database server.
''' </summary>
''' <param name="fileName">The filename of the picture to be uploaded</param>
''' <returns>The id of the file on the server.</returns>
Public Function UploadFile(ByVal FileName As String) As Long
If (Not File.Exists(FileName)) Then
Return -1
End If
Dim fs As FileStream = Nothing
Try
'#Region "Reading file"
fs = New FileStream(FileName, FileMode.Open)
'
' Finding out the size of the file to be uploaded
'
Dim fi As FileInfo = New FileInfo(FileName)
Dim temp As Long = fi.Length
Dim lung As Integer = Convert.ToInt32(temp)
' ------------------------------------------
'
' Reading the content of the file into an array of bytes.
'
Dim picture As Byte() = New Byte(lung - 1) {}
fs.Read(picture, 0, lung)
fs.Close()
' ------------------------------------------
'#End Region
Dim result As Long = uploadFileToDatabase(picture, fi.Name)
Return result
Catch e As Exception
Console.WriteLine(e.Message & " - " & e.StackTrace)
Return -1
End Try
End Function
''' <summary>
''' Wrapper for downloading a file from a database.
''' </summary>
''' <param name="kFileName">The Unique ID of the file in database</param>
''' <param name="fileName">The file name as it was stored in the database.</param>
''' <returns>The byte array required OR null if the ID is not found</returns>
Public Function DownloadFile(ByVal kFileName As Long, ByRef fileName As String) As Byte()
Dim result As Byte() = downloadFileFromDatabase(kFileName, fileName)
Return result
End Function
''' <summary>
''' Returns the connection string for connecting to the database
''' </summary>
''' <returns>The Connection string.</returns>
Public Shared Function ConnectionString() As String
'
' We consider that the database is situated on the same computer that runs the program.
' To connect to a remote server, replace 'Data Source' with the name of that server.
'
Return "Connect Timeout=600;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pictures;Packet Size=4096;Data Source=" & System.Environment.MachineName.Trim()
'.........You can also use........
'Dim Conn As String = "Server=.;uid=sa;pwd=;database=Pictures"
'Return Conn
End Function
''' <summary>
''' Uploades a file to an SQL Server.
''' </summary>
''' <param name="picture">A byte array that contains the information to be uploaded.</param>
''' <param name="fileName">The file name asociated with that byte array.</param>
''' <returns>The unique ID of the file on the server OR -1 if an error occurs. </returns>
Private Function uploadFileToDatabase(ByVal picture As Byte(), ByVal fileName As String) As Long
'
' Defining the variables required for accesing the database server.
'
Dim conn As SqlConnection = Nothing
Dim cmd As SqlCommand = Nothing
Dim kFileName As SqlParameter = Nothing
'INSTANT VB NOTE: The local variable FileName was renamed since Visual Basic will not uniquely identify local variables when other local variables have the same name:
Dim FileName_Renamed As SqlParameter = Nothing
Dim pic As SqlParameter = Nothing
' By default, we assume we have an error. If we succed in uploading the file, we'll change this
' to the unique id of the file
Dim result As Long = -1
Try
'
' Connecting to database.
'
conn = New SqlConnection(ConnectionString())
cmd = New SqlCommand("UploadFile", conn) ' We assume there is a stored procedure called UploadFile
cmd.CommandType = System.Data.CommandType.StoredProcedure
' ----------------------------------------------
'
' Initializing parameters and assigning the values to be sent to the server
'
kFileName = New SqlParameter("@kFileName", System.Data.SqlDbType.BigInt, 4)
kFileName.Direction = ParameterDirection.Output
' This parameter does not have a size because we do not know what the size is going to be.
pic = New SqlParameter("@picture", SqlDbType.Image)
pic.Value = picture
FileName_Renamed = New SqlParameter("@FileName", SqlDbType.VarChar, 250)
FileName_Renamed.Value = fileName
' ----------------------------------------------
'
' Adding the parameters to the database. Remember that the order in which the parameters
' are added is VERY important!
'
cmd.Parameters.Add(pic)
cmd.Parameters.Add(FileName_Renamed)
cmd.Parameters.Add(kFileName)
' ----------------------------------------------
'
' Opening the connection and executing the command.
'
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
' ----------------------------------------------
'
' The result is the unique identifier created on the database.
'
result = CLng(kFileName.Value)
' ----------------------------------------------
'
' Disposing of the objects so we don't occupy memory.
'
conn.Dispose()
cmd.Dispose()
' ----------------------------------------------
Catch e As Exception
'
' If an error occurs, we report it to the user, with StackTrace for debugging purposes
'
Console.WriteLine(e.Message & " - " & e.StackTrace)
result = -1
' ----------------------------------------------
End Try
Return result
End Function
''' <summary>
''' Downloades a file from a database according to the unique id in that database.
''' </summary>
''' <param name="kFile">The ID of the file in the database</param>
''' <param name="FileName">The filename of the file as it was stored in the database.</param>
''' <returns>A byte array containing the information required.</returns>
Private Function downloadFileFromDatabase(ByVal kFile As Long, ByRef FileName As String) As Byte()
Dim conn As SqlConnection = Nothing
Dim cmd As SqlCommand = Nothing
Dim kFileName As SqlParameter = Nothing
'INSTANT VB NOTE: The local variable fileName was renamed since Visual Basic will not uniquely identify local variables when other local variables have the same name:
Dim fileName_Renamed As SqlParameter = Nothing
Dim dr As SqlDataReader = Nothing
Dim result As Byte() = Nothing
Try
'
' Connecting to database.
'
conn = New SqlConnection(ConnectionString())
cmd = New SqlCommand("DownloadFile", conn)
cmd.CommandType = System.Data.CommandType.StoredProcedure
' ----------------------------------------------
'
' Initializing parameters and assigning the values to be sent to the server
'
kFileName = New SqlParameter("@kFileName", System.Data.SqlDbType.BigInt, 8)
kFileName.Value = kFile
fileName_Renamed = New SqlParameter("@FileName", SqlDbType.VarChar, 250)
fileName_Renamed.Direction = ParameterDirection.Output
' ----------------------------------------------
'
' Adding the parameters to the database. Remember that the order in which the parameters
' are added is VERY important!
'
cmd.Parameters.Add(kFileName)
cmd.Parameters.Add(fileName_Renamed)
' ----------------------------------------------
'
' Opening the connection and executing the command.
' The idea behind using a dataReader is that, on the SQL Server, we cannot assign to a
' variable the value of an image field. So, we use a querry to select the record we want
' and we use a datareader to read that query.
' Because we are returnig information based on a primary key, we are always returning
' only one row of data.
'
conn.Open()
dr = cmd.ExecuteReader()
dr.Read()
'
' We are casting the value returned by the datareader to the byte[] data type.
'
result = CType(dr.GetValue(0), Byte())
'
' We are also returning the filename associated with the byte array.
'
FileName = CStr(dr.GetValue(1))
'
' Closing the datareader and the connection
'
dr.Close()
conn.Close()
' ------------------------------------------
'
' Disposing of the objects so we don't occupy memory.
'
conn.Dispose()
cmd.Dispose()
' ------------------------------------------
Catch e As Exception
'
' If an error occurs, we assign null to the result and display the error to the user,
' with information about the StackTrace for debugging purposes.
'
Console.WriteLine(e.Message & " - " & e.StackTrace)
result = Nothing
End Try
Return result
End Function
End Class
End Namespace
I have also written a small application to demonstrate how to use these methods. A screenshot of it is shown below.
Here are some snippets of relevant code for this application:
Private Sub UploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UploadFile.Click
'
' Gets the file to be uploaded
'
Dim ofd As OpenFileDialog = New OpenFileDialog()
ofd.ShowDialog()
If ofd.FileName="" OrElse (Not File.Exists(ofd.FileName)) Then
'
' If the requested file is not ok...
'
Return
End If
Dim up As TransferPictures = New TransferPictures()
Dim id As Long =up.UploadFile(ofd.FileName)
Dim msg As String=Nothing
If id >0 Then
msg = "Upload succesful"
LoadInformationFromDataBase()
Else
msg = "An error has occured"
End If
MessageBox.Show(msg)
End Sub
''' <summary>
''' Gets from the server a list of uploaded files.
''' </summary>
Private Sub LoadInformationFromDataBase()
Dim up As TransferPictures = New TransferPictures()
up.GetUploadedFiles(ds,"Pictures")
UploadedFiles.DataSource = ds.Tables("Pictures")
UploadedFiles.DisplayMember = "Name"
End Sub
Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
LoadInformationFromDataBase()
End Sub
Private Sub UploadedFiles_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles UploadedFiles.DoubleClick
'
' Downloads the selected file and displays it in the picture box.
'
'
' Finds the unique id of the file.
'
Dim drv As DataRowView = CType(UploadedFiles.SelectedItem, DataRowView)
Dim selectedText As String = drv.Row("Name").ToString()
Dim id As Long=-1
'
' the id is stored in text. The structure is: id - FileName.
'
id = Long.Parse(selectedText.Substring(0,selectedText.IndexOf(" - ",0)).Trim())
Dim filename As String=Nothing
Dim up As TransferPictures = New TransferPictures()
Dim result As Byte() = up.DownloadFile(id,filename)
up = Nothing
Try
'
' We cannot assign a byte array directly to an image.
' We use MemoryStream, an object that creates a file in memory
' and than we pass this to create the image object.
'
Dim ms As MemoryStream = New MemoryStream(result, 0, result.Length)
Dim im As Image = Image.FromStream(ms)
Picture.Image = im
Catch ee As Exception
MessageBox.Show("An error has occured.\n" + ee.Message)
End Try
End Sub
Conclusion
Well, choosing which type of image - file - storage technique is up to the person designing a specific application. I have tried here to show you how you can store them in a database.
Happy Coding!
Bibliography
1. Books Online
2. MSDN
NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# CORNER (WWW.C-SHARPCORNER.COM).
|
|
Comment Request!
Thank you for reading this post. Please post your feedback, question, or comments about this post
Here.
|
|
|
|
|
Login
to add your contents and source code to this article
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional
consulting company, our consultants are well-known experts in .NET and many of them
are MVPs, authors, and trainers. We specialize in Microsoft .NET development and
utilize Agile Development and Extreme Programming practices to provide fast pace
quick turnaround results. Our software development model is a mix of Agile Development,
traditional SDLC, and Waterfall models.
|
|
Click here to learn more about C# Consulting. |
|
|
|
|
|
|
|
Introducing MaxV - one click. infinite control. Hyper-V Hosting from MaximumASP.
Finally – a virtual platform that delivers next-generation Windows Server 2008 Hyper-V virtualization technology from a managed hosting partner you can truly depend on. Visit www.maximumasp.com/max for a FREE 30 day trial. Hurry offer ends soon.
Climb aboard the MaxV platform and take advantage of High Availability, Intelligent Monitoring, Recurrent Backups, and Scalability – with no hassle or hidden fees.
As a managed hosting partner focused solely on Microsoft technologies since 2000, MaximumASP is uniquely qualified to provide the superior support that our business is built on. Unparalleled expertise with Microsoft technologies lead to working directly with Microsoft as first to offer IIS 7 and SQL 2008 betas in a hosted environment; partnering in the Go Live Program for Hyper-V; and product co-launches built on WS 2008 with Hyper-V technology.
|
Dynamic PDF
ceTE software specializes in components for dynamic PDF generation and manipulation. The DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and new content to existing PDF documents from within your applications.
|
Nevron Chart for .NET 2010.1 Now Available
The leading .NET charting control now features PDF, Flash and Silverlight export, visualization of large datasets and more. Deliver true charting functionality to your BI, Scorecard, Presentation or Scientific apps. Download evaluation now.
|
ASP.NET 4 Hosting
Get 2 Months Free of ASP.NET Hosting for Only $4.95/month! Receive FREE MS SQL and MySQL Databases Including ASP.NET 4/3.5, MVC 3.0, Silverlight 4, Windows 2008/IIS 7.0 Plus FREE IIS 7 Modules. Host UNLIMITED ASP.NET Web Sites – Click Here!
|
|
|
|
|
|
|
|
|
|
|
|
|