How to upload and download files to/from a SQL Server database?

Wednesday, November 20, 2013 Sourav Mondal ASP.NET

Introduction

In this post, I explain how to upload and download files to/from an SQL Server database?
Recently I have completed a project where I need to store various files to the database. Here in this post I explain how to upload and download files to/from an SQL Server database.

Steps :

Step - 1: Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create a table for Store File(s) data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one tables as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Add a Webpage and Design for upload and download files to/from database.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

     <h3>File Upload / Download from/to Database using ASP.NET</h3>
    <div>
        <table>
            <tr>
                <td>Select File : </td>
                <td>
                    <asp:FileUpload ID="FileUpload1" runat="server" /></td>
                <td>
                    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" /></td>
            </tr>
        </table>
        <br />
        <div>
            <%-- Add a Datalist for show uploaded files --%>
            <asp:DataList ID="DataList1" runat="server" RepeatColumns="4" RepeatDirection="Horizontal" OnItemCommand="DataList1_ItemCommand">
                <ItemTemplate>
                    <table>
                        <tr>
                            <td><%#Eval("FileName","File Name : {0}") %></td>
                        </tr>
                        <tr>
                            <td><%#String.Format("{0:0.00}",Convert.ToDecimal(Eval("FileSize"))/1024)%> KB</td>
                        </tr>
                        <tr>
                            <td>
                                <asp:LinkButton ID="lbtnDownload" runat="server" CommandName="Download" CommandArgument=<%#Eval("FileID") %>>Download</asp:LinkButton></td>
                        </tr>
                    </table>
                </ItemTemplate>
            </asp:DataList>
        </div>
    </div>

Step-6: Write code into page load event for show data.

Write below code into Page_Load event for show data from database.

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                PopulateUploadedFiles();
            }
        }

And here is the functioin PopulateUploadedFiles

         private void PopulateUploadedFiles()
        {
            using (MyDatabaseEntities dc = new MyDatabaseEntities())
            {
                List<UploadedFile> allFiles = dc.UploadedFiles.ToList();
                DataList1.DataSource = allFiles;
                DataList1.DataBind();
            }
        }

Step-7: Write code for Upload file(s) to database.

Write below code into button click event for Upload file(s) to database.

         protected void btnUpload_Click(object sender, EventArgs e)
        {
            // Code for Upload file to database
            if (FileUpload1.HasFile)
            {
                HttpPostedFile file = FileUpload1.PostedFile;
                BinaryReader br = new BinaryReader(file.InputStream);
                byte[] buffer = br.ReadBytes(file.ContentLength);
 
                using (MyDatabaseEntities dc = new MyDatabaseEntities())
                {
                    dc.UploadedFiles.Add(
                        new UploadedFile
                        {
                             FileName = file.FileName,
                              ContentType = file.ContentType,
                               FileID = 0,
                                FileSize = file.ContentLength,
                                 FileExtension = Path.GetExtension(file.FileName),
                                  FileContent = buffer
                        });
                    dc.SaveChanges();
                    PopulateUploadedFiles();
                }
            }
        }

Step-8: Write code for Download file from database.

Write below code into DataList1_ItemCommand event for Download file from database.

         protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
        {
            if (e.CommandName == "Download")
            {
                int fileID = Convert.ToInt32(e.CommandArgument);
                using (MyDatabaseEntities dc = new MyDatabaseEntities())
                {
                    var v = dc.UploadedFiles.Where(a => a.FileID.Equals(fileID)).FirstOrDefault();
                    if (v != null)
                    {
                        byte[] fileData = v.FileContent;
                        Response.AddHeader("Content-type", v.ContentType);
                        Response.AddHeader("Content-Disposition", "attachment; filename=" + v.FileName);
 
                        byte[] dataBlock = new byte[0x1000];
                        long fileSize;
                        int bytesRead;
                        long totalsBytesRead = 0;
 
                        using (Stream st = new MemoryStream(fileData))
                        {
                            fileSize = st.Length;
                            while (totalsBytesRead < fileSize)
                            {
                                if (Response.IsClientConnected)
                                {
                                    bytesRead = st.Read(dataBlock, 0, dataBlock.Length);
                                    Response.OutputStream.Write(dataBlock, 0, bytesRead);
 
                                    Response.Flush();
                                    totalsBytesRead += bytesRead;
                                }
                            }
                        }
                        Response.End();
                    }
                }
            }
        }