private static bool CreateTable(SqlConnection connection, LogTableDefinition table) { try { using (SqlCommand create = DataLogSqlBuilder.BuildCreateTable(table)) { LogTrace($"SQL command: {create.CommandText}"); create.Connection = connection; create.ExecuteNonQuery(); } if (table.Any(n => n.Name == _sessionIdColumn)) { using (SqlCommand key = DataLogSqlBuilder.BuildForeignKey(_sessionSummaryTable, _sessionIdColumn, table.Name, _sessionIdColumn)) { LogTrace($"Building foreign key: {key.CommandText}"); key.Connection = connection; key.ExecuteNonQuery(); } } return(true); } catch (SqlException ex) { LogError($"Table {table.Name} could not be created: {ex.Message}"); return(false); } }
public DataLogDatabaseResult Insert(LogTableDefinition table, LogTableRecord record) { if (table == null) { throw new ArgumentNullException(nameof(table)); } if (record == null) { throw new ArgumentNullException(nameof(record)); } LogTrace($"INSERT {table.Name} (primary key = {record[table.PrimaryKey]})"); DataLogDatabaseResult result; using (SqlCommand insert = DataLogSqlBuilder.BuildInsert(table, record)) { result = new DataLogDatabaseResult(table.Name, insert); try { using (SqlConnection connection = new SqlConnection(_connectionString.ToString())) { connection.Open(); insert.Connection = connection; try { insert.ExecuteNonQuery(); } catch (SqlException ex) when(ex.Message.Contains("Invalid object")) { // Table doesn't exist - create it and try again LogDebug($"Generating new table {table.Name}"); if (CreateTable(connection, table)) { LogDebug($"Table {table.Name} created."); LogTrace($"Retrying INSERT {table.Name}"); insert.ExecuteNonQuery(); } } } } catch (Exception ex) { result.Error = ex.Message; } } if (!result.Success) { LogWarn($"INSERT {table.Name} failed: {result.Error}"); LogTrace($"SQL command: " + result.Command); LogTrace($"SQL parameters: {string.Join("; ", result.Parameters.Select(n => $"{n.Key}={n.Value}"))}"); } return(result); }
public DataLogDatabaseResult Update(LogTableDefinition table, LogTableRecord record) { if (table == null) { throw new ArgumentNullException(nameof(table)); } if (record == null) { throw new ArgumentNullException(nameof(record)); } LogTrace($"UPDATE {table.Name} (primary key = {record[table.PrimaryKey]})"); DataLogDatabaseResult result; using (SqlCommand update = DataLogSqlBuilder.BuildUpdate(table, record)) { result = new DataLogDatabaseResult(table.Name, update); try { using (SqlConnection connection = new SqlConnection(_connectionString.ToString())) { connection.Open(); update.Connection = connection; int affectedRows = update.ExecuteNonQuery(); if (affectedRows == 0) { result.Error = "No rows were affected."; } } } catch (Exception ex) { result.Error = ex.Message; } } if (!result.Success) { LogWarn($"UPDATE {table.Name} failed: {result.Error}"); LogTrace($"SQL command: " + result.Command); LogTrace($"SQL parameters: {string.Join("; ", result.Parameters.Select(n => $"{n.Key}={n.Value}"))}"); } return(result); }