Update Count of File Downloads in Database in ASP.Net

Hi makumbi,

Ref:Upload and Download files from Folder (Directory) in ASP.Net using C# and VB.Net

I have used above article and created a Sample for you.

DataBase Table

CREATE TABLE [dbo].[FileDownloads](
	[FileName] [NVARCHAR](100) NOT NULL,
	[DownloadCount] [int] NOT NULL
) 

Stored Procedure 

CREATE PROCEDURE [FileDownloads_UpdateFileDownloadCount] 
	@FileName NVARCHAR(100)
	
AS
BEGIN
	SET NOCOUNT ON;
	IF NOT EXISTS(SELECT [FileName]		  
					FROM [dbo].[FileDownloads]
					WHERE [FileName] = @FileName)
	BEGIN
		INSERT INTO [dbo].[FileDownloads]
				([FileName]
				,[DownloadCount])
			VALUES
				(@FileName 
				,1)
	END
	ELSE	
	BEGIN
		UPDATE [dbo].[FileDownloads]
		SET[DownloadCount] = [DownloadCount] + 1
		WHERE [FileName] = @FileName
	END
END

HTML

<form id="form1" runat="server">
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="UploadFile" />
    <hr />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" EmptyDataText="No files uploaded">
        <Columns>
            <asp:BoundField DataField="Text" HeaderText="File Name" />
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:LinkButton ID="lnkDownload" Text="Download" CommandArgument='<%# Eval("Value") %>' runat="server" OnClick="DownloadFile"></asp:LinkButton>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:LinkButton ID="lnkDelete" Text="Delete" CommandArgument='<%# Eval("Value") %>' runat="server" OnClick="DeleteFile" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</form>

Namespaces

C#

using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void DownloadFile(object sender, EventArgs e)
{
    string filePath = (sender as LinkButton).CommandArgument;
    Response.ContentType = ContentType;
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(filePath));
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("FileDownloads_UpdateFileDownloadCount", con))
        {
            cmd.Parameters.AddWithValue("@FileName", Path.GetFileName(filePath));
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    Response.WriteFile(filePath);
    Response.End();
}

VB.Net

Protected Sub DownloadFile(ByVal sender As Object, ByVal e As EventArgs)
    Dim filePath As String = (TryCast(sender, LinkButton)).CommandArgument
    Response.ContentType = ContentType
    Response.AppendHeader("Content-Disposition", "attachment; filename=" & Path.GetFileName(filePath))
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand("FileDownloads_UpdateFileDownloadCount", con)
            cmd.Parameters.AddWithValue("@FileName", Path.GetFileName(filePath))
            cmd.CommandType = CommandType.StoredProcedure
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
    Response.WriteFile(filePath)
    Response.End()
End Sub

OutPut