This tutorial will show you how we can use ASP.NET to allow uploading of files directly to a SQL database in C#
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 C# 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" onclick="btn_Upload_Click" /> |
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
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 void btn_Upload_Click(object sender, EventArgs e)
{
} |
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> |
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.
Before we start coding the button click event, we need to make sure we have the following assembly references, as we'll be making use of them:
using System.IO;
using System.Data.SqlClient;
using System.Configuration; |
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 == null || string.IsNullOrEmpty(FileToUpload.PostedFile.FileName) || FileToUpload.PostedFile.InputStream == null)
{
lit_Status.Text = "<br />Error - unable to upload file. Please try again.<br />"; } |
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
If we do have a file to upload, then let's upload it to the database:
else
{
using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) {
try {
const string SQL = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData)";
SqlCommand cmd = new SqlCommand(SQL, Conn);
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim());
cmd.Parameters.AddWithValue("@MIME", FileToUpload.PostedFile.ContentType);
byte[] imageBytes = new byte[FileToUpload.PostedFile.InputStream.Length + 1];
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(); } } |
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:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e) { } protected void btn_Upload_Click(object sender, EventArgs e) {
if (FileToUpload.PostedFile == null || String.IsNullOrEmpty(FileToUpload.PostedFile.FileName) || FileToUpload.PostedFile.InputStream == null) {
lit_Status.Text = "<br />Error - unable to upload file. Please try again.<br />"; } else {
using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) {
try {
const string SQL = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData)";
SqlCommand cmd = new SqlCommand(SQL, Conn);
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim());
cmd.Parameters.AddWithValue("@MIME", FileToUpload.PostedFile.ContentType);
byte[] imageBytes = new byte[FileToUpload.PostedFile.InputStream.Length + 1];
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(); } } } } |
Looking for more .NET Tutorials? Click Here!