Overview

Today, we will see how to upload the files -- that may be Word files, PDFs, Zip files etc. -- save them in the SQL database, retrieve these files, and download these files. This code is useful when you need to upload the various documents in an organization, perhaps to process the document, news etc., and other users will need to download these files to see the content. To see the content that you have uploaded, you have to save it in Binary format. Let's start,

image

Step 1: Let's create a table first

table

  1. CREATE TABLE [dbo].[tblFiles](
  2. [id] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [varchar](50) NOT NULL,
  4. [ContentType] [nvarchar](200) NOT NULL,
  5. [Data] [varbinary](max) NOT NULL
  6. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Step 2: Open Visual Studio

Open Visual Studio File->New Website, as shown below:

New Website

Select ASP.NET empty Website and give the suitable name as DocumentSaveInBinary, as shown below:

ASP.NET Empty Website

Now let's create FileUpload Control, as shown below:

code
  1. <asp:FileUpload ID="FileUpload1" runat="server" />
  2. <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" CssClass="btn-primary" />
Now, let's create Gridview with download link button so that we can download the respective documents or the files, shown below:

code
  1. <asp:GridView ID="GridView1" runat="server"
  3. AutoGenerateColumns="false" CssClass="table">
  4. <Columns>
  5. <asp:BoundField DataField="Name" HeaderText="File Name" />
  6. <asp:TemplateField ItemStyle-HorizontalAlign="Center">
  7. <ItemTemplate>
  8. <asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
  9. CommandArgument='<%# Eval("Id") %>'></asp:LinkButton>
  10. </ItemTemplate>
  11. </asp:TemplateField>
  12. </Columns>
  13. </asp:GridView>
Here, you will see that inside Gridview <asp:BoundField/> is used, that shows HeaderText as FileName in the Gridview. In that <Itemtemplate></Itemtemplate> inside Itemtemplate, you need to bind Link button with ID=”lnkDownload” OnClick=”DownloadFile”.

Thus, my final Document.aspx code is as follows:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="DocumentUpload.aspx.cs" Inherits="_Default" %>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  4. <html xmlns="http://www.w3.org/1999/xhtml">
  5. <head id="Head1" runat="server">
  6. <title></title>
  7. <link rel="Stylesheet" href="Styles/bootstrap.min.css" style="" />
  8. <link rel="Stylesheet" href="Styles/bootstrap.css" />
  9. </head>
  10. <body>
  11. <form id="form1" runat="server">
  12. <div class="container">
  13. <asp:FileUpload ID="FileUpload1" runat="server" />
  14. <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" CssClass="btn-primary" />
  15. <hr />
  16. <asp:GridView ID="GridView1" runat="server"
  18. AutoGenerateColumns="false" CssClass="table">
  19. <Columns>
  20. <asp:BoundField DataField="Name" HeaderText="File Name" />
  21. <asp:TemplateField ItemStyle-HorizontalAlign="Center">
  22. <ItemTemplate>
  23. <asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
  24. CommandArgument='<%# Eval("Id") %>'></asp:LinkButton>
  25. </ItemTemplate>
  26. </asp:TemplateField>
  27. </Columns>
  28. </asp:GridView>
  29. </div>
  30. </form>
  31. </body>
  32. </html>
Our final design looks as shown below:

Design

Step 3: Now let's see the CS Code Part

First include the connection string in web.config file, as shown below:
  1. <connectionStrings>
  2. <add name="constr" providerName="System.Data.SQlClient" connectionString="Data Source=AB-NPC1-D1A315;Initial Catalog=TEST;User ID=sa;Password=p@ssw0rd"/>
  3. </connectionStrings >
Now, we will see the first Action button upload, followed by the code to upload the files and finally save in the database.

Database

Our file upload code is shown below:

Code

As you see in the code, mentioned above, understand what we are saving in the table FileName, Contentype. Here, the content types are Words, PDF, image and so on. Thus, we are saving the posted file in the binary format by using Stream as a posted file, which you had uploaded in Fileupload control and convertedthat file in BinaryReader, as shown below:
  1. using (Stream fs = FileUpload1.PostedFile.InputStream)
  2. {
  3. using (BinaryReader br = new BinaryReader(fs))
  4. {
  5. byte[] bytes = br.ReadBytes((Int32)fs.Length);
  6. byte[] bytes = br.ReadBytes((Int32)fs.Length);
  7. //This line of code is reading the bytes .
  8. string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
  9. using (SqlConnection con = new SqlConnection(constr))
  10. {
  11. string query = "insert into tblFiles values (@Name, @ContentType, @Data)";
  12. using (SqlCommand cmd = new SqlCommand(query))
  13. {
  14. cmd.Connection = con;
  15. cmd.Parameters.AddWithValue("@Name", filename);
  16. cmd.Parameters.AddWithValue("@ContentType", contentType);
  17. cmd.Parameters.AddWithValue("@Data", bytes);
  18. con.Open();
  19. cmd.ExecuteNonQuery();
  20. con.Close();
  21. }
  22. }
  23. }
  24. }
This is used to insert the document in the database by using cmd.Parameters.AddWithValue(“@FieldName”,FileName),
  • Similarly, we will write the code for the download, as we had created on the click in the Gridview, as shown below:

    Code
    Here, what we are doing in DownloadFile is, you are actually reading the bytes which you had saved in the database.
Now we will bind the Gridview, as shown below:

Code

Hence, my final CS code is shown below:
  1. using System;
  2. using System.Web;
  3. using System.Web.Security;
  4. using System.Web.UI;
  5. using System.Web.UI.WebControls;
  6. using System.Web.UI.WebControls.WebParts;
  7. using System.Web.UI.HtmlControls;
  8. using System.IO;
  9. using System.Data;
  10. using System.Data.SqlClient;
  11. using System.Configuration;
  14. public partial class _Default : System.Web.UI.Page
  15. {
  17. protected void Page_Load(object sender, EventArgs e)
  18. {
  19. if (!IsPostBack)
  20. {
  21. BindGrid();
  22. }
  23. }
  24. private void BindGrid()
  25. {
  26. string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
  27. using (SqlConnection con = new SqlConnection(constr))
  28. {
  29. using (SqlCommand cmd = new SqlCommand())
  30. {
  31. cmd.CommandText = "select Id, Name from tblFiles";
  32. cmd.Connection = con;
  33. con.Open();
  34. GridView1.DataSource = cmd.ExecuteReader();
  35. GridView1.DataBind();
  36. con.Close();
  37. }
  38. }
  39. }
  40. protected void Upload(object sender, EventArgs e)
  41. {
  42. string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
  43. string contentType = FileUpload1.PostedFile.ContentType;
  44. using (Stream fs = FileUpload1.PostedFile.InputStream)
  45. {
  46. using (BinaryReader br = new BinaryReader(fs))
  47. {
  48. byte[] bytes = br.ReadBytes((Int32)fs.Length);
  49. string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
  50. using (SqlConnection con = new SqlConnection(constr))
  51. {
  52. string query = "insert into tblFiles values (@Name, @ContentType, @Data)";
  53. using (SqlCommand cmd = new SqlCommand(query))
  54. {
  55. cmd.Connection = con;
  56. cmd.Parameters.AddWithValue("@Name", filename);
  57. cmd.Parameters.AddWithValue("@ContentType", contentType);
  58. cmd.Parameters.AddWithValue("@Data", bytes);
  59. con.Open();
  60. cmd.ExecuteNonQuery();
  61. con.Close();
  62. }
  63. }
  64. }
  65. }
  66. Response.Redirect(Request.Url.AbsoluteUri);
  67. }
  68. protected void DownloadFile(object sender, EventArgs e)
  69. {
  70. int id = int.Parse((sender as LinkButton).CommandArgument);
  71. byte[] bytes;
  72. string fileName, contentType;
  73. string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
  74. using (SqlConnection con = new SqlConnection(constr))
  75. {
  76. using (SqlCommand cmd = new SqlCommand())
  77. {
  78. cmd.CommandText = "select Name, Data, ContentType from tblFiles where Id=@Id";
  79. cmd.Parameters.AddWithValue("@Id", id);
  80. cmd.Connection = con;
  81. con.Open();
  82. using (SqlDataReader sdr = cmd.ExecuteReader())
  83. {
  84. sdr.Read();
  85. bytes = (byte[])sdr["Data"];
  86. contentType = sdr["ContentType"].ToString();
  87. fileName = sdr["Name"].ToString();
  88. }
  89. con.Close();
  90. }
  91. }
  92. Response.Clear();
  93. Response.Buffer = true;
  94. Response.Charset = "";
  95. Response.Cache.SetCacheability(HttpCacheability.NoCache);
  96. Response.ContentType = contentType;
  97. Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
  98. Response.BinaryWrite(bytes);
  99. Response.Flush();
  100. Response.End();
  101. }
  102. }
  • Just run the Application and debug on the action events to see the FileUpload and its content, as shown below:

    Application

    code
You will see the file name which we had downloaded.

Now let's see the upload, as shown below:

Upload

We got the file name, as shown below:

code

Content Type is shown below:

code

Let's see the length of that document which is depicted below:

code

Now we will see what we have successfully uploaded.

result
Conclusion

This article was about uploading the files in the database and saving them in a binary format. I hope this article was helpful.