|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.|
Time Required: 6-10 hours
Cost: Greater than $200
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.
Private Sub KeyPressed(ByVal sender As System.Object,_
ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyUp
Select Case (e.KeyValue)
'Home Offense Button
If _gameMode = GameModeList.ChoosePlayers Then
ElseIf _gameMode = GameModeList.InGame Then
If _gameMode = GameModeList.InGame Then
If _gameMode = GameModeList.InGame Then
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.
Public Sub LoadPlayer()
playerName.DisplayMember = _playerDT.NicknameColumn.ColumnName
Dim tauntsTA As New FoosDataTableAdapters.TauntsTableAdapter()
Dim tauntsDT As New FoosData.TauntsDataTable()
_taunts = New Collection
For Each row As FoosData.TauntsRow In tauntsDT
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))
playerPic.Image = My.Resources.Resource.clarityLogo
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.
Dim voice As New SpVoice
voice.Speak("Whoa Nelly! Welcome to the Clarity Foos League",_
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.
Private Sub PlayRandomSoundFile()
randomInt = r.Next(1, _taunts.Count)
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.
Public Sub ShowReplay()
Dim vidLength As Double
vidLength = vcx.PlayerGetLenMS()
If vidLength > 10000 Then
vcx.PlayerSetPos(vidLength - 10000)
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.
Dim gameTA As New FoosDataTableAdapters.GameTableAdapter()
gameTA.Insert(homeOffense.playerName, homeOffense.goals, _
homeDefense.playerName, homeDefense.goals, _
visitorOffense.playerName, visitorOffense.goals, _
visitorDefense.playerName, visitorDefense.goals, _
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.
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
statTitle.Text = "Most Wins"
statTitle.Text = "Best Win %"
statTitle.Text = "Biggest Foosers"
Figure 4. Teaser Screen
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.