Getting the Number of Twitter Followers Without Using the Twitter API

Twitter logo

Since Twitter upgraded the Twitter REST API to version 1.1 earlier this year, a simple HTTP query against the API no longer works. You now have to include authentication information which would require redevelopment of many programs that perform this previously simple action.

I wanted to include the number of Twitter followers on this blog using the Social Impact Widget, which was made for version 1 of the Twitter API.

Using the YQL language API from Yahoo, this information can still be retrieved by Web scraping, without resorting to using Twitter’s API. Aakash Chakravarthy’s article explains this method well.

By using this method, the PHP code in the Social Impact Widget can be modified to pull the number of Twitter followers via Web scraping.

The original PHP code for the widget that retrieves the Twitter data:

$return_Twitter = $this->_helper_curl(sprintf('https://api.twitter.com/1/users/show.json?screen_name=%1$s',
		$var_sTwitterId
	), $this->var_sUserAgent);

Remove this code and add the below, also making some changes to the try block immediately below it to change the way it stores the returned data:

$return_Twitter = $this->_helper_curl('http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20from%20html%20where%20url=%22http://twitter.com/' . $var_sTwitterId . '%22%20AND%20xpath=%22//a[@class='js-nav']/strong%22&format=json'
                    , $this->var_sUserAgent); // Opening the Query URL
	try {
		$obj_TwitterData = json_decode($return_Twitter);
		if($obj_TwitterData) {
			if(!empty($obj_TwitterData->query->results->strong[2])) {
				$this->array_Options[$this->var_sArrayOptionsKey]['twitter-count'] = intval(str_replace(',', '', $obj_TwitterData->query->results->strong[2]));
			} // END if(!empty($obj_TwitterData->query->results->strong[2]))
		} // ENDif($obj_TwitterData)
	} catch(Exception $e) {
		$this->array_Options[$this->var_sArrayOptionsKey]['twitter-count'] = (int) $var_iTwitterFollowerCount;
	}

And now, your widget will appear correctly! The caveat here is that if Twitter ever changes the layout of the site, then the line

$obj_TwitterData->query->results->strong[2]


will have to be modified accordingly.

Creating an ASP.NET MVC 4 RESTful API for a SQL Stored Procedure

ASP.NET MVC Web API logo

In order to use the information from the SQL stored proc I implemented that would create a JSON Object from a SQL table, I needed to build an RESTful API web service that would retrieve the JSON for use elsewhere.

Finding information on building an ASP.NET-based API that used a stored proc to pull the data was not easy, as there were plenty of pages that had part of the info, but none I found had everything I needed. The article “Build RESTful API’s with ASP.NET Web API” was very helpful in giving me a starting point.

One of the things I did was to eliminate the Model (the “M” in MVC) from the site, as I did not want the column name to appear in the string returned by the API.

(“RESTfulAPI” is the name of the namespace for this project.)

The Controller that I created has the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using RESTfulAPI.Services;

namespace RESTfulAPI.Controllers
{
    public class JsonController : ApiController
    {
        private JsonRepository jsonRepository;

        public JsonController()
        {
            this.jsonRepository = new JsonRepository();
        }

        public string Get(string tableName)
        {
            return jsonRepository.GetAllJsons(tableName, null);
        }

        public string Get(string tableName, string department)
        {
            return jsonRepository.GetAllJsons(tableName, department);
        }
    }
}

I created a folder called “Services” at the same level of Controllers and added the following class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace RESTfulAPI.Services
{
    public class JsonRepository
    {
        public string GetAllJsons(string tableName, string department)
        {
            SqlDataReader rdr = null;
            SqlConnection conn = null;
            SqlCommand command = null;
            var connectionString = string.Empty;
            var json = string.Empty;
            connectionString = "Server=.SQLEXPRESS2008;Database=HackHou2008;Integrated Security=SSPI";

            conn = new SqlConnection(connectionString);
            command = new SqlCommand("GetJSON", conn);

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@table_name", tableName));
            if (department != null)
            {
                command.Parameters.Add(new SqlParameter("@department", department));
            }

            conn.Open();
            rdr = command.ExecuteReader();

            while (rdr.Read())
            {
                json += rdr["json"];
            }
            return json;
        }
    }
}

When the website is installed, the API can be accessed at “(site home)/API/json?tablename=tablename”. If you add other parameters to the stored proc, they will need to be added in the Get() method above, and the URL calling the API will have those parameters appended using ampersands (i.e., “(site home)/API/json?tablename=tablename&parameter2=parameter2&parameter3=parameter3”, etc.).