65.9K
CodeProject is changing. Read more.
Home

Multiple File Upload into a Database with Progress Bar and Drag and Drop

Igor Krupitsky
starIconstarIconstarIconstarIconstarIcon

5.00/5 (21 votes)

Oct 11, 2016

CPOL
viewsIcon

33473

downloadIcon

732

The ASP.NET pages let you upload, delete and browse files into a database.

Introduction

This ASP.NET application will let you upload multiple files to a SQL Server database via drag and drop. It will show progress bar for each file as it uploads. Once uploaded, you can browse, sort and delete these files.

Background

This is a sequel to my earlier article, Multiple file upload with progress bar and drag and drop.

Using the Code

To use this application:

  1. Download Upload.zip and unzip it to C:\inetpub\wwwroot\Upload.
  2. Open SQL Server Management Studio and create "Upload" database. Make sure that SQL Server uses SQL Authentication mode.

  3. In Notepad, open "C:\inetpub\wwwroot\Upload\Web.config" and update user name and password needed to connect to the database:

  4. Point your browser to http://localhost/Upload/Upload.aspx.

    Here is the code for Upload.js:

    var oUploadedFiles = [];

function OnLoad() {

    if (_("tbServer"))_("btnDelete").style.display = "";

    if (_("file1").addEventListener) _("file1").addEventListener_
                ("change", FileSelectHandler, false);

    var xhr = new XMLHttpRequest();
    if (xhr.upload) {
        
        var filedrag = _("divDropHere");
        if (filedrag){
            filedrag.addEventListener("dragover", FileDragHover, false);
            filedrag.addEventListener("dragleave", FileDragHover, false);
            filedrag.addEventListener("drop", FileSelectHandler, false);
            filedrag.style.display = "block";
        }

        _("btnUpload").style.display = "none";
    }
}

function FileDragHover(e) {
    e.stopPropagation();
    e.preventDefault();
    e.target.className = (e.type=="dragover")?"hover":"";
}

function FileSelectHandler(e) {
    FileDragHover(e);

    var oFiles = e.target.files || e.dataTransfer.files;
    if (oFiles.length==0) return;

    var sHtml = "<table id='tbClient' class='StatusTable' 
    border=1 cellspacing=0 cellpadding=3><tr>"
        + "<th><a href='?sort=FileName'>File name</a></th>"
        + "<th><a href='?sort=FileSize'>Size</a></th>"
        + "<th><a href='?sort=DateCreated'>Date Modified</a></th>"
        + "<th><label><input type=checkbox name=chkDeleteAll 
          onclick='DeleteAll(this)'>Delete</label></th></tr>";
    for (var i=0; i<oFiles.length; i++){
        sHtml += GetRowHtml(oFiles[i].name, oFiles[i].size, i + "", "");
    }
    
    for (var i=0; i<oUploadedFiles.length; i++){
        sHtml += GetRowHtml(oUploadedFiles[i].name, oUploadedFiles[i].size, "", 
                 oUploadedFiles[i].fileId);
    }
    
    var sServerHtml = "";
    if (_("tbServer")){
        _("trHeader").style.display = "none";
        _("tbServer").style.display = "none";
        sServerHtml = _("tbServer").innerHTML;
    }
    
    _("divStatus").innerHTML = sHtml + sServerHtml + "</table>";
    
    for (var i=0; i<oFiles.length; i++){
        UploadFile(oFiles[i],i);
    }
}

function GetRowHtml(sName, iSize, i, iFileId) {
    var sHref = "";
    if (iFileId != "") sHref = " href='Download.aspx?id=" + iFileId + "' ";

    var s = "<tr><td><a id=fileLink" + i + " target='_blank'" + 
    sHref + ">" + sName + "</a></td>"
              + "<td>" + (iSize/1024).formatNumber(0,',','.') + " KB</td>"
        
        if (i==""){
            s += "<td><div class='progressBar progressSuccess'>&nbsp;</div></td>";
        }else{
            s += "<td id=progressBar"+i+"></td>";
        }

    return s + "<td><input type=checkbox name=chkDelete value=\"" + 
    sName + "\"></td></tr>";
}

function UploadFile(file,i) {
    var xhr = new XMLHttpRequest();
    if (xhr.upload) {
        var progress = _("progressBar"+i).appendChild(document.createElement("div"));
        progress.className = "progressBar";
        progress.innerHTML = "&nbsp;";

        // progress bar
        xhr.upload.addEventListener("progress", function(e) {
            var pc = parseInt(100 - (e.loaded / e.total * 100));
            progress.style.backgroundPosition = pc + "% 0";
        }, false);

        // file received/failed
        xhr.onreadystatechange = function (e) {
            if (xhr.readyState == 4) {
                progress.className = "progressBar " + 
                   (xhr.status == 200 ? "progressSuccess" : "progressFailed");
                if (xhr.status == 200) {

                    if (xhr.responseText == "") {
                        oUploadedFiles.push({ fileId: 0, name: file.name, size: file.size });
                        alert("ccc");
                    } else {
                        eval(xhr.responseText);
                        var iFileId = oUploadedFiles[oUploadedFiles.length - 1].fileId;
                        _("fileLink" + i).href = "Download.aspx?id=" + iFileId;
                    }

                    _("btnDelete").style.display = ""
                } else {
                    _("divError").innerHTML = xhr.responseText;
                }
            }
        };

        var oFormData = new FormData();
        oFormData.append("myfile"+i, file);
        xhr.open("POST", _("form1").action, true);
        xhr.send(oFormData);
    }
}

function DeleteAll(o){
    var oBoxes = document.getElementsByTagName("input");
    for (var i=1; i<oBoxes.length; i++){
        oBoxes[i].checked = o.checked;
    }
}

function _(id) {
    return document.getElementById(id);
}

Number.prototype.formatNumber = function(decPlaces, thouSeparator, decSeparator) {
    var n = this,
        decPlaces = isNaN(decPlaces = Math.abs(decPlaces)) ? 2 : decPlaces,
        decSeparator = decSeparator == undefined ? "." : decSeparator,
        thouSeparator = thouSeparator == undefined ? "," : thouSeparator,
        sign = n < 0 ? "-" : "",
        i = parseInt(n = Math.abs(+n || 0).toFixed(decPlaces)) + "",
        j = (j = i.length) > 3 ? j % 3 : 0;
    return sign + (j ? i.substr(0, j) + thouSeparator : "") + 
           i.substr(j).replace(/(\d{3})(?=\d)/g, "$1" + thouSeparator) + 
           (decPlaces ? decSeparator + Math.abs(n - i).toFixed(decPlaces).slice(2) : "");
};

    Here is the code for Upload.aspx.vb:

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Request.HttpMethod = "POST" Then

            Dim cn As New System.Data.SqlClient.SqlConnection(GetConnectionString())
            cn.Open()

            If Request.Form("btnDelete") <> "" Then
                'Delete files
                If (Not Request.Form.GetValues("chkDelete") Is Nothing) Then
                    For i As Integer = 0 To Request.Form.GetValues("chkDelete").Length - 1
                        Dim sFileId As String = Request.Form.GetValues("chkDelete")(i)

                        Try
                            Dim cm As New SqlCommand("delete AppFile where FileId = @FileId", cn)
                            cm.Parameters.Add("@FileId", Data.SqlDbType.Int).Value = sFileId
                            cm.ExecuteNonQuery()
                        Catch ex As Exception
                            'Ignore error
                        End Try
                    Next
                End If

            Else
                Dim sJson As String = ""

                'Upload Files
                For i As Integer = 0 To Request.Files.Count - 1
                    Dim oFile As System.Web.HttpPostedFile = Request.Files(i)
                    Dim sFileName As String = System.IO.Path.GetFileName(oFile.FileName)

                    If sFileName <> "" Then
                        Dim iFileSize As Integer = oFile.ContentLength
                        Dim oData(iFileSize) As Byte
                        Dim oStream As System.IO.Stream = oFile.InputStream
                        oStream.Read(oData, 0, iFileSize)
                        oStream.Close()

                        Dim sSql As String = "INSERT INTO AppFile _
                          (FileData, FileName, FileContentType, FileSize) " & _
                          " Values(@FileData, @FileName, @FileContentType, @FileSize); _
                          SELECT @@IDENTITY"

                        Dim cm As New SqlCommand(sSql, cn)
                        cm.Parameters.Add("@FileData", _
                               Data.SqlDbType.Binary, iFileSize).Value = oData
                        cm.Parameters.Add("@FileName", Data.SqlDbType.NVarChar).Value = sFileName
                        cm.Parameters.Add("@FileContentType", Data.SqlDbType.NVarChar).Value = _
                                       oFile.ContentType
                        cm.Parameters.Add("@FileSize", Data.SqlDbType.Int).Value = iFileSize
                        Dim sFileId As String = cm.ExecuteScalar()

                        sJson += "oUploadedFiles.push({fileId: " & sFileId & ", _
                               name: """ & sFileName & """, size: " & iFileSize & "});"
                    End If
                Next

                If Request.Form("btnUpload") = "" Then
                    Response.Write(sJson)
                    Response.End()
                End If
            End If

            cn.Close()
        End If

        SetupDatabase()
    End Sub

    Private Sub SetupDatabase()
        Dim cn As New System.Data.SqlClient.SqlConnection(GetConnectionString())
        cn.Open()

        Dim cm As New SqlCommand("select count(*) from INFORMATION_SCHEMA.TABLES _
                      where TABLE_NAME = 'AppFile'", cn)
        If cm.ExecuteScalar() = "1" Then
            'Table already exists
            cn.Close()
            Exit Sub
        End If

        Dim sSql As String = System.Configuration.ConfigurationManager.AppSettings("TableCreate")
        cm = New SqlCommand(sSql, cn)
        cm.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub ShowFiles()

        Dim sSql As String = "SELECT FileId, FileName, FileSize, DateCreated from AppFile"

        Dim sSort As String = Request.QueryString("sort") & ""
        If sSort = "FileName" OrElse sSort = "FileSize" OrElse sSort = "DateCreated" Then
            sSql += " ORDER BY " & sSort
        End If

        Dim cn As New SqlConnection(GetConnectionString())
        cn.Open()
        Dim ad As SqlDataAdapter = New SqlDataAdapter(sSql, cn)
        Dim ds As Data.DataSet = New Data.DataSet
        ad.Fill(ds)
        cn.Close()
        Dim oTable As Data.DataTable = ds.Tables(0)

        If oTable.Rows.Count = 0 Then
            Exit Sub
        End If

        Response.Write("<table id='tbServer' _
               class='StatusTable' border=1 cellspacing=0 cellpadding=3>")
        Response.Write("<tr id=trHeader>")
        Response.Write("<th><a href='?sort=FileName'>File name</a></th>")
        Response.Write("<th><a href='?sort=FileSize'>Size</a></th>")
        Response.Write("<th><a href='?sort=DateCreated'>Date Modified</a></th>")
        Response.Write("<th><label><input type=checkbox name=chkDeleteAll _
                  onclick='DeleteAll(this)'>Delete</label></th></tr>")

        For i As Integer = 0 To oTable.Rows.Count - 1
            Dim sFileId As String = oTable.Rows(i)("FileId")
            Dim sFileName As String = oTable.Rows(i)("FileName")
            Dim iFileSize As Integer = oTable.Rows(i)("FileSize")
            Dim dDateCreated As DateTime = oTable.Rows(i)("DateCreated")

            Dim sSize As String = FormatNumber((iFileSize / 1024), 0)
            If sSize = "0" AndAlso iFileSize > 0 Then sSize = "1"

            Response.Write("<tr>")
            Response.Write("<td><a href=""Download.aspx?id=" & sFileId & _
                         """ target='_blank'>" & sFileName + "</a></td>")
            Response.Write("<td>" & sSize & " KB</td>")
            Response.Write("<td>" & dDateCreated.ToShortDateString() & _
                         " " & dDateCreated.ToShortTimeString() & "</td>")
            Response.Write("<td><input type=checkbox name=chkDelete _
            value=""" & sFileId & """>")
            Response.Write("</tr>")
        Next

        Response.Write("</table>")
    End Sub

    Private Function GetConnectionString() As String
        Return System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
    End Function

History

  • 11th October, 2016: Initial version