This tutorial will show how we can upload files to a SQL database using ASP.NET and C#.
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
Looking for the VB.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!
In this tutorial, we will be using a FileUpload Control to allow uploading of files to a SQL database. For this example, the database has just one table and five columns:
id, name, img, type, and length.We add a FileUpload control, a button, a GridView and two SqlDataSources to the ASPX page:
<form id="form1" runat="server">
<div>
<table style="width: 90%">
<tr>
<td style="width: 50%">
<asp:FileUpload ID="FileUpload1" runat="server" /><br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Upload to Database" /></td> <td style="width: 50%">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="id" DataSourceID="ContentListDataSource" ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <Columns>
<asp:BoundField DataField="id" HeaderText="ID" ReadOnly="True" SortExpression="id" /> <asp:BoundField DataField="name" HeaderText="Name" SortExpression="name" /> <asp:BoundField DataField="type" HeaderText="Type" SortExpression="type" /> <asp:BoundField DataField="length" HeaderText="Length" SortExpression="length" /> <asp:CommandField ButtonType="Image" DeleteImageUrl="~/media/delete.gif" ShowDeleteButton="True">
<ItemStyle Width="1px" /> </asp:CommandField> </Columns> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> </td> </tr> </table> <asp:SqlDataSource ID="ContentDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM tblImages WHERE [name]=@name" InsertCommand="INSERT INTO tblImages([name],img,type,length) VALUES(@name,@img,@type,@length)" OnDeleting="ContentDataSource_Deleting" OnInserting="ContentDataSource_Inserting" SelectCommand="SELECT [name], type, length FROM tblImages">
<DeleteParameters>
<asp:Parameter Name="name" /> </DeleteParameters> <InsertParameters>
<asp:Parameter Name="name" /> <asp:Parameter Name="img" /> <asp:Parameter Name="type" /> <asp:Parameter Name="length" /> </InsertParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="ContentListDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM tblImages WHERE id=@id" SelectCommand="SELECT id, [name], type, length FROM tblImages">
<DeleteParameters>
<asp:Parameter Name="id" /> </DeleteParameters> </asp:SqlDataSource> <br />
</form> |
Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!
Note there are two SqlDataSources - one is for adding new files to the database, the other is for displaying (and deleting) files to (and from) the GridView.
Also note the CommandField, which allows deletion from the GridView.
In the code-behind, we can ask if the file already exists, and if it does, we will delete the existing file and then save the new one - effectively overwriting it. Then we bind the data to the GridView to update the display.
On the ContentDataSource_Inserting event, we set the values of the uploaded image to the column names of table within the database - inserting a new record.
Similarly, on the ContentDataSource_Deleting even, we delete the record from the database by name.
The code-behind should look something like this:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) {
if (FileUpload1.HasFile) {
ContentDataSource.Delete();
ContentDataSource.Insert();
GridView1.DataBind(); } } protected void ContentDataSource_Inserting(object sender, SqlDataSourceCommandEventArgs e) {
e.Command.Parameters["@name"].Value = FileUpload1.FileName;
e.Command.Parameters["@type"].Value = FileUpload1.PostedFile.ContentType;
e.Command.Parameters["@length"].Value = FileUpload1.PostedFile.ContentLength;
e.Command.Parameters["@img"].Value = FileUpload1.FileBytes; } protected void ContentDataSource_Deleting(object sender, SqlDataSourceCommandEventArgs e) {
e.Command.Parameters["@name"].Value = FileUpload1.FileName; } |
The ConnectionString in Web.config should be something like this:
| <connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> |
Looking for the VB.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!
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.
|