In this tutorial, we will be using the FileUpload control to insert images into a database. We will also write another script to display the images from the database. C#.
Although the FileUpload control is normally used to upload files to a file system, occasionally it is helpful to know how to insert those files into a SQL database. In our example we will be uploading and displaying image files. SQL database already have an image binary type so it will be easy to insert and retrieve the images.
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
First you can start a new Web Application in Visual Studio 2008. Now right-click on the App_Data folder and add a SQL Database. For our example we will add a Table named files to the database with the columns id int, fileData image, and fileType varchar(20). Here is a visual example of our table.
| files |
| id |
int |
| fileData |
image |
| fileType |
varchar(20) |
Make sure the id is a primary key and identity. You can change this by going through the column options. This will make sure that each row has a unique id number.
Next we need to add the FileUpload control to the first page. This page is going to be used for uploading the images. Remember to add a button to the form so we can click it once we have selected the file. We will also add a Literal control lit_status to the top so we can display a message when the file has been uploaded.
|
<form id="form_file" runat="server">
<div>
<asp:Literal ID="lit_status" runat="server" /><br />
<asp:FileUpload ID="UploadedFile" runat="server" /><br />
<asp:Button ID="Button_Upload" runat="server" Text="Upload File"
onclick="Button_Upload_Click" />
</div>
</form>
|
Next we will write the code behind the first aspx page. In our example we created a Click sub for the button control. This can be done by double-clicking on the button in design view. This code is going to be fairly complicated. We will first need to establish a connection to our database. Then we will need to add the image to the database. Finally we will display a status message with a link to our newly added image.
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.
The SqlConnection cn will contain the connection string matching the database name. Normally the connection string would be placed in the Web.config file for security reasons, but we have displayed it here to make things easier. Now we need to create the SqlCommand cmd with our INSERT SQL statement with placeholders for our parameters. The SqlParameters will substitute the placeholders for the binary image data and the image type.
|
///<summary>
///This is the button sub that is executed when someone clicks the Upload button
///</summary>
protected void Button_Upload_Click(object sender, EventArgs e)
{
string imageType = UploadedFile.PostedFile.ContentType;
byte[] imageData = new byte[UploadedFile.PostedFile.InputStream.Length + 1];
UploadedFile.PostedFile.InputStream.Read(imageData, 0, imageData.Length);
SqlConnection cn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand("INSERT INTO files (fileData,fileType) VALUES (@imgData,@imgType);SELECT @@IDENTITY", cn);
SqlParameter para1 = new SqlParameter("@imgData", SqlDbType.Image);
para1.Value = imageData;
cmd.Parameters.Add(para1);
SqlParameter para2 = new SqlParameter("@imgType", SqlDbType.VarChar, 50);
para2.Value = imageType;
cmd.Parameters.Add(para2);
cn.Open();
cmd.ExecuteNonQuery();
int theID = System.Convert.ToInt32(cmd.ExecuteScalar());
cn.Close();
lit_status.Text = "The file has been uploaded to the database. Click <a href=\"Default2.aspx?id="+theID+"\">here</a> to see it.";
} |
Notice the SqlCommand constructor has two SQL statements. The second statement pulls the id number of the last image added. The ExecuteScalar method is used because the statement is returning a single value. The value returned is an integer so it needs to be converted. This id number is added to the query string of the link we've included in the status message.
The first aspx page will now allow you to pick an image and upload it to the database. If you run the script now, you will notice that the link in the status message will be broken. We will need to create another aspx page to display the image. Fortunately, this isn't difficult to accomplish.
First, create a new aspx page. For tutorial purposes, I'm assuming your first aspx page was named Default.aspx and your second will be called Default2.aspx. For better programming practices, you will be better off naming each of these to something more helpful. Just make sure you are consistent with the code that references each page. When you open the second aspx page remove all the code after the opening <% %> brackets.
We will immediately start with the code behind the second aspx page. This page's purpose is to display the image corresponding to the id number that is in the query string. In the Page_Load sub we will need to make another connection to the database. You can use the same SqlConnection value you made in the first aspx page. For the SqlCommand constructor we will need to write a SQL statement that finds the row matching the id number in the query string. The page's contentType will be set to the same value placed in the database, and finally the binary data will be written out to the page.
|
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand("SELECT * FROM files WHERE id="+Request.QueryString["id"], cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read()) //check to see if image was found
{
Response.ContentType = dr["fileType"].ToString();
Response.BinaryWrite((byte[])dr["fileData"]);
}
cn.Close();
} |
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!
Now when the link is clicked on the first aspx page after a file is uploaded it will call the second aspx page which will display the image from the database. Make sure if you change the name of the second page, that you also change the link in the status message. To prevent SQL injection, we always recommend using stored procedures for your SQL statements.
Looking for more ASP.NET Database Tutorials? Click Here!