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