Jagadish Pulakhandam's: September, 2007

September 19

Developing, Hosting (with custom .NET application) and Testing a WCF Service using Visual Studio 2008 beta

Hello guys,

The following sample demonstrates the development of a WCF Service library together with a custom application hosting (the "WCF Service library" as a "WCF Service") and finally accessing the same using WCF Service Client (windows forms client).

-> check the connection strings before execution

-> check the configuration files (app.config files) before execution

-> Execute the "NorthwindServiceHost.exe" (in bin folder "NorthwindServiceHost" project) and start the service

-> Execute the "NorthwindClient.exe" (in bin folder of "NorthwindClient" project) and test

Step by step walkthrough for the above sample is available here http://www.aspfree.com/c/a/ASP.NET/Developing-a-WCF-Service-Library-and-Hosting-it-with-a-Custom-App-Using-VS2K8/

Let me know if you have any problems..

thanks

Jag

10:32 AM | Add a comment | Permalink | Blog it | WCF

September 12

Part 3: Learning LINQ from SQL using Visual Basic 2008 - Grouping

To execute the LINQ statements provided in this post, I suggest you to go through Part-1 (http://jagchat.spaces.live.com/blog/cns!41050F68F010A662!913.entry), where I demonstrated the same.

A simple example on GROUP..BY..operator of LINQ:

The following SQL SELECT

SELECT Deptno, COUNT(*) FROM emp GROUP BY Deptno

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Count()

Select Deptno, NoOfEmp = Count

Using different Aggregations on GROUP..BY.. operator of LINQ:

The following SQL SELECT

SELECT Deptno, SUM(sal) FROM emp GROUP BY Deptno

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Group

Select

Deptno,

SumSal = Group.Sum(Function(p) p.Sal)

You can include more aggregations as following:

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Group

Select

Deptno,

SumSal = Group.Sum(Function(p) p.Sal),

MinSal = Group.Min(Function(p) p.Sal),

MaxSal = Group.Max(Function(p) p.Sal),

AvgSal = Group.Average(Function(p) p.Sal),

NoOfEmp = Group.Count(Function(p) p.Empno)

Order By Deptno

Using LET with GROUP..BY.. operator in LINQ:

The following SQL SELECT (retrieves department wise highest earned employee)

SELECT Deptno,

(SELECT ename FROM emp

WHERE sal = (SELECT MAX(sal) FROM emp

WHERE Deptno=a.Deptno))

FROM EMP a

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Group

Let MaxSal = Group.Max(Function(p) p.Sal)

Select

Deptno,

HighestEarnedEmp =

(Group.Where(Function(p) p.Sal = MaxSal)).First.Ename

The above works if and only if there exists only one employee having the highest salary. This is not practical in most scenarios

11:32 AM | Add a comment | Permalink | Blog it | LINQ

Part 2: Learning LINQ from SQL using Visual Basic 2008 - WHERE operator

To execute the LINQ statements provided in this post, I suggest you to go through Part-1 (http://jagchat.spaces.live.com/blog/cns!41050F68F010A662!913.entry), where I demonstrated the same.

A simple example on WHERE operator of LINQ:

The following SQL SELECT

SELECT ename, sal FROM emp WHERE sal > 6000

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList()

Select p.Ename, p.Sal

Where Sal > 6000

Using LIKE with WHERE operator of LINQ:

The following SQL SELECT

SELECT ename, sal FROM emp WHERE ename LIKE 'J%'

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList()

Select p.Ename, p.Sal

Where Ename Like "J*"

The following SQL SELECT

SELECT ename, sal FROM emp WHERE ename LIKE 'W_____'

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList()

Select p.Ename, p.Sal

Where Ename Like "W?????"

Combined example of SELECT, WHERE and ORDER BY operators in LINQ:

The following SQL SELECT

SELECT ename, sal FROM emp WHERE deptno = 10 ORDER BY deptno DESC

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList()

Where p.Deptno = 10

Order By p.Deptno Descending

Select p.Ename, p.Sal

Note: If other operators (WHERE, ORDER BY etc.) are using the columns not listed in SELECT, they need to be moved before SELECT (as above)

11:17 AM | Add a comment | Permalink | Blog it | LINQ

Part 1: Learning LINQ from SQL using Visual Basic 2008 - Selecting columns, DISTINCT, ORDER BY

For this sample, I simply created an ASP.NET 3.5 web site project (using Visual Studio 2008 beta 2) and dragged a GridView on to a web page. Added a class "Employee.vb" to the project as following (code removed for clarity):

Imports Microsoft.VisualBasic

Public Class Employee

...

Public Sub New(ByVal e As Integer, ByVal en As String, ByVal s As Double, ByVal d As Integer) ...

Public Property Empno() As Integer ...

Public Property Ename() As String ...

Public Property Sal() As Double ...

Public Property Deptno() As Integer ...

End Class

Added a new method (to generate data) to the web page as following:

Private Function GetEmployeeList() As List(Of Employee)

Dim EmpList As New List(Of Employee)

EmpList.Add(New Employee(1001, "Jagadish", 3400, 20))

EmpList.Add(New Employee(1002, "Chatarji", 6700, 30))

EmpList.Add(New Employee(1003, "Pulakhandam", 3200, 10))

EmpList.Add(New Employee(1004, "Winner", 7800, 20))

EmpList.Add(New Employee(1005, "Sunitha Paruchuri", 6500, 30))

EmpList.Add(New Employee(1006, "Malli", 4200, 20))

EmpList.Add(New Employee(1007, "Ram Mullangi", 6200, 30))

EmpList.Add(New Employee(1008, "Veditha Davuluri", 7600, 10))

EmpList.Add(New Employee(1009, "Jyothi", 8900, 10))

EmpList.Add(New Employee(1010, "Srinivas", 6900, 20))

EmpList.Add(New Employee(1011, "Krishna", 2300, 30))

EmpList.Add(New Employee(1012, "Chandra Kamala", 5400, 30))

Return EmpList

End Function

Now, let us consider the following SQL SELECT statement:

SELECT ename FROM emp

The corresponding LINQ statement (along with execution code) for the above SQL is as follows:

Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load

If Not IsPostBack Then

Dim query = From p In GetEmployeeList() Select p.Ename

Me.GridView1.DataSource = query

Me.GridView1.DataBind()

End If

End Sub

The only important statement from the above is the following:

Dim query = From p In GetEmployeeList() Select p.Ename

which selects only the property (or column) "Ename" of every successive object available in "p" from the collection returned by "GetEmployeeList()"

Selecting more columns:

Further, the following SQL SELECT

SELECT ename, sal FROM emp

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() Select p.Ename, p.Sal

Columns with Expressions:

The following SQL SELECT

SELECT ename, sal * 12 AS AnnSal FROM emp

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() Select p.Ename, AnnSal = (p.Sal * 12)

Make sure that Column Alias is compulsory in LINQ, if more than one column is available in the list.

Working with DISTINCT:

The following SQL SELECT

SELECT DISTINCT deptno FROM emp

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() Select p.Deptno Distinct

Working with ORDER BY:

The following SQL SELECT

SELECT ename, sal FROM emp ORDER BY ename

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() Select p.Ename, p.Sal Order By Ename

We can also make use of Alias columns as part of ORDER BY operator as following:

Dim query = From p In GetEmployeeList()

Select p.Ename, Annsal = (p.Sal * 12)

Order By Annsal Descending