Uploading files to a Database in VB
Introduction
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:
We stand behind Server Intellect and their whole support team, they offer dedicated servers of course, and they now offer the cloud, which is nice.
The ConnectionString in Web.config should be something like this:
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we’ve found so far.
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:
We stand behind Server Intellect and their whole support team, they offer dedicated servers of course, and they now offer the cloud, which is nice.
Code Block
Default.aspx
<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 />
</div>
</form>
We chose Server Intellect for its cloud servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
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:
Code Block
Default.aspx.cs
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Partial Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
If FileUpload1.HasFile Then
' delete and content with the same name
ContentDataSource.Delete()
' insert new content
ContentDataSource.Insert()
' refresh the content list
GridView1.DataBind()
End If
End Sub
Protected Sub ContentDataSource_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
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
End Sub
Protected Sub ContentDataSource_Deleting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
e.Command.Parameters("@name").Value = FileUpload1.FileName
End Sub
End Class
Code Block
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>
Conclusion
In this tutorial we learned how to upload Files to an SQL Database in C#. This can be useful for large scale websites where you need to store information on a Server for easy access.
23-VB.zip
