League/Game Association in API

[elcray]

Is there some way of getting what leagues a game is associated with via the API. I have all of the gamedetail imported, but need to know which leagues each game belongs to.

[FTMSupport]

If you are talking about the Team Website API, you can use the "Get Leagues" call to get a list of all leagues, then when calling the "Get Games" call, you can pass the lg= parameter to filter to only games for a specific league.

[elcray]

So if the league filter is the only way to get the league/game association, here's the pseudocode that I've come up with so far. The only thing I don't like is that every time I do an import, I will have to delete all data from both reference tables. Not very clean, and will get slower and slower as the season progresses due to data volume. But it is the only way to account for changes to leagues and their respective association to current and past games.

Does this look sound? :

1.) Delete existing data from "tblLeague" table
a.) Table Structure:
a1.) "LID" = nvarchar(125) (PK, No Null)
a2.) "name" = nvarchar(125) (PK, No Null)

2.) Delete existing data from "tblLeagueGameXref" table
a.) Table Structure:
a1.) "GameID" = nvarchar(125) (PK, No Null)
a2.) "LID" = nvarchar(125) (PK, No Null)

3.) Begin Parsing process:
a.) For each "<LEAGUE guid..." in "leagues.php"
http://api.iscoresports.com/teamwebsite ... EAM&p=PASS" onclick="window.open(this.href);return false;
a1.) insert guid, name in "tblLeague" table
a2.) For each "<GAME guid..." in "games.php" with league filter
http://api.iscoresports.com/teamwebsite ... &lg=LEAGUE" onclick="window.open(this.href);return false;
a2-1.) Insert GameID, LID in "tblLeagueGameXref" table
a3.) End Loop
b.) End Loop

[CSThunderCoach]

That is the way I am doing it. Since there is no API that provides a list of leagues for a game you have to build your own xRef by fetching the games for each league. Obviously, there will some overlap (such as the "ALL" league and season leagues) but that is what you are trying to find anyway.

My only comment would be to remove the (PK, no null) from your tblLeague "name" column. By its definition the GUID will always be unique - no need to store the extra index on the name as well.

To prevent unnecessary deletes/adds from/to the database (since that space is not automatically recaptured) I also use an "exists flag" on league and game data so I am not truncating the entire table each time. I will clear the flag (using update...set), compare the XML data to my tables (update if required) and set the flag. Once complete you delete any record that does not have the flag set. Then the only delete that happens in the database is the delete that happens on the device.

[elcray]

Yes, the PK on the name field was a copy/paste error. Thanks for the suggestion.

[elcray]

The process proposed above is now complete. It actually is much less of a server hit than I expected. It can clear out the 2 tables and import the XML data in less than a full second. So adding this to the import automation shouldn't cause any performance issues. Here's the VB code for those who are interested. It expects the following variables:

strTeam = your API team name
strAPIPass = Your API password
strConnectionString = The connection string to your database (expects 2 tables as defined above)

Code: Select all

Protected Sub cmdLeague_Click(sender As Object, e As System.EventArgs) Handles cmdLeague.Click
        Dim xmlDocLeague As New XmlDocument
        Dim strXMLFile As String = "http://api.iscoresports.com/teamwebsite/leagues.php?s=baseball&t=" & strTeam & "&p=" & strAPIPass
        Dim strLID As String
        xmlDocLeague.Load(strXMLFile)

        Dim xmlDocLeagueGameXref As New XmlDocument

        txtError.Text = "Started import League Data: " & Now & vbCrLf & txtError.Text

        setDeleteAllByTable("tblLeague")
        setDeleteAllByTable("tblLeagueGameXref")

        Dim node As XmlNode = xmlDocLeague.SelectSingleNode("/LEAGUES")
        For Each RootLevel As XmlNode In node.ChildNodes
            Select Case RootLevel.Name
                Case Is = "LEAGUE"
                    strLID = RootLevel.Attributes("guid").Value
                    setInsertLeague(strLID, RootLevel.Attributes("name").Value)

                    xmlDocLeagueGameXref.Load("http://api.iscoresports.com/teamwebsite/games.php?s=baseball&t=" & strTeam & "&p=" & strAPIPass & "&lg=" & strLID)
                    Dim node2 As XmlNode = xmlDocLeagueGameXref.SelectSingleNode("/TEAM")
                    For Each RootLevel2 As XmlNode In node2.ChildNodes
                        Select Case RootLevel2.Name
                            Case Is = "GAME"
                                setInsertLeagueXREF(RootLevel2.Attributes("guid").Value, strLID)
                        End Select
                    Next
            End Select
        Next

        txtError.Text = "Ended import League Data: " & Now & vbCrLf & txtError.Text
    End Sub

Here's the "setDeleteAllByTable" Function:

Code: Select all

Private Function setDeleteAllByTable(ByVal strTableName As String) As Boolean

        Try
            Using mConn As New OleDb.OleDbConnection(strConnectionString)
                Using cmdDelete As OleDb.OleDbCommand = mConn.CreateCommand
                    cmdDelete.CommandText = "DELETE FROM " & strTableName & ";"

                    mConn.Open()
                    cmdDelete.ExecuteNonQuery()
                    mConn.Close()

                End Using
            End Using
            txtError.Text = "Successfully Deleted Data (" & strTableName & "): " & Now & vbCrLf & txtError.Text
            Return True
        Catch ex As Exception
            txtError.Text = "ERROR (DeleteDataByGameID: " & strTableName & ") - " & ex.Message & ": " & Now & vbCrLf & txtError.Text
            Return False
        End Try
    End Function

Here's the "setInsertLeague" Function:

Code: Select all

Private Function setInsertLeague(ByVal LID As String, LName As String) As Boolean

        Try
            Using mConn As New OleDb.OleDbConnection(strConnectionString)
                Using insertCommand As OleDb.OleDbCommand = mConn.CreateCommand
                    insertCommand.CommandText = "INSERT INTO tblLeague (LID, Name) " & _
                        "Values (?, ?)"

                    Dim parLID As New OleDb.OleDbParameter("@LID", OleDb.OleDbType.VarWChar, 125)
                    parLID.Direction = ParameterDirection.Input
                    insertCommand.Parameters.Add(parLID).Value = LID


                    Dim parLName As New OleDb.OleDbParameter("@Name", OleDb.OleDbType.VarWChar, 125)
                    parLName.Direction = ParameterDirection.Input
                    insertCommand.Parameters.Add(parLName).Value = LName

                    mConn.Open()
                    insertCommand.ExecuteNonQuery()
                    mConn.Close()

                End Using
            End Using
            Return True
        Catch ex As Exception
            txtError.Text = "ERROR (InsertLeague) - " & ex.Message & ": " & Now & vbCrLf & txtError.Text
            'txtError.Refresh()
            Return False
        End Try
    End Function

Here's the "setInsertLeagueXREF" Function:

Code: Select all

Private Function setInsertLeagueXREF(ByVal GameID As String, LID As String) As Boolean

        Try
            Using mConn As New OleDb.OleDbConnection(strConnectionString)
                Using insertCommand As OleDb.OleDbCommand = mConn.CreateCommand
                    insertCommand.CommandText = "INSERT INTO tblLeagueGameXref (GameID, LID) " & _
                        "Values (?, ?)"

                    Dim parGameID As New OleDb.OleDbParameter("@GameID", OleDb.OleDbType.VarWChar, 125)
                    parGameID.Direction = ParameterDirection.Input
                    insertCommand.Parameters.Add(parGameID).Value = GameID


                    Dim parLID As New OleDb.OleDbParameter("@LID", OleDb.OleDbType.VarWChar, 125)
                    parLID.Direction = ParameterDirection.Input
                    insertCommand.Parameters.Add(parLID).Value = LID

                    mConn.Open()
                    insertCommand.ExecuteNonQuery()
                    mConn.Close()

                End Using
            End Using
            Return True
        Catch ex As Exception
            txtError.Text = "ERROR (InsertLeagueXREF) - " & ex.Message & ": " & Now & vbCrLf & txtError.Text
            'txtError.Refresh()
            Return False
        End Try
    End Function