Database Updater Client

This section explains how to implement the Example Entry/Exit Recipes in a database updater client.

The following screenshot shows what you get when the database updater client is running.

The database client requires an SQL Server database to connect to. It creates a table called RecipeTags, which keeps track of the relation between recipes (identified by their pair of identifiers) and tags.

By opening the Server Explorer window in Visual Studio, you can view the contents of the RecipeTags table.

The code for this example shows how to minimize the changes made to the database if the connection is broken and then recovered. When a session is broken (for example when the TCP connection is lost), the whole state is sent from the reader to the client. In such cases, do not delete everything from the table at session start, and then build up the session state again; if there are triggers on the database table, this could cause spurious events.

The following example shows how to deal with the session start dump by pulling out a cache of the initial database state, and then calculating and enacting a minimal set of changes to make to the table at session start time.

#region Event handler and session start logic
// All the recipe/tag rows stored in the database at the start of the 
// session, which must be removed at the next keepalive message.
// If we were expecting large numbers of tags we would use some kind
// of set structure to represent the tags matching each recipe, but
// in this case we just stick with the list where find is O(N).
Dictionary<uint, LinkedList<string>> SessionDumpCache = null;

/// <summary>
/// True iff we are between the session start and the first keepalive.
/// </summary>
bool ProcessingSessionDump
{
    get { return SessionDumpCache != null; }
}

/// <summary>
/// When the session starts, store a local copy of the table.
/// </summary>
void AngleID_OnSessionStart()
{
    SessionDumpCache = new Dictionary<uint, LinkedList<string>>();
    using (SqlCommand command = new SqlCommand("SELECT * FROM RecipeTags", database))
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            ushort id1 = (ushort)reader.GetInt16(0);
            ushort id2 = (ushort)reader.GetInt16(1);
            string tag = reader.GetString(2);

            uint key = Key(id1, id2);
            if (!SessionDumpCache.ContainsKey(key))
                SessionDumpCache[key] = new LinkedList<string>();
            SessionDumpCache[key].AddLast(tag);
        }
    }
}

/// <summary>
/// When the first keepalive happens, remove all rows that have not been inserted
/// during the dump at the start of the session.
/// </summary>
void AngleID_OnKeepalive()
{
    if (ProcessingSessionDump)
    {
        foreach (var recipe in SessionDumpCache)
            foreach (var tag_id in recipe.Value)
            {
                ushort id1, id2;
                ExtractIdsFromKey(recipe.Key, out id1, out id2);
                DeleteRow(id1, id2, tag_id);
            }
        SessionDumpCache = null;
    }
}

/// <summary>
/// When a tag entry is detected, during the session dump phase at the
/// start of the session, if the tag is in the table then remove it from
/// the cache of rows otherwise insert it in the table.  Outside the
/// session dump phase, just insert the row into the table.
/// </summary>
/// <param name="id1">The recipe Id 1</param>
/// <param name="id2">The recipe Id 2</param>
/// <param name="tags">The list of tags</param>
void AngleID_OnEntry(ushort id1, ushort id2, List<ulong> tags)
{
    List<string> tag_ids = StringifyTagIds(tags);
    uint key = Key(id1,id2);

    LinkedList<string> existing_tags = null;
    if ((ProcessingSessionDump) && (SessionDumpCache.ContainsKey(key)))
        existing_tags = SessionDumpCache[key];
            
    foreach (var tag_id in tag_ids)
    {
        var node = existing_tags == null ? null : existing_tags.Find(tag_id);
        if (node == null)
            InsertRow(id1, id2, tag_id);
        else
            existing_tags.Remove(node);
    }
}

/// <summary>
/// When a tag exit is detected, during the session dump phase there's
/// no need to do anything (this will just be data that is telling users
/// that this empty recipe exists, and in this case we are not providing a 
/// way of representing recipes that don't match any tags).  Outside the
/// session dump phase, delete the row from the table.
/// </summary>
/// <param name="id1">The recipe Id 1</param>
/// <param name="id2">The recipe Id 2</param>
/// <param name="tags">The list of tags</param>
void AngleID_OnExit(ushort id1, ushort id2, List<ulong> tags)
{
    if (!ProcessingSessionDump)
    {
        List<string> tag_ids = StringifyTagIds(tags);
        foreach (var tag_id in tag_ids)
            DeleteRow(id1, id2, tag_id);
    }
}
#endregion