/// <summary>
/// Updated a previously recorded entry
/// </summary>
public void Update(Entry entry)
{
//Sync the schema of the entry with the database and cache
SyncTable(entry);
//Update the entry based on a matching Key value
UpdateEntry(entry);
}
/// <summary>
/// Insert a new entry
/// </summary>
public void Insert(Entry entry)
{
//Sync the schema of the entry with the database and cache
SyncTable(entry);
//Insert the new entry into the database
InsertEntry(entry);
}
/// <summary>
/// Syncronizes the database and cache with any new tables or columns based on entry data
/// </summary>
private void SyncTable(Entry entry)
{
Table table;
//Attempt to find the cached fields for the table
if (!Tables.TryGetValue(entry.Table, out table))
{
//Create the table in the database
CreateTable(entry.Table);
//Cache the table
table = Tables.GetOrAdd(entry.Table, new Table(entry.Table));
}
//Check if there are any new columns on the entry which the cache doesn't have
var newColumns = entry.Data.Where(d => !table.Columns.Contains(d.Key)).Select(c => c.Key).ToList();
if (newColumns.Any())
{
//Alter the existing table with any new fields that are found and add to cache
AlterTable(entry.Table, newColumns);
//Cache the columns
newColumns.ForEach((c) => table.Columns.Add(c));
}
}
/// <summary>
/// Updates a previously recorded entry using the Key value as the identifier
/// </summary>
private void UpdateEntry(Entry entry)
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
string[] parameters = entry.Data.Select(d => string.Format("[{0}] = @{1}", d.Key, d.Key)).ToArray();
string query = string.Format(@"UPDATE [{0}].[{1}] SET [UpdatedDate] = @UpdatedDate,{2} WHERE [Key] = @Key;", this.Schema, entry.Table, string.Join(",", parameters));
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(new SqlParameter("@UpdatedDate", DateTime.UtcNow));
cmd.Parameters.Add(new SqlParameter("@Key", entry.Key));
cmd.Parameters.AddRange(entry.Data.Select(d => new SqlParameter("@" + d.Key, d.Value)).ToArray());
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Inserts a new entry into an existing table
/// </summary>
private void InsertEntry(Entry entry)
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
string[] columnNames = entry.Data.Select(d => "[" + d.Key + "]").ToArray();
string[] dataParameters = entry.Data.Select(d => "@" + d.Key).ToArray();
string query = string.Format(@"INSERT INTO [{0}].[{1}] ([Key],[CreatedDate],{2}) VALUES (@Key,@CreatedDate,{3});", this.Schema, entry.Table, string.Join(",", columnNames), string.Join(",", dataParameters));
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(new SqlParameter("@Key", entry.Key));
cmd.Parameters.Add(new SqlParameter("@CreatedDate", DateTime.UtcNow));
cmd.Parameters.AddRange(entry.Data.Select(d => new SqlParameter("@" + d.Key, d.Value)).ToArray());
cmd.ExecuteNonQuery();
}
}