File Inventory and Statistics

• Download source code - 480 KB

Introduction

After several million years of human evolution, world population is estimated at 7 billions, but only a few decades of the first computer birth, today the average PC contains more than 1.5 million files. Accustomed of growing up owning a few things and toys, it is mystifying to know that my PC contains that many files; am I ever going to use or know them?

To tackle this mystic, I am showing a program to tally files in a directory, inventory the tally result, and store records in SQLite tables, and how I:

• Design and code file inventory and statistics
• Design a friendly user interface
• Fix bugs and improve previous application
• Practically use SQLite in the C# application
• Dynamically create a database and tables
• Save persistent data to a SQLite database
• Discuss some future enhancements and variety to design

Background

After I wrote File Inventory - A Hybrid Version Control System[^], I continued the thought on what summary information to disk drive or folder is useful to the user. To track every file is not a realistic goal and it might take too long to develop such a program.

To count the number of the files and directories and inventory the counting summary is easier to achieve. The file count lets the user know how many files have increased over a period of time. The user can get the count from a root drive or just a sub-directory. I recommend that the user try out a small sub-directory first until the user gets familiar with the program then try on the whole drive.

If you try on the whole drive, it might take 10 to 30 minutes or hours to finish.

I modified the program in my previous article to do the files and directories counting.

The emphasize is on a Friendlier User Interface, File Maintenance Interface, and Programming Execution Speed.

This is not a nice, complete running utility program. It requires more work to make it really useful. The program is a programming practice, it may serve as a demo to pitch a future project.

Prerequisites for following this article

As this is a variation of the File Inventory - A Hybrid Version Control System[^], the Prerequisites section of that article also applies. It is beneficial if you have read that article.

Bugs in the previous version

If you have read File Inventory - A Hybrid Version Control System[^] and downloaded the source, you might have noticed that the program behaving strangely if you run on a directory that contains more than 500 files. The program seems to hang and is not responsive for a while; this is caused by the batch program busily inserting records. Adding two three lines of the Thread calling just before calling the batch function will return the control immediately. I added the Thread code in this article.

It also has the bug of trying to delete the SQL script file after running the batch file. Therefore, in this release, I use a different script file for each batch command and do not delete it. For practice, limit to the small directory.

The following screenshot shows temporary SQL script files and a Maintenance tab to delete:

Programming a friendlier user interface

The first improvement is changing the TextBox to a ComboBox and adding a small icon-sized button in front of the ComboBox. This subtle change lets the user have the option to not key in the directory name, instead click on this small button to bring up the Directory Search dialog. The ComboBox also saves a history of the user’s selections. This feature is similar to Word saving the ten previously opened documents in a list, except this program has no such limit.

A second improvement is to replace the ListBox with a DataGrid to list inventory catalogs. This way, it can display the sums of the files along with each inventory catalog.

Third is to add a few functions into Tab3-maintenance. This tab displays all files the program created, and allows users to clear temporary SQL script files that the program generated as well as delete the Inventory.db SQLite database to allow a fresh restart.

Points of interest

The design is simple, it consists of a minimum of three tables. Map directory name to table name, Statistics summary table, and individual tables to store directory details. Following is the script to create tables and insert records.

--******** create inventory.db *********   
--sqlite3 inventory.db  setup.sql   
   
create table path_table_map(
    no INTEGER PRIMARY KEY,  
    dirpath varchar(500),
    tblname varchar(500));

create table statistic_page (
    no INTEGER PRIMARY KEY, 
    ckey      varchar(20), 
    dirpath   varchar(500),
    sumdir  INTEGER,
    sumfile INTEGER);

create table c_temp(
    no INTEGER PRIMARY KEY,
    ckey  varchar(20), 
    dirpath varchar(500), 
    sumfile integer);

insert into path_table_map (dirpath, tblname) values ('c:\temp', 'c_temp');
insert into statistic_page (ckey, dirpath, sumdir, sumfile) 
             values ('_2012 01/22 06:53:32', 'c:\temp', 5, 27);
insert into c_temp (ckey, dirpath, sumfile) 
             values ('_2012 01/22 08:13:43', 'c:\temp', 14);
insert into c_temp (ckey, dirpath, sumfile) 
             values ('_2012 01/22 08:13:43', 'c:\temp\6032', 2);

I adopted Using SQLite in your C# Application[^]. The LoadData function accesses the SQLite database and retrieves the data from the mains table and fills the DataSet.

private void LoadData() 
{ 
    SetConnection(); 
    sql_con.Open(); 
    sql_cmd = sql_con.CreateCommand(); 
    string CommandText = "select id, desc from mains"; 
    DB = new SQLiteDataAdapter(CommandText,sql_con); 
    DS.Reset(); 
    DB.Fill(DS); 
    DT= DS.Tables[0]; 
    Grid.DataSource = DT; 
    sql_con.Close(); 
}

I modified it slightly to pass in a SQL comment, so I don't have to duplicate code many times.

//passing a sql command
private void sqlQueryToTable(string CommandText)
{
    setConnection(a_dbname);
    asql_con.Open();
    asql_cmd = asql_con.CreateCommand();
    aDB = new SQLiteDataAdapter(CommandText, asql_con);
    asql_con.Close();
    aDS.Reset();
    aDB.Fill(aDS);
    aDT = aDS.Tables[0];
}

// calling module
private void getCatalog()
{
    string sql = "select ckey as 'catalog', sumdir as 'total_dir', " + 
                 "sumfile as 'total_file' from statistic_page where dirpath = '" + 
                 aDir + "' order by ckey desc;";
    sqlQueryToTable(sql);

    // assign result table to a datagrid
    if (aDT.Rows.Count > 0)
        a_table2 = aDT;
}

Code Debugging

I added a CheckBox to view the batch program running. The following screenshot displays an error that can be valuable in debugging. SQLite3 runs _2012_0122_0644_35_2.sql script on inventory.db:

The above error is caused by the following code:

// summary saved to statistic_page table
txtQuery = "insert into statistic_page values ('" + a_catalog + "', '" + 
           aDir + "', " + a_total_dir + ", " + a_total_file + ");";

The above code generates the following SQL script that is later saved to the file _2012_0122_0644_35_2.sql:

insert into statistic_page values ('_2012 01/22 08:13:43', 'c:\temp', 5, 27);

But the correct syntax should be as follows:

insert into statistic_page (ckey, dirpath, sumdir, sumfile) 
                          values ('_2012 01/22 08:13:43', 'c:\temp', 5, 27);

By providing a CheckBox we can view the batch program running in the foreground which can ease debugging. Saving the SQL statement to a temporary file allows the programmer to spot any errors instead of having to debug at run time.

Conclusion

This article documents how I continued to improve the program and add more functions to it. I have tried it on c:\users, a large directory which returns 22,000 files and it takes a minute or two to finish. My next task will focus on how to manage a large directory and how to display and flag differences between two logs.

Addendum

If you download the code, it is best you delete and recreate the batchjob.bat file (save the content of the bat file to a txt file before you delete it). Otherwise, you will get an annoying security confirmation prompt every time the program calls out to run the batch job. Windows has a security alert for batch files not originated from your own system and warns the user of the danger.

A reader requested to fix a bug and to add thread support. As I ran a test on my C drive, it had half a million files. To save the directories counter, 15,000 rows, into SQLite will need 10 minutes. While it is running, the user can move around the screen.

I thank reader comments and feedback; these are very valuable to improve the program.

References

• Using SQLite in your C# Application[^]
• msdn.microsoft.com FlowLayoutPanel[^]

History

• 22-Jan-2012 - First version.
• 4-Feb-2012 - Second version - Added threading support and fixed bugs.