Contextual Keywords in C#

Contextual Keywords in C#

A contextual keyword is used to provide a specific meaning in the code, but it is not a reserved word in C#. This will give some understanding on different contextual keywords and make you comfortable while using those...

yield:
Used in an iterator block to return a value to the enumerator object or to signal the end of iteration.


Use of this construct is vital for LINQ. Yield return allows one enumerable function to be implemented in terms of another. It allows us to write functions that return collections that exhibit lazy behavior. This allows LINQ and LINQ to XML to delay execution of queries until the latest possible moment. it allows queries to be implemented in such a way that LINQ and LINQ to XML do not need to assemble massive intermediate results of queries. Without the avoidance of intermediate results of queries, the system would rapidly become unwieldy and unworkable.

The following two small programs demonstrate the difference in implementing a collection via the IEnumerable interface, and using yield return in an iterator block.

With this first example, you can see that there is a lot of plumbing that you have to write. You have to implement a class that derives from IEnumerable, and another class that derives from IEnumerator. The GetEnumerator() method in MyListOfStrings returns an instance of the class that derives from IEnumerator. But the end result is that you can iterate through the collection using foreach.

public class MyListOfStrings : IEnumerable
{
private string[] _strings;
public MyListOfStrings(string[] sArray)
{
_strings = new string[sArray.Length];

for (int i = 0; i < sArray.Length; i++)
{
_strings[i] = sArray[i];
}
}

public IEnumerator GetEnumerator()
{
return new StringEnum(_strings);
}
}

public class StringEnum : IEnumerator
{
public string[] _strings;

// Enumerators are positioned before the first element
// until the first MoveNext() call.
int position = -1;

public StringEnum(string[] list)
{
_strings = list;
}

public bool MoveNext()
{
position++;
return (position < _strings.Length);
}

public void Reset()
{
position = -1;
}

public object Current
{
get
{
try
{
Console.WriteLine("about to return {0}", _strings[position]);
return _strings[position];
}
catch (IndexOutOfRangeException)
{
throw new InvalidOperationException();
}
}
}
}

class Program
{
static void Main(string[] args)
{
string[] sa = new[] {
"aaa",
"bbb",
"ccc"
};

MyListOfStrings p = new MyListOfStrings(sa);

foreach (string s in p)
Console.WriteLine(s);
}
}

Using the yield return keywords, the equivalent in functionality is as follows. This code is attached to this page:

class Program
{
public static IEnumerable MyListOfStrings(string[] sa)
{
foreach (var s in sa)
{
Console.WriteLine("about to yield return");
yield return s;
}
}

static void Main(string[] args)
{
string[] sa = new[] {
"aaa",
"bbb",
"ccc"
};

foreach (string s in MyListOfStrings(sa))
Console.WriteLine(s);
}
}

As you can see, this is significantly easier.

This isn't as magic as it looks. When you use the yield contextual keyword, what happens is that the compiler automatically generates an enumerator class that keeps the current state of the iteration. This class has four potential states: before, running, suspended, and after. This class has Reset and MoveNext methods, and a Current property. When you iterate through a collection that is implemented using yield return, you are moving from item to item in the enumerator using the MoveNext method. The implementation of iterator blocks is fairly involved. A technical discussion of iterator blocks can be found in the C# specifications.

Yield return is very important when implementing our own query operators (which we will want to do sometimes).

There is no counterpart to the yield keyword in Visual Basic 9.0, so if you are implementing a query operator in Visual Basic 9.0, you must use the approach where you implement IEnumerable and IEnumerator.

One of the important design philosophies about the LINQ and LINQ to XML technologies is that they should not break existing programs. Adding new keywords will break existing programs if the programs happen to use the keyword in a context that would be invalid. Therefore, some keywords are added to the language as contextual keywords. This means that when the keyword is encountered at specific places in the program, it is interpreted as a keyword, whereas when the keyword is encountered elsewhere, it may be interpreted as an identifier. Yield is one of these keywords. When it is encountered before a return or break keyword, it is interpreted by the compiler as appropriate, and the new semantics are applied. If the program was written in C# 1.0 or 1.1, and it contained an identifier named yield, then the identifier continues to be parsed correctly by the compiler, and the program is not made invalid by the language extensions.

Extension Methods

Extension methods are special methods that, while they are not part of a data type, you can call them as though they were part of the data type. Extension methods are a feature of C# 3.0.

Writing Extension Methods
To write an extension method, you do the following:

· Define a public static class.
· Define a public static method in the class where the first argument is the data type for which you want the extension method.
· Use the this keyword on the first argument to your public static method. The this keyword denotes the method as an extension method.

This special syntax is actually simply a natural extension of what you do normally when you want to make a utility method for a class that you don't want to (or can't) extend. A common pattern when you want to write a utility method is to define a static method that takes an instance of the class as the first parameter. This is exactly what an extension method is - the only difference is that you can then write a call to the method as though it were part of the class, and the compiler will find and bind to the extension method.

Note that the following class does not define a PrintIt method:

public class MyClass
{
public int IntField;
public MyClass(int arg)
{
IntField = arg;
}
}

This static class defines an extension method for the MyClass class:

public static class MyClassExtension
{
public static void PrintIt(this MyClass arg)
{
Console.WriteLine("IntField:{0}", arg.IntField);
}
}

You can now call the PrintIt method as though it were part of the class.

MyClass mc = new MyClass(10);
mc.PrintIt();

When you run this code, it outputs:

IntField:10

But the really cool thing is that you can write extension methods for a type that in and of itself, can't contain methods. The most important example of this is that you can write extension methods for interfaces. You could also write an extension method for an abstract class.

You can define extension methods for parameterized types as well as non-parameterized types. The standard query operators are almost all extension methods that are defined on IEnumerable.

Note that you can define your own extension methods for IEnumerable. When there isn't a standard query operator that does exactly what you want, you can write your own extension method. This is a powerful technique that adds expressiveness to your code.

When you are writing pure functional code, extension methods are important. There are times that writing extension methods on IEnumerable is the way that you want do things. We'll be using this technique sometimes when writing FP code.

Extension Methods are Vital for LINQ
Extension methods are an integral part of LINQ. Consider the following code that contains a LINQ query expression:

int[] source = new[] { 3, 6, 4, 8, 9, 5, 3, 1, 7, 0 };

IEnumerable query =
from i in source
where i >= 5
select i * 2;

foreach (var i in query)
Console.WriteLine(i);

This code is functionally identical to this:

int[] source = new[] { 3, 6, 4, 8, 9, 5, 3, 1, 7, 0 };

IEnumerable query =
source.Where(i => i >= 5)
.Select(i => i * 2);

foreach (var i in query)
Console.WriteLine(i);

In fact, you can see that there is a direct translation from the query expression to the same query that is expressed in method notation. I believe that in early versions of the C# 3.0 compiler, this translation was implemented almost like a macro. But in any case, queries that are implemented via method syntax rely, of course, on the extension methods that are included with the .NET framework, and hence query expressions also rely on them. It is the ability to write extension methods for generic interfaces that enables queries.

Lambda Expressions

A lambda expression is an anonymous function that can contain expressions and statements, and can be used to create delegates or expression tree types.
A lambda anonymous methods, that allows you to declare your method code inline instead of with a delegate function.

All lambda expressions use the lambda operator =>, which is read as "goes to". The left side of the lambda operator specifies the input parameters (if any) and the right side holds the expression or statement block. The lambda expression x => x * x is read "x goes to x times x."

(int x) => x + 1 // explicitly typed parameter
(y,z) => return y * z; // implicitly typed parameter

Note:
1. Lambdas are not allowed on the left side of the is or as operator.

To show lambda expressions in context, consider the problem where you have an array with 10 digits in it, and you want to filter for all digits greater than 5. In this case, you can use the Where extension method, passing a lambda expression as an argument to the Where method:

int[] source = new[] { 3, 8, 4, 6, 1, 7, 9, 2, 4, 8 };

foreach (int i in source.Where(x => x > 5))
Console.WriteLine(i);

First, a quick review of delegates:

Defining, Creating, and Using a Delegate
In C#, a delegate is a data structure that refers to either a static method, or an object and an instance method of its class. When you initialize a delegate, you initialize it with either a static method, or a class instance and an instance method.

The following code shows the definition of a delegate and a method that can be used to initialize the delegate:

// Defines a delegate that takes an int and returns an int
public delegate int ChangeInt(int x);

// Define a method to which the delegate can point
static public int DoubleIt(int x)
{
return x * 2;
}

Now, you can create and initialize an instance of the delegate, and then call it:

ChangeInt myDelegate = new ChangeInt(DelegateSample.DoubleIt);
Console.WriteLine("{0}", myDelegate(5));

This, as you would expect, writes 10 to the console.

Using an Anonymous Method
With C# 2.0, anonymous methods allow you to write a method and initialize a delegate in place:

ChangeInt myDelegate = new ChangeInt(
delegate(int x)
{
return x * 2;
}
);
Console.WriteLine("{0}", myDelegate(5));

Using a Lambda Expression
With Lambda expressions, the syntax gets even terser:

ChangeInt myDelegate = x => x * 2;
Console.WriteLine("{0}", myDelegate(5));

This lambda expression is an anonymous method that takes one argument x, and returns x * 2. In this case, the type of x and the type that the lambda returns are inferred from the type of the delegate to which the lambda is assigned.

If you wanted to, you could have specified the type of the argument, as follows:

ChangeInt myDelegate = (int x) => x * 2;
Console.WriteLine("{0}", myDelegate(5));

Using a Lambda with Two Arguments
When using the Standard Query Operators, on occasion, you need to write a lambda expression that takes two arguments.

If you have a delegate that takes two arguments:

// Defines a delegate that takes two ints and returns an int
public delegate int MultiplyInts(int arg, int arg2);

You can declare and initialize a delegate:

MultiplyInts myDelegate = (a, b) => a * b;
Console.WriteLine("{0}", myDelegate(5, 2));

Statement Lambda Expressions
You can write a more complicated lambda expression using statements, enclosing the statements in braces. If you use this syntax, you must use the return statement, unless the lambda returns void:

int[] source = new[] { 3, 8, 4, 6, 1, 7, 9, 2, 4, 8 };

foreach (int i in source.Where(
x =>
{
if (x <= 3)
return true;
else if (x >= 7)
return true;
return false;
}
))
Console.WriteLine(i);

Sometimes developers wonder how to pronounce the => token.

If the lambda expression is a predicate, expressing some condition: c => c.State == "WA" then the => can be spoken as "such that". In this example, you could say "c such that c dot state equals Washington". If the lambda expression is a projection, returning a new type: c => new XElement("CustomerID", c.CustomerID); then the => can be spoken as "becomes". In the above example, you could say "c becomes new XElement with a name of CustomerID and its value is c dot CustomerID". Or "maps to", or "evaluate to", as suggested in the comments below. But most often, I just say "arrow". J

A quick note: predicates are simply boolean expressions that are passed to some method that will use the boolean expression to filter something. A lambda expression used for projection takes one type, and returns a different type. More on both of these concepts later.

The Func Delegate Types
The framework defines a number of parameterized delegate types:

public delegate TR Func();
public delegate TR Func(T0 a0);
public delegate TR Func(T0 a0, T1 a1);
public delegate TR Func(T0 a0, T1 a1, T2 a2);
public delegate TR Func(T0 a0, T1 a1, T2 a2, T3 a3);

In the above delegate types, notice that if there is only one type parameter, it is the return type of the delegate. If there are two type parameters, the first type parameter is the type of the one and only argument, and the second type is the return type of the delegate, and so on. Many of the standard query operators (which are just methods that you call) take as an argument a delegate of one of these types.

Expression Trees
Lambda expressions can also be used as expression trees. This is an interesting topic, but is not part of this discussion on writing pure functional transformations.

InternalsVisibleToAttribute (C#)

Today I am going to give an idea on internal visible attribute which was introduced in .NET 2.0. This feature is better than accessing non-public member using reflection. Before starting this topic it is better to take a look on the different access modifier available in C# which will give us a clear picture on this.

Access Modifiers:
-Public
The type or member can be accessed by any other code in the same assembly or another assembly that references it.

-Private
The type or member can only be accessed by code in the same class or struct.

-Protected
The type or member can only be accessed by code in the same class or struct, or in a derived class.

-Internal
The type or member can be accessed by any code in the same assembly, but not from another assembly.

-Protected Internal
The type or member can be accessed by any code in the same assembly, or by any derived class in another assembly.

Friend Assemblies:

The friend assemblies feature allows you to access internal members; private types and private members will remain inaccessible. To give an assembly (assembly B) access to another assembly's (assembly A's) internal types and members, use the InternalsVisibleToAttribute attribute in assembly A.

It is better to give an example to understand better:
1. Add a class libarary (with name 'A')
Code will looks:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace A
{

public class Class1
{
private int num1 = 1;
public int num2 = 2;
protected int num3 = 3;
internal protected int num4 = 4;
int num5 = 5;
}
}

2. Add another console application (With name 'B') bellow
3. Add the project reference 'A' to project 'B' and add the below code to program.cs file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using A;

namespace B
{
class Program
{
static void Main(string[] args)
{
Class1 obj = new Class1();
int n1 = obj.num1; //Compiler error
int n2 = obj.num2; //No error
int n3 = obj.num3; //Compiler error
int n4 = obj.num4; //Compiler error
int n5 = obj.num5; //Compiler error
}
}
}

3. Compile the both the project and watch carefully. We are able to access only num2 as its access modifier is public. Don't worry we will do some this which will allow to access num4
4. Add the below code line to AssemblyInfo.cs on project 'A'. which will tell the 'A' to allow 'B' to access his protected internal members
[assembly: InternalsVisibleTo("B")]
5. Now recompile the project and you will see:
int n1 = obj.num1; //Compiler error
int n2 = obj.num2; //No error
int n3 = obj.num3; //Compiler error
int n4 = obj.num4; //No error
int n5 = obj.num5; //Compiler error



We can also provide the public token key whiling specifying the assembly name to the assemblyinfo.cs. To do this you may need following:

This example shows how to make internal types and members available for assemblies that have strong names.

To generate the keyfile and display public key, use the following sequence of sn.exe commands (for more information, see Strong Name Tool (Sn.exe)):

sn -k friend_assemblies.snk // Generate strong name key

sn -p friend_assemblies.snk key.publickey // Extract public key from key.snk into key.publickey

sn -tp key.publickey // Display public key stored in file'key.publickey

Pass the keyfile to the compiler with /keyfile.

Example:
[assembly:InternalsVisibleTo("B, PublicKey=002400000480000094…")]

Enjoy..:):)

Full-Text Search (SQL Server)

SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max). Each full-text index indexes one or more columns from the base table, and each column can have a specific language. Beginning in SQL Server 2008, full-text search supports more than 50 diverse languages, such as English, Spanish, Chinese, Japanese, Arabic, Bengali, and Hindi. For a complete list of the supported full-text languages, see sys.fulltext_languages (Transact-SQL).

For each supported language, SQL Server provides language-specific linguistic components, including a word breaker and stemmer and an empty thesaurus file. For each full-text language, SQL Server also provides a file in which you can optionally define language-specific synonyms to extend the scope of search queries (a thesaurus file). In addition, beginning in SQL Server 2008, a system stoplist is provided. To support specific language or business scenarios, you can alter the system stoplist by adding and removing stopwords (also known as noise words), and you can create additional stoplists as needed.

For writing full-text queries, SQL Server provides a set of full-text predicates (CONTAINS and FREETEXT) and rowset-valued functions (CONTAINSTABLE and FREETEXTTABLE). Using these, applications and users can perform a variety of types of full-text searches, such as searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, or searching on synonymous forms of a specific word.

Note: Full-text search is an optional component of the SQL Server Database Engine. For more information, see Installing SQL Server 2008.

How to Configure a Database for Full-Text Searching
For any scenario, a database administrator performs the following basic steps to configure table columns in a database for full-text search:

1. Create a full-text catalog.

2. On each table that you want to search, create a full-text index by:

a. Identify each text columns that you want to include in the full-text index.

b. If a given column contains documents stored as binary data (varbinary, varbinary(max), or image data), you must specify a table column (the type column) that identifies the type of each document in the column being indexed.

c. Specify the language that you want full-text search to use on the documents in the column.

d. Choose the change-tracking mechanism that you want to use on the full-text index to track changes in the base table and its columns.

Full-text search supports multiple languages through the use of the following linguistic components: word breakers and stemmers, stoplists that contain stopwords (also known as noise words), and thesaurus files. Thesaurus files and, in some cases, stoplists require configuration by a database administrator. A given thesaurus file supports all full-text indexes that use the corresponding language, and a given stoplist can be associated with as many full-text indexes as you want.

After the columns have been added to a full-text index, applications and users can run full-text queries on the text in the columns. These queries can search for any of the following:

1. One or more specific words or phrases (simple term)

2. A word or a phrase where the words begin with specified text (prefix term)

3. Inflectional forms of a specific word (generation term)

4. A word or phrase close to another word or phrase (proximity term)

5. Synonymous forms of a specific word (thesaurus)

6. Words or phrases using weighted values (weighted term)

Full-text queries all use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE). However, the search goals of a given business scenario influence the exact structure of the full-text queries;

for example:

a. e-business—searching for a product on a website:
SELECT product_id FROM products WHERE CONTAINS(product_description, ”Snap Happy 100EZ” OR FORMSOF(THESAURUS,’Snap Happy’) OR ‘100EZ’) AND product_cost<200

b. Recruitment scenario—searching for job candidates that have experience working with SQL Server:
SELECT candidate_name,SSN FROM candidates WHERE CONTAINS(candidate_resume,”SQL Server”) AND candidate_division =DBA

Partitioned Views in SQL Server

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column. A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which member table contains the rows.

For example, a sales table that records sales for 1998 has been partitioned into 12 member tables, one for each month. Each member table has a constraint defined on the OrderMonth column:

CREATE TABLE May1998sales
(OrderID INT,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL
CHECK(DATEPART(mm, DeliveryDate) = 5)
CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID, OrderMonth)
)

The application that populates May1998sales must make sure that all rows have 5 in the OrderMonth column and that the order date specifies a date in May 1998. This is enforced by the constraints defined on the table.

A view is then defined that uses UNION ALL to select the data from all 12 member tables as a single result set:

CREATE VIEW Year1998Sales
AS
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
SELECT * FROM Apr1998Sales
UNION ALL
SELECT * FROM May1998Sales
UNION ALL
SELECT * FROM Jun1998Sales
UNION ALL
SELECT * FROM Jul1998Sales
UNION ALL
SELECT * FROM Aug1998Sales
UNION ALL
SELECT * FROM Sep1998Sales
UNION ALL
SELECT * FROM Oct1998Sales
UNION ALL
SELECT * FROM Nov1998Sales
UNION ALL
SELECT * FROM Dec1998Sales

For example, the following SELECT statement queries for information about specific months.

SELECT *
FROM Year1998Sales
WHERE OrderMonth IN (5,6) AND CustomerID = 64892

The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the May1998Sales and Jun1998Sales tables. Therefore, it limits its search to those tables.

To perform updates on a partitioned view, the partitioning column must be a part of the primary key of the base table. If a view is not updatable, you can create an INSTEAD OF trigger on the view that allows updates. You should design error handling into the trigger to make sure that no duplicate rows are inserted. For an example of an INSTEAD OF trigger designed on a view, see Designing INSTEAD OF Triggers.

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.

If all the member tables referenced by a partitioned view are on the same server, the view is a local partitioned view. If the member tables are on multiple servers, the view is a distributed partitioned view. Distributed partitioned views can be used to spread the database processing load of a system across a group of servers. For more information, see Federated Database Servers.

Partitioned views make it easier to maintain the member tables independently. For example, you can do the following at the end of a period:

1. The definition of the partitioned view for current results can be changed to add the newest period and drop the oldest period.

2. The definition of the partitioned view for past results can be changed to add the period just dropped from the current results view. The past results view can also be updated to remove and archive the oldest period it covers.

When you insert data into the partitioned views, the sp_executesql system stored procedure can be used to create INSERT statements with execution plans that have a significant chance of being reused in systems with many concurrent users.

Note:Bulk importing into a partitioned view is unsupported by both the bcp command and the BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements. However, you can insert multiple rows into a partitioned view by using an INSERT statement

Common Table Expressions (Recursive Queries)

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive steps. For more information about common table expressions, see Using Common Table Expressions.


Structure of a Recursive CTE
The structure of the recursive CTE in Transact-SQL is similar to recursive routines in other programming languages. Although a recursive routine in other languages returns a scalar value, a recursive CTE can return multiple rows.

A recursive CTE consists of three elements:

1. Invocation of the routine.
The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.

CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

2. Recursive invocation of the routine.
The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.

3. Termination check.
The termination check is implicit; recursion stops when no rows are returned from the previous invocation.

Example
The following example shows the semantics of the recursive CTE structure by returning a hierarchical list of employees, starting with the highest ranking employee, in the Adventure Works Cycles company. The statement that executes the CTE limits the result set to employees in the Research and Development Group. A walkthrough of the code execution follows the example.

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO

Common Table Expressions in SQL Server

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

1. Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

2. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

4. Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.


Example
The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, NumberOfOrders, and MaxDate) and is defined as the total number of sales orders and the most recent sales order date in the SalesOrderHeader table for each salesperson. When the statement is executed, the CTE is referenced two times: one time to return the selected columns for the salesperson, and again to retrieve similar details for the salesperson's manager. The data for both the salesperson and the manager are returned in a single row.


USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Derived Tables in SQL Server

The power of SQL Server never fails to amaze me; it literally seems that you can do just about anything in SQL. All you need is a little creativity and knowledge of the syntax, and you can put the power of SQL behind your web application. One of the neatest things I've yet done with SQL Server is using derived tables.

If you've used a VIEW before, you've used a more formal, more correct form of a derived table. For example, we could do the following:


CREATE VIEW vwEmployeesFromNewYork AS
SELECT * FROM Employee
WHERE State = "NY"
GO

Then if we wanted to see all of the Employees from New York with the last name Smith, ordered alphabetically, we could write:


SELECT LastName, FirstName
FROM vwEmployeesFromNewYork
WHERE LastName = "Smith"
ORDER BY FirstName

However, using derived tables, we could eliminate the view entirely. (Of course the view could be eliminated by simply adding an "AND State = "NY"" to the above WHERE clause, but what's important here is the concept, not the example!) Here is the same resultset as above but with the use of a derived table in place of a veiw:


SELECT LastName, FirstName
FROM
(SELECT * FROM Employee
WHERE State = "NY") AS EmployeeDerivedTable
WHERE LastName = "Smith"
ORDER BY FirstName

Isn't that neat? What we are doing is first getting the result set from our derived table (the SELECT statement in the FROM clause). Once we have that resultset, it is as though it was a table in itself. We then perform the SELECT on the derived table, returning our results! You can find another example of using derived tables here on 4GuysFromRolla.com in the article Obtaining Ranked Values from a Table page.

Happy Programming!

QTP

SQL Server 2008 - MERGE Operator

MERGE Operator
Use GROUPING SETS to obtain results similar to those generated by using CUBE and ROLLUP, however GROUPING SETS is more flexible, offers better performance, and is ANSI SQL 2006 compliant. GROUPING SETS enables the GROUP BY clause to generate multiple grouped aggregations in a single result set. It is equivalent to using UNION ALL to return a result set from multiple SELECT statements, each of which has a GROUP BY clause.

SQL Server 2008 – GROUPING SETS

GROUPING SETS

Lets try with some realtime example...

Create sales table and fill some data into it:
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)

Then run the bellow query:
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP

Output:


Also try this:
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
Output:


We can rewrite these two queries using the new syntax as:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY ROLLUP(EmpId, Yr)

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY CUBE(EmpId, Yr)

The new GROUPING SETS syntax is considerably more powerful. It allows us to specify precisely which aggregations we want to compute. As the following table illustrates, our simple two dimensional schema has a total of only four possible aggregations:



ROLLUP and CUBE are just shorthand for two common usages of GROUPING SETS. We can express the above ROLLUP query as:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), ())



This query explicitly asks SQL Server to aggregate sales by employee and year, to aggregate by employee only, and to compute the total for all employees for all years. The () syntax with no GROUP BY columns denotes the total. Similarly, we can express the above CUBE query by asking SQL Server to compute all possible aggregate combinations:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())


We can also use GROUPING SETS to compute other results. For example, we can perform a partial rollup aggregating sales by employee and year and by employee only but without computing the total for all employees for all years:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))



We can skip certain rollup levels. For example, we can compute the total sales by employee and year and the total sales for all employees and all years without computing any of the intermediate results:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), ())


We can even compute multiple unrelated aggregations along disparate dimensions. For example, we can compute the total sales by employee and the total sales by year:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), (Yr))


Note that we could also write GROUPING SETS (EmpId, Yr) without the extra set of parenthesis, but the extra parenthesis make the intent of the query more explicit and clearly differentiate the previous query from the following query which just performs a normal aggregation by employee and year:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr))


Here are some additional points worth noting about the GROUPING SETS syntax:

As with any other aggregation query, if a column appears in the SELECT list and is not part of an aggregate function, it must appear somewhere in the GROUP BY clause. Thus, the following is not valid:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), ())

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.Yr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The order of the columns within each GROUPING SET and the order of the GROUPING SETS does not matter. So both of the following queries compute the same CUBE although the order that the rows are output differs:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((), (Yr), (EmpId), (Yr, EmpId))

If the order that the rows are output matters, use an explicit ORDER BY clause to enforce that order.

We can nest CUBE and ROLLUP within a GROUPING SETS clause as shorthand for expressing more complex GROUPING SETS. This shorthand is most useful when we have more than three dimensions in our schema. For example, suppose we add a month column to our sales table:

CREATE TABLE Sales (EmpId INT, Month INT, Yr INT, Sales MONEY)

Now, suppose we want to compute sales for each employee by month and year, by year, and total. We could write out all of the GROUPING SETS explicitly:

SELECT EmpId, Month, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr, Month), (EmpId, Yr), (EmpId))

Or we can use ROLLUP to simplify the query:

SELECT EmpId, Month, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, ROLLUP(Yr, Month)))

Note that once again the correct use of parenthesis is critical. If we omit one set of parenthesis from the above query, the meaning changes significantly and we end up separately aggregating by employee and then computing the year and month ROLLUP for all employees.

SQL Server 2008 – Some Cool Features for Database Developer

This is an overview of four features in SQL Server 2008 that I think are neat and will be important to both developers and DBAs. For a comprehensive feature list, go to Microsoft's SQL Server 2008 Product Overview page.

1. Table variable parameters

For a long time, I have wished that developers could pass a table variable as a parameter to stored procedures. With the advent of SQL Server 2005, XML variables can be passed into procedures and parsed out with XQUERY inside the procedure to form a table. However, with the ability to pass a table variable in SQL Server 2008, developers will not have to be fully XQuery literate to pass a table of data into or out from a stored procedure. In my opinion, this will be one of the more integral tools for the developer in SQL Server 2008.

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory.

Note: You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

2. MERGE Statement

The new MERGE operator streamlines the process of populating a data warehouse from a source database. For example, rows that get updated in the source database will probably already exist in the data warehouse but rows that are inserted into the source database will not already exist in the data warehouse. The MERGE statement distinguishes between the new and updated rows from the source database so that the appropriate action (insert or update) can be performed against the data warehouse in one single call.

3. Grouping Sets
Use GROUPING SETS to obtain results similar to those generated by using CUBE and ROLLUP, however GROUPING SETS is more flexible, offers better performance, and is ANSI SQL 2006 compliant. GROUPING SETS enables the GROUP BY clause to generate multiple grouped aggregations in a single result set. It is equivalent to using UNION ALL to return a result set from multiple SELECT statements, each of which has a GROUP BY clause.

SQL Server 2008 - Table Valued Parameters

Table Valued Parameters

In SQL Server 2005 and earlier, it was bit difficult to pass numerous parameters (Table variable) to the T-SQL Statements or Functions or Stored Procedures. The approach we used to take is creating a Temporary Table on Demand and inserts the values to the Temporary Table and then calls it in requisite procedure. In SQL Server 2008 the Table-Valued Parameters have been introduced that helps us eliminating the cumbersome process. This is the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.
User-Defined Table Type

When first take a look at the new table-valued parameters, I thought that using this feature is a bit complicated. There are several steps involved. The first thing to do is to define a table type. If you look at the Types section under Programmability in the 2008 Management Studio, you will see the new “User-Defined Table Types”


Table-valued Parameters are the new parameter types in SQL Server 2008 and it could be declared by declaring the user defined table types. The Table-valued parameters could be used to send multiple rows of data to a T-SQL statement or routine. Table-valued parameters are like the parameter arrays in OLE DB and ODBC but with better flexibility. It could be used to participate in the set-based operations. Permissions for the Table-valued parameters follow SQL Server Object Model so CREATE, GRANT, DENY, ALTER etc. could be used.

Table Valued Parameters help in the following:
1. They are Strongly Typed
2. We can specify the sort order and the unique key
3. Reduce Round trips to the server
4. We can have the table structure of different cardinality
5. It doesn't need or cause the statement to recompile
6. It doesn't need lock to initially populate the data from the client
7. Enables us to include complex business logic in a single routine
8. It provides a very simple programming model.

Lets start with an example:
Syantax:



After running the above code, the object definition is created and viewable in the “User-Defined Table Type”. You can view the properties there, but not modify them. To modify the type, you will have to drop it and then create it again with the modified definition.



Using the User-Defined Table Type
So far, it seems like we have done quite a bit of work, but we only have the type definition created. Like other programmable objects, it will stick around unless it is dropped. To use it in T-SQL code, you must create a variable of the new type and then populate it as you would any other table. Once it is populated, you can use it in other T-SQL statements. Because it is a variable, it goes out of scope automatically when the batch is completed. Notice in the code below that the name of the data type is the same as the type we just created.



As long as the variable does not go out of scope, you can do just about anything with it that you can do with a regular table variable, such as join another table or be used to populate another table. Like a table variable, you can not modify the table definition.

As I mentioned, the variable is gone once it goes out of scope. For example if you have a T-SQL script that is composed of more than one batch, the variable is only valid within the batch where it was created.

Using the Variable as a Parameter
So far, we haven’t seen anything that is not possible with a regular table variable. The benefit is being able to pass the variable of the new type to a stored procedure. A stored procedure must be created that uses the new type. Here is an example of that along with the code to create a regular table that we will be populating.



Notice the READONLY qualifier after the table parameter. This is required because a copy of the user-defined table variable is not passed to the procedure. To be more efficient, a pointer is passed to the proc. Therefore, to eliminate changes to the variable inside the proc that would affect the original, no changes are allowed.

Finally, let’s put it all together and call the stored procedure. In the next code snippet, code from the previous section is used to create and populate the variable.



In order for a user to use the User-Defined Table Type, EXECUTE or CONTROL permission must be granted. This is the command to grant permission to a user:

GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;

Calling from a .Net Application
The coolest way to use the table-valued parameter is from a .Net application. To do so, you need to have .NET 3.5 installed and make sure you are using the System.Data.SQLClient namespace. This gives you a new SQL data type called Structured that you will use when creating the parameter.

First create a local DataTable and populate it. Be sure that the DataTable that you create matches the user-defined table type’s column count and data types.



Since we will be working with a stored proc, create a command object and add the two parameters. This code assumes that you have an open connection to your test database.



Notice the data type of the @MyTableParam parameter. This is the new type added with .Net 3.5 to work with this new functionality. Finally, assign the local table to the parameter and execute the command.

Conclusion
The table-valued parameter feature introduced with SQL Server 2008 has been sorely needed. It will allow developers to write much better performing applications by decreasing round-trips to the server and by letting SQL Server work how it does best – on sets of data. It is not difficult to use once all the pieces are put into place.

Let me know if I missed anything...

Expence

Regular Expressions with The Microsoft .NET Framework

Introduction
Regular expressions have been used in various programming languages and tools for many years. The .NET Base Class Libraries include a namespace and a set of classes for utilizing the power of regular expressions. They are designed to be compatible with Perl 5 regular expressions whenever possible.

In addition, the regexp classes implement some additional functionality, such as named capture groups, right- to-left pattern matching, and expression compilation.

In this article, I'll provide a quick overview of the classes and methods of the System.Text.RegularExpression assembly, some examples of matching and replacing strings, a more detailed walk-through of a grouping structure, and finally, a set of cookbook expressions for use in your own applications.

The RegularExpression Assembly
The regexp classes are contained in the System.Text.RegularExpressions.dll assembly, and you will have to reference the assembly at compile time in order to build your application. For example: csc /r:System.Text.RegularExpressions.dll foo.cs will build the foo.exe assembly, with a reference to the System.Text.RegularExpressions assembly.

There are actually only six classes and one delegate definition in the assembly namespace. These are:

Capture: Contains the results of a single match

CaptureCollection: A sequence of Capture's

Group: The result of a single group capture, inherits from Capture

Match: The result of a single expression match, inherits from Group

MatchCollection: A sequence of Match's

MatchEvaluator: A delegate for use during replacement operations

Regex: An instance of a compiled regular expression

The Regex class also contains several static methods:

Escape: Escapes regex metacharacters within a string

IsMatch: Methods return a boolean result if the supplied regular expression matches within the string

Match: Methods return Match instance

Matches: Methods return a list of Match as a collection

Replace: Methods that replace the matched regular expressions with replacement strings

Split: Methods return an array of strings determined by the expression

Unescape: Unescapes any escaped characters within a string

Regular expressions are used to search specified in the source string.

Examples:

Pattern#1
Regex objNotNaturalPattern=new Regex("[^0-9]");

Pattern#2
Regex objNaturalPattern=new Regex("0*[1-9][0-9]*");

Pattern#1 will match for strings other than 0 to 9.^ symbol is used for Specifying not condition.[] brackets if we are to give range values such as 0 - 9 or a-z or A-Z

eg. abc will return true

123 will return false.

Pattern#2 will match for string which are Natural Numbers.Natural numbers Are numbers which are always greater than 0.The pattern 0* tells that a natural Number can be prefixed with any number of zero's or no zero's.the next [1-9] tells that it should contain atleast one number from 1 to 9 followed by any numbers of

0-9's

Eg. 0007 returns true whereas 00 will return false.

Basic things to be understood in RegEx:

"*" matches 0 or more patterns
"?" matches single character
"^" for ignoring matches.
"[]" for searching range patterns.

1. Getting numbers in string
First, here we look at how you can get all numbers in a string, and then actually parse them into integers for easier usage in your C# program. The important part of the example is that it splits on all non-digit values in the string, and then loops through the result strings and uses int.TryParse.

=== Program that uses Regex.Split (C#) ===
using System;
using System.Text.RegularExpressions;

class Program
{
static void Main()
{
//
// String containing numbers.
//
string sentence = "10 cats, 20 dogs, 40 fish and 1 programmer.";
//
// Get all digit sequence as strings.
//
string[] digits = Regex.Split(sentence, @"\D+");
//
// Now we have each number string.
//
foreach (string value in digits)
{
//
// Parse the value to get the number.
//
int number;
if (int.TryParse(value, out number))
{
Console.WriteLine(value);
}
}
}
}

=== Output of the program ===

10
20
40
1

2. Splitting on multiple whitespaces
Here we see how you can extract all substrings in your string that are separated by whitespace characters. You could also use string Split, but this version is simpler and can also be extended more easily. The example gets all operands and operators from an equation string.

=== Program that tokenizes (C#) ===

using System;
using System.Text.RegularExpressions;

class Program
{
static void Main()
{
//
// The equation.
//
string operation = "3 * 5 = 15";
//
// Split it on whitespace sequences.
//
string[] operands = Regex.Split(operation, @"\s+");
//
// Now we have each token.
//
foreach (string operand in operands)
{
Console.WriteLine(operand);
}
}
}

=== Output of the program ===

3
*
5
=
15

3. Getting all uppercase words

Here we look at a method that gets all the words that have an initial uppercase letter in a string. The Regex.Split call used actually just gets all the words, and the loop checks the first letter for its case. In most programs, it is useful to combine regular expressions and manual looping and string operations. Programs are not art projects.

=== Program that collects uppercase words (C#) ===

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;

class Program
{
static void Main()
{
//
// String containing uppercased words.
//
string sentence = "Bob and Michelle are from Indiana.";
//
// Get all words.
//
string[] uppercaseWords = Regex.Split(sentence, @"\W");
//
// Get all uppercased words.
//
var list = new List();
foreach (string value in uppercaseWords)
{
//
// Check the word.
//
if (!string.IsNullOrEmpty(value) &&
char.IsUpper(value[0]))
{
list.Add(value);
}
}
//
// Write all proper nouns.
//
foreach (var value in list)
{
Console.WriteLine(value);
}
}
}

=== Output of the program ===

Bob
Michelle
Indiana

4. Using class-level compiled Regex
Here we see how you can use a compiled regular expression, and store it at the class level. We see two new approaches here. The Regex is stored as a static field, meaning it can be reused throughout the application without recreating it.

=== Program that uses static compiled Regex (C#) ===

using System;
using System.Text.RegularExpressions;

class Program
{
static Regex _wordRegex = new Regex(@"\W+", RegexOptions.Compiled);

static void Main()
{
string s = "This is a simple /string/ for Regex.";
string[] c = _wordRegex.Split(s);
foreach (string m in c)
{
Console.WriteLine(m);
}
}
}

=== Output of the program ===

This
is
a
simple
string
for
Regex

5. Using instance Regex with Split
Here we see faster approach than the above example. This example creates an expression with new Regex. It works the same, but has better performance. It stores the Regex as a method-level instance.

=== Program that uses instance Regex (C#) ===

using System;
using System.Text.RegularExpressions;

class Program
{
static void Main()
{
string s = "This is a simple /string/ for Regex.";
Regex r = new Regex(@"\W+");
string[] c = r.Split(s);
foreach (string m in c)
{
Console.WriteLine(m);
}
}
}

=== Output of the program ===

This
is
a
simple
string
for
Regex

6. Using static Regex.Split
Here we look at the slowest of the examples in this document. This is the static Regex.Split method in System.Text.RegularExpressions. For the next three examples, I use Split, but other methods such as Matches, Match, and Replace have similar characteristics.

=== Program that uses Regex.Split (C#) ===

using System;
using System.Text.RegularExpressions;

class Program
{
static void Main()
{
string s = "This is a simple /string/ for Regex.";
string[] c = Regex.Split(s, @"\W+");
foreach (string m in c)
{
Console.WriteLine(m);
}

}
}

=== Output of the program ===

This
is
a
simple
string
for
Regex

Windows Communication Foundation - Hello World Tutorial

Windows Communication Foundation forms part of the .net Framework 3.0 runtime. This runtime is completely backwards compatible to Windows XP (SP2) and Windows 2003, and it allows you to support hosting and calling WCF services.

Create WCF Service Application
1. Open Microsoft Visual Studio
2. File->New Project->Type project name as 'ServiceLayer'


3. Now you can see your solution explorer. It will look like bellow

4. Now delete IService1.cs and Service1.svc
5. Add a new item WCF Service with name 'HelloWorldService'
Now youe solution explorer will look like bellow


6. Double click on and enter the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace ServiceLayer
{
[ServiceContract]
public interface IHelloWorldService
{
[OperationContract]
string SayHello(string name);
}
}

This interface defines exactly what type of work our service will be doing, and will quite literally form the definition of our service. We will refer directly to the IHelloWorld interface from our configuration file as specifying the contract of what our service will do. The only difference between this code and another .net interface is that parts of it are decorated with WCF attributes. The first attribute (“ServiceContract”) states that this interface will form the contract for a WCF service, while the second one (“OperationContract”) defines the method as an operation that the service will offer and perform. One ServiceContract can contain multiple OperationContracts and these will be translated by the framework into the appropriate wsdl tags, but all of this is entirely transparent to the developer.


Next comes our class – the implementation of our interface. This class appears below, and you should take note of two important aspects, which we will discuss below.



7. Go to the HelloWorldService.svc.cs and change the code like bellow
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace ServiceLayer
{

public class HelloWorldService : IHelloWorldService
{
public string SayHello(string name)
{
return "Hello, " + name;

}

}
}

Now your done and all set to run the service. Rebuild the solution.
Our next step is to to run our service and see how it appears in the browser. All being well, we should see something like the screenshot below:


Create A Client Application
In order for our service to have any value (well, as valuable as a “Hello World” service can be!), it must have something that consumes it, so we’ll now go about creating our client. Add a new console application

8. Add a new project (Type Console Application) called TestClient
9. Right click on TestClient project and click Add Service Reference
10. Click on Discover
11. HelloWorldService.svc will display inServices list
12. Expand the node in the services list
13. Select the HellowWorldService and change the namespace 'MyService' and click on OK



14. Double click on Program.cs and place the folowing code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace TestClient
{
class Program
{
static void Main(string[] args)
{
MyService.HelloWorldServiceClient client = new MyService.HelloWorldServiceClient();

// Use the 'client' variable to call operations on the service.
Console.WriteLine(client.SayHello("RUDRA"));

// Always close the client.
client.Close();

Console.ReadLine();

}
}
}

Now rebuild the solution and change the TestClient as start up project and press F5
You will see the bellow:



Enjoy...

Windows Communication Foundation

Overview
The WCF unifies the various communications programming models supported in .NET 2.0, into a single model. Released in November 2005, .NET 2.0 provided separate APIs for SOAP-based communications for maximum interoperability (Web Services), binary-optimized communications between applications running on Windows machines (.NET Remoting), transactional communications (Distributed Transactions), and asynchronous communications (Message Queues). WCF unifies the capabilities from these mechanisms into a single, common, general service-oriented programming model for communications.

WCF can use SOAP messages between two processes, thereby making WCF-based applications interoperable with any other process that communicates via SOAP messages. When a WCF process communicates with a non–WCF process, XML-based encoding is used for the SOAP messages but when it communicates with another WCF process, the SOAP messages can be encoded in an optimized binary format. Both encodings conform to the data structure of the SOAP format, called Infoset.

WCF uses a pluggable encoding system, allowing developers to write their own encoders. With the release of the .NET Framework 3.5 in November 2007, Microsoft released an encoder that added support for the JSON serialization format to WCF. This allows WCF service endpoints to service requests from AJAX-powered web pages.

Service oriented architecture
WCF is designed in accordance with Service oriented architecture principles to support Distributed computing where services are consumed by consumers. Clients can consume multiple services and services can be consumed by multiple clients. Services typically have a WSDL interface which any WCF client can use to consume the service, irrespective of which platform the service is hosted on. WCF implements many advanced web services (WS) standards such as WS-Addressing, WS-ReliableMessaging and WS-Security. While Microsoft is a board member of WS-I it is not clear how many WS-I profiles they are committing to support completely.

WCF Service
A WCF Service is composed of three parts — a Service class that implements the service to be provided, a host environment to host the service, and one or more endpoints to which clients will connect. All communications with the WCF service will happen via the endpoints. The endpoints specify a Contract that defines which methods of the Service class will be accessible via the endpoint; each endpoint may expose a different set of methods. The endpoints also define a binding that specifies how a client will communicate with the service and the address where the endpoint is hosted.

WCF provides Windows Activation Services which can be used to host the WCF service. Otherwise the WCF service can also be hosted in IIS or in any process by using the ServiceHost class, which is provided by WCF. Services can also be self-hosted, in a console-based application or a Windows-forms application for example.

Defining WCF services
In programming code, a WCF Service is implemented as a class. The class typically implements a Service Contract - a specially annotated interface whose methods stipulate the operations that the service performs. Typically, the methods on the interface accept inputs and output messages, which conform to Data Contracts, described with specially annotated classes. Think of these as Data Transfer Objects.

The Data and Service Contracts are defined using annotations in programming code, known formally in .NET as Attributes. Any class that is to be exposed as a WCF service must be either marked with the ServiceContract attribute, or implement an interface marked with it. All methods in the class or interface that a client can invoke using SOAP messages must be marked with OperationContract attribute. All classes or structures that define the data to be passed into or out of the operations are marked with the DataContract attribute. The attributes support the automatic generation of WSDL descriptions for the exposed methods, which can then be accessed by clients, or advertised to them.

A service can expose multiple Service Contracts. This can be done by defining multiple .NET interfaces, each marked as a Service Contract. The service class can then implement all the interfaces.

The ServiceContract and OperationContract attributes also allow an interface to reference a previously existing contract, thus providing an option for supporting interface versioning.

All Service Contracts have an associated implicit or explicit Data Contract which defines the data that the service works on. If the parameters accepted and returned by the service methods consist of primitive types (integer, double, boolean, etc), then the Data Contract is defined implicitly by WCF. If, on the other hand, the data is of a complex type like an object or a struct, then it must be defined explicitly by the programmer via attributes. Data contracts specify how the data is serialized and de-serialized, allowing for custom representation of objects passing in and out.

A Data contract is defined by using a DataContract attribute on a class or structure. Members of the data structure which will be used by the service need to be marked with the DataMember attribute. Only those members will be transferred between the service and its client. In the same way that different classes can implement the same interface, different classes can implement the same Data Contract, and can serialize and de-serialize the same data. Taking the idea further, a .NET class defined in C# or VB can implement a Data Contract, and serialize to a data packet, that can then be de-serialized by a Java class or a PHP class.

The behavior of the Service in general and the operations in particular can be controlled using the ServiceBehavior and the OperationBehavior attributes respectively. The ServiceBehavior attribute has different properties. The ConcurrencyMode property specifies whether the service will be concurrent, i.e., whether it will support simultaneous clients or not. Similarly, the InstanceMode property specifies whether a single instance of the service will serve all requests or a new instance of the service will be created for each request, or a new instance of the service will be created for each session.


Defining Endpoints
A WCF client connects to a WCF service via an endpoint.

Each Service exposes its Contract via one or more endpoints. An endpoint has an address, which is a URL specifying where the endpoint can be accessed, and binding properties that specify how the data will be transferred.

The mnemonic "ABC" can be used to remember Address / Binding / Contract. Binding specifies what communication protocols are used to access the service, whether security mechanisms are to be used, and the like. WCF includes predefined bindings for most common communication protocols such as SOAP over HTTP, SOAP over TCP, and SOAP over Message Queues etc.

When a client wants to access the service via an endpoint, it not only needs to know the Contract, but it also has to adhere to the binding specified by the endpoint. Thus, both client and server must have compatible endpoints.

Communication with the service
A client can communicate with a WCF service using any of the RPC-based mechanisms in which the service can be invoked as a method call. Using synchronous communications, any call to the service will be blocking - that is, it will halt the execution of the client until the service processes the request. The client has to connect to a service using a proxy object, which is connected to the specified endpoint of the service and abstracts the service as an object. All method calls to the proxy object will be routed to the service and the proxy will return the results returned by the service to the caller.

Tools shipped with the .NET Framework SDK can consume WSDL and create client-side proxy classes for use with WCF. Such classes handle the serialization and data transmission to and from the service, as well as faults and exceptions.

WCF also supports non-blocking (asynchronous) calls between client and service, via several mechanisms. One option is to use Message Queues as the transport for the delivery and receipt of the messages. (Keep in mind that the use of Message Queues does not imply a change to a Put/Get style programming model. Even with the use of persistent queues, the programming model still uses friendly proxy classes.) A second mechanism for supporting asynchronous communications is via multiple threads - there is a simple mechanism to do this in the generated client-side proxies for WCF. (see Calling WCF Services asynchronously)

In addition to the higher-level programming model supported by the tool-generated proxy classes, WCF exposes a lower-level programming model where applications manually construct and pass messages to services. Communicating using messages does not require the use of the proxy object, and provides more control, although lower usability, to the programmer.

Round a double to x significant figures after decimal point

Microsoft .Net frame work doesn't have any number in function to a double to a significant digits. This can be achievable by the following:

Scale your number scale so that so that your first significant digit is right after the decimal point, round (or truncate), then scale back. The following code should do the trick:

static double RoundToSignificantDigits(this double d, int digits)
{
double scale = Math.Pow(10, Math.Floor(Math.Log10(d)) + 1);
return scale * Math.Round(d / scale, digits);
}

To Truncate:

static double TruncateToSignificantDigits(this double d, int digits)
{
double scale = Math.Pow(10, Math.Floor(Math.Log10(d)) + 1 - digits);
return scale * Math.Truncate(d / scale);
}

Windows Services with Timer Objects C#.NET 2008

Timer events can be very useful when you need to run a routine on a regular basis. In .NET, creating Windows services with the timer object is very easy to do. In this article we are going to create a timer which writes text to a file on regular intervals, and we’ll employ a Windows Service to control the timer.
Timer Object Concept

From the timer object (in the System.Timers namespace) we use the following properties and events:

Elapsed: Everything in the timer evolves around the Elapsed event, which is the event that is raised every interval. You create code to be executed and call that code in the Elapsed event.

Interval: Used to set the time between raising the Elapsed event.

AutoReset: Ensures that the timer will be reset after every Elapse event. Therefore, if you would only like to execute the Elapse event once, you set the AutoReset property to false. When you omit the AutoReset property, it is assumed to be true.

Enabled: Used to tell the timer to start or stop.

Windows Service Concept

A Windows Service has very defined start and stop events. Starting and stopping timers using these events is very organized and is run as a background process. If you define the Windows Service to start automatically, you need not worry about starting the timer again; this background process will keep on running until you stop the service and disable it. Since this is a background process, there will not be a user interface to dialog with the user. In case of exceptions, messages would be written to the Windows Event Log.

Every Windows Service must have a Main method where you issue a Run command, which loads the service into the Services Control Manager. However, if you use Visual Studio.NET, all this code will be generated automatically.

Windows Services with Timer Objects C#.NET - Setting up the Project

1. Create a C# Windows Service project and name it TimerSrv.


The project will come with a class, Service1.cs. Double-click Service1.cs in the project explorer to reveal the properties. Name the service TimerSrv and in the ServiceName field also fill in TimerSrv.



2. Next we are going to add an installer to the project. Right Click on the design page and click Add Installer. A design screen will be added to the project with 2 controls: serviceProcessInstaller1 and ServiceInstaller1.

3. Click the serviceProcessInstaller1 control and, in the properties dialog, change the account to LocalSystem.


4. In the serviceInstaller control, change the start type to Automatic, and give it a nice display name, like Timer Service.



Windows Services with Timer Objects C#.NET - Adding Code
(Page 3 of 5 )

1. Switch to the code view of the Service1.cs file.

Note: we renamed the service to TimerSrv, so we need to change the Run command in the Main method of this class. Find the following line:

ServicesToRun = new System.ServiceProcess.ServiceBase[]
{ new Service1() };

Now, change this to the following:

ServicesToRun = new System.ServiceProcess.ServiceBase[]
{ new TimerSrv() };

2. In the top of the file add use statements:

Use System.IO;
// we need this to write to the file
use System.Timers;
//we need this to create the timer.

3. Somewhere in the class, add a new private void AddToFile() method. We will use this to write text to a file.

private void AddToFile(string contents)
{

//set up a filestream
FileStream fs = new
FileStream(@”c:\timerserv.txt”,

FileMode.OpenOrCreate, FileAccess.Write);

//set up a streamwriter for adding text

StreamWriter sw = new StreamWriter(fs);

//find the end of the underlying filestream

sw.BaseStream.Seek(0, SeekOrigin.End);

//add the text
sw.WriteLine(contents);
//add the text to the underlying filestream

sw.Flush();
//close the writer
sw.Close();
}

Now we can start coding the events.

Windows Services with Timer Objects C#.NET - Coding the Windows Service Start and Stop Event Handlers

In the Service1.cs file we only need to write code for the OnStart and OnStop events. In the OnStart() void method, add the following line of code:

AddToFile(“Starting Service”);

Now, in the OnStop event add the following line:

AddToFile(“Stopping Service”);

This is all we need to do to have a working Windows Service. Next we’ll add the timer to the service.

Creating the Timer

Just under the class definition in the Service1.cs file, add the following global variables:

//Initialize the timer
Timer timer = new Timer();

The idea behind the timer is that it sleeps for a specified period (defined by the interval method), and then executes the code specified with the elapsed event. We need to define a method that will be executed when the Elapsed event occurs, and we do this with the following code, which adds a line of text to the file:

Private void OnElapsedTime(object source, ElapsedEventArgs e)
{
AddToFile(“ Another entry”);
}

Now we can setup the timer.

In the OnStart method we add code to reflect what to do when the elapsed event is raised. In this case, we need to invoke the OnElapsedTime method we defined above, set the interval (in milliseconds) the project needs to sleep, and enable the timer so it raises the Elapsed event.

The complete OnStart method looks like this:

protected override void OnStart(string[] args)

{

//add line to the file
AddToFile(“starting service”);

//ad 1: handle Elapsed event
timer.Elapsed += new ElapsedEventHandler(OnElapsedTime);

//ad 2: set interval to 1 minute (= 60,000 milliseconds)

timer.Interval = 60000;

//ad 3: enabling the timer
timer.Enabled = true;
}

The OnStop event also needs to be modified. A mere timer.Enabled = false suffices. The complete OnStop method looks like this:

protected override void OnStop()
{
timer.Enabled = false;
AddToFile(“stopping service”);
}


That’s all the coding we need to do!

After doing all the above your service1.cs will looks like bellow:
///////////////////
///////////////////
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Timers;
using System.IO;

namespace TimerSrv
{
public partial class TimerSrv : ServiceBase
{
//Initialize the timer
Timer timer = new Timer();

public TimerSrv()
{
InitializeComponent();
}

protected override void OnStart(string[] args)
{
AddToFile("Starting Service");

//ad 1: handle Elapsed event
timer.Elapsed += new ElapsedEventHandler(OnElapsedTime);

//ad 2: set interval to 1 minute (= 60,000 milliseconds)

timer.Interval = 60000;

//ad 3: enabling the timer
timer.Enabled = true;

}

protected override void OnStop()
{
timer.Enabled = false;
AddToFile("Stopping Service");
}

private void OnElapsedTime(object source, ElapsedEventArgs e)
{
AddToFile(" Another entry");
}

private void AddToFile(string contents)
{

//set up a filestream
FileStream fs = new FileStream(@"c:\timerserv.txt",FileMode.OpenOrCreate, FileAccess.Write);

//set up a streamwriter for adding text

StreamWriter sw = new StreamWriter(fs);

//find the end of the underlying filestream

sw.BaseStream.Seek(0, SeekOrigin.End);

//add the text
sw.WriteLine(contents);
//add the text to the underlying filestream

sw.Flush();
//close the writer
sw.Close();
}

}
}
////////////////
///////////////

Windows Services with Timer Objects C#.NET - Building and Installing the Service

Build the executable: Build->Build Solution

In order to install the service we need to use the installutil console command, which comes with the .NET Framework.

Open a command line window by going to Start -> Programs -> Microsoft Visual Studio.Net -> Visual Studio.Net Tools -> Visual Studio.Net Command Prompt, and change to the directory where the executable is located. Enter the following command:

installutil TimerServ.exe
// Whatever you defined the executable
// name to be




Now the service is installed. To start and stop the service, go to Control Panel -> Administrative Tools -> Services. Right click the service and select Start.

Now the service is started, and you will be able to see entries in the log file we defined in the code.