This tutorial will show you how we can use ASP.NET to allow uploading of files directly to a SQL database in VB.NET
In this tutorial, we will explore how to use ASP.NET to allow users to upload files directly to a SQL database, rather than storing the file in the Web Server's file system.
The main advantage to doing this that when it comes to the backing up of the data, it is all stored in one secure place; there is no need to worry about backing up files in different locations that the database may link to.
We will start by creating our VB.NET project in Visual Studio and adding a SQL Database to the project. To do this, right-click your App_Data folder in Solution Explorer, then choose Add New Item.. SQL Server Database.
We will be adding a new table to the database, name it what you want (right-click Tables folder in Server Explorer and choose Add New Table.) We will create the following columns with the respective data types: ID (bigint), FileName (varchar(50)), DateTimeUploaded (datetime), MIME (varchar(50)), and BinaryData (varbinary(MAX)). The varbinary data type will be used to store the uploaded file, which can hold up to 2GB of data.
The next thing we need to do is create our upload form. Open up the ASPX page if it's not already, and add the following:
<form id="form1" runat="server">
Please upload file:<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" /> |
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
Here, we have a Literal control to display messages to the user, a textbox for the user to enter a name for the uploaded file, a File Upload control, and then a button to initiate the upload. Notice the method we are referencing on the OnClick event of the button. To create this handler, double-click on the button in design view. We should get the following:
Protected Sub btn_Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Upload.Click
End Sub |
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
Now we need to make sure we can connect to our database, we'll add the connection string in the Web.config:
(A quick way to do this is to add a SqlDataSource control to the ASPX page, then configure it in design view using the Smart Tag. Then simply delete the control when you're done - the connection string will have been automatically inserted into the Web.config for you).
| <connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings> |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
Before we start coding the button click event, we need to make sure we have the following assembly reference, as we'll be making use of them:
| Imports System.Data.SqlClient |
Now we have prepared, we can begin writing our code. The first thing we will do is to check to see whether we have a file to upload; if not, we need to notify the user:
| 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 />" End If |
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
If we do have a file to upload, then let's upload it to the database:
Else
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", FileToUpload.PostedFile.ContentType)
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)
Conn.Open()
cmd.ExecuteNonQuery()
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />"
Conn.Close() Catch
Conn.Close() End Try End Using |
Here, we are gathering the data to add to the database from the form, and also using the bytes data type to insert the binary data into the database.
The full code-behind will look something like this:
Imports System.Data.SqlClient
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 />" Else
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", FileToUpload.PostedFile.ContentType)
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)
Conn.Open()
cmd.ExecuteNonQuery()
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />"
Conn.Close() Catch
Conn.Close() End Try End Using End If End Sub |
Looking for more .NET Tutorials? Click Here!