Feeds:
Posts
Comments

In ASP.Net, we develop custom user control as a reusable server control independent of any containing parent aspx page. User control has its own public properties, methods, delegates, etc that can be used by parent aspx page. When a user control is embedded or loaded into a page, the page can access public properties, methods, delegates, etc that are in user control. After loading the user control, there a situation may arise like calling methods in page itself. But when a user control is developed, it has no knowledge of containing page. So it becomes a trick to call the page method.

In .Net, Delegate class has one method DynamicInvoke. DynamicInvoke method is used to invoke (late-bound) method referenced by delegate. We can use this method to call a method in parent page from user control. Let’s try with this example.

First create a user control called CustomUserCtrl. Its code will look some thing like this:

public partial class CustomUserCtrl : System.Web.UI.UserControl
{
private System.Delegate _delWithParam;
public Delegate PageMethodWithParamRef
{
set { _delWithParam = value; }
}

private System.Delegate _delNoParam;
public Delegate PageMethodWithNoParamRef
{
set { _delNoParam = value; }
}

protected void Page_Load(object sender, EventArgs e)
{
}

protected void BtnMethodWithParam_Click(object sender, System.EventArgs e)
{
//Parameter to a method is being made ready
object[] obj = new object[1];
obj[0] = “Parameter Value” as object;
_delWithParam.DynamicInvoke(obj);
}

protected void BtnMethowWithoutParam_Click(object sender, System.EventArgs e)
{
//Invoke a method with no parameter
_delNoParam.DynamicInvoke();
}
}

Then add this user control into an aspx page. The code behind of this page is as:

public partial class _Default : System.Web.UI.Page
{
delegate void DelMethodWithParam(string strParam);
delegate void DelMethodWithoutParam();
protected void Page_Load(object sender, EventArgs e)
{
DelMethodWithParam delParam = new DelMethodWithParam(MethodWithParam);
//Set method reference to a user control delegate
this.UserCtrl.PageMethodWithParamRef = delParam;
DelMethodWithoutParam delNoParam = new DelMethodWithoutParam(MethodWithNoParam);
//Set method reference to a user control delegate
this.UserCtrl.PageMethodWithNoParamRef = delNoParam;
}

private void MethodWithParam(string strParam)
{
Response.Write(“<br/>It has parameter: ” + strParam);
}

private void MethodWithNoParam()
{
Response.Write(“<br/>It has no parameter.”);
}
}

BtnMethodWithParam and BtnMethowWithoutParam are two different buttons on the user control that are invoking the methods in the parent page. On Page_Load of the page, we are setting the references of page class methods to delegate type properties in the user control. Click different buttons of user control, you will see MethodWithParam(string strParam) and MethodWithNoParam() methods called.

This is all we have to do to call page class methods from user control in ASP.Net.

Today we will explore the way of loading ASP.Net user control at run time using jQuery. jQuery has one method load(fn) that will help here. This load(fn) method has following definition.

load (url, data, callback): A GET request will be performed by default – but if any extra parameters are passed, then a POST will occur.
url (string): URL of the required page
data (map – key/value pair): key value pair data that will be sent to the server
callback (callback method): call back method, not necessarily success

Now comes custom HttpHandler that will load the required user control from the URL given by this load(fn) method. We all know that it is either in-built or custom HttpHandler that is the end point for any request made in ASP.Net.

Let’s see by example. In the ASP.Net application, add one aspx page and user control. Then, add one more class derived from IHttpHandler. The aspx html markup will look something like this.

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Load ASP.Net User Control</title>
    <script src="jquery-1.2.6.js"></script>
    <script>
        $(document).ready(function() {
            $("#BtnLoadUserCtrl").click(function() {
                $("#UserCtrl").load("SampleUserCtrl.ascx");
            });
        }); 
    </script>
</head>
<body>
    <form runat="server">
    <div>
    <br />
    <input value="Load User Control" /> <br />
    <div id="UserCtrl"></div>
    </div>
    </form>
</body>
</html>

The code is quite readable. On the click event of BtnLoadUserCtrl button, SampleUserCtrl.ascx user control is being tried to load in the <div> element having id UserCtrl.

Then, write our custom Httphandler called jQueryHandler as below.

public class jQueryHandler:IHttpHandler
{
       public void ProcessRequest(HttpContext context)
       {
                // We add control in Page tree collection
                using(var dummyPage = new Page())
                {
                        dummyPage.Controls.Add(GetControl(context));
                        context.Server.Execute(dummyPage, context.Response.Output, true);
                }
       }

       private Control GetControl(HttpContext context)
       {
                // URL path given by load(fn) method on click of button
                string strPath = context.Request.Url.LocalPath;
                UserControl userctrl = null;
                using(var dummyPage = new Page())
                {
                        userctrl = dummyPage.LoadControl(strPath) as UserControl;
                 }
                // Loaded user control is returned
                return userctrl;
       }

       public bool IsReusable
       {
                get { return true; }
       }
}

Do not miss to add this HttpHandler in the web.config.

<httpHandlers>
                <add verb="*" path="*.ascx" type="JQUserControl.jQueryHandler, JQUserControl"/>
</httpHandlers>

This web.config configuration tells that jQueryHandler will process request for file type having .ascx extension and methods all (GET, POST, etc). The type attribute value is something like:
type=”Namespace.TypeName, Assembly name where Handler can be found”

Now we are ready to test our sample. Run the page, and see on the click of button, the sampleusertCtrl.ascx is loaded.

I hope we can now extend this concept to fit any such programming requirement in future.
Happy Coding!

Notify When an Item is Removed from Cache in ASP.Net

While adding or inserting an item into cache object, we add dependency object as well to ensure the cache is automatically invalidated if any change is detected in that dependent object like file, for example. Then, we again read or update that item from the original source to make sure the item is still fresh with data. This is one of the main reasons that really appeal the use of ASP.Net caching feature where one can decide about dependencies and expiry time policy. There are other properties also that can be used in combination to set the scope of cached object within time frame and location. See also HttpCacheability .

But today we will explore the CacheItemRemovedCallback delegate provided by ASP.Net. It is used to notify the application about cache removal or deletion with some reason. CacheItemRemovedReason enumeration is used as a parameter in call back method to tell the appropriate reason of removal.

Let’s take an example to know more about the CacheItemRemovedCallback delegate.

protected void Page_Load(object sender, EventArgs e)
{
//Fetch item list from cache
ArrayList cacheditems = CachedItemList();
}

private static CacheItemRemovedCallback OnCachedItemRemoved = null;
private ArrayList CachedItemList()
{
//
OnCachedItemRemoved = new CacheItemRemovedCallback(this.CachedItemRemovedCallback);
ArrayList cacheditems = HttpContext.Current.Cache.Get("CACHE_KEY") as ArrayList;
// Found in cache
if (cacheditems != null)
{
return cacheditems;
}
else
{
// Not found in cache
cacheditems = ItemList();
HttpContext.Current.Cache.Insert("CACHE_KEY", cacheditems, new System.Web.Caching.CacheDependency(Server.MapPath("~//CacheDependentFile.txt")), Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default, OnCachedItemRemoved);
return cacheditems;
}
}

private void CachedItemRemovedCallback(string key, Object val, CacheItemRemovedReason reason)
{
//
if (reason == CacheItemRemovedReason.DependencyChanged)
{
// Log the cache key name, reason and time details
// when the cached object was removed from the cache
}
}

private ArrayList ItemList()
{
//
ArrayList lst = new ArrayList();
lst.Add("First Item");
lst.Add("Second Item");
lst.Add("Third Item");
lst.Add("Fourth Item");
lst.Add("Fifth Item");
return lst;
}

Let’s see carefully this call back method:

private void CachedItemRemovedCallback(string key, Object val, CacheItemRemovedReason reason)
{
//
}

The first parameter specifies the cache key name that we used to store an item (an ArrayList collection values). Second parameter is the object that we stored in the cache. The third parameter is an enumeration which has enum values like Removed, Expired, Underused, and DependencyChanged.

In the above example, if any change is made in CacheDependentFile.txt file, the call back method is automatically fired, and the reason captured will be DependencyChanged. Try and see.

Important Point: When using a CacheItemRemovedCallback make sure that you make the callback method ("OnCachedItemRemoved " in the sample above) a static method.

This feature can be used in many cases like logging the reason why any cached item was removed from the cache, and many others depending upon the scenario.

In .Net, we can view variables contents in different ways like local window, quick watch, etc. In System.Diagnostics namespace, we can see a DebuggerDisplayAttribute class that is used to view variables’ contents in debugger window. This attribute can be applied to class, field, property, enum, delegate, struct, etc.  Using this attribute, we can easily view variable contents in debug mode, and those contents are easily visible as data tip when we move mouse pointer over that variable. This attribute becomes quite useful when we have to view inner contents of custom type object variable when it has collection of values.

For example, we will apply this attribute to a class and watch the collection values in debug window.

[DebuggerDisplay("Client Name = {CustomerName} Client Type = {CustomerType, nq}")]
class Customer
{
private string _CustomerName;
private string _CustomerType;
public Customer(string strCustomerName, string strCustomerType)
{
_CustomerName = strCustomerName;
_CustomerType = strCustomerType;
}
public string CustomerName
{
get { return _CustomerName; }
}
public string CustomerType
{
get { return _CustomerType; }
}
}
Now after loading a Customer type collection, we see following view in data tip: 
DebuggerDisplayAttribute 

Fig 1: DebuggerDisplayAttribute changing the data view.
Had not we used DebuggerDisplayAttribute on Customer class type, we would have to traverse a long hierarchy of tree view of each index value of collection object to view data contents.

Programmers often override ToString() method in the custom class type method to view data. But still DebuggerDisplayAttribute wins the heart!

When ToString() method is overridden inside Customer class, then ToString() method of Customer object will result as:

public new string ToString()
{
return ("Customer Name: " + _CustomerName + "\n" + "Customer Type: " + _CustomerType);
}
ToString Method 
Fig 2: Result of overridden ToString() method.

DebuggerDisplayAttribute constructor has only one parameter as string. The {} braces contain field or property or method name. In the example above, we have used this way.

[DebuggerDisplay("Client Name = {CustomerName} Client Type = {CustomerType, nq}")]

One can also quickly see {CustomerType, nq}. Due to this “nq” specifier, Client Type value is shown without double quotes, whereas Client Name value is still in double quote (see Fig 1). The “nq” specifier is used for string type properties.

Happy debugging for next time!

Since .Net 2.0, we are aware of using statement to perform resource intensive operations like database operations, file IO operations, etc. Using statement basically marks a boundary for the objects specified inside using () braces. So when code block using (){} (in C#) or Using – End Using (in VB.Net) is exited either after normal execution or some exception cause, the framework invokes the Dispose method of these objects automatically. Before creating or using any object inside using block, one must make sure that the object type implements the IDisposable interface.
We can specify multiple objects inside using-block, and also write using-blocks in stack as shown in example below.
public class DotNetTips
{
private void DoSomeOperation()
{
using (SqlConnection con1 = new SqlConnection("First Connection String"), con2 = new SqlConnection(("Second Connection String"))
{
//Rest of the code goes here
}
}

private void DoSomeOtherOperation()
{
using (SqlConnection con = new SqlConnection("Connection String"))
using (SqlCommand cmd = new SqlCommand())
{
//Rest of the code goes here
}
}
}
Using statement is useful when we have to call dispose method multiple times on different objects. Otherwise, we will have to call Dispose method on each object as:
if (con != null) con.Dispose();
if (cmd != null) cmd.Dispose();
When the using block code compiles to intermediate language, it translates the using statements into equivalent try/finally block.
I hope this tip can be useful for all.

Most of the time when we re-throw exception from our code block, we supply some meaningful and friendly message if any error condition occurs. This message is supplied in the constructor method of that exception type. But in this re-throwing process, we often forget to preserve Inner Exception property. And when we log the exception message (ex.Message), we lose the details of original exception.

In the example below, we have re-thrown exception with only friendly message in the constructor method.

private void DivideOperation()
{
try
{
int x = 5;
int y = 0;
int result = x/y;
}
catch (Exception ex)
{
throw new DivideByZeroException("Invalid operands were given.");
}
}

Null InnerException

Fig 1: InnerException property is null.

This is of course not a good practice to do exception handling. So to preserve the details of original exception, we have to pass the exception object as second parameter in additional to friendly message as:

private void DivideOperation()
{
try
{
int x = 5;
int y = 0;
int result = x/y;
}
catch (Exception ex)
{
throw new DivideByZeroException("Invalid operands were given.", ex);
}
}

Valid inner exception

Fig 2: InnerException property detail is preserved.

One can see the difference of InnerException property value in these two cases.

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/")]
    [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">
<html xmlns="http://www.w3.org/1999/xhtml" >
<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">
<html xmlns="http://www.w3.org/1999/xhtml" >
<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.

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">
<html xmlns="http://www.w3.org/1999/xhtml" >
<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.

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.

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.

Older Posts »