Old School High Score Table borrowed from
Coding Horror
Introduction
This article discusses a solution for creating an active Leaderboard for a Windows
Phone 7 (WP7) XNA game so that game scores can be posted to a back-end repository
and a Leaderboard of the top ten scores can be displayed within the game.
Background
I am working on developing a series of
casual puzzle games for WP7. One of the features that I want to implement
in these games is an active Leaderboard or High Score Table. When a player completes
a game, the game will post the score to a web page. The mobile device can also request
the Leaderboard from the web page.
As I want my Leaderboard functionality to work with my existing website, I want
to implement a simple web page that will accept requests for the current Leaderboard
and accept new game scores from WP7 devices.
Using the code
Database
As my Internet host provider uses SQL Server as a back-end database, I will use
the same for this article so that I can plug this solution into my current web site.
To get started with the database, I created a new database in Microsoft SQL Server
Management Studio called "Leaderboard". Within this "Leaderboard"
database, I created a new table also called "Leaderboard" with the following
scheme:
CREATE TABLE [dbo].[Leaderboard](
[RowId] [int] IDENTITY(1,1) NOT NULL,
[GameId] [int] NOT NULL,
[PlayerId] [int] NOT NULL,
[Score] [int] NOT NULL,
[Moves] [int] NOT NULL,
[TimestampUTC] [datetime] NOT NULL,
[TimestampServer] [datetime] NOT NULL,
[TimestampDevice] [datetime] NULL,
[IPAddress] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[CountryName] [nvarchar](50) NULL,
[RegionName] [nvarchar](50) NULL,
[CityName] [nvarchar](50) NULL,
[ZipPostalCode] [nvarchar](50) NULL,
[Latitude] [nvarchar](50) NULL,
[Longitude] [nvarchar](50) NULL,
[GmtOffset] [nvarchar](50) NULL
) ON [PRIMARY]
RowId is an INT Identity field. TimestampUTC
defaults to GETUTCDATE() and TimestampServer defaults
to GETDATE().
You should have an additional table with player information that you can create
a relationship to on the PlayerId, but to keep this article simple, I am just displaying
the PlayerId in the Leaderboard.
ASP.NET
The service can be written in a few different ways.
One way is to create a web page that will accept a query string which it parses
to get the relevant data and then writes that data to the database. The same page
can also return the existing Leaderboard with the correct parameters in the query
string.
Another way is to create a web service to handle the Leaderboard requests.
Posting High Scores
GEO Location Information
In the ASP.NET code, I am using the IP address reported by the WP7 device to get
additional GEO Location information. I was thinking I could use this information
in a future GEO Location game feature. If you want to use this feature, you
will need to get a free API key from
IPInfoDB. Otherwise, if you’re not interested in this functionality,
just comment out this whole try/catch block of code.
try
{
string ApiKey = "xxxx you'll need to get your own api key xxxx";
string ApiUrlFormat = "http://api.ipinfodb.com/v3/ip-city/?key={0}&ip={1}";
string reqUrl = string.Format(ApiUrlFormat, ApiKey, IpAddress);
HttpWebRequest httpReq = (HttpWebRequest) HttpWebRequest.Create(reqUrl);
string webResponseString = string.Empty;
HttpWebResponse webResponse = (HttpWebResponse) httpReq.GetResponse();
using (StreamReader reader = new StreamReader(webResponse.GetResponseStream()))
{
webResponseString = reader.ReadToEnd();
}
string[] webResponseArray = webResponseString.Split(';');
if (webResponseArray.Length > 0)
{
apiStatus = webResponseArray[0];
apiIp = webResponseArray[2];
apiCountryCode = webResponseArray[3];
apiCountyName = webResponseArray[4];
apiRegionName = webResponseArray[5];
apiCityName = webResponseArray[6];
apiZipPostalCode = webResponseArray[7];
apiLatitude = webResponseArray[8];
apiLongitude = webResponseArray[9];
apiGmtOffset = webResponseArray[10];
}
}
Requesting Leaderboard
When the Leaderboard is requested by a WP7 device, the web page queries the database
for the relevant information. Once the requested data is in a DataTable, the DataTable's
WriteXml() method is called to write the data and data structure out to a string
with XML formatting. This block of XML is sent to the requesting WP7 device.
dataTable = dataSet.Tables[0];
using (StringWriter stringWriter = new StringWriter())
{
dataTable.WriteXml(stringWriter);
result = stringWriter.ToString().Trim();
}
For testing purposes, I had to set the Response object Cache to expire after one
second, otherwise I could not see quick updates on the WP7 device. This setting
should be moved to the web.config so that it can be changed later if needed.
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1));
Windows Phone 7
The WP7 project is a bare minimum XNA project.
User Interface
The WP7 User Interface simply includes two buttons. The "Post Score" button
posts a random game score to the web page. The "Get Scores" button
requests the Leaderboard from the web page and displays it on the WP7 screen.
Posting A Score
To get the IP address of the WP7 device, an asynchronous call is made to
http://whatismyip.org.
Uri uri = new Uri("http://whatismyip.org");
WebClient webClient = new WebClient();
webClient.DownloadStringCompleted += new DownloadStringCompletedEventHandler(Address_DownloadStringCompleted);
webClient.DownloadStringAsync(uri);
Once the IP address of the WP7 device is known, the score is posted to the web page
string IpAddress = e.Result;
string GameId = "1";
string PlayerId = "21";
string Score = new Random().Next(999).ToString();
string Moves = new Random().Next(100).ToString();
string PostFormat = "post={0}|{1}|{2}|{3}|{4}";
string Post = String.Format(PostFormat, GameId, PlayerId, Score, Moves, IpAddress);
Uri uri = new Uri("http://localhost:45291/Leaderboard.aspx?" + Post);
WebClient webClient = new WebClient();
webClient.DownloadStringCompleted += new DownloadStringCompletedEventHandler(PostScore_DownloadStringCompleted);
webClient.DownloadStringAsync(uri);
Here, the GameId is the ID of the current game being played. The
GameId is passed so that the Leaderboard can maintain High Scores for
different games. Sounds good in theory, but might not work well in practice if different
games keep scores in different ways.
The PlayerId is the player ID of the current player. Some kind
of registration system should be implemented so that players can register. But,
for now, that is beyond the scope of this article.
The current URL is set for testing.
Requesting the Leaderboard
The Leaderboard is requested by passing the GameId to the web page
in an asynchronous call. The current URL is set for testing.
Uri uri = new Uri("http://localhost:45291/Leaderboard.aspx?request=1");
WebClient webClient = new WebClient();
webClient.DownloadStringCompleted += new DownloadStringCompletedEventHandler(Leaderboard_DownloadStringCompleted);
webClient.DownloadStringAsync(uri);
Regular Expression
Regular Expression is used to make sure we only get the XML block between the
<Leaderboard> tags, inclusive. The rest of the response object
is ignored.
result = result.Replace(System.Environment.NewLine, string.Empty);
result = result.Trim();
string xml_block = string.Empty;
System.Text.RegularExpressions.MatchCollection matchCollectionTitle = System.Text.RegularExpressions.Regex.Matches(result, @"
<leaderboard>(.*?)</Leaderboard>");
if (matchCollectionTitle.Count == 1)
{
xml_block = matchCollectionTitle[0].Value;
}
Converting the XML block to an XML Document
Now that we have our cleaned up XML block, it is converted to an XML document for
processing. The xml block string is converted to a byte array. From there it is
converted to a memory stream. The memory stream is then converted to a stream reader.
Finally we have the xml block in a format that can be loaded into an XML document.
byte[] byteArray = Encoding.UTF8.GetBytes(xml_block);
MemoryStream stream = new MemoryStream(byteArray);
StreamReader streamReader = new StreamReader(stream);
XDocument doc = XDocument.Load(streamReader);
LINQ and Anonymous Objects
Once the XML document is created, the magic of LINQ and Anonymous objects is used
to parse the XML document and populate the Leaderboard.
var xmlLeaderboardEntries = from xmlLeaderboardEntry in doc.Descendants("Table")
select new
{
PlayerId = xmlLeaderboardEntry.Element("PlayerId").Value,
Score = xmlLeaderboardEntry.Element("Score").Value,
Moves = xmlLeaderboardEntry.Element("Moves").Value,
TimestampUTC = xmlLeaderboardEntry.Element("TimestampUTC").Value,
};
leaderboard.Clear();
foreach (var xmlLeaderboardEntry in xmlLeaderboardEntries)
{
LeaderboardEntry leaderboardEntry = new LeaderboardEntry();
leaderboardEntry.PlayerId = xmlLeaderboardEntry.PlayerId;
leaderboardEntry.Score = xmlLeaderboardEntry.Score;
leaderboardEntry.Moves = xmlLeaderboardEntry.Moves;
leaderboardEntry.TimestampUTC = xmlLeaderboardEntry.TimestampUTC;
leaderboard.Add(leaderboardEntry);
}
Testing
To test the solution, load and execute the web project in one instance of Visual
Studio 2010 (VS2010). Load the WP7 project in a second instance of VS2010. Run the
WP7 project in the WP7 emulator.
On the WP7 emulator, click "Post Score" to post some random score data.
Click "Get Scores" to retrieve the current Leaderboard.
Points of Interest
One issue that I ran into was that when I was repeatedly attempting to update the
Leaderboard from within the WP7 project, the Leaderboard did not change. The WP7
project kept getting a cached copy of the response. After some research I found
a few solutions for this issue.
I added a line to the ASP.NET code to cause the response object cache to expire
after one second.
Alternatively, you can add an additional field to the query string in the WP7 project
to include a random number or perhaps a datetime stamp. This will cause the server
to always send fresh data rather than a cached copy.
Still To Do
The functionality for the solution described in this article is really a proof of
concept. I need to deploy solution to my website, implement the Leaderboard code
with in one of my Windows Phone 7 games and give it a test drive. I plan on implementing
some additional features before moving the solution to my web host:
- Move connection string and other hardcoded configuration settings to web.config
file<
- Move SQL code from C# to stored procedures in the database itself
- Encrypt the data sent to and from the mobile device
- Implement some kind of authentication between mobile device and ASP.NET
Summary
Let me know what you think of this article and the solution. Any questions, concerns
and critics are welcome.
References
Links
History
2012-02-26 Initial Article
