Saturday, April 28, 2012

Fetch DLL information for storage (SQL Server)


Title says it all, following is the implementation I did some time back using SQL server. There are surely better implementations than this and will appreciate if you can give your feedback regarding this.

Objective:
To retrieve information of all DLLs in a specific location and store in database.

Reason:
There are many ways this information can be utilized:
  • Keep record of DLL information on a day to day basis
  • Use it to compare DLL file changes of different dates


I had a specific requirement of tracking DLLs but you can use it as per your requirement.

Implementation:
SQL table structure – following are the table columns in table

Table_1 – to store DLL infoamtion
  • UniqueIdentifier – You can use GUID for storing record at a specific instance
  • Name – DLL name
  • Version – DLL version (X.X.X.X)
  • ModifiedDt – Last modified date
  • FileSize – DLL file size


Table_2 – to store this instance details (this is something can be used to display in a list – 1 instance per import instance)
  • UniqueIdentifier – GUID (this should be same that stored in above table)
  • Date – Date of import
  • Time – Time of import
  • Product Version – something optional
  • User Name – User who performed
  • Computer Name – Network location which has been used for operation
  • Comment – User comment if any
  • Network Path – Location from where information has been retrieved


There are couple of salient features in the implementation I want to highlight. Our team is scattered across globe and need to have an implementation which is efficient
  • For storing all the file information in table individually in different rows executing only one SQL query (this reduced execution time to half). For adding multiple rows in a single query used “UNION ALL”.
  • There is a observation that using default time stamp lead to different time logged for same file accessed from different location. For that used TimeZoneInfo class object and set it to PST. Then before storing the date converted time in that time zone.


Implemented Code:


public void StoreDLLFileInfo()
{
    MainScreen Main = (MainScreen)Application.OpenForms["MainScreen"]; // Open form instance
    string sQueryLine = "", sFileSize = "";
    bool bFirstTime = false;
    // tbPath - text box which stored and display the path
    // tbComment - text box which stored and display user comment
    // SqlConnection SQLConn maintains the SQL connection instance for firing query
    if (SQLConn.State == ConnectionState.Closed)
        SQLConn.Open();
    if (Directory.Exists(tbPath.Text))// first basic validation to check directory
    {
        // Operation 1 - to add single entry of the import operation
        DateTime SysTime = DateTime.Now.Date;
        string[] sDate = SysTime.GetDateTimeFormats('s');
        string sTime11 = DateTime.Now.TimeOfDay.ToString();
        string sUID = Guid.NewGuid().ToString();
        try
        {
            // fire query to add single entry for the import instance
            SQLCommand = SQLConn.CreateCommand();
            SQLCommand.CommandText = "INSERT INTO Table_2 values ('" + sUID + "', CONVERT(datetime, '" + sDate[0] + "', 126), '" + sTime11.Substring(0, 8) + "', '" + "Product 1.0.0" + "', '" + SystemInformation.UserName.ToString() + " (" + SystemInformation.ComputerName.ToString() + ") - " + tbComment.Text + "', '" + tbPath.Text + "')";
            SQLCommand.ExecuteNonQuery();
        }
        catch (SqlException w)
        {
            MessageBox.Show(w.Message, "SQL Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
            return;
        }
        // Operation 2 - to store DLL information of all DDLs in the stated directory
        string sVersion = "", sName = "";
        DateTime dtWriteDate;
        if (SQLConn.State == ConnectionState.Closed)
            SQLConn.Open();

        DirectoryInfo DirInfo = new DirectoryInfo(tbPath.Text);
        FileVersionInfo FileVerInfo;
        TimeZoneInfo hwZone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");// assign specific time zone
        FileInfo[] FileDet = DirInfo.GetFiles("*.dll");// retrieving file information of DLLs only
        iFileCnt = FileDet.Count();
        iFlCntNow = 0;
        foreach (FileInfo File in FileDet)
        {
            FileVerInfo = FileVersionInfo.GetVersionInfo(File.FullName);
            sVersion = FileVerInfo.FileVersion;
            if (sVersion == null)
                sVersion = "N/A";
            sVersion.Replace(",", ".");// DLL version
            sName = File.Name;// DLL Name
            dtWriteDate = File.LastWriteTime;// DLL Last write time
            sFileSize = File.Length.ToString();// DLL file size
            if (bFirstTime)
            {
                sQueryLine += " UNION ALL ";
            }
            bFirstTime = true;
            dtWriteDate = TimeZoneInfo.ConvertTime(dtWriteDate, hwZone);
            sQueryLine += "SELECT '" + sUID + "', '" + sName.ToLower() + "', '" + sVersion.Replace(",", ".") + "', '" + dtWriteDate.GetDateTimeFormats('u')[0].Substring(0, 16) + "', '" + sFileSize + "'";
        }
        try
        {
            Main.Cursor = Cursors.WaitCursor;
            SQLCommand = SQLConn.CreateCommand();
            SQLCommand.CommandText = "INSERT INTO Table_1 (UniqueIdentifier, Name, Version, ModifiedDt, FileSize) " + sQueryLine;
            SQLCommand.ExecuteNonQuery();
            Main.Cursor = Cursors.Default;
        }
        catch (SqlException w)
        {
            MessageBox.Show(w.Message, "SQL Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
        }
    }
}

No comments:

Post a Comment