Cross-domain JSONP with jQuery call step-by-step guide

I’ve been banging my head all day to accomplish this, it’s like a puzzle.. but since i get it to work i thought i could write this post for you and myself.

What we want to accomplish?

Simple way to communicate cross-domain with ASMX .NET 3.5 Web Service

How can we do it?

1. Implement a web service method like the following

   [ScriptService]
   public class JSONP_EndPoint : System.Web.Services.WebService
   {
       [WebMethod]
       [ScriptMethod(UseHttpGet = true,ResponseFormat = ResponseFormat.Json)]
       public string Sum(string x,string y)
       {
           return x + y;
       }
   }

2. Add New class library with a name ContentTypeHttpModule

The reason for this is no matter how you specify the content-type of your ajax call ASP.NET send the request with Content-Type text/xml; charset=utf-8 this is security feature explained here by ScottGu

3. Add the following code to your Class (Code by Jason i just did a simple modification)

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace ContentTypeHttpModule
{
    public class ContentTypeHttpModule : IHttpModule
    {
        private const string JSON_CONTENT_TYPE = "application/json; charset=utf-8";

        #region IHttpModule Members
        public void Dispose()
        {
        }

        public void Init(HttpApplication app)
        {
            app.BeginRequest += OnBeginRequest;
            app.ReleaseRequestState += OnReleaseRequestState;
        }
        #endregion

        public void OnBeginRequest(object sender, EventArgs e)
        {
            HttpApplication app = (HttpApplication)sender;
            HttpRequest resquest = app.Request;
            if (!resquest.Url.AbsolutePath.Contains("JSONP-EndPoint.asmx")) return;

            if (string.IsNullOrEmpty(app.Context.Request.ContentType))
            {
                app.Context.Request.ContentType = JSON_CONTENT_TYPE;
            }
        }

        public void OnReleaseRequestState(object sender, EventArgs e)
        {
            HttpApplication app = (HttpApplication)sender;
            HttpResponse response = app.Response;
            if (app.Context.Request.ContentType != JSON_CONTENT_TYPE) return;

            response.Filter = new JsonResponseFilter(response.Filter);
        }
    }

    public class JsonResponseFilter : Stream
    {
        private readonly Stream _responseStream;
        private long _position;

        public JsonResponseFilter(Stream responseStream)
        {
            _responseStream = responseStream;
        }

        public override bool CanRead { get { return true; } }

        public override bool CanSeek { get { return true; } }

        public override bool CanWrite { get { return true; } }

        public override long Length { get { return 0; } }

        public override long Position { get { return _position; } set { _position = value; } }

        public override void Write(byte[] buffer, int offset, int count)
        {
            string strBuffer = Encoding.UTF8.GetString(buffer, offset, count);
            strBuffer = AppendJsonpCallback(strBuffer, HttpContext.Current.Request);
            byte[] data = Encoding.UTF8.GetBytes(strBuffer);
            _responseStream.Write(data, 0, data.Length);
        }

        private string AppendJsonpCallback(string strBuffer, HttpRequest request)
        {
            return request.Params["callback"] +"(" + strBuffer + ");";
        }

        public override void Close()
        {
            _responseStream.Close();
        }

        public override void Flush()
        {
            _responseStream.Flush();
        }

        public override long Seek(long offset, SeekOrigin origin)
        {
            return _responseStream.Seek(offset, origin);
        }

        public override void SetLength(long length)
        {
            _responseStream.SetLength(length);
        }

        public override int Read(byte[] buffer, int offset, int count)
        {
            return _responseStream.Read(buffer, offset, count);
        }
    }
}

4. Register the HttpModule in the service project

4.1 Add referance to the HttpModule assembly to the service project
4.2 Add this code to web.config to register the module
<add name="ContentTypeHttpModule"
                    type="ContentTypeHttpModule.ContentTypeHttpModule, ContentTypeHttpModule" />
This goes under system.web / httpmodules section

5. Add a web project for testing the application

5.1 add the following libs
jquery-1.3.1.js
json2.js
5.2 add new script file caller.js

function test() {
    $.ajax({ url: "http://localhost:1690/JSONP-EndPoint.asmx/Sum",
    data: { x: JSON.stringify("Now i am getting jsop string"), y: JSON.stringify("2nd param") },
        dataType: "jsonp",
        success: function(json) {
            alert(json.d);
        },
        error: function() {
            alert("Hit error fn!");
        }
    });
}

5.3 Add referances to jquery-1.3.1.js and json2.js

5.4 Add Default.aspx page with input button that has onclick=”return test();”

6. Remarks

6.1 I use the JSON.stringify function to serialize the string data parameters.
6.2 .d is a security features on ASP.NET 3.5

Download the code

Report Viewer Control missing Header Icons

Hi,

In IIS 7, we need to make sure we configurate the ReportViewer handler.

You can follow these steps:

• Open Internet Information Services (IIS) Manager and select your Web application.
• Under IIS area, double-click on Handler Mappings icon.
• At the Action pane on your right, click on Add Managed Handler.
• At the Add Managed Handler dialog, enter the following:
  Request path: Reserved.ReportViewerWebControl.axd
  Type: Microsoft.Reporting.WebForms.HttpHandler
  Name: Reserved-ReportViewerWebControl-axd
• Click OK.

For more information, see http://otkfounder.blogspot.com/2007/11/solving-reportviewer-rendering-issue-on.html

ROW_NUMBER(), RANK(), and DENSE_RANK() – Flexibility at a Price

One of the most handy features introduced in SQL 2005 were the ranking functions; ROW_NUMBER(), RANK(), and DENSE_RANK(). For anyone who hasn’t been introduced to these syntactic gems, here’s a quick rundown (for those of you who are very familiar with these functions already, feel free to read through, or skip right down to “There’s No Such Thing as a Free Ride” below).
OK – so the general syntax for any one of these commands is more or less the same:

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>) RANK() OVER ([<partition_by_clause>] <order_by_clause>) DENSE_RANK() OVER ([<partition_by_clause>] <order_by_clause>) 

So the PARTITION BY part is optional, but everything else is required. An example of a non partitioned, and then a partitioned ROW_NUMBER() clause are listed below:

ROW_NUMBER() OVER (ORDER BY TotalDue DESC) ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC) 

The difference between the three functions is best explained using an example. Here’s the data I’m using for this example, in case you want to follow the bouncing ball at home ;-)

CREATE TABLE OrderRanking

   (

   OrderID INT IDENTITY(1,1) NOT NULL,

   CustomerID INT,

   OrderTotal decimal(15,2)

   )

   INSERT OrderRanking (CustomerID, OrderTotal)
SELECT 1, 1000
UNION 
SELECT 1, 500
UNION 
SELECT 1, 650
UNION 
SELECT 1, 3000
UNION 
SELECT 2, 1000
UNION 
SELECT 2, 2000
UNION 
SELECT 2, 500
UNION 
SELECT 2, 500
UNION 
SELECT 3, 500

I’ll use the following (admittedly ugly) query to demonstrate the difference between each function:

SELECT  *,

        ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS RN,

        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RNP,

        RANK() OVER (ORDER BY OrderTotal DESC) AS R,

        RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RP,

        DENSE_RANK() OVER (ORDER BY OrderTotal DESC) AS DR,

        DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS DRP
FROM    OrderRanking
ORDER BY OrderTotal DESC

Excuse the terrible aliases. Anything longer and the code snippets and output in this blog entry get really, really ugly. When we run the query, this is what we get:


So from the example above, we can see that:

• ROW_NUMBER() assigns sequential numbers to each partition in a result set (an unpartitioned result set simply has a single partition), based upon the order of the results as specified in the ORDER BY clause. If you look carefully, you’ll see that the values in column RN are based upon a simple sort of TotalDue, while the values in Column RNP (Row_Number partitioned) are first partitioned or “grouped” by CustomerID, and then numbered by TotalDue, with the row number resetting on change of customer.
• Contrary to popular belief, RANK() does not sort rows based upon how bad they smell. RANK() does much the same thing as ROW_NUMBER(), only it acknowledges ties in the columns specified in the ORDER BY clause, and assigns them the same rank. Where a tie occurs (as was the case for orders 6/3, and 1/5/8), the numbers that would otherwise have been “used up” are skipped, and numbering resumes at the next available number. As you can see, RANK() leaves a gap whenever there is a tie.
• DENSE_RANK() doesn’t like gaps. It’s more of an Abercrombie & Fitch kind of function (ba-dum-ching!). Ohhhhh…that was terrible. My sense of humour may give me up for lent. You might follow it. Anyway….DENSE_RANK() “fills in the gaps”. It starts from the next number after a tie occurs, so instead of 1, 2, 3, 3, 5 you get 1, 2, 3, 3, 4.

There’s No Such Thing as a Free Ride
Ranking functions are not only useful for simple ranking – they’re also great for solving complex problems. In fact, once you get to know them, you’ll find that you’re using them for waaaaaay more than just ranking. In anything from splitting strings to deleting duplicates, ranking functions are the cat’s meow.
But just like most things in life SQL Server, less is more, when you can get away with it. For example, let’s say that you need to get the top order (by TotalDue) for each Customer in AdventureWorks (AdventureWorks2008 in my examples below). You can definitely use ROW_NUMBER (or any of the other ranking functions, for that matter) to do this:

SELECT soh.*
FROM   (SELECT CustomerID, SalesOrderID, TotalDue, 
               ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC) AS RowNumber

       FROM    Sales.SalesOrderHeader) AS soh
WHERE  soh.RowNumber = 1

The WHERE soh.RowNumber = 1 restricts our results to the top order for each customer. Lovely. And the really beautiful thing about this is, if you need the top 2 orders for each customer, or 3, or 4, or x, all you need to do is replace the = 1 with <=2 (for example), and you’re good to go. Now, with that in mind, let’s look at this query:

SELECT soh.CustomerID, soh.TotalDue
FROM   Sales.SalesOrderHeader soh
JOIN   (SELECT     CustomerID, MAX(TotalDue) AS MaxTotalDue

       FROM        Sales.SalesOrderHeader

       GROUP BY CustomerID) AS ttls   ON soh.CustomerID = ttls.CustomerID

                                       AND soh.TotalDue = ttls.MaxTotalDue

If you plug this bad boy in, and run it, you might be surprised by the outcome. It’s actually about half as expensive as the Row_Number solution – but why? Well, as you may or may not know, sorts can be very, very expensive in SQL Server. If we’re only fetching the highest $ sales order for a given customer, the MAX solution does it without a sort, whereas the ROW_NUMBER solution needs to sort (the ORDER BY clause is mandatory, remember).
But there are some caveats to the MAX solution – most notably, how in the world can we get the top 5 orders for each customer? Well…the short answer is, we can’t. We need to change the query up, and in doing so, we’re once again going to incur a sort. Once we get beyond a query that the MAX or MIN tricks can satisfy – for instance, if we need to fetch the top 5 orders for each customer, we may as well take advantage of the ease of coding, and the improved readability of the Row_Number solution. If we want a solution for the “top 5” problem without invoking a ranking function, we’re going to end up with something like this:

SELECT soh.CustomerID, soh.TotalDue
FROM   Sales.SalesOrderHeader soh
WHERE  soh.SalesOrderID IN  
       (SELECT     TOP 5 SalesOrderID

       FROM        Sales.SalesOrderHeader soh2

       WHERE       soh2.CustomerID = soh.CustomerID

       ORDER BY TotalDue DESC)

Which in this case is a very, very crappy alternative to a ranking function. Not only is it uglier, but the query plan isn’t nearly as efficient, and the execution times were consistently about 20% longer in my tests.
Now that said, my tests are against a single data set only, and based upon the nature of your data, your mileage may vary. As a general rule, I would use aggregate functions if I’m only looking for the highest or lowest data point in a series, and a ranking function for anything that can’t be solved by simple aggregation.

How to get relationship between two tables in SQL Server 2008

SELECT f.name AS ForeignKey
       ,SCHEMA_NAME(f.SCHEMA_ID) SchemaName
       ,Object_name(f.parent_object_id) AS TableName
       ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
       ,SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName
       ,Object_name (f.referenced_object_id) AS ReferenceTableName
       ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM   sys.foreign_keys AS f
       INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
       INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE  Object_name(f.parent_object_id) IN ( 'MTAMODE', 'MTACLASS' )