Web service proxy in ASP.NET MVC – how to avoid cross-site script warnings

In this article I will walk through the process of creation of web service proxy in ASP.NET MVC. This is specifically useful as a method of preventing cross-site scripting (XSS) warnings on web page. This is a feature of all modern web browsers and cannot be easily omitted as it enforces same origin policy. This sort of attack can happen when browser tries to download client script from outside of application domain – simply speaking when web browser downloads script from different location.

For the purpose of this demo I will use IP look-up web service available at http://ipinfodb.com/. This web service allows to obtain geodata for given IP address and can be extremely useful for in example centralizing map (i.e. Google maps or Bing maps) on user’s location.

Valid request should look like this: http://ipinfodb.com/ip_query.php?ip=

Of course it is possible to call this web service at Ajax request level, by simply writing:

    var ipaddress = '';
    $.get('http://ipinfodb.com/ip_query.php?ip=' + ipaddress + '&timezone=false',
        function(data) {...}

although on all most recent web browsers (IE 8, FF 3.6, Chrome, Safari) user will get very ugly security warning every time browser executes this code. This is due to XSS vulnerability mentioned above and can be extremely annoying to user when application performs this operation on very first page load.

Thankfully ASP.NET provides set of mechanisms to overcome this problem. In simplest words there is an extra layer between web service and Ajax call needed, and this layer is web service proxy.

The core component of proposed solution is a HTTP handler class, in this example named LocationProxyHandler. This class will create and execute HTTP request to web service and return response to client. It needs to implement two interfaces: IHttpHandler and IRouteHandler. First interface guarantees that class can process HTTP request, second allows to use class in routing rule that will be required to redirect requests to the handler class. Implementation of class is as follows:

    public class LocaionProxyHandler : IHttpHandler, IRouteHandler
        public bool IsReusable
            get { return true; }

        public void ProcessRequest(HttpContext context)
            string ipAddress = context.Request.QueryString["ipaddress"];

            string str = string.Format(@"http://ipinfodb.com/ip_query.php?ip={0}",

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(str);
            request.Method = "GET";
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            StreamReader reader = new StreamReader(response.GetResponseStream());

            HttpResponse res = context.Response;
            res.ContentType = "application/xml; charcode=utf8";
            res.StatusCode = 200;

        public IHttpHandler GetHttpHandler(RequestContext requestContext)
            return this;

The most important method in the class is ProcessRequest(). It takes current HttpContext as parameter and for this context it executes HTTP request to external web service and builds and returns response. Before it fires HTTP request, it takes IP address as parameter from URL.

There is one more thing that needs doing before this proxy can serve the purpose. It has to be registered in ASP.NET MVC routing table within the application. To accomplish this the following code is needed in RegisterRoutes() method inside Global.asax.cs:

    routes.Add(new Route("{action}.proxy", new SocialCitiProxy.LocaionProxyHandler()))

This makes calls like http://localhost:1431/myApp/Service.proxy understandable for MVC engine and ensures the request is redirected to LocationProxyHandler.

Very final step is Ajax request to LocationProxyHandler:

var ipaddress = '';
        url: 'GetUserLocation.proxy',
        data: 'ipAddress=' + ipaddress,
        dataType: 'xml',
        contentType: 'application/xml; charset=utf8',
        error: function (xhr, status, e) {...},
        success: function (data) {...}

No security warning messages appears now and this is exactly what we wanted to achieve.

Clustered vs. Non-clustered indexes in SQL Server

In this article I will make attempt to outline differences between clustered and non-clustered indexes in SQL Server. Be fully aware that it is not going to be comprehensive explanation, but rather brief overview highlighting purpose of both types of indexes.


Indexes are one of the most important part of database optimization, especially for large and growing database tables. They work in very similar way to index in a book – just imagine massive address book, with thousands pages but no index. Searching this book for specific address is nearly impossible without index.

When query is ran against SQL Server database, Query Optimizer analyses all available indexes for tables involved, and creates most efficient execution plan to run statement. SQL Server reads data in 8KB pages, so if table is small enough to fit on one or two pages, there is no need for an index at all, as SQL Server will only have to make one or two read operations. This operation is called Full Table Scan and for small table it is the most efficient way of pulling the data out. On the other hand, if table has 1,000,000 records and size of row allows reading only 50 rows per page, number of read operations rises to 20,000. That clearly will take a lot of time and this is where indexes come into play. Indexes can massively improve SQL query, however is worth mentioning, that it does not come for free – indexes can take a lot of storage. When index is involved in selecting data from table, this operation is called Index Seek. In general we can distinguish two types of indexes: clustered and non-clustered.

Clustered Index

This index assumes that data in a table is physically sorted in specific order. This means that when new row is inserted, it physically goes to the place determined by index, allowing data after new row to be moved to next page if required. The table can only have one clustered index. This index is specifically useful if we use BETWEEN statement in WHERE clause of our statement (i.e. SELECT * FROM Table WHERE Col1 BETWEEN ‘A’ AND ‘Z’).

Non-clustered Index

When this type of index is used logical order of index does not match physical order of data stored on the disk. Index plays a role of “pointer to row”, allowing faster access to the row that is requested.

A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. This type of index works best when used having = in WHERE clause (i.e. SELECT * FROM Table WHERE Col1 = ‘A’) or if the column is used in JOIN.


How to refer to previous row in SQL 2005

In this post I will describe simple mechanism allowing to refer back to previous row in SQL Server 2005/2008 database table.  

For the purpose of this article let’s create a table dbo.AgentLog:  

create table dbo.AgentLog (
	 AgentID	int
	,LogInTime	datetime
	,LogOutTime	datetime

insert dbo.AgentLog
select 1, '2010-09-08 09:40:00.000', '2010-09-08 10:14:00.000'
union all
select 1, '2010-09-08 10:35:00.000', '2010-09-08 12:04:00.000'
union all select 1, '2010-09-08 13:11:00.000', '2010-09-08 14:24:00.000'
union all select 1, '2010-09-08 14:35:00.000', '2010-09-08 16:08:00.000'
union all select 2, '2010-09-08 09:00:00.000', '2010-09-08 09:23:00.000'
union all select 2, '2010-09-08 10:12:00.000', '2010-09-08 12:05:00.000'
union all select 2, '2010-09-08 13:12:00.000', '2010-09-08 15:07:00.000'
union all select 2, '2010-09-08 15:10:00.000', '2010-09-08 16:25:00.000'

Our table will look like this:  

AgentID LogInTime LogOutTime
----------- ----------------------- -----------------------
1           2010-09-08 09:40:00.000 2010-09-08 10:14:00.000
1           2010-09-08 10:35:00.000 2010-09-08 12:04:00.000
1           2010-09-08 13:11:00.000 2010-09-08 14:24:00.000
1           2010-09-08 14:35:00.000 2010-09-08 16:08:00.000
2           2010-09-08 09:00:00.000 2010-09-08 09:23:00.000
2           2010-09-08 10:12:00.000 2010-09-08 12:05:00.000
2           2010-09-08 13:12:00.000 2010-09-08 15:07:00.000
2           2010-09-08 15:10:00.000 2010-09-08 16:25:00.000

The task is to for each agent list all breaks the person made and its length. To do this I need to somehow refer back to previous row to calculate difference in minutes between login and previous logout.  

To accomplish the task I will use two features new to SQL Server 2005 (when compared to SQL Server 2000). These are CTE (Common Table Expression) and Row_Number() function.  

First one in essence can be thought as variable table of results from SELECT statement. This data set only exists in current scope.  

Row_Number() is a SQL Server built-in function that adds extra identity column to data set retrieved with SELECT statement.  

     Row_Number() over (partition by AgentID order by LogInTime) AS RowID
FROM dbo.AgentLog

Row_Number() takes partition by argument, which tells function that you want unique row numbers against specific column. If this isn’t provided, SQL Server will just number all rows on one-by-one basis. Combining CTE and Row_Number() we can now get the following: 

 ;with AgentLogCTE as
(select Row_Number() over (partition by AgentID order by LogInTime) as RowID, * from dbo.AgentLog )

This CTE then contains following information:

RowID                AgentID     LogInTime               LogOutTime
-------------------- ----------- ----------------------- -----------------------
1                    1           2010-09-08 09:40:00.000 2010-09-08 10:14:00.000
2                    1           2010-09-08 10:35:00.000 2010-09-08 12:04:00.000
3                    1           2010-09-08 13:11:00.000 2010-09-08 14:24:00.000
4                    1           2010-09-08 14:35:00.000 2010-09-08 16:08:00.000
1                    2           2010-09-08 09:00:00.000 2010-09-08 09:23:00.000
2                    2           2010-09-08 10:12:00.000 2010-09-08 12:05:00.000
3                    2           2010-09-08 13:12:00.000 2010-09-08 15:07:00.000
4                    2           2010-09-08 15:10:00.000 2010-09-08 16:25:00.000

Now what we need to do is just join this table to itself in some clever way, linking current row to previous one:

   ,t2.LogOutTime as PrevLogout
   ,t1.LogInTime as Login
   ,convert(varchar, t1.LogInTime - t2.LogOutTime, 108) as BreakTime
from AgentLogCTE t1
   join AgentLogCTE t2
      on t2.RowID = (t1.RowID-1) and t1.AgentID = t2.AgentID

And this is it! Resultset looks like the following:

AgentID     PrevLogout              Login                   BreakTime
----------- ----------------------- ----------------------- ------------------------------
1           2010-09-08 10:14:00.000 2010-09-08 10:35:00.000 00:21:00
1           2010-09-08 12:04:00.000 2010-09-08 13:11:00.000 01:07:00
1           2010-09-08 14:24:00.000 2010-09-08 14:35:00.000 00:11:00
2           2010-09-08 09:23:00.000 2010-09-08 10:12:00.000 00:49:00
2           2010-09-08 12:05:00.000 2010-09-08 13:12:00.000 01:07:00
2           2010-09-08 15:07:00.000 2010-09-08 15:10:00.000 00:03:00

Whole SQL script explained in this article can be obtained from here.

Cascading dropdowns with just a bit of Ajax in ASP.NET 2.0

In this sample I am going to go through very common idea of cascading dropdowns. Very basically speaking it is all about having set of dropdowns where next dropdown is populated on the basis of value selected from previous one.

Let’s start with very simple xml document:

<?xml version="1.0" encoding="utf-8" ?>
    <Person ID="1" Name="Laith Francis" ParentID="0" />
    <Person ID="2" Name="Hammett Swanson" ParentID="0" />
    <Person ID="3" Name="Macon Hayden" ParentID="0" />
    <Person ID="4" Name="Kennan Mooney" ParentID="0" />
    <Person ID="5" Name="Jin Hutchinson" ParentID="4" />
    <Person ID="6" Name="Emery Schwartz" ParentID="1" />
    <Person ID="7" Name="Melvin Garrett" ParentID="2" />
    <Person ID="8" Name="Giacomo Lamb" ParentID="4" />
    <Person ID="9" Name="Stephen Harding" ParentID="3" />
    <Person ID="10" Name="Ian Ward" ParentID="1" />
    <Person ID="11" Name="Raphael Walters" ParentID="2" />
    <Person ID="12" Name="Lee Gould" ParentID="1" />
    <Person ID="13" Name="Carter Carpenter" ParentID="4" />
    <Person ID="14" Name="Kareem Shepard" ParentID="14" />
    <Person ID="15" Name="Zahir Montgomery" ParentID="12" />
    <Person ID="16" Name="Zachery Mcmahon" ParentID="14" />
    <Person ID="17" Name="Hu Dillon" ParentID="12" />
    <Person ID="18" Name="Allistair Bradford" ParentID="13" />
    <Person ID="19" Name="Vincent Bryan" ParentID="11" />
    <Person ID="20" Name="Vance Santos" ParentID="12" />

Now let’s create very simple .aspx page, that has three dropdowns. First one we will populate using standard ASP.NET binding method so we will use DropDownList control in this instance. Because we do not populate other dropdowns straight away, nothing stops us from using html select tags. The markup can be like this:

<form id="form1" runat="server">
    <asp:DropDownList DataSourceID="xmlBosses"
        ID="ddlBosses" runat="server"></asp:DropDownList>
    <asp:XmlDataSource DataFile="~/App_Data/data.xml"
        ID="xmlBosses" runat="server" XPath="/People/Person[@ParentID=0]">
    <br />
    <select id="ddlManagers"></select>
    <br />
    <select id="ddlWorkers"></select>

Having this markup done we now need a mechanism to populate ddlManagers when ddlBosses changes. Same action needs to happen to ddlWorkers when ddlManagers is changed. To accomplish that we need make Ajax request to some data provider. In our instance, because application is created in ASP.NET 2.0, I opted for web service, (.Net 3.5 with MVC with it’s JsonResult class and Json() method is much more friendly on this front thought).

Firstly we need web service. We want to give it a boss Id and get list of all managers that report to this boss. Web service can look like this one:

public class People : System.Web.Services.WebService {
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml)]
    public List GetData(int ParentID) { 
        List<int> result = new List<int>(); 

        XmlDocument doc = new XmlDocument(); 
        XmlNodeList nodes = doc.SelectNodes("/People/Person[@ParentID=" 
            + ParentID.ToString() + "]");

        foreach (XmlNode node in nodes) {
            Person p = new Person();
            p.ID = Convert.ToInt32(node.Attributes["ID"].Value);
            p.Name = node.Attributes["Name"].Value;
            p.ParentID = Convert.ToInt32(node.Attributes["ParentID"].Value);
        return result;

    public class Person {
       private int _id;
       private string _name;
       private int _parentId;

       public int ID {
           get { return _id; }
           set { _id = value; }

       public string Name {
           get { return _name; }
           set { _name = value; }

       public int ParentID {
           get { return _parentId; }
           set { _parentId = value; }

Having all of that we are now ready for very final stage – building Ajax call to execute web service and process returned data. The only thing we need is a bit of jQuery code, that will do it all:

$(function() {
    // for each dropdown bind change event
    $('select').change(function(e) {
        // capture which dropdown caused request
        if(!e) var e = window.event;
        var srcId = e.srcElement.id;

        // create ajax request
            url: 'WebServices/People.asmx/GetData', //call to created web method
            data: { ParentID: $(this).val() },  //web method expects ParentID
            contentType: 'application/xml; charset=utf-8',
            dataType: 'xml',
            error: function(xhr, status, e) { ... },
            success: function(data) {
                // if success get dropdown to be updated
                var select = null;
                switch(srcId) {
                    case 'ddlBosses':
                        select = $('select#ddlManagers');
                    case 'ddlManagers':
                        select = $('select#ddlWorkers');

                // remove items from dropdown

                // loop through Ajax request results
                $('Person', data).each(function() {
                    // for each result create option element
                    var option = document.createElement('option');
                    $(option).text($('Name', this).text()).val($('ID', this).text());
                    // and append it to dropdown

And this is it!

I hope you will enjoy it!

How to create page with header and iframe covering rest of the page

A few times I needed to have a page where I have an iframe (I know, I know – it is bad design, but sometimes you need to sacrifice!) with header to the top. I tried many different CSS hacks, but could not get it right. Then I came around pageY(), tiny function created by John Resig (jQuery author), which helps to resolve this particular problem brilliantly:

    function ResizeIFrame() {
        var buffer = 40; //scroll bar buffer
        var height = document.documentElement.clientHeight -
            pageY(document.getElementById('ifrm')) + buffer;
        height = (height < 0) ? 0 : height;
        $('iframe#ifrm').css('height', height + 'px');

    function pageY(elem) {
        return elem.offsetParent ? (elem.offsetTop + pageY(elem.offsetParent)) : 

Where ‘ifrm’ is ID of iframe.

The only thing we need now is handing load and resize events:

    window.onload = window.onresize = ResizeIFrame;

This could not be simpler!

How to drop all schema objects in MS SQL 2005

I needed to test my SQL implementation script that was a part of big SQL Server Data Warehouse release. For anybody who have ever done large SQL implementation fundamental question is always “Have I scripted everything that is needed?”. So how to check if your implementation script contains all objects you require? Here is a few steps that helped me:

  1. Collate all individual scripts into one or more implementation script (I recommend having  one script per development, but it always depends on circumstances).
  2. Order your scripts so you know the sequence of running them. This is specifically important if there is dependency between objects created by different scripts.
  3. Create backout script for each implementation one and order them as well.
  4. Drop all objects that come into release. “How to drop all schema objects in one go?” – this is where it turns out to be priceless to have DBA in your team:
declare @schema varchar(200)
select @schema = 'MySchema'

'DROP ' + case
    when o.xtype = 'U' then 'TABLE'
    when o.xtype = 'V' then 'VIEW'
    when o.xtype = 'P' then 'PROCEDURE'
    when o.xtype = 'FN' then 'FUNCTION'
    end + ' ' + s.name + '.' + o.name as SQL
    sys.sysobjects as o
    join sys.schemas as s on o.uid = s.schema_id
    s.name = @schema and
    o.xtype in ('U','V','P','FN')
union all
'DROP SCHEMA ' + @schema

This will give you DROP statement for each table, view, stored procedure and user defined function in your schema, as well as schema itself. Just grab whichever statement you need and execute it!

Passing list as parameter from AJAX request to MVC controller

I came across this recently when needed to pass array of selected values into MVC controller during Ajax request. Scenario was that on the page I had set of checkboxes and button. User can then make multiple choice selection and submit form using button. Page could look like this:

<ul id="sampleList">
    <li><input type="checkbox" value="1" checked="checked" />text 1</li>
    <li><input type="checkbox" value="2" checked="checked" />test 2</li>
    <li><input type="checkbox" value="3" checked="checked" />test 3</li>
<input type="button" id="submitButton" value="submit" />
<span id="spanResults"></span>

Also in the application I have, let’s say, “TestController”. Within this controller I have “GetValue” method, that I want to concatenate input data into a string:

public JsonResult GetValue (List<string> id)
return Json(id);

Then jQuery code to collect values of checked checkboxes and make Ajaxcall would be as following:

var items = new Array();
$('#sampleList li input:checkbox:checked').each(function() {
$('#submitButton').click(function() {
        type: 'post',
        url: '/Test/GetValues',
        data: { id: items },
        contentType: 'application/json; charset=utf-8',
        dataType: 'json',
        success: function(data) {
        error: function(e) {

This should hopefully put some light on “how on the earth do I pass list as parameters via Ajax to MVC”. Exactly the same mechanics applies to passing complex types and multiple parameters – just wrapp multiple parameters in complext type and you are done!

Hello world!

let's scratch some heads...Welcome to my technical blog,

This is a very first post here (and I really hope not the last one).

Being in IT industry for a few years now, I realise more and more everyday that having good “knowledge base” is essential for any serious computer professional. Just look here if you do not believe my previous sentence.

Although I had collection of “hacks” and “tricks” it was allover the place. Some bits on my pc at work, some on laptop at home – that obviously is no good when you think “I definitely remember I did it, but how???”.

So the simplest answer to this problem was to create this “webspace”, which I hope also have some value for others as well.

If you by any luck found this place in a fantastic world of Internet, welcome! Feel more than encouraged to leave your fingerprint.