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