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