PIVOT/UNPIVOT expression in SQL Server.

Pivot expression in SQL converts values returned by a query into columns. For example in below example, the temp table contains revenue data for different months for companies. If the result has to be displayed with each month as columns , then it can done easily using PIVOT expression.

---

Create temp table to store data

CREATE table #tmp (comp_id int,comp_name varchar(20), [month] int,rev int)

GO

insert into #tmp values (200,'comp1',1,32144)
insert into #tmp values (200,'comp1',2,56234)
insert into #tmp values (200,'comp1',3,1000)
insert into #tmp values (211,'comp2',1,2364)
insert into #tmp values (211,'comp2',2,8762)
insert into #tmp values (211,'comp2',3,7644)
insert into #tmp values (211,'comp2',3,1111)
insert into #tmp values (211,'comp2',3,1111)
insert into #tmp values (213,'comp3',3,1111)

Now if we want to show the month as columns (like ‘1’,’2’,’3’ etc) we can use PIVOT expression as given below

SELECT * FROM #tmp t PIVOT 
 (SUM(rev) FOR [month] IN ([1],[2],[3])) pvt

UNPIVOT

We can also UNPIVOT the result as given below

/*get  ‘pivot’ data into another temp table #tmp2*/

SELECT * INTO #tmp1 FROM #tmp t PIVOT 
 (SUM(rev) FOR [month] IN ([1],[2],[3])) pvt

/*UNPIVOT the data*/
SELECT * FROM #tmp1 t  UNPIVOT 
 (rev FOR [month] IN ([1],[2],[3])) pvt

Generating Excel file using .NET

Given below is an example of how to create excel file using .NET (C#)

Excel has to be installed on the machine for the below example to work, since EXCEL COM object is used to generate excel file

---

1. Create a (C#) console application in Visual Studio

2. Add reference to “Microsoft.Office.Interop.Excel”

3. import the required namespaces

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Text;
using System.Data;
using System.Runtime.InteropServices;

4. The function to create EXCEL is given below

public void WriteToExcel(System.Data.DataTable dtResult, string strFileName, string strDirectoryName)
{
   
    Application xlApp = default(Application);
    Workbook xlWorkBook = default(Workbook);
    Worksheet xlWorkSheet = default(Worksheet);

    string fName = strFileName = strDirectoryName + strFileName;

    try
    {
        object misValue = Missing.Value;
        xlApp = new ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

        int iRow = 1;
        int iColumn = 1;

        /*Add header to excel File*/
        StringBuilder oBuilder;
        oBuilder = new StringBuilder();
        foreach (DataColumn dc in dtResult.Columns)
        {
            if (dc.ColumnName != null)
            {
                xlWorkSheet.Cells[iRow, iColumn] = dc.ColumnName;
            }
            else
            {
                xlWorkSheet.Cells[iRow, iColumn] = "";
            }

            iColumn = iColumn + 1;
        }


        /*Add data to excel File*/
        foreach (DataRow dr in dtResult.Rows)
        {
            iColumn = 1;
            iRow = iRow + 1;
            oBuilder = new StringBuilder();
            foreach (DataColumn dc in dtResult.Columns)
            {
                if (dr[dc.ColumnName] == null || dr[dc.ColumnName] == DBNull.Value)
                {
                    xlWorkSheet.Cells[iRow, iColumn] = "";
                }
                else
                {
                    xlWorkSheet.Cells[iRow, iColumn] = dr[dc.ColumnName].ToString();
                }
                iColumn = iColumn + 1;
            }
        }

        
        xlWorkBook.SaveAs(fName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);

        if (xlApp != null)
        {
            Marshal.FinalReleaseComObject(xlApp);
        }

    }
    finally
    {
        if (xlApp != null)
            Marshal.ReleaseComObject(xlApp);
        if (xlWorkBook != null)
            Marshal.ReleaseComObject(xlWorkBook);
        if (xlWorkSheet != null)
            Marshal.ReleaseComObject(xlWorkSheet);

        if (xlApp != null)
        {
            Marshal.FinalReleaseComObject(xlApp);
        }
    }
}

5. Now call the function by passing in the datatable ,excel name and folder path

DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");

DataRow dr = dt.NewRow();
dr[0] ="Name1";
dr[1] = "12";
   
dt.Rows.Add(dr);

WriteToExcel(dt, "test.xls", @"Path");

Delegates And Events in .NET

Today I was working on a project which required the use of 'delegates'. I wanted to see the difference in calling a function using delegates and by events; so created a simple example to show the difference in invoking methods using events and delegates.

Delegates are function pointers in .net. The advantage of using delegates is that the calling program can define the function for a specific task. For example, let us consider the logging of message or exceptions in an application. We can just expose a delegate with signature “public delegate void LogMessage (string msg);” and the calling program can decide whether to do the logging in file or database or to display in console/UI.

An event is a message sent by an object to signal the occurrence of an action.Events are raised in response of an user action or it could be program triggered. It normally has the sender and event arguments as input parameters. But for the example below, I am comparing delegate and event calls and not using sender/event argument parameters

---

1) Create a console application in .NET (C#).

2) Create a delegate as given below in 'Program.cs'

public delegate void LogMessage(string msg);

3) Create a class (DelegateTestClass) for delegate test

public class DelegateTestClass
    {
        public LogMessage MessageLogger { get; set; }

        public void PerformTask1()
        {
            MessageLogger("Performing Task1");
        }

        public void PerformTask2()
        {
            MessageLogger("Performing Task2");
        }
    }

4) Create a class for Event test as given below

class EventTestClass
    {
        public event LogMessage MessageLogger;

        public void PerformTask1()
        {
            MessageLogger("Performing Task1");
        }

        public void PerformTask2()
        {
            MessageLogger("Performing Task2");
        }
    }

5) In “Program.cs” create a function to handle the delegate and event

private static void ShowMessage(string strMsg)
        {
            Console.WriteLine(strMsg);
        }

6) Now call the event and delegate from ‘Main’ as given below

static void Main(string[] args)
        {
            DelegateTestClass oDelegateTest = new DelegateTestClass();
            /*Specify the function to handle the message logging*/
            oDelegateTest.MessageLogger = new LogMessage(ShowMessage);

            oDelegateTest.PerformTask1();
            oDelegateTest.PerformTask2();


            EventTestClass oEventTest = new EventTestClass();
            /*Attach function to the event*/
            oEventTest.MessageLogger += new LogMessage(ShowMessage);

            oEventTest.PerformTask1();
            oEventTest.PerformTask2();

            Console.ReadLine();
        }

Convert image to binary and upload to database using ASP.NET

This article is about uploading images to server/database using ASP.NET. For uploading an image to database ,it has to be first converted to binary format. Then it has to be saved to a database table; the table should have a column with data type [image] for storing the binary image data.

Ok, enough of theory. Let us try out an example.

---

1) Create a ASP.NET web application using VS2008/2010

2) In default.aspx provide a input control of type “file” and a button

<input type="file" runat="server" id="photoupload" />
<asp:Button runat="server" ID="btnSave" Text="Save" onclick="btnSave_Click"/>

3) Create a function to convert stream to byte[]

public byte[] StreamToByteArray(Stream input)
{
    byte[] total_stream = new byte[0];
    byte[] stream_array = new byte[0];
    byte[] buffer = new byte[1024];

    int read = 0;
    while ((read = input.Read(buffer, 0, buffer.Length )) > 0)
    {
 stream_array = new byte[total_stream.Length + read];
 total_stream.CopyTo(stream_array, 0);
 Array.Copy(buffer, 0, stream_array, total_stream.Length, read);
 total_stream = stream_array;
    }

    return total_stream;
}

4) In the button click event, write code to get the stream from uploaded image and convert to byte array.(Import System.IO- using System.IO;)

Stream oStream = photoupload.PostedFile.InputStream;
byte[] arr = StreamToByteArray(oStream);
oStream.Close();
oStream.Dispose();

5) Now pass the byte[] data to database

SqlConnection oConnection= new SqlConnection("[connectionstring]”);
SqlParameter oParam1 = new SqlParameter("@data", arr);
oParam1.DbType = System.Data.DbType.Binary;
SqlParameter oParam2 = new SqlParameter("@description", photoupload.PostedFile.FileName);

SqlCommand oCommand = new SqlCommand(“[storedprocname]”);
oCommand.Parameters.Add(oParam1);
oCommand.Parameters.Add(oParam2);

oCommand.CommandType = CommandType.StoredProcedure;
oCommand.Connection = oConnection;
oConnection.Open();
int i = oCommand.ExecuteNonQuery();
oConnection.Close();

Done! Now the image is saved in database.

6) Code snippet for getting the binary data back from database is given below

/*Get binary data into dataset*/
DataSet ds = oDal.GetData("[stored procedure name]" + photoid.ToString());

byte[] arr = (byte[])ds.Tables[0].Rows[0]["data"];
MemoryStream oStream = new MemoryStream(arr);
Response.ContentType = "image/png";
Response.BinaryWrite(arr);

C#.NET and Greenplum

This article is about connecting to greenplum (GP) database using C#.NET. Greenplum DB is based on the open source database PostgreSQL.

Greenplum database is optimized for faster searches. For searching data in tables having millions of rows, Greenplum is really fast.

Given below is a C# example for opening a connection to GP database and executing a search query.

---

1) The first step is to download the dll which connects to GP. It is called Npgsql.dll. It can be downloaded from http://npgsql.projects.postgresql.org/docs/manual/UserManual.html

2) Create a C#.NET console application using Visual Studio.


3) Create a new file for GP connection. Lets name it GPDData.cs

4) Copy the dll to a local folder in the project and add a reference to the dll from project. Or you can register it in Global Assembly Cache using "gacutil".

5) Include the namespace

using Npgsql;

6) In the GPData class create method to execute query in greenplum as given below.

public DataSet FillDataSet(string strCommand,string strConnection)
{
NpgsqlConnection oConnection = new NpgsqlConnection(strConnection);
DataSet ds = new DataSet();

NpgsqlCommand oCommand = new NpgsqlCommand(strCommand, oConnection);
NpgsqlDataAdapter oAdp = new NpgsqlDataAdapter();
oCommand.CommandTimeout = 100;
oAdp.SelectCommand = oCommand;

oConnection.Open();
oAdp.Fill(ds);
CloseConnection();

return ds;
}

public void CloseConnection()
{
if (oConnection.State == System.Data.ConnectionState.Open)
{
oConnection.Close();
}
}

7) Now you can execute query by calling the method as given below.

GPData oData = new GPData();
DataSet ds = oData.FillDataSet([command],[connectionstring]);

Parallel Processing in .NET 4.0

I was reading about the new features of .net 4.0 today and found the new parallel processing feature really interesting. The new parallel processing class is called “Task” and it is available in the namespace “System.Threading.Tasks”

The example given below is a comparison of how functions are called in thread model and in the new parallel task way. Though I have included the time taken for to execute the task by each method (thread and task), this example is not specifically to compare performance. It is more about the difference in invoking functions.

I used VS2010 express edition for this test. Though it is easy to do the threading tests in console application, I did this in web application. I implemented ‘ICallbackEventHandler’ and did the example in the “RaiseCallbackEvent” function. For simplicity you can use a console application also.

---

1) Create a function which does looping as given below

 public string Task1(int iCount)
        {
            DateTime dtStart = DateTime.Now;
            StringBuilder oBuilder = new StringBuilder();
            for (int i = 0; i < iCount; i++)
            {
                oBuilder.Append(i.ToString());
            }
            DateTime dtEnd = DateTime.Now; 
            string strReturn = "Time Taken for Method2 " + dtStart.ToLongTimeString() + "- " + dtEnd.Subtract(dtStart).TotalMilliseconds.ToString(); 
            return strReturn;
        }

2) Since the “ThreadStart” object takes a pointer to void function, I created 4 other functions to test “Thread”

private void Task1Thread()
{
str1 = "\n" + Task1(500000);
}
private void Task2Thread()
{
str2 = "\n" + Task1(600000);
}

private void Task3Thread()
{
str3 = "\n" + Task1(700000);
}

private void Task4Thread()
{
str4 = "\n" + Task1(800000);
}

3) And then I wrote the code to call the functions as below.

DateTime dtStart = DateTime.Now;
switch (eventArgument)
{
case "1":// sequential 
data = Task1(500000);
data = data + "\n" + Task1(600000);
data = data + "\n" + Task1(700000);
data = data + "\n" + Task1(800000);
break;
case "2": //Task - the NEW parallel processing way in .NET 4.0

Task<string> mytask1 = Task<string>.Factory.StartNew(() => Task1(500000));
Task<string> mytask2 = Task<string>.Factory.StartNew(() => Task1(600000));
Task<string> mytask3 = Task<string>.Factory.StartNew(() => Task1(700000));
Task<string> mytask4 = Task<string>.Factory.StartNew(() => Task1(800000));

data = mytask1.Result;
data = data + "\n" + mytask2.Result;
data = data + "\n" + mytask3.Result;
data = data + "\n" + mytask4.Result;

break;
case "3":// Thread – common method of multi threading in .NET 1.1, 2.0

Thread oThread1 = new Thread(new ThreadStart(Task1Thread)); 
Thread oThread2 = new Thread(new ThreadStart(Task2Thread));
Thread oThread3 = new Thread(new ThreadStart(Task3Thread));
Thread oThread4 = new Thread(new ThreadStart(Task4Thread));

oThread1.Start();
oThread2.Start();
oThread3.Start();
oThread4.Start();

oThread1.Join();
oThread2.Join();
oThread3.Join();
oThread4.Join();

data = str1 + str2 + str3 + str4;

break;
}
DateTime dtEnd = DateTime.Now;
string finalTime = "Total Time Taken - " + dtStart.ToLongTimeString() + "- " + dtEnd.Subtract(dtStart).TotalMilliseconds.ToString();

data = data + "\n" + finalTime;

You can see that calling methods using “Task” object is much easier than thread. And also its more efficient than using thread.

ASP.NET and HighCharts

High charts (http://www.highcharts.com ) is a really nice tool to generate graphs/reports on web applications. This article is about how to use high charts in ASP.Net and also shows how to pass data from .net to high chart control (JS/JQuery function) using JSON.

The charts generated by HighCharts control are really nice and have lots of client side features like zoom, selecting/highlighting series etc.

Before starting the example, we need two JS files for this. Both can be obtained from high charts website (http://www.highcharts.com ). The javascript file required are ‘highcharts.js’ and ‘jquery-1.4.1.min.js’

Ok..now let us develop an ASP.NET web application which displays high-chart graph.

---

1 Create a new web application using Visual Studio

2 Include the two js files inside the head tag of default.aspx

<script type="text/javascript" src="Scripts/jquery-1.4.1.min.js"></script>
<script type="text/javascript" src="Scripts/highcharts.js"> </script>

3) Now we need X and Y coordinate data to generate the graph. Let us first create X-axis data. Since we are going to generate a revenue graph, let us include year as X-axis. Add the below code in code behind page, in page_load event

/*X axis coordinates*/
List<int> lstXaxis = new List<int>();
lstXaxis.Add(2007);
lstXaxis.Add(2008);
lstXaxis.Add(2009);
lstXaxis.Add(2010);

4) Create public properties for X and Y axis so that they can be accessed from aspx page.

public string Series1 { get; set; }
public string Xaxis { get; set; }

5) Using JavascriptSerializer, convert the x-axis data to Json

JavaScriptSerializer oSerializer = new JavaScriptSerializer();
Xaxis= oSerializer.Serialize(lstXaxis);

6) Let us now generate Y-axis data (revenue data). Create a class to store the Y-axis data

public class ChartEx
{
public string name { get; set; }
public List<int> data{ get; set; }
}

7) Fill Y-axis object with data and serialize it using Javascript serializer

/*Series1 - Yaxis*/
ChartEx oEx = new ChartEx();
oEx.data = new List<int> ();

oEx.name = "Revenue";
oEx.data.Add(350);
oEx.data.Add(410);
oEx.data.Add(220);
oEx.data.Add(421);

JavaScriptSerializer oSerializer1 = new JavaScriptSerializer();
Series1= oSerializer1.Serialize(oEx); ;

8) Create a div container in aspx page. The graph will be rendered in this container.

<div id="container" > </div >

9) Now call the Javascript function to draw the graph and pass the serialized C# object to javascript function. (Place the below code inside script tags)

$(document).ready(DrawMyGraph1);

function DrawMyGraph1 () {

chart = new Highcharts.Chart({
chart: {
renderTo: 'Container',
defaultSeriesType: 'area'
},
title: {
text: 'Revenu'
},
subtitle: {
text: 'Source: Test '
},
xAxis: {
categories: <%=Xaxis %>
},
yAxis: {
title: {
text: 'Revenue Vs Profit'
}
},
tooltip: {
formatter: function () {
return this.series.name + ' produced ' +
Highcharts.numberFormat(this.y, 0) + 'revenue in ' + this.x;
}
},
series: [<%=Series1 %>]
});
}

Done ! Now run the application and you can see the hightchart graph rendered in UI.

There are a lot of useful client side functionalities provided by this graph component like - zooming , highlighting specific graphs etc. Try it out an you will be amazed by how powerful this tool is.

Video Streaming in ASP.NET using Silverlight

This article is about how to use Silverlight to stream videos in ASP.Net application.

I used Visual Studio 2010 express edition and Silverlight 4.0 (ASP.NET 4.0) for this example. In below example we will just focus on a simple video streaming example. We will look at how to implement progress bar, stop/start buttons etc in another post.

So, let us look at how to create an ASP.NET web application with video streaming.

---

1 Create a new Silverlight application from Visual studio 2010. Visual studio creates a test web application to test the Silverlight application. So you will have two projects in the solution.

2 In MainPage.xaml ,drag and drop the media element from toolbor. Or include the below XAML in the page

<MediaElement Height="153" HorizontalAlignment="Left" Margin="24,150,0,0" Name="mediaElement1" VerticalAlignment="Top" Width="462" AutoPlay="true" />

3. We have to now provide the video file path for the media element to play . For this test, I copied a video clip to a folder named “Video” in the test web application. Note that I did not copy the file to the Silverlight project folder, but it was placed inside the test web application.

4. Get the URL for the video file. (For my test it was http://localhost:[portnumber]/Video/WindowsMedia.wmv")

5.Set the path of the video file in the MediaElement.

<MediaElement Height="153" HorizontalAlignment="Left" Margin="24,150,0,0" Name="mediaElement1" VerticalAlignment="Top" Width="462" Source="http://localhost:[portnumber]/Video/WindowsMedia.wmv" AutoPlay="False" / >

That’s it ! Now run the application and you can see the video getting rendered in the web site.

We will look into how to implement progress bar , start/stop buttons etc in another post. Till then, happy coding!

ASP.NET and Json

JSON (Javascript Object Notation) is a language independent open standard for representing data. It is human readable and light weight compared to XML. JSON can be used to serialize objects and transmit data.

So - how do you convert .net objects to JSON and pass to javascript function. I could not find a good/simple example on this, which lead me to write the below example.

As always – lets go directly to example and see JSON and .net at work .

---

1) Create an ASP.Net application. Create a class which we will pass to javascript (client side) as JSON

public class ChartEx
{
public string name { get; set; }
public List<int> data{ get; set; }
}

2) In the page load of default.aspx ,create an instance of the object and fill data

ChartEx oEx = new ChartEx();
oEx.data = new List();

oEx.name = "Revenue";
oEx.data.Add(350);
oEx.data.Add(410);
oEx.data.Add(220);
oEx.data.Add(421);

3) Create a public property for the page.

public string Series1 { get; set; }

4) Serialize the object using Javascript serializer (in namespace Sytem.Web.Script.Serialization) And set serialized data to the public property.

JavaScriptSerializer oSerializer = new JavaScriptSerializer();
string strData = oSerializer.Serialize(oEx);
Series1 = strData;

5) Access the public property from ASPX page JS function inside script block

function ShowAlert()
{
var sdata =<%=Series1 %> ;
alert(sdata.name);
}

6) And call the js function in Load event of the page as given below.

<body onload="ShowAlert();">

Run the application and we can see that the .net object is serialized to a JSON object which can be now be used by a JS function. Isn’t that awesome (and easy)?

jQuery Basics

I wanted to learn jquery for long time, but kept postponing since it is possible to do most of the normal client functions that I do (like validation, showing alerts, pop up etc) using classic javascript itself. But when it comes to complex functions (like fading out/fading in controls or animation), doing it in javascript becomes difficult. And you have to worry about browser compatability too.

Thats the reason why I started to learn jQuery.... and it was pretty easy and really helpful.

Given below are the steps that I followed in creating a simple example.

---

1) The first thing to do is to download the jquery Js file from http://jquery.com/. (When I downloaded the file the version was "jquery-1.5.1.min.js")

2) Create an html file and include the download js file in the html file

<script src="Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>

Ok... now we are ready to start using jQuery. Elements are accessed in jQuery as $("#controlId")... the same code in javascript would be "document.getElementById("controlId").

3) Let us set a value in textbox using jQuery

a) First Create a textbox and button control in html as given below

<input type="text" id="txtBox1" value="test" />

<input type="button" value="Set Value" onclick="SetValue();" />

b) Set text "Hello World" in text box using jQuery as given below

function SetValue() {
$("#txtBox1").val("Hello world")
}

Wasn't that easy. Let us look at some other simple functions of jQuery.

Fade Out

function FadeOut() {
$("#txtBox1").fadeOut("slow")
}

Fade In

function FadeIn() {
$("#txtBox1").fadeIn("slow")
}

Let us now see how to animate a div

Include a div element in html

<div id="divAnimate" class="ui-dialog" style="display:none;border:1px solid gray;width:0px;">
<div class="ui-widget-header" style="width:300px;">
Just a box
</div>
</div>

Include 2 buttons to invoke 'hide' and 'display' animations

<div class="codeBlock">
<input type="button" value="Animate-Display" onclick="Animate_Display();" />
<input type="button" value="Animate-Hide" onclick="Animate_Hide();" />
</div >

Include script for "hide" and "display" functions

function Animate_Display() {
$("#divAnimate").css({ "display": "block", "overflow": "auto" });
$("#divAnimate").animate({ width: "300px",height:"300px" }, 1000);
}
function Animate_Hide() {
$("#divAnimate").animate({ width: "0px", height: "0px" }, 1000, function() {
Animation_Complete();
});
}
function Animation_Complete() {
$("#divAnimate").css({ "display": "none", "overflow": "auto" });
}

Consuming ReST Service

Today let us review how to consume the ReST service that was created in previous post.

Given below are 3 ways to consume the service

---

1) By using XMLDocument

XmlDocument oDoc = new XmlDocument();
oDoc.Load("http://localhost:49492/UserService.svc/users/World");
Console.WriteLine(oDoc.InnerXml);
Console.ReadLine();

2) Using Http Request

HttpWebRequest oRequest = (HttpWebRequest)WebRequest.Create("http://localhost:49492/UserService.svc/users/World");
HttpWebResponse webResponse = (HttpWebResponse)oRequest.GetResponse();
Stream receiveStream = webResponse.GetResponseStream();
StreamReader oReader = new StreamReader(receiveStream);
Console.WriteLine(oReader.ReadToEnd());
oReader.Close();
webResponse.Close();
Console.ReadLine();

3) Using Channel Factory

ChannelFactory factory = new ChannelFactory(new WebHttpBinding(), new EndpointAddress("http://localhost:49492/UserService.svc"));
factory.Endpoint.Behaviors.Add(new WebHttpBehavior());
IUserService proxy = factory.CreateChannel();
Console.WriteLine(proxy.GetFullName("World"));

int i = proxy.PutUserAccount("world", "newval");
Console.WriteLine(i.ToString());
Console.ReadLine();

ReST Application using WCF

This article is about creating a simple ReST(Representational State Transfer) application using WCF. Using ReST model of communication, web services can be invoked without the WSDL.

Let us go through an example on how to develop a ReST application

---

1) Create a WCF application in VS 2008
(File -> New project-> Select "WCF Service Application" (Under Visual C#, Web))
Visual Studio will create service file (Service1.svc and IService1.cs)

2) Add reference to "System.ServiceModel.Web" ( This is required for "WebGet" Attribute that is used in interface method definition )

Let us reuse IService.cs for our example.

3) Create a new method definition in IService1 as given below

[WebGet(UriTemplate = "users/{username}")]
[OperationContract]
string GetFullName(string username);

4) Implement the interface method in Service1.svc as given below

public string GetFullName(string username)
{
return "Hello " + username;
}

5)In web.config make below 2 changes

 a) Remove the existing endpoint and add the new endpoint

<endpoint address="" behaviorconfiguration="webHttp" binding="webHttpBinding" contract="RestTestApp.IService1">
<identity>
<dns value="localhost">
</identity>
</endpoint>

 b) Include endpoint behavior for webHttp. This should be inside tag

<endpointbehaviors>
<behavior name="webHttp">
<webhttp>
</behavior>
</endpointbehaviors>

6)The development of ReST application is complete! Run the application.

Navigate to -
http://localhost:{Port if using Development server}/UserService.svc/users/test

This will return an xml

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/%22%3EHello test</string>

With ReST model , we are now able to access the service without passing the parameters in XML (SOAP). The service can be accessed and executed using a simple http webrequest.

We will look at how to consume this ReST application in later articles.

Till then, happy coding.

Callback Feature in ASP.NET

The callback feature in ASP.NET helps to display content in webpage in an asynchronous way.

Lets go straight to an example and get it working.

---

1)Create a web application in VS 2008

2)In Page Load of "Default.aspx.cs" , enter the following code

/*Client function to Receive Server Data*/
string strReceiveServerData = "function ReceiveServerData(arg,context){RecieveServerDate2(arg);}";

/*Get Callback Reference*/
string strReceiveCallReference = Page.ClientScript.GetCallbackEventReference(this, "arg", "ReceiveServerData", strReceiveServerData);

/*Client function to Call Server... the reference of "Receive server data" function is added here*/
string strCallTheServer = "function CallServer(arg,context){" + strReceiveCallReference + ";}";

/*Register the script*/
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallServer", strCallTheServer, true);

3) Inherit interface “ICallbackEventHandler” in page

public partial class AsyncServerCall : System.Web.UI.Page,ICallbackEventHandler

4) Add method code for interface "ICallbackEventHandler" in "Default.aspx.cs"

#region ICallbackEventHandler Members

string strVar = "";
public string GetCallbackResult()
{
return strVar;
}

public void RaiseCallbackEvent(string eventArgument)
{
strVar = "Hello World," + eventArgument;
}

#endregion

5) Add below Javascript to aspx page. Make sure you add "script" tags.

<script>
function RecieveServerDate2(arg) {
alert(arg);
}
</script>

6) And the final step... call the javascript function to make server call in body onload

<body onload=" CallServer('Guys', '');" >

Run it! And you will get an alert “Hello World, Guys”. Wasn’t the callback easy in ASP.NET?

Generics and LINQ

Generics is a feature that is very useful in ASP.NET. If generic collection is used to store data then quering data from the collection becomes really easy.

Given below is a simple example for creating a generic collection and quering from it using LINQ.

---

1) Create an object collection
a) Define the object "Employee"

public class Employee
{
public Employee(int iID, string strName, string strAddress, int iAge)
{
ID = iID;
Name = strName;
Address = strAddress;
Age = iAge;
}
public int ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public int Age { get; set; }
}

b)Add objects to collection

public List GetEmployees()
{

List<Employee> lstEmps = new List<Employee>();

lstEmps.Add(new Employee(1, "Emp 1", "212 Street1", 34));
lstEmps.Add(new Employee(2, "Emp 2", "543 Street2", 31));
lstEmps.Add(new Employee(3, "Emp 3", "3332 Street2", 27));
lstEmps.Add(new Employee(4, "Emp 4", "1011 Street2", 40));
lstEmps.Add(new Employee(5, "Emp 5", "3214 Street4", 33));
lstEmps.Add(new Employee(6, "Emp 6", "5543 Street1", 35));

return lstEmps;
}

2) Query collection using LINQ

List<employee> EmpList =GetEmployees();

/**LINQ Examples**/

//Select employees with age > 30
List<employee> emps1 = (from x in EmpList where x.Age > 30 select x).ToList();

//Select employees with name starting with "Emp"
List<employee> emps2 = (from x in EmpList where x.Name.StartsWith("Emp") select x).ToList();

//Select employee with address "212 Street1"
List<employee> emps3 = (from x in EmpList where x.Address == "212 Street1" select x).ToList();

Delete Duplicate Rows from SQL Server Table

This example is about deleting duplicate rows in SQL server table. In this example duplicate records are deleted from the table based on rank.

/*Create temp table*/

create table #tmp(id int,name1 varchar(20),cre_date datetime)

GO

/*Insert rows with duplicate data in it*/

insert into #tmp values(1,'name1','2011-03-30 9:00 am')
insert into #tmp values(1,'name1','2011-03-30 9:20 am')
insert into #tmp values(2,'name2','2011-03-30 9:30 am')
insert into #tmp values(3,'name3','2011-03-30 9:40 am')
insert into #tmp values(3,'name3','2011-03-30 9:50 am')
insert into #tmp values(3,'name3','2011-03-30 9:55 am')

GO

/*Delete the rows that are duplicate*/

with t as (
Select id,name1,row_number() over (partition by id,name1 order by cre_date) rnk from #tmp )
delete from t where rnk>1