This tutorial shows you how to store raw binary data in a SQL Database using C# 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.
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.
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" onclick="btn_Upload_Click" /> |
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
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:
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)
{
} } |
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.
The first thing we want to do is to check if the file uploaded successfully:
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 />";
return; } |
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:
else
{
string extension = Path.GetExtension(FileToUpload.PostedFile.FileName).ToLower(); string fileType = null; switch (extension) {
case ".gif":
fileType = "image/gif";
break; case ".jpg": case ".jpeg": case ".jpe":
fileType = "image/jpeg";
break; case ".png":
fileType = "image/png";
break; default:
lit_Status.Text = "<br />Error - invalid file type.<br />";
return; } |
Finally, we use our connection to insert into the database:
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", fileType);
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);
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />";
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close(); } catch {
Conn.Close(); } |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
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:
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 />";
return; } else {
string extension = Path.GetExtension(FileToUpload.PostedFile.FileName).ToLower(); string fileType = null; switch (extension) {
case ".gif":
fileType = "image/gif";
break; case ".jpg": case ".jpeg": case ".jpe":
fileType = "image/jpeg";
break; case ".png":
fileType = "image/png";
break; default:
lit_Status.Text = "<br />Error - invalid file type.<br />";
return; } 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", fileType);
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);
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />";
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close(); } catch {
Conn.Close(); } } } } |
Looking for more ASP.NET Database Tutorials? Click Here!
|