Visual Foos 2005

	In this article, I'm going to cover a few ways to harness the power of Visual Studio 2005, SQL Server 2005, and a free foosball table to create the ultimate break room accessory.
Kevin Marshall, Clarity Consulting Inc. Kevin's Blog Difficulty: Advanced Time Required: 6-10 hours Cost: Greater than $200 Software: [Visual Studio Express Editions, SQL Server 2005 Hardware: Download: Download

Late one Friday afternoon, while working at my desk, a coworker asked, "Does anyone want a foosball table? My friend's office is getting rid of one, but we have to pick it up now." Even if I didn't like foosball, carrying a table a mile through the streets of Chicago would have made a great story. I had to get it.

Because we're competitive software developers, we started recording the games in an Excel spreadsheet to track stats. While arguing whether I had lost my last four games or my last five games, I realized there had to be a better way. And so the inspiration for the .NET foosball tracker was born. In this article, I'm going to cover a few ways to harness the power of Visual Studio 2005, SQL Server 2005, and a free foosball table to create the ultimate break room accessory.

Hacking the Table

The first step in building the foosball scoreboard application was to wire the table to a spare computer via the I-Pac, a PC interface for arcade buttons purchased from Ultimarc. Using what little carpentry skills were available in our software development consultancy office, we drilled several holes in the table to mount the buttons. The I-Pac translates button presses into keystrokes. Each player has a button to select his or her account at the beginning of a game (we have a very competitive woman's division) or to signal that he or she scored a goal. Three other buttons were added for game setup and other special features. We used the KeyUp event, so the Visual Basic .NET scoreboard WinForm application could respond to any of the button presses. It's not the most exciting code, but hey, this is just the beginning of the article.

Visual Basic

Private Sub KeyPressed(ByVal sender As System.Object,_
   ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyUp

        Select Case (e.KeyValue)
            Case Windows.Forms.Keys.V
                'Home Offense Button
                If _gameMode = GameModeList.ChoosePlayers Then
                    homeOffense.ChangePlayer()
                ElseIf _gameMode = GameModeList.InGame Then
                    AddGoal(PlayerList.HomeOffense)
                End If
            Case Windows.Forms.Keys.X
                If _gameMode = GameModeList.InGame Then
                    'InstantReplay
                    _instanyReplay.ShowReplay()
                Else
                    StartScreenSaver()
                End If
            Case Windows.Forms.Keys.Menu
                'Undo Button
                If _gameMode = GameModeList.InGame Then
                    RemoveLastGoal()
                End If
        End Select

Figure 1. Foosball Table

Designing the Scoreboard

Lucky for us, our break room contains a 30-inch TV with VGA inputs near the foosball table to display the scoreboard application. The scoreboard consists of a form with several picture boxes and labels with transparent backgrounds on a foosball background image. Each set of player names and icons is a user control that tracks information such number of goals scored. Using the new table adapters in Visual Studio 2005, it's easy to bind the controls to a data table. The following code loads player attributes such as the player image and sounds from SQL Server.

Visual Basic   

Public Sub LoadPlayer()
        
        playerName.DisplayMember = _playerDT.NicknameColumn.ColumnName

        Dim tauntsTA As New FoosDataTableAdapters.TauntsTableAdapter()
        Dim tauntsDT As New FoosData.TauntsDataTable()
          _taunts = New Collection

        tauntsTA.FillByPlayerID(tauntsDT, _playerID)
        For Each row As FoosData.TauntsRow In tauntsDT
            _taunts.Add(row.Item("SoundClip"))
        Next row

        Dim dr As DataRow() = _playerDT.Select("PlayerID = " & _playerID)
        If Not dr(0).Item("Avatar") Is DBNull.Value Then
            Dim img As Byte()
            img = CType(dr(0).Item("Avatar"), Byte())
            playerPic.Image = Image.FromStream(New System.IO.MemoryStream(img))
        Else
            playerPic.Image = My.Resources.Resource.clarityLogo
        End If
End Sub

After cycling through a list of employee accounts in the database using the player's button, it's time to play some foosball. Without budget enough to hire Keith Jackson to provide game commentary full-time, we added our own virtual announcer using the Microsoft Speech API. To make your application speak, all you need to do is add a reference to SpeechLib.dll and declare a new speech object.

Visual Basic

Dim voice As New SpVoice
voice.Speak("Whoa Nelly! Welcome to the Clarity Foos League",_
             SpeechVoiceSpeakFlags.SVSFDefault)

Figure 2. Scoreboard

Taunting Your Opponent

When playing office foosball, nothing is more important than a witty taunt to demoralize your opponent. When a player scores a goal, he presses the button in his corner, which fires a goal scored event which then triggers several actions. One action is to play a random sound clip from the player's personal sound collection retrieved from SQL Server. I'm mostly a C# developer, so the new Visual Basic .NET My Classes was something I wanted to try out. My Classes make it simple to perform dozens of tasks like playing any .wav file.

Visual Basic   

Private Sub PlayRandomSoundFile()
            randomInt = r.Next(1, _taunts.Count)
            My.Computer.Audio.Play(_taunts(randomInt),_
            AudioPlayMode.WaitToComplete)
End Sub

Let's See That in Instant Replay

When we first started this project, instant replay was buried in my list of dream features. I never thought I'd actually code half of those and instant replay seemed like it would take longer than my break to do. With a little lunchtime Internet surfing, I found this easy-to-use video capture/player ActiveX control from Fath Software called VideoCapX. (Oh, and I nearly tore apart the ceiling while trying to run a 50-ft USB cable through the tiles to the Clarity SkyCam©, but let's keep that from the office admin) In just a few lines of code, I can watch over and over again how terrible I am at blocking a pull shot.

Visual Basic  

Public Sub ShowReplay()
        Dim vidLength As Double
        vcx.StopCapture()
        vcx.PlayerOpen(FOOS_REPLAY_WMV)
        vidLength = vcx.PlayerGetLenMS()
        If vidLength > 10000 Then
            vcx.PlayerSetPos(vidLength - 10000)
        End If
        vcx.PlayerSetSize(640, 480)
        Me.BringToFront()
        My.Computer.Audio.Play(My.Resources.Resource.InstantReplay,_
                               AudioPlayMode.WaitToComplete)
        vcx.PlayerStart()
End Sub

Figure 3. Instant Replay

Archiving the Results

It's game over and there is an 80 percent chance that I lost in a blowout; time to put that box score in the record books. For those of you keeping score at home, the table adapters make it simple to save the game record to the database.

Visual Basic   


        Dim gameTA As New FoosDataTableAdapters.GameTableAdapter()
        gameTA.Insert(homeOffense.playerName, homeOffense.goals, _
        homeDefense.playerName, homeDefense.goals, _
        visitorOffense.playerName, visitorOffense.goals, _
        visitorDefense.playerName, visitorDefense.goals, _
        gameStartTime, gameEndTime)

With all the game stats recorded in SQL Server, we can produce dozens of reports such as win percentage, total goals scored, shutouts, average goals scored per game, average goals allowed per game, and using a formula based loosely on the BCS (U.S. college football) computer rankings, the top overall player. Sadly, I wrote the application and I still can't get my name into the top 10 players.

These stats are displayed on an arcade-like teaser screen that loads after the application has been idle for a few minutes. The teaser screen cycles through several dozen datasets to provide a different set of stats on the screen.

Visual Basic


Private Sub LoadStats()
        statTimer.Enabled = True
        Dim statType As Integer = ChooseRandomStatList()
        Dim playerDT As New FoosData.PlayerDataTable()
        Dim playerTA As New FoosDataTableAdapters.PlayerTableAdapter()

        Select Case statType
            Case 1
                playerTA.FillByTop10Winners(playerDT)
                statTitle.Text = "Most Wins"
                ResizeFont(FontSize.Large)
            Case 2
                playerTA.FillByTop10WinPercentage(playerDT)
                statTitle.Text = "Best Win %"
                ResizeFont(FontSize.Large)
            Case 3
                playerTA.FillByTop10Foosers(playerDT)
                statTitle.Text = "Biggest Foosers"
                ResizeFont(FontSize.Large)
                    …More stats…
                End Select
End Sub

Figure 4. Teaser Screen

Conclusion

Visual Basic.NET has dozens of new features that make quickly building an application very easy. In just a few hours I was able to put together an application that has significantly improved the fun of having a foosball table in the office. Unfortunately, it still can't make me play better. Maybe next version. Speaking of the next version, on my brand new blog, I'm going to cover some future additions to Visual Foos 2005 like a Web front-end to view game logs and upload sound files. Some other additions I'd like to develop are a RSS feed of games played, pre-game predictions using SQL Server Analysis Services, player identification through RFID readers, and a foosball speed radar. If anyone has any suggestions, I'd love to hear them! Or if you have a foosball table you don't want.