//The core output of the GeneralOperations is a DataTable for use in writing to the SQL database and the CSV file
public static DataTable PerformOperations(int daysBack, string officeLocation, string officeDrivePath, string officeDbTableName, string csvSaveDirectory)
{
//Get the current date and the date from the number of days back
DateTime date = DateTime.Now;
DateTime startDate = DateTime.Now.AddDays(daysBack);
//Parse the date to use in formatting a date output
string year = DateTime.Today.Year.ToString();
string month = DateTime.Today.Month.ToString();
string day = DateTime.Today.Day.ToString();
//Prepare a new output log to record the events of the SLOG parsing
CreateOutputLog LogFile = new CreateOutputLog(officeLocation, startDate, date, officeDrivePath);
//Give some feedback in the console by stating which office is being evaluated
Console.WriteLine("Collecting " + officeLocation + " SLOG Files");
//Collect the project SLOG files
List <string> filesToCheck = GetAllRvtProjectSlogs(officeDrivePath, startDate, LogFile);
//When the file collection is done, give feedback that the SLOG Data collection is proceeding
Console.WriteLine("Collecting " + officeLocation + " SLOG Data");
//Pass the list of files to parse to the FillDataTable method and let it parse the SLOG
DataTable dataTable = FillDataTable(filesToCheck, LogFile.m_slogReadErrors);
//Create a SQL connection to prepare to write to the database
SqlConnection sqlConnection = DatabaseOperations.SqlOpenConnection(DatabaseOperations.adminDataSqlConnectionString);
//Provide feedback that the data is being written to the database
Console.WriteLine("Writing " + officeLocation + " SLOG Data to SQL Database");
//Then, write to the database
DatabaseOperations.SqlWriteDataTable(officeDbTableName, sqlConnection, dataTable, LogFile);
//When the database is written to, save the DataTable out to a CSV file
CreateCSVFromDataTable(dataTable, officeLocation + " SLOG FILES " + year + month + day, csvSaveDirectory);
//Last, create the output log and return the DataTable
LogFile.SetOutputLogData(officeLocation, startDate, date, officeDrivePath, LogFile.m_slogReadErrors, LogFile.m_newDbEntries, LogFile.m_existingDbEntries, LogFile.m_dbTableName, year, month, day);
return(dataTable);
}
//Use the table name to record who wrote to the SQL database
public static void SqlLogWriter(string writtenTableName)
{
try
{
//Open the SQL connection and get the existing tables
SqlConnection sqlConnection = DatabaseOperations.SqlOpenConnection(DatabaseOperations.adminDataSqlConnectionString);
DataTable dt = sqlConnection.GetSchema("Tables");
List <string> existingTables = new List <string>();
foreach (DataRow row in dt.Rows)
{
string tableName = (string)row[2];
existingTables.Add(tableName);
}
//If the table exists, add the username, table, and DateTime to the table
if (existingTables.Contains("BARevitTools_SQLWriterLog"))
{
string commandString = "INSERT INTO [BARevitTools_SQLWriterLog] (UserName, TableName, WriteDate) VALUES (@userName, @tableName, @dateTime)";
using (SqlCommand sqlInsert = new SqlCommand(commandString, sqlConnection))
{
sqlInsert.Parameters.AddWithValue("@userName", Environment.UserName);
sqlInsert.Parameters.AddWithValue("@tableName", writtenTableName);
sqlInsert.Parameters.AddWithValue("@dateTime", DateTime.Now);
sqlInsert.ExecuteNonQuery();
}
}
//Else, make a new table and add the values
else
{
SqlCommand sqlCreateTable = new SqlCommand("CREATE TABLE BARevitTools_SQLWriterLog (UserName varchar(255), TableName varchar(255), WriteDate datetime)", sqlConnection);
sqlCreateTable.ExecuteNonQuery();
string commandString = "INSERT INTO [BARevitTools_SQLWriterLog] (UserName, TableName, WriteDate) VALUES (@userName, @tableName, @dateTime)";
using (SqlCommand sqlInsert = new SqlCommand(commandString, sqlConnection))
{
sqlInsert.Parameters.AddWithValue("@userName", Environment.UserName);
sqlInsert.Parameters.AddWithValue("@tableName", writtenTableName);
sqlInsert.Parameters.AddWithValue("@dateTime", DateTime.Now);
sqlInsert.ExecuteNonQuery();
}
}
//Close the connection
DatabaseOperations.SqlCloseConnection(sqlConnection);
}
catch (Exception e) { Console.WriteLine(e.Message); }
}
