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);
}
}
}
