Goodbye Stored procedures, It’s the time to move on "Linq to Sql"

ORM Vs Stored Procedures. Which is the best approach?

For the last few years, many developers and architects are engage in a series of debates about O/RM Vs Stored Procedures. Many of them argue for ORM and others are argue for stored procedures. The interesting thing is that people with highly object orientation sense, recommends ORM. The J2EE community strongly recommending the ORM approaches instead of using stored procedures. Some .NET developers coming from Visual Basic 6.0 background supports stored procedures. Hibernate and NHibernate (.NET version of java version Hibernate) are the highly successful ORM that using both .NET and J2EE community. Then which is the best approach for data persist? Personally I hate stored procedures and strongly recommend for ORM instead of using the legacy stored procedure programming.

Why I hate stored procedures?

Stored Procedures are written by DB languages such as PL/SQL and T-SQL and this type of languages are not designed for writing business logic and debugging process is really a nightmare. And stored procedures hide the business logic and lacks readability of business process. If you are going to port DB from one RDBMS to another one, you have to re-write your all stored procedures. If you want to run your product on multiple databases, the ORM is the right approach. And ultimately the stored procedure restricts the proper business abstraction. Many people argue that stored procedure provides better performance than dynamic generated Sql from an ORM and people believed that all stored procedure are pre-compiled. According to Microsoft Sql Server documentation, Sql Server does cache the execution plan for stored procedure instead of pre-compiled. Have a look at the MSDN article Execution Plan Caching and Reuse. And I believe that maintainability, scalability and proper abstraction are the key factors of enterprise applications. The ORM approach enables these benefits. If you are a .NET developer, there is happy news for you. A new ORM named Linq to Sql coming from the Redmond campus along with the .Net framework 3.5.

What is LINQ to SQL?

LINQ to SQL is an O/RM (object relational mapping) of the .NET Framework 3.5. It provides you to model a relational database using .NET classes. You can then query the database using LINQ, as well as insert, update and delete data from it. LINQ to SQL supports all types of database objects such as views, and stored procedures and also transactions. It also provides an easy way to integrate data validation and business logic rules into your data model. Visual Studio 2008 provides a Linq to SQL designer that enables to model and visualize a database as a LINQ to SQL object model. You can create the all database representations using the Linq to SQL designer. With the Linq to SQL designer, you can drag and drop the tables into the Linq to SQL designer surface and can represent the relations between tables. Linq to SQL allows you to model classes that map to tables within a database. These classes are known as "Entity Classes" and instances of them are called "Entities". Like other OR/Ms, the Linq to SQL OR/Ms will generate the SQL statements at the runtime when interacting with the Entity Classes.

Lets look at the below business object that mapped the customers table using Linq to Sql.

[Table(Name="Customers")]
public class Customer{
[Column(Id=true)]
public string CustomerID;[Column]
public string CustomerName;[Column]
public string City;[Column]
public string Phone;
}

After modeling the Database with Linq to Sql, we can do all DB operations against it.
The below code is the query against Customer object that represents the Customer table.

DataContext db = new DataContext();
var q = from c in db.Customer where c.City == "Cochin" select c;

In the above query will select customers of city Cochin .The DataContext represent an abstraction of your database.The below code is update existing customer

DataClassesDataContext db=new DataClassesDataContext();
Customer cust=db.Customer.Single(c=> c.CustomerID ==”C101”);
cust.Phone="919847059589";
db.SubmitChanges();

The below code is add new customer
DataClassesDataContext db=new DataClassesDataContext();
Customer cust=new Customer();
cust.CustomerName=”ABC Ltd”;
cust.City=”Mumbai”;
cust.Phone=”919847059589”;

db.Customer.Add(cust);
db.SubmitChanges();
The below code is delete customer

DataClassesDataContext db=new DataClassesDataContext();
Customer cust=db.Customer.Single(c=> c.CustomerID ==”C101”);
db.Customer.Remove(cust);

The below code is using a join query

var orders =from o in db.Ordersjoin c in db.Customer on o.CustomerID equals c.CustomerID where c.CustomerID == "C101"select new {c.CustomerName, o.ShipName, o.ShipAddress };

Linq to Sql is an exciting ORM tool from Microsoft and I hope that people will use this ORM along with .NET 3.5 applications.

New features of WCF in .Net 3.5

The upcoming .Net 3.5 framework has cool new features for WCF. Some of the new features are
1. Ajax enabled WCF web services.
2. Improved Support for WS-* Standards.
3. WCF designer for developing configuration files.
4. Support for REST-Style services.
5. Improved interaction between WF and WCF.

WCF Rocks !

WCF is Rocking in the SOA world. Many companies are adopting WCF as their platform for building Service Oriented Applications. Below are some of the clients that using WCF.

1. Avaya
2. Choicelinx
3. Crutchfield
4. FNAC
5. Kiwibank
6. Nike
7. OPC Foundation
8. OTTO
9. Pfizer
10. Schneider Electric
11. ST Electronics
12. Commonwealth of Massachusetts
13. Thomson Financial
14. Thomson Tax and Accounting
15. Tyco Fire and Security

JSON Supports in WCF with .Net 3.5

JSON (JavaScript Object Notation) is a lightweight data-interchange format. JSON is a text format language and becoming a common data-interchange standard for Ajax communication. Most of the Ajax applications are using JSON for communication between the client and server because JSON is faster than XML and easy to manipulate.

In .Net 3.5, WCF can supports JSON. You can send and receive JSON objects from WCF. You need to change config file to support JSON.

The following config that enables JSON support from WCF


<service name="OrderService">
<endpoint contract="IOrders"
binding="webHttpBinding"
bindingConfiguration="jsonBinding"
address=""
behaviorConfiguration="jsonBehavior" />
</service>

<webHttpBinding>
<binding name="jsonBinding" messageEncoding="Json" />
</webHttpBinding>
<behaviors>
<endpointBehaviors>
<behavior name ="jsonBehavior">
<webScriptEnable />
</behavior>
</endpointBehaviors>
</behaviors>

The UpdateProgress Control of ASP.net Ajax Extension

Check my codeproject article from http://www.codeproject.com/useritems/updateprogress.asp

ASP.net Ajax - LoadScriptsBeforeUI Property

LoadScriptsBeforeUI property of the ScriptManager class allows the user to control the nature of the download of the Ajax scripts. The boolean value indicate that the scripts are loaded before or after the page UI is loaded. If LoadScriptsBeforeUI is true( default value is true), all scripts will load before the page UI. If the value is false, the scripts will load after the page UI loaded. The end result is UI can quickly load and it will help the users to view the content first. So it is recommended to immediate display for your pages.

The syntax is
<asp:ScriptManager ID="ScriptManager1" runat="server"
EnablePartialRendering="true" LoadScriptsBeforeUI="false">

Ajax: The next evolution of web applications

The below is my two year old blog article published in many community web sites.

What is Ajax

Today Ajax is the hottest buzzword of the web. Thanks to web developers of google, they are making Ajax more popular. Ajax is not a new technology; it combines a lot of existing technologies. Ajax stands for Asynchronous JavaScript and XML. It is a combination of existing technologies such as DHTML, CSS, JavaScript, DOM, XML, XSLT and XMLHttpRequest. The core component of Ajax is XMLHttpRequest that enables us to execute server side code without any page refresh. Ajax can create an asynchronous request to the web server using JavaScript and XMLHttpRequest object, and map a function (a Callback method) to be executed when the response is received. You can execute the asynchronous request without any page refresh or page post back. The beauty of Ajax is that, you can execute server side code with out any page refresh. Ajax is a world of “No Page Refresh” and rich user interface like windows applications. In traditional web applications, the user actions in the user interface invoke an HTTP request to the web server. Then the server will do lot of process on the server and then returns an HTML page to the client. In Ajax applications, the request is not for GUI or HTML, It is only for data. After fetching the data, you can create the user interface from the client using JavaScript and DOM. Ajax is a client-side technology and can interact any server side technologies such as ASP.net, JSP, PHP, ColdFusion, and others.

Advantages of Ajax

1.Rich User Interface

Ajax allows us to create highly interactive user interface. A user don’t like page refresh continuously. In Ajax, we can do lot of things without any page refresh. Ajax is crossing the barriers between windows applications and web applications. Ajax communities are fans of rich user interface.

2. High Performance

Another advantage of Ajax is better performance than traditional web applications. In the Ajax applications there will be no post back to the server that will render entire GUI as HTML. Ajax request is only for page data. So it enable us better performance.

Google: The early adopter of Ajax

Ajax is not a new technology, it is old thing. But google is leveraging very highly in Ajax and they are making interactive Ajax web applications such as gmail, Google Groups, Google Suggest and Maps. The popularity behind the Ajax is the success of google applications.

Methods of XMLHttpRequest

abort()
Abort the current request.
getAllResponseHeaders()
Returns all the response headers for the HTTP request as key/value pairs.
getResponseHeader("header")
Returns the string value of the specified header.
open("method", "url")
Sets the stage for a call to the server. The method argument
can be either GET, POST, or PUT. The url argument
can be relative or absolute. This method includes three
optional arguments.
send(content)
Sends the request to the server.
setRequestHeader("header", "value")
Sets the specified header to the supplied value.

Properties of XMLHttpRequest

Onreadystatechange
The event handler that fires at every state change, typically a call to a
JavaScript function.
readyState
The state of the request. The five possible values are 0 = uninitialized,
1 = loading, 2 = loaded, 3 = interactive, and 4 = complete.
responseText
The response from the server as a string.
responseXML
The response from the server as XML. This object can be parsed and
examined as a DOM object.
status
The HTTP status code from the server (that is, 200 for OK, 404 for Not
Found, and so on).
statusText
The text version of the HTTP status code (that is, OK or Not Found, and
so on).

Instantiate an XMLHttpRequest object and send request to server

<script type=\"text/JavaScript\">
var xmlHttp;
//function creating new instance of XMLHttpRequest object
function createXMLHttpRequest() {
//In IE XMLHttpRequest is a ActiveX object
if (window.ActiveXObject) {
xmlHttp = new ActiveXObject(\"Microsoft.XMLHTTP\");
}
//non IE browsers
else if (window.XMLHttpRequest) {
xmlHttp = new XMLHttpRequest();
}
}
//function call asynchronous request to server
function startRequest() {
createXMLHttpRequest();
//set callback method
xmlHttp.onreadystatechange = callBackMethod;
var url=\"MyAjaxServer.aspx\";
//instantiate request
xmlHttp.open(\"GET\",url, true);
//send request to server
xmlHttp.send(null);
}
//callback method
function callBackMethod()
{
}
</script>

JSON : An Emerging standard for Ajax Communication
An alternative to XML is JSON, which you can find at http://www.json.org/. JSON stands for JavaScript Object Notation and is a lightweight data-interchange format. It is a standard for communication between the client and server. JSON is a text format that is language independent but that uses conventions similar to the C family of languages such as C, C#, JavaScript, and others. Today JSON is a growing data-interchange standard for Ajax communication. Most of the Google’s Ajax applications are using JSON for communication between the client and server. JSON is faster than XML.

Microsoft ASP.NET Ajax : The next evolution of Ajax
Microsoft ASP.net Ajax is the first scalable and reliable platform for building Ajax applications. Today ASP.net is the undisputed leader in the server-side programming and faster than JSP and PHP. ASP.net Ajax is the next level of growing ASP.net technology. Today the problem of Ajax is high complexity for implementing it. A developer should be highly expert in JavaScript for implementing Ajax. ASP.net Ajax is reducing this complexity for implementing Ajax. ASP.net Ajax extends the Ajax concept by providing a rich, integrated server development platform in ASP.NET 2.0. The ASP.NET 2.0 Ajax package of client and server-side development tools and components is a significant evolution and enhancement of the Ajax concept.

A search example application that implementing Ajax

In this sample below, I have written a sample code in order to demonstrate a search functionality. There is an input textbox where user can input a seacrhtext.On each keypress of textbox, an asynchronous search is performed without any page refresh and the results in dynamically generated as a table

HTML Code

<b>Search For Books</b>

<span id=\"header\"></span>

<table width=\"500\" border=\"0\">

<tbody>

<tr>

<td>Search</td>

<td><input onkeyup=\"doSearch(this.value);\" size=\"40\">

</td>

</tr>

<tr>

<td colspan=\"2\">

<table id=\"tblSearchResults\" width=\"100%\" border=\"0\">

<tbody id=\"tbSearchResults\"></tbody>

</td>

</tr>

</tbody>

</table>

td></tr></tbody></table>

JavaScript Code(search.js)
//global variable for hold XMLHttpRequest Object
var xmlHttp;
//Creating and setting the instance of appropriate XMLHTTP Request object to a "xmlHttp" variable
function CreateXmlHttp()
{
//Creating object of XMLHTTP in IE
try
{
//it will work if IE have JavaScript version 5.0
xmlHttp = new ActiveXObject("Msxml2.XMLHTTP");
}
catch(e)
{
try
{
xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
}
catch(oc)
{
xmlHttp = null;
}
}
//Creating object of XMLHTTP for non-IE browsers
if(!xmlHttp && typeof XMLHttpRequest != "undefined")
{
xmlHttp = new XMLHttpRequest();
}
}
//function to search
function doSearch(searchText)
{
//create XMLHttpRequest Object
CreateXmlHttp();
//Requested url
var ajaxRequest="ProcessSearch.aspx?search="+searchText;
//set callback function
xmlHttp.onreadystatechange = callBackMethod;
//Initializes request
xmlHttp.open("GET",ajaxRequest, true);
//send request
xmlHttp.send(null);
}
function callBackMethod()
{
//if request completed sucessfully clear the previous result and show the new search results
if(xmlHttp.readyState == 4)
{
if(xmlHttp.status == 200)
{
clearResults();
showResults();
}
}
}
//function clear previous results
function clearResults()
{
var tableBody = document.getElementById("tbSearchResults");
while(tableBody.childNodes.length > 0) {
tableBody.removeChild(tableBody.childNodes[0]);
}
}
//function show search results in a table
//dynamically creating table rows for show the results
function showResults() {
var results = xmlHttp.responseXML;
var book = null;
var title = "";
var author= "";
var publisher = "";
var books = results.getElementsByTagName("book");
if(books.length<1)return; i =" 0;" book =" books[i];" title =" book.getElementsByTagName(" author =" book.getElementsByTagName(" publisher =" book.getElementsByTagName(" row =" document.createElement(" cell =" createCellWithText(title);" cell =" createCellWithText(author);" cell =" createCellWithText(publisher);" cell =" document.createElement(" textnode =" document.createTextNode(text);" row =" document.createElement(" cell =" createCellWithText(" cell =" createCellWithText(" cell =" createCellWithText(" search =" Request["> 0)
{
Response.Clear();
DataSet dsResults=new DataSet();
//call business layer method for search results
dsResults =Books.GetBookList(dsResults);
string resultsString= dsResults.GetXml();
Response.Clear();
Response.ContentType ="text/xml";
Response.Write(resultsString);
//end the response
Response.End();
}
else
{
//clears the response written into the buffer and end the response.
Response.Clear();
Response.End();
}
}
else
{
//clears the response written into the buffer and end the response.
Response.Clear();
Response.End();
}
}

Welcome to the Ajaxian community.If you want any help to implement Ajax enabled applications feel free to contact me via shiju.varghese@gmail.com