Deleting older files after a specified number of days using VB.NET

If You want to Temp or your specific Export file History maintain folder to delete file after some days then this function resolve your problem.

Calling Way:

First Way : 

Dim DeleteInfo As Long()

DeleteInfo = DeleteFilesXDaysOld("c:\temp\", "*.*", 60)

Console.Writeline(DeleteInfo(0) & " file(s) deleted.")
Console.Writeline("Freeing " & DeleteInfo(1) & " bytes.")


Second Way:

Imports System.IO

 Dim DeleteInfo As Long()
 If My.Computer.FileSystem.DirectoryExists(Path.GetTempPath & "\Export\Invoice") = True Then
 DeleteInfo = DeleteFilesXDaysOld(Path.GetTempPath & "\Export\Invoice", "*.*", 2)
 End If

Check Directory Exist or Not in Vb.Net else create

Dim GLFilePath as string = Path.GetTempPath & "\Export\Invoice"

 If My.Computer.FileSystem.DirectoryExists(Path.GetTempPath & "\Export\Invoice") = False Then
 My.Computer.FileSystem.CreateDirectory(Path.GetTempPath & "\Export\Invoice")
 End If

Function Delete Files:

Private Function DeleteFilesXDaysOld( _ ByVal Path As String, _ Optional ByVal Pattern As String = "*.*", _ Optional ByVal Days As Integer = 365) As Long() ' ReturnValues is an array which holds 2 values ' The first being the count of files deleted ' The second being the amount of space freed Dim ReturnValues = New Long() {0,0} Dim File As String Dim FileInfo As System.IO.FileInfo Dim Files() As String = _ System.IO.Directory.GetFiles(Path, Pattern) Try For Each File In Files FileInfo = New System.IO.FileInfo(File) If FileInfo.LastWriteTime < _ Today.AddDays(0 - Days) Then ReturnValues(0) += 1 ReturnValues(1) += FileInfo.Length() FileInfo.Delete() End If Next Return ReturnValues Catch exn As Exception MsgBox(exn.ToString, _ MsgBoxStyle.Critical Or _ MsgBoxStyle.OkOnly Or _ MsgBoxStyle.SystemModal, _ "Error") Return ReturnValues End Try End Function

 

group by sequence number MySQL

Before you use below sample code i prefer you use this URL code which better for you.

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

 

SAMPLE DataTable

CREATE TABLE CountryCity
 (
 city VARCHAR(100),
 country VARCHAR(100),
 population int
 );

INSERT INTO CountryCity VALUES ('Lahore', 'Pakistan',8175133); 
 INSERT INTO CountryCity VALUES ('Multan', 'Pakistan',3792621); 
 INSERT INTO CountryCity VALUES ('Liquat Pur', 'Pakistan',2695598); 
 
 INSERT INTO CountryCity VALUES ('Kalkata', 'India',2181000);
 INSERT INTO CountryCity VALUES ('Dehli', 'India',808000);
 INSERT INTO CountryCity VALUES ('Goya', 'India',422000);
 
 INSERT INTO CountryCity VALUES ('Riyadh', 'KSA',7825300);
 INSERT INTO CountryCity VALUES ('Madina', 'KSA',1016800);
 INSERT INTO CountryCity VALUES ('Makkah', 'KSA',770800);

MySQL Query to Get Top 2

To get the 2 largest Country cities for each country, you can use the following query in MySQL:
SELECT city, country, population
FROM
(SELECT city, country, population,
@country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM CountryCity
ORDER BY country, population DESC
) ranked
WHERE country_rank <= 2;

 

The query returns:

 

city	country	population
Kalkata	India	2181000
Dehli	India	808000
Riyadh	KSA	7825300
Madina	KSA	1016800
Lahore	Pakistan	8175133
Multan	Pakistan	3792621

 

How It Works

Explanation of the MySQL query:

• Session Variables

Currently MySQL does not support ROW_NUMBER() function that can assign a sequence number within a group, but as a workaround we can use MySQL session variables.

These variables do not require declaration, and can be used in a query to do calculations and to store intermediate results.

@current_country := country

This code is executed for each row and stores the value of country column to @current_country variable.

@country_rank := IF(@current_country = country, @country_rank + 1, 1)

In this code, if @current_country is the same we increment rank, otherwise set it to 1. For the first row@current_country is NULL, so rank is also set to 1.

For correct ranking, we need to have ORDER BY country, population DESC

So if we just execute the subquery:

   SELECT city, country, population, 
       @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
       @current_country := country 
  FROM Countrycities
  ORDER BY country, population DESC

We get the list of cities ranked by population within the country:

city	country	population	country_rank	current_country
Kalkata	India	2181000	1	India
Dehli	India	808000	2	India
Goya	India	422000	3	India
riyadh	KSA	7825300	1	KSA
Madina	KSA	1016800	2	KSA
Makkah	KSA	770800	3	KSA
Lahore	Pakistan	8175133	1	Pakistan
Multan	Pakistan	3792621	2	Pakistan
Liquat Pur	Pakistan	2695598	3	Pakistan

• Selecting Range

When we have a rank assigned to each city within its country, we can retrieve the required range:

   -- Get top 2 for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank <= 2;
 
   -- Get the city with 3rd population for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank = 3;

ROW_NUMBER() – Oracle, SQL Server and PostgreSQL

In Oracle, SQL Server and PostgreSQL you can achieve the same functionality using ROW_NUMBER function:

   SELECT city, country, population
   FROM
    (SELECT city, country, population, 
                  ROW_NUMBER() OVER (PARTITION BY country ORDER BY population DESC) as country_rank
      FROM cities) ranked
   WHERE country_rank <= 2;

This query works in Oracle, SQL Server and PostgreSQL without any changes and returns:

city	country	population
Kalkata	India	2181000
Dehli	India	808000
Riyadh	KSA	7825300
Madina	KSA	1016800
Lahore	Pakistan	8175133
Multan	Pakistan	3792621