static void Main(string[] args)
{
//Search # days back from the date of running to try to catch any project whose SLOG file was inaccessible
int daysBack = -Properties.Settings.Default.DaysBack;
//The output folder is where the CSV files will be stored
string CsvOutputFolder = DpaSlogParser.Properties.Settings.Default.CsvOutputFolder;
//For each office, a DataTable from the GeneralOperations class is created, taking in the parameters for the office and its project drive
DataTable dpaCollection2017 = GeneralOperations.PerformOperations(daysBack, "DPA2017", Properties.Settings.Default.DpaDrivePath2017, "BIM_Slog_Data", Properties.Settings.Default.CsvOutputFolder);
DataTable dpaCollection2018 = GeneralOperations.PerformOperations(daysBack, "DPA2018", Properties.Settings.Default.DpaDrivePath2018, "BIM_Slog_Data", Properties.Settings.Default.CsvOutputFolder);
DataTable dpaCollection2019 = GeneralOperations.PerformOperations(daysBack, "DPA2019", Properties.Settings.Default.DpaDrivePath2019, "BIM_Slog_Data", Properties.Settings.Default.CsvOutputFolder);
}
//This method makes a CSV file from a DataTable by writing to a file the string obtained from the BuildCSVFromDataTable StringBuilder
public static void CreateCSVFromDataTable(DataTable dt, string exportName, string exportDirectory)
{
string exportPath = exportDirectory + @"\" + exportName + ".csv";
if (File.Exists(exportPath))
{
File.Delete(exportPath);
}
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTable(dt);
File.WriteAllText(exportPath, sb.ToString());
}
//Write a DataTable to the SQL server
public static void SqlWriteDataTable(string tableName, SqlConnection sqlConnection, DataTable dataTable, CreateOutputLog log)
{
//Collect the list of tables from the SQL server
DataTable dt = sqlConnection.GetSchema("Tables");
List <string> existingTables = new List <string>();
foreach (DataRow row in dt.Rows)
{
string existingTableName = (string)row[2];
existingTables.Add(existingTableName);
}
//Check if the SQL server already has a table with the same name
if (existingTables.Contains(tableName))
{
//Record in the log the database table name
log.m_dbTableName = tableName;
using (sqlConnection)
{
//Report in the console that the data is going to be copied
Console.WriteLine("Copying Data To SQL Table");
//Cycle through each row in the DataTable
foreach (DataRow row in dataTable.Rows)
{
bool skip = false;
//Determing if the table already has a row with the SessionId value. If so, add it to the log as an existing entry and set the skip boolean to true because we don't want duplicate entries
using (SqlCommand command = new SqlCommand("SELECT COUNT (*) FROM " + tableName + " WHERE SessionId LIKE '" + row["SessionId"] + "'", sqlConnection))
{
try
{
Int32 count = Convert.ToInt32(command.ExecuteScalar());
if (count > 0)
{
skip = true;
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTableRow(dataTable, row);
log.m_existingDbEntries.Add(sb.ToString());
}
}
catch { continue; }
}
//Assuming this is a new table entry, continue
if (skip == false)
{
//Specify an entry into the database table by adding the values from the DataTable
using (SqlCommand comm = new SqlCommand("INSERT INTO " + tableName + " (SessionId, ProjectNumber, FileName, FilePath, UserName, Build, Journal, Host, Server, Central, Local, DateTimeStart, DateTimeEnd, Duration) VALUES (@v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14)"))
{
comm.Connection = sqlConnection;
comm.Parameters.AddWithValue("@v1", row["SessionId"]);
comm.Parameters.AddWithValue("@v2", row["ProjectNumber"]);
comm.Parameters.AddWithValue("@v3", row["FileName"]);
comm.Parameters.AddWithValue("@v4", row["FilePath"]);
comm.Parameters.AddWithValue("@v5", row["UserName"]);
comm.Parameters.AddWithValue("@v6", row["Build"]);
comm.Parameters.AddWithValue("@v7", row["Journal"]);
comm.Parameters.AddWithValue("@v8", row["Host"]);
comm.Parameters.AddWithValue("@v9", row["Server"]);
comm.Parameters.AddWithValue("@v10", row["Central"]);
comm.Parameters.AddWithValue("@v11", row["Local"]);
comm.Parameters.AddWithValue("@v12", row["DateTimeStart"]);
comm.Parameters.AddWithValue("@v13", row["DateTimeEnd"]);
comm.Parameters.AddWithValue("@v14", row["Duration"]);
try
{
//Execute the command to insert the row into the database table and add the row to the log for new entries
comm.ExecuteNonQuery();
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTableRow(dataTable, row);
log.m_newDbEntries.Add(sb.ToString());
}
catch (SqlException e)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
}
}
}
SqlCloseConnection(sqlConnection);
}
//If the database table does not yet exist, create a new table using the SqlBulkCopy
else
{
log.m_dbTableName = tableName;
Console.WriteLine("Creating New SQL Table");
try
{
//Create a new SQL table with the specified headers and data types
SqlCommand sqlCreateTable = new SqlCommand("CREATE TABLE " + tableName + " (SessionId text, ProjectNumber text, FileName text, FilePath text, UserName text, Build text, Journal text, Host text, Server text, Central text, Local text, DateTimeStart datetime, DateTimeEnd datetime, Duration float)", sqlConnection);
sqlCreateTable.ExecuteNonQuery();
}
catch (SqlException f)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(f.Message);
Console.ReadLine();
}
//Try to simply do a bulk copy to the table
try
{
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default;
Console.WriteLine("Copying Data To SQL Table");
using (SqlBulkCopy s = new SqlBulkCopy(sqlConnection, options, null))
{
//Specify the destination table and map each column from the DataTable to the database table columns
s.DestinationTableName = "[" + tableName + "]";
foreach (DataColumn appColumn in dataTable.Columns)
{
s.ColumnMappings.Add(appColumn.ToString(), appColumn.ToString());
}
s.WriteToServer(dataTable);
}
//Build a string of the new table and write it to the log as new data entries
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTable(dt);
log.m_newDbEntries.Add(sb.ToString());
}
catch (SqlException g)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(g.Message);
Console.ReadLine();
}
}
//Close the connection and write the log of the user actions to the SQL table for tracking data writes
DatabaseOperations.SqlCloseConnection(sqlConnection);
SqlLogWriter(tableName);
}