Archive

Archive for July, 2009

jQuery, JSON and ASP.Net Web Service

Today we will see a simple example of jQuery, JSON and ASP.Net Web Service. We will mainly see how to consume ASP.Net web service web methods using both jQuery and JavaScript and see how to handle DateTime object result. Also, how jQuery can be used to call a web method by passing arguments values.

We know JSON type lacks Date/Time literal because JavaScript too does not have Date/Time literal directly. I think JavaScript author was smart enough to leave Date/Time literal! As Date/Time is a complex subject, that should be better left for programmers who write code to interact with servers that have different running time zones, and we know JavaScript is a client-side scripting language.

Let’s see how to handle this with examples. Write a simple web service as shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Xml.Serialization;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
namespace JsonAndScriptSvc
{
/// <summary>
/// Summary description for WebServerDate
/// </summary>
[WebService(Namespace = “http://tempuri.org/&#8221;)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script,
// using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WebServerDate : System.Web.Services.WebService
{
//
private JsonDateSez objJson = null;
public WebServerDate()
{
objJson = new JsonDateSez();
}
[WebMethod]
public object GetServerDate()
{
JavaScriptSerializer serz = new JavaScriptSerializer();
return serz.Serialize(objJson);
}
[WebMethod]
public Int32 GetSumResult(Int32 xValue, Int32 yValue)
{
return (xValue + yValue);
}
}

public class JsonDateSez
{
public DateTime JsonSerzDate
{
get { return new DateTime(2009, 9, 17); }
}
}
}

Then, add one Default.aspx page whose mark-up code looks like this:

<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”Default.aspx.cs” Inherits=”JsonAndScriptSvc._Default” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html xmlns=”http://www.w3.org/1999/xhtml&#8221; >
<head runat=”server”>
<title>JavaScript and JavaScriptSerializer</title>
<script type=”text/javascript” language=”javascript” src=”JSWebService.js”></script>
</head>
<body>
<form id=”frmJson” runat=”server”>
<asp:ScriptManager ID=”ScriptManager1″ runat=”server” EnablePageMethods=”true”>
<Services>
<asp:ServiceReference Path=”~/WebServerDate.asmx” />
</Services>
</asp:ScriptManager>
<div>
<input id=”BtnFetchDate” type=”button” value=”Check Server Date” onclick=”ServerDate();”/>
<br />
</div>
</form>
</body>
</html>

The onClick() event of BtnFetchDate button will call ServerDate() method in JSWebService.js file. This js file has two methods as:

function ServerDate() {
JsonAndScriptSvc.WebServerDate.GetServerDate(ReceivedDate);
}
function ReceivedDate(result) {
var svrdt = Sys.Serialization.JavaScriptSerializer.deserialize(result);
alert(“Result in Json Format: ” + result + “\n” + “Result desired: ” + svrdt.JsonSerzDate);
}

Once clicked, we may see result as shown in figure below.

JSON-WebService

We saw how JavaScriptSerializer class helped to get the desired date-time result.

Similarly, we can see jQuery based example similar to above one. Let’s add another JQryPage.aspx page whose mark-up may look like this:

<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”JQryPage.aspx.cs” Inherits=”JsonAndScriptSvc.JQryPage” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html xmlns=”http://www.w3.org/1999/xhtml&#8221; >
<head runat=”server”>
<title>JQuery and Web Service</title>
<script type=”text/javascript” language=”javascript” src=”jquery-1.2.3.min.js” />
<script type=”text/javascript” language=”javascript” src=”JSWebService.js”></script>
<script type=”text/javascript” language=”javascript”>
/* jQuery calling webmethod to fetch server date-time values */
function JQueryWebMethod() {
$.ajax({
type: “POST”,
url: “WebServerDate.asmx/GetServerDate”,
data: “{}”,
contentType: “application/json;charset=utf-8”,
dataType: “json”,
success: function(result) {
alert(“Result in Json Format: ” + result.d);
},
error: function(request, status, throwerror) {
alert(status);
}
});
}
/* jQuery calling webmethod by passing two arguments */
function JQuerySumResultWebMethod() {
$.ajax({
type: “POST”,
url: “WebServerDate.asmx/GetSumResult”,
data: “{‘xValue’:’10’,’yValue’:’15’}”,
contentType: “application/json;charset=utf-8”,
dataType: “json”,
success: function(result) {
alert(result.d);
},
error: function(request, status, throwerror) {
alert(status);
}
});
}
</script>
</head>
<body>
<form id=”form1″ runat=”server”>
<asp:ScriptManager ID=”ScriptManager1″ runat=”server” EnablePageMethods=”true”>
<Services>
<asp:ServiceReference Path=”~/WebServerDate.asmx” />
</Services>
</asp:ScriptManager>
<div>
<input id=”BtnJQry” type=”button” value=”jQry Check Server Date” onclick=”JQueryWebMethod();”/>
<br /><br />
<input id=”BtnSumResult” type=”button” value=”jQry Sum Result” onclick=”JQuerySumResultWebMethod();”/>
</div>
</form>
</body>
</html>

Run this page, click BtnJQry button to see the result of Date/Time. We may see the same raw result that we received in JavaScript before deserializing ‘result’ object using JavaScriptSerializer class. Click other button called BtnSumResult will get the sum result from web method by passing two arguments values.

The documentation of $.ajax() global method can be found at http://docs.jquery.com/Ajax/jQuery.ajax. However, we should remember type, url, data, contentType and dataType key values when constructing parameters for $.ajax() global method.

Thus, we saw jQuery, JSON and ASP.Net Web Service example.

JSON and JavaScript

We all know that XML is widely used in applications that leverage service-oriented architectures for sending and receiving data. But it cannot always be an ideal candidate to carry light-weight data. So JSON like format is increasingly becoming popular these days. JSON (JavaScript Object Notation) is a subset of the object literal notation of JavaScript. JSON is a text format that is completely language independent. When some data is JSON formatted, it is concise, human readable and easy to write.

Let’s see one JSON format data.

{
“Asia”:”China”,
“Africa”:”Zimbabwe”,
“North America”:”Canada”,
“South America”:”Brazil”,
“Antarctica”:”Netherlands”,
“Europe”:”Russia”,
“Australia”:”Sydney”,
“Cities”:[
“Shanghai”,
“Moscow”,
“Johannesburg”,
“Vancouver”,
“New York”,
“Kathmandu”,
“Singapore”
]
}

If the same data were to be represented in XML, then the structure would like as:

<?xml version=”1.0″ encoding=”utf-8″ ?>
<CountryCities>
<Asia>China</Asia>
<Africa>Zimbabwe</Africa>
<NorthAmerica>Canada</NorthAmerica>
<SouthAmerica>Brazil</SouthAmerica>
<Antarctica>Netherlands</Antarctica>
<Europe>Russia</Europe>
<Australia>Sydney</Australia>
<Cities>
<City>Shanghai</City>
<City>Moscow</City>
<City>Johannesburg</City>
<City>Vancouver</City>
<City>New York</City>
<City>Kathmandu</City>
<City>Singapore</City>
</Cities>
</CountryCities>

As compared to XML format, we see JSON is more concise, clear and has higher data-to-markup ratio. That’s why; JSON can be a better candidate for data interchange in client and server communication. However, we should not take JSON as a document format. Also, it is not a markup language. It is a subset of the object literal notation that JavaScript supports natively.

According to http://www.json.org/fatfree.html, the object notation in JSON can be done in two ways: Unordered key-value pairs and Ordered lists.

In the example above, the continent and country names are unordered key-value notation.
“Asia”:”China”,
“Africa”:”Zimbabwe”,
“North America”:”Canada”,
“South America”:”Brazil”,
“Antarctica”:”Netherlands”,
“Europe”:”Russia”,
“Australia”:”Sydney”,

And the city names are ordered list.
“Cities”:[“Shanghai”, “Moscow”, “Johannesburg”, “Vancouver”, “New York”, “Kathmandu”, “Singapore”]

The key is always represented in string type, while values may be any JSON types (string, number, Boolean, object, array and null).You may wonder why not date/time also. Because JavaScript also does not support date/time literal directly, instead JavaScript uses Date object. Se before representing any data in JSON format, one should also check for the compatible and supporting types. For .Net, we can see more about JSON types at http://msdn.microsoft.com/en-us/library/bb299886.aspx. There are plenty of open source tools and libraries that will help write and parse valid JSON text. Similarly, we can also validate the syntax check of JSON text using JSONLint. I think we may know about JSLint as well. It is a code quality tool and is used to validate JavaScript code.

Let’s come to an example of JSON and JavaScript. We will take the same JSON text in the example above to parse and convert into an object.

<%@Page Language=”C#” AutoEventWireup=”true” CodeBehind=”Default.aspx.cs” Inherits=”JsonAndJS._Default” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html xmlns=”http://www.w3.org/1999/xhtml&#8221; >
<head runat=”server”>
<title>Json and JavaScript</title>
<script language=”javascript” type=”text/javascript”>
var JsonString =
‘ {‘ +
‘ “Asia”: “China”,’ +
‘ “Africa”: “Zimbabwe”,’ +
‘ “NorthAmerica”: “Canada”,’ +
‘ “SouthAmerica”: “Brazil”,’ +
‘ “Antarctica”: “Netherlands”,’ +
‘ “Europe”: “Russia”,’ +
‘ “Australia”: “Sydney”,’ +
‘ “Cities”:  [‘ +
‘             “Shanghai”,’ +
‘             “Moscow”,’ +
‘             “Johannesburg”,’ +
‘             “Vancouver”,’ +
‘             “New York”,’ +
‘             “Kathmandu”,’ +
‘             “Singapore”‘ +
‘             ]’ +
‘}’;
function ListJsonValues() {
var CountryCities = eval(“(” + JsonString + “)”);
var CityNames = “<br />” + “City Names: ” + “<br />”;
for (var j = 0; j < CountryCities.Cities.length; j++) {
CityNames += “City: ” + (j + 1) + ” – ” + CountryCities.Cities[j] + “<br />”;
}
document.getElementById(‘dvPopularCities’).innerHTML = CityNames;
var CountryNames = “”;
CountryNames = “Countries: ” + “<br/>” + CountryCities.Asia + ” – ” + CountryCities.Africa + ” – ”
+ CountryCities.NorthAmerica + ” – ” + CountryCities.SouthAmerica + ” – ” + CountryCities.Antarctica + ” – ”
+ CountryCities.Europe + ” – ” + CountryCities.Australia + “<br />”;
document.getElementById(‘dvCountryList’).innerHTML = CountryNames;
}
</script>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<input id=”BtnList” type=”button” value=”List Country and City Names” onclick=”ListJsonValues();” />
</div>
<div id=”dvCountryList”>
</div>
<div id=”dvPopularCities”/>
<br />
</form>
</body>
</html>

One should try this example to see the result.
We see how the eval() method of JavaScript is used to parse the JSON text and return an object to CountryCities variable object. The extra parenthesis in eval() method is used to treat the JSON text as expression. But since eval() always parses the input text blindly, we have to be careful about the nature of input text expression from both coding and security point of views. There are various open source libraries in JavaScript which provide parse methods to return valid object out of JSON text.

Seeing the ease and nature of JSON text in AJAX world, JSON is gaining popularity rapidly. Even its MIME media type is now formalized as application/json. JSON may be favored among programmers to make point to point integration and data exchange between browser client and web server, but not similar to XML where it has huge support of integration in SOA world. But yes, if the data is small, JSON is a good option.

T-SQL and CLR Code in .Net 3.5 and SQL Server 2008

Due to CLR (Common Language Runtime) integration with SQL Server 2005/2008, programmers can now multiply the power of database programming using T-SQL and advanced programming languages like C# and VB.Net. We can write stored procedures, user-defined functions, triggers, aggregates and user-defined types in managed code. But we have to know what is best at what situation. T-SQL is still the best way to interact with database server and data. But what when T-SQL has to access files, environment variables or registry? Then we use extended stored procedures to perform such operations. Since extended stored procedures are not managed code, it cannot be considered a good option. But since SQLCLR objects are managed code and run within SQL Server (in-process), it can be a good option.

T-SQL code can run only at the server end, but SQLCLR code can run at both the client and server ends. And hence SQLCLR code can perform CPU intensive tasks at both the ends. This is one of the most compelling reasons to choose SQLCLR object over T-SQL code. Now a day, even client PCs are very powerful. SQLCLR objects can be used to perform complex string operations, encryption-decryption, array operations, accessing external resources like files and registry, etc. Apart from all these, we should not forget the benefits of .Net code.

Let’s take a simple example to calculate factorial of a number in both T-SQL and SQLCLR.

CREATE FUNCTION dbo.SqlFactorial(@factno decimal(38, 0))
RETURNS decimal(38, 0)
AS
BEGIN
DECLARE @temp decimal(38, 0)
IF (@factno <= 1)
SELECT @temp = 1
ELSE
SELECT @temp = @factno * dbo.SqlFactorial(@factno – 1)
RETURN @temp
END
GO
This will create one user-defined function called dbo.SqlFactorial.

Now create SQLCLR user-defined function in .Net 3.5.
Steps:
Create one project: ->Visual C# -> Database – > SQL Server Project.
A database reference will be asked for where to create the object. The wizard will guide you; it’s simple.
Right click on the project name, select Add -> User-Defined Function… Name it CLRFactorial.
Write a factorial method as:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static Double CLRFactorial(Double factno)
{
//
if (factno <= 1)
factno =1;
else
{
factno = factno * CLRFactorial(factno – 1);
}
return factno;
}
};
To test it, build the project. Modify the Test Scripts – > Test.sql file in the solution explorer of the same project to call this method: Select dbo.CLRFactorial(21);

Run the project in debug or non-debug mode to see its result in the output window.
Now it’s time to deploy this assembly to SQL Server database whose reference we added when following SQL Server Project wizard. Choose Build – > Deploy Solution. This will add CLRFactorial assembly in the database node at: -> Programmability -> Assemblies -> CLRFactorial. Then, enable CLR in SQL Server by running following command.

EXEC sp_configure ‘clr_enabled’, 1;
RECONFIGURE;

By this time, we have two user-defined functions to calculate factorial of a number: SqlFactorial and CLRFactorial. Test both the functions in Query Analyzer for numbers 5, 10, 25, 30, 32 and so on. Now run.

SELECT dbo.SqlFactorial(33);

A lovely message comes in result as:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

But we do not get such error message with SQLCLR function. We get result 8.68331761881189E+36 in exponential form.

See the benefit of SQLCLR code! But one should also know the PERMISSION_SET of such assembly in SQL Server before writing magical code in .Net.

MERGE Statement in SQL Server 2008

While working with table records from more than one table, there is often need of synchronizing data across many tables. For this purpose, we generally write UPDATE, INSERT and DELETE commands one after another based upon certain conditions. Oracle has already MERGE command to perform inserts and updates into a single table in a single command since Oracle9i. But with SQL Server 2008, there is MERGE command to do updates, inserts and deletes in a single statement. MERGE statement will take the source data from a table or view or query, and then perform DML operations (insert, update, delete) if the right conditions match.

Let’s see from an example where we have two tables: dbo.BusinessClients_Old as ‘Target’ table and dbo.BusinessClients as ‘Source’ table. We are going to synchronize the records between these two tables. We will update ‘ContactNo’ in Target table, insert new record into Target table that is only in Source table and delete unmatched record in Target table. The result will be synchronized records between both the tables.

InitialDataFig 1: Initial data found in both the tables

Now, issue a MERGE command as:

MERGE dbo.BusinessClients_Old as tblTarget
USING dbo.BusinessClients as tblSource
ON (tblTarget.ID = tblSource.ID)
WHEN MATCHED THEN
UPDATE SET tblTarget.ContactNo = tblSource.ContactNo
WHEN NOT MATCHED BY SOURCE THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (Name, Email, Profession, ContactNo, ID)
VALUES (tblSource.Name, tblSource.Email, tblSource.Profession, tblSource.ContactNo, tblSource.ID)
OUTPUT $action, inserted.id as InsertedId, inserted.name AS InsertedName, inserted.contactno AS InsertedContactNo,
deleted.id AS DeletedId, deleted.name AS DeletedName;

The result is shown in figure below. Merge ResultFig 2: Result of MERGE command that is synchronized records now

See the updated ContactNo and inserted new record in the Target table: dbo.BusinessClients_Old. Synchronized dataFig 3: Synchronize records in Target table: dbo.BusinessClients_Old

Points to be noted:
MERGE statement must end with a semicolon. The OUTPUT clause is optional here which is used to show recently inserted or updated or deleted records in the same session by INSERT or UPDATE or DELETE statement. This OUTPUT clause is there since SQL Server 2005. We may be aware of ‘inserted’ and ‘deleted’ tables if we have made our hands dirty by writing triggers. ‘inserted’ and ‘deleted’ are temporary tables created automatically by SQL Server whenever there is row modification in a table.

We can remove the OUTPUT clause, but it is there to see what all modifications happen when issuing MERGE command. ‘$action’ is a nvarchar(10)  column that shows which action was performed by the MERGE command: INSERT or UPDATE or DELETE based upon the condition specified. Better to write ‘$action’ always in lower case as its case is dependent upon database collation for case sensitiveness. For WHEN clause, we have to be little careful. I suggest to refer to SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms130214.aspx for more details on WHEN clause.

That’s all.

Categories: CodeProject, Sql Server Tags:

Fiddler Tool and ASP.Net Applications

I have been working with one project in ASP.Net for quite long time. Most of the time, I see my colleagues fixing some problems by debugging .Net code. It is a very good way to understand application codes and .Net classes and objects. But what I feel is that Asp.net developers should equally try to know browsers they are using. When we come to browser, there comes HTTP protocol, GET/POST methods, headers, session, cache, cookies, content-type, status codes, etc. And we know all these objects are not far from Asp.net web pages or say request and response. If we know and take care of these things while debugging the application, I am sure we can save lots of time to fix the problems.

Fiddler tool can be used to know details of HTTP web request, response and many other objects that are part of web pages and browsers. Fiddler is an HTTP Debugging Proxy which logs all HTTP traffic between your computer and the Internet. But while debugging Asp.net application with Fiddler, one should not use ‘localhost’ as the machine name in the address bar; we have to use computer name or IP address.

For example,
http://Dev36/MyWeb/frmLogin.aspx is the right way, not http://localhost/MyWeb/frmLogin.aspx . Fiddler tool is easily downloadable from http://www.fiddler2.com/fiddler2/version.asp .

One thing that I found very useful about this tool is HTTP response status codes returned for all the requests made. And of course, its capability to show Parent Request, Child Requests and Duplicate Requests of any content-type. One can see from the figure shown below.FiddlerFig 1: Fiddler used to capture HTTP traffic

When we look at Result column on the left grid, we may see mostly 200, 204, 301, 404, 501, etc. like numeric codes. These are nothing but HTTP response status codes returned for each content or request type. These status codes have different meaning. I would like to summarize here in brief.

Status Codes Summary to remember:

Status Code Meaning
1xx Informational Request received and continuing the process
2xx Success Client request successfully received, understood and accepted
3xx Redirection Generally URL redirection
4xx Client Error Request contains bad syntax, IIS permissions, wrong paths of any content
5xx Server Error Server error when request arrived

1xx can be 101 or 102. Similarly, 2xx can be 204 or 206. The post fix xx will be always some numbers as recognized by HTTP standard. These results can be really helpful in debugging scenario when web pages have rich contents.

Fiddler tool is very user friendly and one will always like to use it.