Posts Tagged ‘twitter’

In a previous post, we saw how easy it was to start consuming the Twitter Streaming API and display the messages on a console window. In this post, we’ll take it a step further and try to derive some useful information about the the activity stream on Twitter in real-time. I’ve given myself a hypothetical goal of deriving an answer to the following question:

Which Twitter users are mentioned most often when a given keyword is included in the tweet?

How useful is this information?  One idea could be to identify influencers in real time when you’re expecting a sudden increase in a keyword or hashtag for a sponsored event or TV spot.  If people are mentioning a particular user over and over again with your brand in it, you could connect with that user and help spread your message.

Let’s Get Started

For demonstration purposes, the components I’ll be building include:

  1. twitter_stream_db (SQL Server Database) – This will store the mention count for individual users
  2. MSMQ – I’ll be creating a queue that will be sent messages from the Twitter Streaming API. I’m going to leave this for a future post since I’m just doing a POC at this point. If I were to even consider using this in production I would definitely develop a queuing system but at this time it’s a bit of overkill.
  3. TwitterReader (console) – A small application that will read the Twitter API and drop messages into an MSMQ channel.
  4. TwitterWriter (console) – A small application that will read messages from MSMQ and update the database No MSMQ as explained above so no need to read off the queue. Again, I’ll write these components so we can scale in a later post. For now, consider this just a POC


This will be a slightly modified version of the console application we created in the previous post. Instead of writing to a console window, we’re going to parse the JSON objects using and then insert users mentioned in the tweet into a table using the SQL MERGE command.

Looking at the code, you’ll see we modified the stream URL slightly to include the keyword we want to track and sending that JSON result to a new method ParseJson.   In this case, let’s see who are the most popular users mentioned every time someone tweets something with the keyword “love” in it.   (I know it sounds corny but I needed something popular so that I can show off the results.)

We’re using’s Linq to Json feature to navigate to the user_mentions array. Once we have it, we just loop through all the users in the array and MERGE them into the database table through the stored procedure (see below).


For simplicity, I’m going to create a single table to store the data as it comes in.  The primary key is the Twitter user’s id since there should be only one record per user at any given time.

I used the new MERGE command to perform an “upsert” of the data.  If its the first time the user has been mentioned, it will perform an insert and set the mention_count to 1.  Otherwise, we’ll update the record by setting mention_count to mention_count + 1.

Since I’m using the MERGE command, I encapsulated it into a stored procedure as oppose to writing LINQ queries. The stored procedure receives a user id and twitter name and performs the insert/update logic.

The MERGE command is an incredibly useful feature introduced in SQL Server 2008.

Parsing the JSON Result

As mentioned above,  we’re just going to parse the JSON object and iterate through the users mentioned in the tweet.  As we do that, we’ll pass the users into the stored procedure above and MERGE the data into the SQL table.

Important: Since we’re not using a queuing system, the rate at which we can process tweets will depend largely on the speed of our SQL stored procedure. If you’re considering something similar in a production environment, please implement a queuing system to handle the load


Once you start running the application, you’ll start to see Twitter ids and screen names appearing on the console window.  Let it run for a few minutes and, depending on the popularity of your search term, you should start to see some results.  You can then go to SQL Server Management Studio and run a simple query to get a view of the activity on Twitter for that keyword / user mention combination.

Hope you enjoyed this post and have some ideas for implementing something similar with your next social media and Twitter campaigns!

Several social networking sites including Twitter and Digg have implemented some form of a streaming API. This is an emerging pattern with many high-volume content providers. At first glance, a streaming API may seem to be more resource intensive than traditional polling API, but as discussed here, it is actually much more streamlined.

Most of these server implementations use some sort of queuing system and a long-lived HTTP connection which clients use to have data delivered in near-realtime. In this post, I’m going to demonstrate just how easy it is to hook into the Twitter Streaming API.  Specifically, we’ll be using the streaming/filter method to consume any tweets which match a filter condition in realtime. I’m sure you can think of some neat ideas that can leverage this concept!

To demonstrate, let’s just create a console application in Visual Studio. We’re going to create a simple WebRequest to connect and just start reading the stream. Then, we’ll just print out the json result to the console window. I’ll leave it up to you to write some parsing logic to actually do something with the data.

That’s all there is to it!   Your output on the console window will look like this:

What you do with the data is where the real magic happens. The last I heard Twitter was producing somewhere in the range of 1000 tweets per second and I’m sure its much higher than that now.  You will probably want to implement message queuing system where you hand of the response as soon as possible for some other process to handle without blocking the incoming stream.

In a future post I’ll describe some neat ideas for what you can do with the data from the stream.

Hope this helps.

Solution Setup

For this project, I went with my tried and true solution structure:


I copied the System.Web.Mvc assembly to the lib folder and updated the reference accordingly.  I always recommend this approach especially when dealing with pre-release libraries.  The New Project dialog adds a reference to the default installation directory for the ASP.NET MVC version you choose.  This is usually “C:\Program Files\Microsoft ASP.NET\ASP.NET MVC 3\Assemblies”.  The problem with this is that your web host probably doesn’t install pre-release software.  By including it as a local library and setting “Copy Local” to true you can ensure that the required version is copied to the output directory.  It also helps when you add to version control and want to update the library to the latest release candidate.
copy local true

I also did the same for the LinqToTwitter and DotNetOpenAuth libraries.

Razor Template

Before you start using Razor templates, I highly recommend you read Scott Guthrie’s overview on Razor layouts.   There’s a couple new files you need to be familiar with including  _ViewStart.cshtml and _Layout.cshtml.  Essentially, _ViewStart removes the need for you to explicitly set the layout for each view you add and specify any common view code you want to have execute before rendering the view.  _Layout is what you commonly know as your Site.Master page.

First Try – Twitter API

To wrap this post up, I want to just take a quick look at wiring up all the Twitter API authentication process. The first step to get OAuth working is to get a consumer key and secret from Twitter. It’s a quick and easy process. Just go to and select “Register a New Application”. Complete all the required fields and you’ll be presented with the key and secret. Make sure you select “Browser” as the Application Type. Don’t worry about the callback URL for now – just put or something similar for now.

Next, add the required assemblies that you downloaded with Linq to Twitter to your project. These include LinqToTwitter.dll and DotNetOpenAuth.dll. An important step is to create a class that implements the IConsumerTokenManager interface in the DotNetOpenAuth library. This is the class that will manage all the access tokens and secrets for OAuth. For the sake of this quick POC, I’m using the InMemoryTokenManager included in the Using OAuth with ASP.NET MVC sample project. The only configuration change you have to make is to add a your consumer secret and key to the Web.config as illustrated below:

Linq to Twitter Configuration Settings

Linq to Twitter Configuration Settings

Take a look at that project to get a quick connection to the Twitter API going. I ran my project and was able to connect exactly as expected. Next time we’ll look at the wireframes for the application and getting more of the functionality of the site going.


Have you ever tweeted something that you would like your friends to retweet as soon as possible.  I’ve seen this with new product announcements, Twitter contests, building buzz and even when applying for a social media gig.  Usually the tweet includes something like “Pls RT!”  With this project, I thought I’d create a retweeting engine to announce to your friends that you have a status you’d like them to retweet.  This sounded like a great opportunity to deep dive into the Twitter API and OAuth within .NET application.  Hence a neat side project that may have some usefulness and will definitely get us exploring some important API’s.

Technology Stack

Twitter API

There are plenty of good .NET Twitter Libraries available with varying degrees of functionality.  I am a huge fan of Linq so I decided to give Linq to Twitter a spin since it has high API coverage and supports oAuth out of the box.


I’ve been using the ASP.NET MVC framework since it’s first community preview several years ago and have fallen in love. It has evolved in many ways since the first CTP and I’m looking forward to experiment with some of its newest features – particularly the Razor view engine.


I believe the best way to get up to speed with an API is to jump right in and start working with it.  One of the components I’m most excited to explore is the OAuth implementation for the Twitter API.  With more and more services adopting the OAuth protocol, I think its safe to expect it to become the de-facto security layer for most social API’s available.

Other Tools

As to the other frameworks and tools involved in the project, you can expect to see the usual suspects. At this time I don’t think they’ll be a need for a database since most of the data will be coming from the Twitter API. The tools of the trade up to this point will include:

  • Visual Studio 2010 Ultimate
  • jQuery v1.4.4

I’m looking forward to sharing the experience step-by-step so don’t be surprised if I hit some speed bumps (or canyons) along the way. In the next part we’ll look at some preliminary solution setup and wiring some of the external libraries together. We’ll also mockup some screens and see what views and controllers it would be implying.