This tutorial shows you how to store raw binary data in a SQL Database using VB.NET and ASP.NET 3.5
In this tutorial, we will be looking at how to upload and store binary data in a SQL database. Probably the most popular way to store uploaded files is within the file system of the server, but storing files in a database does have its advantages. Using a database to store the raw binary data of files will help with backing up, as all the data is encapsulated within the database - no need to worry about any files that the database will be linking to.
Using the SQL data type varbinary, we can store files upto 2GB in size. In this example, we will create a simple upload form and demonstrate how to upload a GIF or JPEG and store it within the database. Doing it this way, we will need to record the MIME data type of the uploaded file, also, as it will be stored in raw binary form.
The first thing we should do is design our database. Let's create a table with the following columns (and data types):
- ID (bigint);
- FileName (varchar(50));
- DateTimeUploaded (datetime);
- MIME (varchar(50));
- BinaryData (varbinary(MAX)).
Now we have our table designed, let's move on to creating our upload form. We will require a textbox for the name of the file, a file upload control as well as a button:
<form id="form1" runat="server">
Please upload either a JPEG or a GIF:<br /> <asp:Literal ID="lit_Status" runat="server" /><br /> <b>Name:</b> <asp:TextBox ID="FileName" runat="server" /> <br /> <b>File:</b> <asp:FileUpload ID="FileToUpload" runat="server" /> <br /> <asp:Button ID="btn_Upload" runat="server" Text="Upload" /> |
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
We will also use a Literal control to display status messages to the user.
Now we will need to connect to our database, so make sure we have our connection string in the Web.config:
| <connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings> |
If you do not, a quick way to add the connection string is to drag a SqlDataSource control from the toolbox onto the page and configure it using the Smart Tag in design view. This will set the DataSource to the database we created earlier and input the connection string in the Web.config, and once you are done configuring, you can simply delete the SqlDataSource.
Next up, we will create a handler for the Submit button. A quick way to do this is to enter design view of the ASPX page and double-click the Submit button:
Imports System.Data.SqlClient
Imports System.IO
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub btn_Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Upload.Click
End Sub End Class |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
The first thing we want to do is to check if the file uploaded successfully:
| If FileToUpload.PostedFile Is Nothing OrElse String.IsNullOrEmpty(FileToUpload.PostedFile.FileName) OrElse FileToUpload.PostedFile.InputStream Is Nothing Then
lit_Status.Text = "<br />Error - unable to upload file. Please try again.<br />"
Exit Sub End If |
If the file is not uploaded, or there is no file to upload, we want to tell the user and break out of the Sub.
The next thing to do is to find out the file extension of the file:
Dim extension As String = Path.GetExtension(FileToUpload.PostedFile.FileName).ToLower()
Dim fileType As String = Nothing
Select Case extension
Case ".gif"
fileType = "image/gif" Case ".jpg", ".jpeg", ".jpe"
fileType = "image/jpeg" Case ".png"
fileType = "image/png" Case Else
lit_Status.Text = "<br />Error - invalid file type.<br />"
Exit Sub |
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
Finally, we use our connection to insert into the database:
Using Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Try
Const SQL As String = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData)"
Dim cmd As New SqlCommand(SQL, Conn)
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim())
cmd.Parameters.AddWithValue("@MIME", fileType)
Dim imageBytes(FileToUpload.PostedFile.InputStream.Length) As Byte
FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
cmd.Parameters.AddWithValue("@BinaryData", imageBytes)
cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now)
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />"
Conn.Open()
cmd.ExecuteNonQuery()
Conn.Close() Catch ex As Exception
Conn.Close() End Try |
We use a regular SQL Statement to insert the data into the database, and use SQL Parameters to pass the data from our form to the database.
The entire code-behind looks like this:
Imports System.Data.SqlClient
Imports System.IO
Partial Class _Default
Inherits System.Web.UI.Page Protected Sub btn_Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Upload.Click
If FileToUpload.PostedFile Is Nothing OrElse String.IsNullOrEmpty(FileToUpload.PostedFile.FileName) OrElse FileToUpload.PostedFile.InputStream Is Nothing Then
lit_Status.Text = "<br />Error - unable to upload file. Please try again.<br />"
Exit Sub End If Dim extension As String = Path.GetExtension(FileToUpload.PostedFile.FileName).ToLower() Dim fileType As String = Nothing Select Case extension
Case ".gif"
fileType = "image/gif" Case ".jpg", ".jpeg", ".jpe"
fileType = "image/jpeg" Case ".png"
fileType = "image/png" Case Else
lit_Status.Text = "<br />Error - invalid file type.<br />" Exit Sub End Select Using Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Try
Const SQL As String = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData)"
Dim cmd As New SqlCommand(SQL, Conn)
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim())
cmd.Parameters.AddWithValue("@MIME", fileType)
Dim imageBytes(FileToUpload.PostedFile.InputStream.Length) As Byte
FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
cmd.Parameters.AddWithValue("@BinaryData", imageBytes)
cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now)
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />"
Conn.Open()
cmd.ExecuteNonQuery()
Conn.Close() Catch ex As Exception
Conn.Close() End Try End Using End Sub |
Looking for more ASP.NET Database Tutorials? Click Here!
|