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