Cricket season is rolling around, and where I work, we are mad cricket fans. Our office has two projectors setup in the office just to project cricket onto the walls while we are working (this year I've used a TV Tuner card in an old computer - and used Jetcast
to transmit the channel audio to staff pc's.
Last year, we also had the idea of getting Cricket Scores on our intranet homepage, at the time, the page was a simple html page, so I just created an IFrame to the Baggy Green
desktop scorecard. This worked fine, except that there is over 60 staff, all sending requests to Baggy Green over and over.
Now, we have moved to an ASP.Net web parts page as our frontpage, customizable by each individual user. So, I decided to make a web parts page for the cricket, I could just do the same thing as before, but I decided it was time to save some bandwidth, for both Baggy Green and our company. After some thought, my idea was this: Create a web part, which screen scrapes the desktop scorecard from Baggy Green, and saves the information in cache, and reloads it every minute. This would mean that only 1 person out of the 60 queries the page once a minute, rather than everyone potentially multiple times a minute. I also wanted to create a page that would allow anyone in the office to put in a URL to the match's scorecard, and it would set the frontpage going, rather than myself pulling out parts of the URL and entering in code.
Here's my solution:
Firstly, I have a page in which users submit the URL, and that URL gets searched using Regular Expressions for the Match ID which is displayed as a part of the GET variable string. This match ID is then stored in a database table I have setup for misc values (this could possibly be stored elsewhere, like in an XML file, I tried having it stored in cache for the duration of the match - however cache gets recycled/isn't reliable enough to count on it), with 2 columns, KeyName and KeyValue, which contained 'CricketMatch' and then the MatchID.
The code for that section is as follows:
Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'inserts the ID of a match into the table.
'Use regex to find the id of the game.
Dim MatchID As String = MatchURL.Text
Dim r As Regex = New Regex("match", RegexOptions.None)
Dim m As Match = r.Match(MatchID)
Dim MatchLoc As Integer = m.Index
Dim ScoreBegin = MatchID.IndexOf("/", MatchLoc) + 1
Dim ScoreEnd = MatchID.IndexOf(".", ScoreBegin)
MatchID = MatchID.Substring(ScoreBegin, ScoreEnd - ScoreBegin)
'Inserting the CricketMatch key into PP_MiscValues table
' This gives the ID of the match for the score card to detect and use.
If Not MatchID = "None" Then
'Submit it to SQL Database, your SQL implementation may/will differ.
Dim _ibcon As SqlConnection = ibcon.ReturnConnection
Dim InfoBaseCommand As SqlCommand = New SqlCommand("Insert into PP_MiscValues (KeyName, KeyValue) Values _
('CricketMatch', '" & MatchID & "');", _ibcon)
Dim Result = InfoBaseCommand.ExecuteNonQuery
' Confirm to the user that the matchID is entered
Response.Write("Match with ID " & MatchID & " added, coverage has begun")
Response.Write("No Match ID Found")
That is submitted from a text box with a submit button, I also have a delete button which removes any entry from that table, thus ending coverage.
From there, I created a web part, which looks in the database and checks for a CricketMatch record in the MiscValues table, if there isn't any, it simply says 'no games are currently being covered', if there is one, then it checks to see if there is already cached code for the match, if there is, it simply displays it. If not, it then screenscrapes the page, and stores it in cache, with an expiry of 1 minute.
The code for this is:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
'Check if a match is on, by checking for a MatchID in the database.
'SQL Connection to check if there is a CricketMatch record in the database
Dim _ibcon As SqlConnection = ibcon.ReturnConnection
Dim InfoBaseCommand As SqlCommand = New SqlCommand("Select KeyValue from PP_MiscValues where KeyName _
= 'CricketMatch';", _ibcon)
Dim ResultReader As SqlDataReader = InfoBaseCommand.ExecuteReader
MatchID = ResultReader.Item("KeyValue")
Catch ex As Exception
' If there is a record in the database, check the cache.
If Not MatchID Is Nothing Then
'Check if score is in cache
If Cache("CricketScore") Is Nothing Then
'create web client instance for scraping
Dim objWebClient As New WebClient()
'Scrape the page, getting the match ID from cache (idea is that you have another "admin" page to set this)
'Construct the URL of the desktop scorecard, using the MatchID
Dim strURL = "http://content-aus.cricinfo.com/australia/engine/current/match/" & _
MatchID & ".html?template=desktop;view=main;wrappertype=frame"
Dim aScrapedHTML() As Byte
aScrapedHTML = objWebClient.DownloadData(strURL)
'Convert the Byte array into a String
Dim objUTF8 As New UTF8Encoding()
Dim strScrapedHTML As String
strScrapedHTML = objUTF8.GetString(aScrapedHTML)
'Fix the bottom links on the desktop scorecard, they are relative, so they won't work anymore.
strScrapedHTML = Replace(strScrapedHTML, " ' Remove the close window link
strScrapedHTML = Replace(strScrapedHTML, " Close window", "")
'Replace the white text with black
strScrapedHTML = Replace(strScrapedHTML, "", "")
'If it worked, insert into cache, with an expiry of one minute, so only 1 computer per minute scrapes the page.
' Could lower this to 30 seconds if you want scores quicker
If Not strScrapedHTML Is Nothing Then
Cache.Insert("CricketScore", strScrapedHTML, Nothing, DateTime.Now.AddMinutes(1), TimeSpan.Zero)
' Display cricket scores on page
lblHTMLOutput.Text = strScrapedHTML
' If there was no value in cache,
'load the html from the cache & output it.
lblHTMLOutput.Text = Cache("CricketScore")
lblHTMLOutput.Text = "Currently no games are being played, this will be updated when one begins"
That then nicely displays the scorecard once a match is entered, and then doesn't display it when it's removed. Of course, this will get properly stress tested come match day, but from my preliminary testing
Hi Dear Great post but could please tell me the connection string you used to get the access to the data base.
i didn't get the ibcon.returnconnection
Pleace help me
I apologize I didn't clarify that, ibcon.returnconnection is my class to return the connection string for our internal database.
I'm not accessing a database for the cricket score, what I'm doing is screen scraping the page, and saving the html code in OUR internal database.
So you'd replace that section of the code with your own SQL database or some other method of storing the html code.
Post a Comment