Import File Logging Application

05132017FileLogging

In this post, I will use T-SQL and Microsoft C# to create a application to log file metadata into a SQL Server table.

Often times when processing data into and between data systems you need away to determine if all the data that is in the file is added to your tables. I like to store file information in the database and use this information as a way to true-up the data, in my data systems.  When a file is stored on a file system it is stored with metadata that can provide very useful information. This information include file name, Creation time, Last Modified Time, File Path, File Size and you can all so get row count information.  Collecting this information requires you to create a table inside the database to store the file metadata. You will also need to create an application to pull the metadata information from the files system folder directories.

In the code below I used C# to create an application that will accept a directory file path string. This path will then be used to search all sub directories in the path and log details about the files contained in the folders  into a table.  The C# code below contains three method calls that will be used to log the file information into a table.

The ProcessFile method is used to get the file name, Create Time, Last Access Time, File Size in Bytes and Line Count.

These data points are then passed to the LogToDB method. This method creates a SQL Connection to the database and executes a query to insert the data into a table named FileLogDetail.

To configure this to run you must first create a table to store the file information. Execute the below code in SQL Server Management Studio.

CREATE TABLE [dbo].[FileLogDetail](
  [FileLogDetailID] [int] IDENTITY(1,1) NOT NULL,
  [FilePath] [varchar](5000) NULL,
  [FileName] [varchar](200) NULL,
  [FileCreateTime] [datetime] NULL,
  [FileLastAccessed] [datetime] NULL,
  [FileSize] [bigint] NULL,
  [LineCount] [int] NULL,
  [JobName] [varchar](100) NULL,
  [FileLogTime] [datetime] NULL DEFAULT (getutcdate())
) ON [PRIMARY]




After the Table is created you will then need to create a C# console application. The code below is the source code needed.

Note: You will need to change the LogToDB method to connect your your SQL Server DB.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using System.Data;


namespace DBAToolBelt
{
    class Program
    {
        static void Main(string[] args)
        {
            //string inputDir = args[0]; 
            string inputDir = @"C:\DataIn\";
            try
            { 
                ProcessDirectory(inputDir);
            }
            catch (Exception)
            {
                throw;
            }
                     
        }
        // Process all files in the directory passed in, recurse on any directories 
        // that are found, and process the files they contain.
        public static void ProcessDirectory(string targetDirectory)
        {
            // Process the list of files found in the directory.
            string[] fileEntries = Directory.GetFiles(targetDirectory);
            foreach (string fileName in fileEntries)
                ProcessFile(fileName);

            // Recurse into subdirectories of this directory.
            string[] subdirectoryEntries = Directory.GetDirectories(targetDirectory);
            foreach (string subdirectory in subdirectoryEntries)
                ProcessDirectory(subdirectory);
        }

        // Insert logic for processing found files here.
        public static void ProcessFile(string path)
        {
            FileInfo info = new FileInfo(path);
            DateTime createtime = info.CreationTime;
            DateTime LstAcessTime = info.LastAccessTime;
            long fieSize = info.Length;
            string fileName = info.Name;
            int lineCount = File.ReadAllLines(path).Count();

            LogToDB(path, fileName, createtime, LstAcessTime, fieSize, lineCount);

            Console.WriteLine("FilePath: " + path + "|CreateTime: " + createtime+ "|LastAccessTime: " + LstAcessTime +  "|fieSize: " + fieSize
                                + "|FileName: "+ fileName+ "|LineCount: "+ lineCount);

        }

        public static void LogToDB(string path, string fName, DateTime createT, DateTime LstTime, long fSize,  int lCount)
        {
            string Query = "insert into FileLogDetail(FilePath,FileName,FileCreateTime,FileLastAccessed,FileSize"
                          + ",LineCount) values('"+ path+ "','" + fName +"','"+ createT+ "','"+ LstTime+ "','"+ fSize+"','"+ lCount + "')";
            using (SqlConnection connection = new SqlConnection("server=localhost;" +   //Server Name 
                                                            "Trusted_Connection=yes;" +
                                                            "database=DBATools; " +     //Database Name
                                                            "connection timeout=30"))
            {
                using (SqlCommand command = new SqlCommand(Query, connection))
                {
                    command.Connection.Open();
                    command.ExecuteNonQuery();

                }                              
            }
                          
        }

    }
}

Conclusion

I created this solutions as a way to get detail information about the files on my file system. Although this is not the only way to achieve the goal logging information about files but it one way. I have found this code useful.

 


Leave a Reply

Your email address will not be published. Required fields are marked *