DataSet inside a class not updating asynchronously?

TL; DR

Sorry if the title of the question is misleading; this is a work in progress as I try to understand the essence of the problem.

I am making a call .Single( Row => ... ... )

against an extension .AsEnumerable( )

DataTable

that throws an exception. There are two rows in the table, which is not possible because looking at the SOURCE table (the one in the database from which this DataTable

MUST be populated) I only see one of those rows.

In an attempt to narrow down the problem, I nominate the primary key for the table, which is now impossible to generate an exception, saying that the primary key already exists in the table, it should not be so, because the table must have been created only .

Why is this happening?

Reading:

I was hoping that in the course of formulating this question, I would have come across a solution in one of these "peer talks / AHA cardboard cutouts"; not so much.

There is quite a bit of code here and I will explain how much I can achieve what I am trying to accomplish (I also apologize as I don’t know exactly what the correct terminology is for what I am trying to describe).

I am working on a project component that will effectively act as an intermediary between an online database and a desktop application. Since portability is a major concern, I make the component primarily event driven.

There are also many events going on that need to run concurrently; as such, I have tried to implement this component asynchronously ( async

and await

).

The program depends on a static class (which I created) that contains the number DataTables

.

Before posting my code, I must say that my initial suspicion is that multiple concurrent asynchronous tasks that are running in parallel "somehow bump into each other." However, I don't know if this is true.

I'll start where I think the problem is and work my way through the stack. I will try to do this in a depth-first manner for the sake of brevity. More details can be added later if needed.

Before getting on the trail, I want to post a method that is my main suspect:

/// <summary>
/// Asynchronously read tables for this SqlLibAsync instance.
/// </summary>
public async Task ReadTables( ) {
    this.DS = new DataSet( );
    using ( MySqlConnection MSQCon = new MySqlConnection( this.ConnectionString ) ) {
        await MSQCon.OpenAsync( );
        try {
            foreach ( MySqlDataAdapter Adapter in this.Adapters ) {
                Adapter.SelectCommand.Connection = MSQCon;
                await Adapter.FillAsync( this.DS, 
                     Adapter.TableMappings.Cast<DataTableMapping>()
                         .First( )
                         .SourceTable 
                ); //In fact, could the troubles be originating from this .Cast<>() call?
            }
        } catch ( Exception ex ) {
            ex.Report( );
        }
        await MSQCon.CloseAsync( );
    }
    if ( this.DS.Tables.Count == 0 )
        await this.ReadTables( );
}

      

        
        
        
      

    

This method gave me a bit of trouble and went through several iterations. I used to get an exception informing me that the connection was attempted while DataReader

already accessing a connection or something, which I think was because I was using multiple instances of this class to work asynchronously and (mostly, multiple connections from the same computer to the same database), so I went with this route, which seems to have dealt with this problem, but now I am getting this new problem. I believe that for some reason parallel asynchronous tasks and this method don't get along, but I'm missing out on knowing why.

The beginning of using a tree to use this method, in the context of which my problems arise, is here:

public static async Task Start( ) {
    /*I have omitted code that appears to be working properly. #1*/
    bool
        Pending = true;
    /*Another Code Omission. #2*/           
    while ( Session.Instance.Status != SessionStatus.FINALIZED && Session.UserID != 0 ) {
        List<Task> TList = new List<Task>();
        TList.Add( Session.Instance.PlayerCheck( ) );
        switch ( Session.Instance.Status ) {
            case SessionStatus.PENDING:
                if ( Pending ) {
                    Pending = false;
                    TList.Add( Session.OnSessionReport( ) );
                }
                break;      
        }
        /*Another Omission #3*/
        await Task.WhenAll( TList );
    }
    /*More omitted code*/
}

      

        
        
        
      

    

Let's start with Session.Instance

private static Session _Instance; // Private static reference to a session.

/// <summary>
/// Static reference to instance of the Session.
/// </summary>
public static Session Instance { get { return Session._Instance; } }

      

        
        
        
      

    

Next, we have a Status

class property Session

:

private SessionStatus Status { //Retrieve fresh session status.
    get {
        switch ( this.FreshRow.Field<string>( "status" ) ) {
            /*Translates the incoming string into an enum. #4*/
        }
    }
}

      

        
        
        
      

    

The property Status

refers to the FreshRow property:

private DataRow FreshRow { //Retrieves datarow from which fresh session information can be extracted. (Status, BuzzedInUser, etc).
    get {
        if ( this.SessionID == 0 )
            throw new ObjectDisposedException( "Session", "Illegal attempt to access disposed Session object." );
        return QatiTables.GameSessions.Fresh.AsEnumerable( ).Single( Row => Row.Field<UInt32>( "game_session_id" ).Equals( this.SessionID ) );
    }
    /*Session.SessionID is a simple property with a getter and a private setter.*/
}

      

        
        
        
      

    

Hence we have the property QatiTables.GameSessions

:

public static SqlTableAsync GameSessions {
    get {
        return QatiTables.Tables[1] ?? ( QatiTables.Tables[1] = new SqlTableAsync(
            "QAndATimeServer.txt",
            "game_sessions",
            "created_by = @param_val_0", //We have to grab all of these every time because the row status may change mid-game and we would lose it.
            new object[ ] { QatiTables.Users.Fresh.AsEnumerable( ).Single( ).Field<UInt32>( "user_id" ) } ) );
    }
    /*The referenced QatiTables.Tables[] is an array of SqlTableAsync objects.*/
}

      

        
        
        
      

    

This property uses the class SqlTableAsync

:

/// <summary>
/// SELECT * FROM [TABLE] WHERE [FILTER]
/// </summary>
/// <param name="ServerFile">FTP File containing database connection data.</param>
/// <param name="TableName">Table to retrieve.</param>
/// <param name="Filter">Query Filter</param>
/// <param name="Parameters">Parameters on which to filter.</param>
public SqlTableAsync( string ServerFile, string TableName, string Filter, object[ ] Parameters ) {
    this.TableLib = new SqlLibAsync( "QAndATimeServer.txt" );
    try { this.TableLib.GetTable( TableName, Filter, Parameters ).Wait( ); } catch ( Exception e ) {
        e.Report( );
        /*Report is a custom extension method for exceptions. #5*/
    }
    this.TableName = TableName;
}

      

        
        
        
      

    

Inside this constructor, we have a class SqlLibAsync

:

/// <summary>
/// Initialize a new SqlLib
/// </summary>
/// <param name="DatabaseConnection">Text File from which to read database connection parameters on File Server</param>
public SqlLibAsync( string DatabaseConnection ) { this.ConnectionFile = DatabaseConnection; }

/// <summary>
/// Set FTP file from which to read connection data.
/// </summary>
private string ConnectionFile {
    set {
         /*This populates local variables with information from a file read 
         directly from an FTP server. As such, I have omitted this code as 
         it functions as it should, and also as it contains some sensitive 
         bits of information.*/
    }
}

      

        
        
        
      

    

We have reached the end of our first branch! Hooray!

Coming back, our next reference method is a method SqlLibAsync.GetTable( )

:

/// <summary>
/// SELECT * FROM [TABLE] WHERE [FILTER]
/// </summary>
/// <param name="Table">Table Name</param>
/// <param name="Filter">Query Filter</param>
/// <param name="Parameters">Object of parameters with which to populate filter.</param>
public async Task GetTable( string Table, string Filter, object[ ] Parameters ) {
    await this.GetTables( new string[ ] { Table }, new string[ ] { Filter }, new object[ ][ ] { Parameters } );
}

      

        
        
        
      

    

This method refers to the SqlLibAsync.GetTables (...) method:

/// <summary>
/// Return Multiple Tables from a Data Set.
/// </summary>
/// <param name="Tables">Tables for which to query</param>
public async Task GetTables( string[ ] Tables, string[ ] Filters, object[ ][ ] Parameters ) {
    this.Adapters = new MySqlDataAdapter[Tables.Length];
    int FilterIndex;
    object[ ] ParameterSet = null;
    string Query = null, Filter = null;
    foreach ( string Table in Tables ) {
        FilterIndex = Tables.ToList( ).IndexOf( Table );
        Filter = Filters[FilterIndex];
        ParameterSet = Parameters[FilterIndex];
        Query = "SELECT * FROM " + Table + " WHERE ";
        if ( string.IsNullOrEmpty( Filter ) )
            Query += "1;";
        else
            Query += Filter + ";";
        MySqlDataAdapter Adapter = new MySqlDataAdapter( new MySqlCommand( Query ) { CommandType = CommandType.Text } );
        if ( ParameterSet != null )
            for ( int x = 0; x < ParameterSet.Length; x++ )
                Adapter.SelectCommand.Parameters.AddWithValue( "@param_val_" + x, ParameterSet[x] );
        Adapter.TableMappings.Add( Table, Table );
        this.Adapters[Tables.ToList( ).IndexOf( Table )] = Adapter;
    }
    await this.ReadTables( );
}

      

        
        
        
      

    

This method uses the first described method SqlLibAsync.ReadTables

. This brings us to the end of our second branch.

Now let's unbind the property QatiTables.GameSessions

to the property QatiTables.Users

:

public static SqlTableAsync Users {
    get {
        return QatiTables.Tables[0] ?? ( QatiTables.Tables[0] = new SqlTableAsync(
            "QAndATimeServer.txt",
            "users",
            "last_ip_address = @param_val_0 AND role IN (@param_val_1, @param_val_2) AND is_login = @param_val_3",
            new object[ ] { Methods.IPAddress, "admin", "entertainer", 1 } ) );
    }
}

      

        
        
        
      

    

This property refers to a static property Methods.IPAddress

, which I will omit as I feel the context in which it is used defines it enough.

This property also uses the class SqlTablesAsync

and its methods and properties that were described earlier.

This property also takes us to the end of another branch.

The next branch is a Fresh

class property SqlTableAsync

specified in a FreshRow

class property Session

:

/// <summary>
/// Get fresh table.
/// </summary>
public DataTable Fresh { get {
    try {
        this.TableLib.ReadTables( ).Wait( );
        return this.TableLib.Tables[this.TableName];
    } catch ( Exception EX ) {
        EX.Report( );
        return null;
    }
} }

      

        
        
        
      

    

This refers to a variable TableLib

; local class object SqlTableAsync

. It also refers to the method of ReadTables

this class (described earlier) as well as the property of Tables

this class:

/// <summary>
/// Get this SqlLibs table set.
/// </summary>
public DataTableCollection Tables { get { return this.DS.Tables; } }

      

        
        
        
      

    

This property refers to the class variable DS

, a DataSet

, described in the previous methods.

And finally, we have reached the end of our first main branch, the properties of the Status

class Session

and all associated classes, properties, methods and functions, which brings us to the next major branch: the UserID property of the Session class:

private static UInt32 UserID { // Static Logged In UserID
    get {
        IEnumerable<DataRow> Users = QatiTables.Users.Fresh.AsEnumerable( ); //To avoid multiple unnecessary queries.
        return Users.Count( ) == 1 ? Users.Single( ).Field<UInt32>( "User_ID" ) : 0;
    }
}

      

        
        
        
      

    

Fortunately, this branch ends quickly as it refers to a Users

class property QatiTables

detailed above. This ends the start of our initial While Loop in the Start method above.

The next branch is the PlayerCheck

class method Session

. Before we get to the meat of the method, the method itself is designed to frequently check the database and inform the program when players join and leave the game. This method may also be considered my second most likely suspect due to the cause of my problem.

private async Task PlayerCheck( ) {
    List<Task> TList = new List<Task>( );
    IEnumerable<Player> Candidates = ( await QatiTables.Players.FreshAsync( ) ).AsEnumerable(
        ).Select<DataRow, Player>( Row => new Player( Row.Field<UInt32>( "participant_id" ), this.SessionID ) );
    Candidates.Where( P =>
        !( PlayerStatus.BLOCKED | PlayerStatus.KICKED | PlayerStatus.QUIT | PlayerStatus.LOGOFF ).HasFlag( P.Status ) &&
        !this._Players.Contains( P ) ).ToList( ).ForEach( P => {
            this._Players.Add( P );
            TList.Add( Session.OnPlayerJoined( P ) );
        } );
    Candidates.Where( P =>
        ( PlayerStatus.BLOCKED | PlayerStatus.KICKED | PlayerStatus.QUIT | PlayerStatus.LOGOFF ).HasFlag( P.Status ) &&
        this._Players.Contains( P ) ).ToList( ).ForEach( P => {
            this._Players.Remove( P );
            TList.Add( Session.OnPlayerLeft( P ) );
        } );
    await Task.WhenAll( TList );
}

      

        
        
        
      

    

In this method, we first refer to a property of the Players

class QatiTables

:

public static SqlTableAsync Players {
    get {
        try {
            return QatiTables.Tables[7] ?? ( QatiTables.Tables[7] = new SqlTableAsync(
                "QAndATimeServer.txt",
                "session_participants",
                "session_id = @param_val_0",
                new object[ ] { QatiTables.GameSessions.Fresh.AsEnumerable( ).Where( Row =>
                !QatiTables.GameEndedFilter.Contains( Row.Field<string>( "status" ) )
                ).Single( ).Field<UInt32>( "game_session_id" ) } ) );
        } catch ( Exception ex ) {
            ex.Report( );
            return null;
        }
    }
}

      

        
        
        
      

    

This method refers to a class SqlTableAsync

that we are already familiar with. Going back, we see a method Session.PlayerCheck

that refers to a FresAsync( )

class function SqlTableAsync

:

/// <summary>
/// Get fresh table asynchronously.
/// </summary>
/// <returns>Refreshed Table</returns>
public async Task<DataTable> FreshAsync( ) {
    await this.TableLib.ReadTables( );
    return this.TableLib.Tables[this.TableName];
}

      

        
        
        
      

    

This method is identical to the property Fresh

only if it has been tagged with a keyword async

.

Returning to the method Session.PlayerCheck

, we see that the method fetches rows QatiTables.Players

into the collection of class objects Player

using the session_participant_id

data and SessionID

session class rows :

/// <summary>
/// Create new Player object.
/// </summary>
/// <param name="PlayerID">Player ID</param>
/// <param name="SessionID">ID of session player is in.</param>
public Player( UInt32 PlayerID, UInt32 SessionID ) {
    this.PlayerID = PlayerID;
    this.SessionID = SessionID;
}

      

        
        
        
      

    

Coming back, the part filters ours Candidates

IEnumerable<Player>

according to the criteria that they are present and active in the current game (Not BLOCKED

, KICKED

etc.) and that they are currently not counted in our current session. This uses the property Player.Status

...

    /// <summary>
    /// Get player status.
    /// </summary>
    public PlayerStatus Status {
        get {
            switch ( this.FreshRow.Field<string>( "status" ) ) {
                /*Converts the string into an appropriate player status*/
            }
        }
    }

      

        
        
        
      

    

... which uses the property Player.FreshRow

...

private DataRow FreshRow {//Retrieve a fresh row from which data may be extracted.
    get {
        if ( this.PlayerID == 0 || this.SessionID == 0 )
            throw new ObjectDisposedException( "Player", "Illegal attempt to access disposed Player object." );
        try {
            return QatiTables.Players.Fresh.AsEnumerable( ).Single( Row =>
                Row.Field<UInt32>( "participant_id" ).Equals( this.PlayerID ) &&
                Row.Field<UInt32>( "session_id" ).Equals( this.SessionID ) );
        } catch ( Exception EX ) {
            EX.Report( );
            return null;
        }
    }
}

      

        
        
        
      

    

... which actually comes from there an exception !!! Here Single( Row => ... ... )

throws an exception saying that there are TWO ROWS in this datasheet that match the criteria provided !!! WE DID IT!!! This property also refers to a property of a Fresh

property of a Players

class QatiTables

, an object of the class SqlTableAsync

with which we should be familiar.

Phew !!!

To be complete, a _Players

class reference value Session

is just a list of class objects Player

, and we refer to a static method as well Session.OnPlayerJoined( Player )

:

private static async Task OnPlayerJoined( Player P ) {
    if ( Session._PlayerJoined != null )
        await Task.Run( ( ) => Session._PlayerJoined( Session.Instance, P ) );
}

      

        
        
        
      

    

This method raises an event if it exists asynchronously. Nothing unusual here, at least I don't think so.

Thus, the first filter ends Candidates

IEnumerable

. The next filter does something very similar; it filters the players by the criteria they were removed from the game ( BLOCKED

, KICKED

etc.), and now they are counted, that is, in the player list for this session.

Some more information: the Player class implements the interface IEquatable<Player>

as follows:

public class Player : IDisposable, IEquatable<Player> {
    /*Code omitted*/
    /// <summary>
    /// Check if this player is equivalent to another player.
    /// </summary>
    /// <param name="other">Player with which to compare this player.</param>
    /// <returns>PlayerID.Equals(other.PlayerID)</returns>
    public bool Equals( Player other ) { return this.PlayerID.Equals( other.PlayerID ); }
}

      

        
        
        
      

    

This will take us to the end of the branch in the method PlayerCheck

and return to the method Start

. The only other branch left in the tree is a method Session.OnSessionReport( )

, which for all intents and purposes is identical to methods OnPlayerJoined

and OnPlayerLeft

: it invokes an asynchronous event handler if the event handler is not null.

Now that we have traversed the tree, the problem I ran into (as outlined above briefly) is this: when I call a property, a Player.Status

method Single( Row => ... ... )

on a property of FreshRow

that class throws an exception, telling me that there are multiple rows that match the criteria that I am filtering on. This is absurd because I am filtering on PRIMARY KEY

this data table to get this row. Nevertheless, at one time, when I was able to look at this table, it was enough, there were two, yes two, rows that met the criteria provided. However, when I looked at the table in the database from which this table gets its information, I only find one.

Why is this happening?

EDIT

To mitigate the problem, I'm going to implement in support of classes SqlTableAsync

and SqlLibAsync

for adding primary keys. This should throw an exception when DataTable

populated with multiple rows containing the same primary key.

For this purpose, I changed the property Fresh

and methods of the FreshAsync

class SqlTableAsync

to the following:

public DataTable Fresh { get {
    try {
        this.TableLib.ReadTables( ).Wait( );
        if (this.PrimaryKeyColumn != null)
            this.TableLib.Tables[TableName].Constraints.Add( "PrimaryKey",
            this.PrimaryKeyColumn.Select<string, DataColumn>( columnName => this.TableLib.Tables[TableName].Columns[columnName] ).ToArray( ),
            true );
        return this.TableLib.Tables[this.TableName];
    } catch ( Exception EX ) {
        EX.Report( );
        return null;
    }
} }

public async Task<DataTable> FreshAsync( ) {
    await this.TableLib.ReadTables( );
    if ( this.PrimaryKeyColumn != null )
        this.TableLib.Tables[TableName].Constraints.Add( "PrimaryKey",
            this.PrimaryKeyColumn.Select<string, DataColumn>( columnName => this.TableLib.Tables[TableName].Columns[columnName] ).ToArray( ),
            true );
    return this.TableLib.Tables[this.TableName];
}

      

        
        
        
      

    

Also; the class constructor SqlTableAsync

takes string[ ]

the names of the columns on which the primary key is to be based and assigns that value to a local variable PrimaryKeyColumn

string[ ]

. Now I am getting this new exception on the property QatiTables.Players.Fresh

, telling me that there is already a constraint in the table that matches that constraint.

, what

A method Fresh

must reset DataSet DS

in a class SqlLibAsync

when it calls a method of ReadTables

that class, which means that no keys should exist in the tables of this dataset as it only has been created because the method ReadTables

is called JUST PRIOR to try to assign this primary key.

I need coffee ...