SQL Stored Procedure Parameters

SQL Server Stored-Procedures can return data in 4 forms:
1. Return Code: which are always an integer value.
2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.

In SQL Server, there are two ways with we can pass parameters to procedures.

• INPut parameter
• OUTPut parameter

INPut parameter
These types of parameters are used to send values to stored procedures.
OUTPut parameter
These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
In this post we will try to understand how stored procedure returns the parameter
Example

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- Create a table CREATE TABLE myTable (     ID INT Identity(1,1),     Name VARCHAR(20) ) -- Now Create a Stored Procedure with the OUTPUT  parameter CREATE PROCEDURE my_sp (     @name VARCHAR(20),     @id_out INT OUTPUT ) AS BEGIN INSERT INTO myTable VALUES (@name) SELECT  @id_out = Scope_Identity () END GO

Stored Procedure with Output Parameters

Similarly, for the second row

Stored Procedure with Output Parameters

Stored Procedure with Output Parameters

Similarly you can get values from stored procedures as you need or required. Hope this post helps you to understand the OUTPut parameters.

Input parameters are those parameters that you pass to the stored procedure

which the SP uses to retrieve records or do some manipulation. 

Output parameters are those parameters that you want the stored procedure

to return to the application that is calling it.

Basically you use output parameters if you want to

return a single value to the calling application.

If you intend to return multiple values or multiple rows,

you can use a record set instead (by calling a SELECT statement).

Common Table Expression

What is a Common Table Expression (CTE)

A CTE can be thought of as a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table while providing the same benefits to the user. However; a CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query.

The basic syntax structure for a CTE is shown below:
WITH MyCTE
AS ( SELECT EmpID, FirstName, LastName, ManagerID
FROM Employee
WHERE ManagerID IS NULL )
SELECT *
FROM MyCTE

Building a Recursive CTE

In the following examples, you will learn how to harness the power of a recursive CTE query by fulfilling a common business requirement, retrieving hierarchical data. By the time the final query is complete you will be able to easily determine how many levels from the top executive each employee is.

A recursive CTE requires four elements in order to work properly.

1. Anchor query (runs once and the results ‘seed’ the Recursive query)
2. Recursive query (runs multiple times and is the criteria for the remaining results)
3. UNION ALL statement to bind the Anchor and Recursive queries together.
4. INNER JOIN statement to bind the Recursive query to the results of the CTE.

WITH MyCTE
AS ( SELECT EmpID, FirstName, LastName, ManagerID
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT EmpID, FirstName, LastName, ManagerID
FROM Employee
INNER JOIN MyCTE ON Employee.ManagerID = MyCTE.EmpID
WHERE Employee.ManagerID IS NOT NULL )
SELECT *
FROM MyCTE


Identify the Anchor and Recursive Query

Anyone who does not have a boss is considered to be at the top level of the company and everyone who does have a boss either works for the person(s) at the top level (upper management), or the people that work for them (mid-management thru base employees).

For example, a CEO is at the top level and thus has a ManagerID of null. Likewise, everyone below the CEO will have a ManagerID. This is demonstrated in the two queries below:

The first SELECT statement will become your Anchor query as it will find the employee that has a ManagerID of null (representing Level 1 of the organization). The second SELECT statement will become your Recursive query and it will find all employees that do have a ManagerID (representing Level 2-3 of this organization).

As you can see from the results so far, these queries are unable to give hierarchical data on which level each employee is at within the organization.

Add the Anchor and Recursive query to a CTE

Begin transforming this entire query into a CTE by placing a UNION ALL statement between the Anchor and Recursive queries. Now add parentheses around the entire query, indenting it, moving it down, and adding the declaration WITH EmployeeList AS before the open parenthesis, and then add SELECT * FROM EmployeeList on the next line after the close parenthesis.

Your query should now look like the screenshot below:

As you can see, the results from your CTE are exactly the same as the results returned from running the anchor and Recursive queries simultaneously in the previous example.

Add an expression to track hierarchical level

The Anchor query (aliased as ‘Boss’) inside the CTE represents everyone at Level 1 (i.e. Sally Smith). The Recursive query (aliased as ‘Emp’) represents everyone at Levels 2 and 3. In order to visualize each level in a result set, you will need to add an expression field to each query.

Add the expression “1 AS EmpLevel” to the Anchor query and the expression “2 AS EmpLevel” to the Recursive query. Before executing the entire query, look closely at the expression field. The EmpLevel expressions in the Anchor query will hard-code the numeral 1 (for Sally Smith’s level), while the EmpLevel expressions in the Recursive query will hard-code the numeral 2 for everyone else.

Your query should now look like the screenshot below:

The two new expression fields were a helpful step. In fact, they show the correct EmpLevel information for Sally Smith and for the people at Level 2 (i.e., Adams, Bender, Brown, Kennson, Lonning and Osako). However, the 2 is just a hard-coded placeholder to help visualize your next step. Lisa Kendall and several other employees need to be at Level 3.

Ideally you would like to make the expression dynamic by replacing “2 AS EmpLevel” with the expression “EmpLevel + 1”.

Add a self-referencing INNER JOIN statement

Let’s take a moment and recognise why this is not going to work quite so simply. The idea to increment EmpLevel in the recursive query of the CTE is on the right track. Unfortunately, the recursive query is trying to reference a field called EmpLevel but can’t find one, since it has only been materialized in the result set of the Anchor query and does not yet exist in the recursive set.

How can you materialize the EmpLevel field for the recursive query? We can use the CTE for this! Remember, a recursive CTE requires an INNER JOIN to connect the recursive query to the CTE itself. Go ahead and write an INNER JOIN statement binding the recursive query ‘Emp’ to the CTE ‘EmployeeList AS EL’ ON Emp.ManagerID = EL.EmpID.

Your query should now look like the screenshot below:

Success! You can now see that Sally is at the first level, Alex is at the second level and Lisa appears at the third level. Since a CTE can reference itself, the ‘Emp’ recursive query can now access the EmpLevel field materialized in the EmployeeList CTE.

links to read

Return type of list

 Generally, LINQ to Objects, LINQ to XML, LINQ to DataSets, and all that works on in-memory CLR objects return IEnumerable<T>.   For LINQ to SQL, LINQ to Entities which work on Expression Trees to generate SQL commands, the return type is IQueryable<T>.  

More references:http://blogs.msdn.com/wriju/archive/2008/05/06/linq-ienumerable-t-and-iqueryable-t.aspx
http://jonkruger.com/blog/2007/10/19/iqueryable-vs-ienumerable-in-linq-to-sql-queries/

http://www.codeproject.com/Articles/231163/IQueryable-Vs-IEnumerable-in-terms-of-LINQ-to-SQL 

View in SQL
http://www.codeproject.com/Articles/38560/Overview-of-View-in-SQL-Server-2005

http://msdn.microsoft.com/en-us/library/aa214068%28v=sql.80%29.aspx

used Defined Functions Limiatations/
Difference between userdefined functions and stored procedure

Difference Between User Defined Function and Stored Procedure

A stored procedure is a program (or procedure) which is physically stored within a database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

Functions are computed values and cannot perform permanent environmental changed to SQL Server (i.e. no INSERT or UPDATE statements allowed).
A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

Differences between Stored procedure and User defined functions

Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.

  UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

1. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
2. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
3. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations. 
4. Unlike Stored Procedure DML operations, like INSERT/UPDATE/DELETE, are not allowed in UDF.
5. A stored procedure can have both input and output parameters  whereas UDF can only have input parameters.
   UDF can be directly selected from, whereas a stored procedure requires one to insert the results into a tempory table or table variabl
   
   
6. Stored procedures different than multi-line TVF is that the optimizer can use statistics to create a better query plan for a stored procedure; however, no statistics are maintained on Multi-line UDFs, so the optimizer has to guess at the number of rows, which can lead to a sub-optimal plan.
7. Stored procs can create a table but can’t return table.
   Functions can create, update and delete the table variable. It can return a table
8. Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
   Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.
9. Stored procedures are stored in database in the compiled form.
   Function are parsed and conpiled at runtime only.
10. Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause.
    Function are called from select/where/having clause. Even we can join two functions. 
11. Stored procedure allows getdate () or other non-deterministic functions can be allowed.
    Function won’t allow the non-deterministic functions like getdate().
12. In Stored procedures we can use transaction statements. We can’t use in functions.
13. The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records.
    The function won’t allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.
14. Temporary tables (derived) can be created in stored procedures.
    It is not possible in case of functions.
15. when sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code.
    In case of functions, T-SQL will stop execution of next statements.

In a function you can’t:
– create temporary tables
– use transactions
– call a stored procedure
– use set commands(example: FORCEPLAN ON, ANSI DEFAULT NULL etc.), and the context inside the function is the same like the one from which is called.
– execute update,insert,delete statement
– have an ouput parameter like stored procedure have(this one can have an workaround no mather you need to return a single value or a set of values)
– multiple results(a stored procedure can return up to 1024, I think)
– call exec(‘some valid sql string’) statement(why should you want? but you can’t)
– use begin try end try begin catch end catch

SQL SERVER – User Defined Functions (UDF) Limitations

May 29, 2007 by pinaldave

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

• UDF has No Access to Structural and Permanent Tables.
  • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
• UDF Accepts Lesser Numbers of Input Parameters.
  • UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
• UDF Prohibit Usage of Non-Deterministic Built-in Functions
  • Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
• UDF Returns Only One Result Set or Output Parameter
  • Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
• UDF can not Call Stored Procedure
  • Only access to Extended Stored Procedure.
• UDF can not Execute Dynamic SQL or Temporary Tables
  • UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
• UDF can not Return XML
  • FOR XML is not allowed in UDF
• UDF does not support SET options
  • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
• UDF does not Support Error Handling
  • RAISEERROR or @@ERROR are not allowed in UDFs.

As I said earlier this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

Advantages of UDFs

So why are UDFs important? What can you do with UDFs that you cannot with stored procedures? Well, UDFs are not functionally stronger than stored procedures (in fact, UDFs have many limitations, which we will examine shortly). However, UDFs do provide certain advantages, discussed in the following sections.

Execution Within the SELECT Statement

UDFs are executed within an inline query; for instance, you can call a scalar UDF that calculates the number of business days between two dates, as follows:

SELECT dbo.no_of_business_days(date1, date2)

The developer's ability to execute UDF within inline queries makes UDF easier to use than a procedure within other routines. If you had to perform the same calculation in a procedure, you'd have to return the value as an output parameter. Using output parameters isn't necessarily cumbersome, but would involve more coding, as follows:

DECLARE @no_of_business_days INT

EXEC dbo.proc_for_calculating_business_days date1, date2, @no_of_business_days OUTPUT

Execution from Various Parts of SQL Statements

Scalar UDFs can be called from within the WHERE/HAVING clause of queries or even within a SET list of the UPDATE statement. For instance, the following UDF determines the total sales for a given customer within a particular year by using the Northwind database:

CREATE FUNCTION dbo.total_orders (@customerid CHAR(6), @year INT)
RETURNS INT
AS
BEGIN
DECLARE @total INT

SELECT @total = SUM(unitprice * quantity) 
FROM [order details] a INNER JOIN orders b
ON a.orderid = b.orderid
WHERE 
DATEPART(YEAR, orderdate) = @year
AND
customerid = @customerID


RETURN @total
END

This function can be executed within a WHERE clause of the query returning customers who have purchased goods for more than $15,000 in 1996:

SELECT DISTINCT CustomerID 
FROM 
orders a INNER JOIN [order details] b
ON a.orderid = b.orderid
WHERE 
dbo.total_orders(customerID, 1996) > 15000

Results:

CustomerID

ERNSH

UDFs can also be used within a CASE statement; for instance, you can use total_orders UDF to rank customers according to their sales in 1996, as follows:

SELECT CompanyName, 
SalesRank_1996 = CASE
  WHEN dbo.total_orders(customerID, 1996) < 1000 THEN 'poor'
  WHEN dbo.total_orders(customerID, 1996) BETWEEN 1001 AND 5000 THEN 'average'
  WHEN dbo.total_orders(customerID, 1996) BETWEEN 5001 AND 13000 THEN 'good'
  ELSE 'super'
  END
FROM customers

Results (abbreviated list):

CompanyName                                  SalesRank_1996 
----------------------------------------     -------------- 
Alfreds Futterkiste                          super
Ana Trujillo Emparedados y helados           poor
Antonio Moreno Taquería                      poor
Around the Horn                              average
Berglunds snabbköp                           average
Blauer See Delikatessen                      super
Blondesddsl père et fils                     good
Bólido Comidas preparadas                    poor
Bon app'                                     average
Bottom-Dollar Markets                        average

UDF Output Can Be Used as a Rowset

UDFs that return tables can be treated as another rowset. Thus, you can join other tables within your code module to the output of a UDF. For example, if you have a UDF returning two best-selling titles along with their authors, you can easily join it with another table, as follows:

SELECT 
      a.title, 
      a.author_name, 
      b.royalty_amount
FROM dbo.udf_bestselling_titles a INNER JOIN royalty b
ON a.author_id = b.author_id

If you had to use a stored procedure to do the same procedure, you'd have to do the following:

1. Define a temporary table with the CREATE TABLE statement.
2. Populate the temporary table with the output of the stored procedure.
3. Join the temporary table to other tables.
4. Drop the temporary table at the end of the routine.

Here is the code for a solution that uses a stored procedure instead of a UDF:

CREATE TABLE #temp_table (
    Title_id  INT,
    Title  VARCHAR(200), 
    Author_name VARCHAR(45)
)

INSERT #temp_table (
    title_id, 
    title, 
    author_name )
EXECUTE dbo.usp_bestseller_titles

SELECT 
      a.title, 
      a.author_name, 
      b.royalty_amount
FROM #temp_table a INNER JOIN royalty b
ON a.author_id = b.author_i

UDFs as Parameterized Views

In-line UDFs can be thought of as views that accept parameters. This flavor of UDFs can be used effectively to return different data from the same tables based on the parameter values. For instance, the following UDF will return different categories of titles based on the supplied parameter:

CREATE FUNCTION dbo.udf_category_titles (@category VARCHAR(12))
RETURNS TABLE 
AS
RETURN (
SELECT title, pub_id, price FROM titles 
WHERE 
type = @category)

In-line UDFs can also be used for security—they can return different data based on the user executing the UDF. For instance, the following UDF returns the hire date only if executed by the database owner:

CREATE FUNCTION dbo.udf_get_employee (@p1 INT)
RETURNS TABLE
AS
RETURN
(
SELECT emp_id, fname, lname, 
hire_date = 
CASE WHEN user_name() = 'dbo' THEN CAST(hire_date AS VARCHAR(12)) ELSE 'n/a' END
FROM 
employee 
WHERE job_id = @p1
)

This UDF can be executed as follows:

SELECT * FROM dbo.udf_get_employee(13)

If executed by the database owner, the output is the following:

emp_id          fname                 lname                            hire_date  
---------       --------------------  ------------------------------   ------------ 
PMA42628M       Paolo                 Accorti                          Aug 27 1992 
TPO55093M       Timothy               O'Rourke                         Jun 19 1988 
CGS88322F       Carine                Schmitt                          Jul 7 1992

If executed by any other user, the output will contain "n/a" in the "hire_date" column.

Multi-Statement Functions: Alternatives to Stored Procedures

Multi-statement functions allow you to perform additional logic, such as variable declaration, populating of table variables, and updating values within it, looping through records and more. Multi-statement functions are a great way to rewrite a stored procedure if the following conditions are true:

• The output of the stored procedure can be used by other routines.
• Stored procedures don't make any data changes in the permanent tables.
• The only purpose of the stored procedure is to create a result set that might have to be manipulated prior to returning it to the user.
• Stored procedures do not perform any tasks prohibited inside the UDFs (see the next section for details).

SQL SERVER – Stored Procedures Advantages and Best Advantage

April 13, 2007 by pinaldave

There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.
I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)

Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines

Advantages and Disadvantages of Stored Procedure

http://mukund.wordpress.com/2005/10/14/advantages-and-disadvantages-of-stored-procedure/

Good Link to read on MVC

http://msdn.microsoft.com/en-us/library/gg416514(v=vs.98).aspx

Anonymous Types

Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to explicitly define a type first. The type name is generated by the compiler and is not available at the source code level. The type of each property is inferred by the compiler.
You create anonymous types by using the new operator together with an object initializer. For more information about object initializers, see Object and Collection Initializers (C# Programming Guide).
The following example shows an anonymous type that is initialized with two properties named Amount and Message.

C#

Copy

var v = new { Amount = 108, Message = "Hello" };

// Rest the mouse pointer over v.Amount and v.Message in the following
// statement to verify that their inferred types are int and string.
Console.WriteLine(v.Amount + v.Message);

Anonymous types typically are used in the select clause of a query expression to return a subset of the properties from each object in the source sequence.
'
nonymous types contain one or more public read-only properties. No other kinds of class members, such as methods or events, are valid. The expression that is used to initialize a property cannot be null, an anonymous function, or a pointer type.
The most common scenario is to initialize an anonymous type with properties from another type. In the following example, assume that a class exists that is named Product. Class Product includes Color and Price properties, together with other properties that you are not interested in. Variable products is a collection of Product objects. The anonymous type declaration starts with the new keyword. The declaration initializes a new type that uses only two properties from Product. This causes a smaller amount of data to be returned in the query.
If you do not specify member names in the anonymous type, the compiler gives the anonymous type members the same name as the property being used to initialize them. You must provide a name for a property that is being initialized with an expression, as shown in the previous example. In the following example, the names of the properties of the anonymous type are Color and Price.

C#

Copy

var productQuery = 
    from prod in products
    select new { prod.Color, prod.Price };

foreach (var v in productQuery)
{
    Console.WriteLine("Color={0}, Price={1}", v.Color, v.Price);
}

Typically, when you use an anonymous type to initialize a variable, you declare the variable as an implicitly typed local variable by using var. The type name cannot be specified in the variable declaration because only the compiler has access to the underlying name of the anonymous type. For more information about var, see Implicitly Typed Local Variables (C# Programming Guide).
You can create an array of anonymously typed elements by combining an implicitly typed local variable and an implicitly typed array, as shown in the following example.

C#

Copy

var anonArray = new[] { new { name = "apple", diam = 4 }, new { name = "grape", diam = 1 }};

compiler generates an internal sealed class that models the anonymous type. The anonymous type is immutable; all the properties are read only.  you cannot declare methods that take anonymous types as parameters, nor can you return them from methods

 if anonymous types are so limited, why use them at all? In my opinion, there are two related reasons. First, the compiler writes code faster than I do. The compiler creates a page full of code for each new anonymous type. That’s code I don’t have to write, test, and debug myself. It’s a great time saver. Second, anonymous types are great for local values that support algorithms, but aren’t part of the overall object model for a system. Because the types are anonymous, they don’t clutter the picture of the system as a whole. You don’t browse the code for an anonymous type. Anonymous types do not show up in Class View. Anonymous types don’t require external documentation. They just quickly provide a small bit of functionality. 

Anonymous types as the name suggests,  help us  to create an object without declaring its data type. Since name of the data type is not specified that the type is referred to as an anonymous type.
We have lot of resources around us on this concept, but I still see some confusion and arguments on this concept that made me to write this blog post. I will try to explain it in simple words so that a novice can understand the basics of the concept.
VB.NET

view sourceprint?

1.Emp = New With {

2.         .EmplD = 123,

3.         .FName = "Hima",

4.         .LName = “Vejella” ,

5.         .Country = "India"

6.            }

C#.NET

view sourceprint?

1.var Emp = new {

2.        FirstName = "Hima",

3.        LastName = "Vejella",

4.        DOJ = DateTime.Now,

5.        EMPCode = 150

6.          };

The above code snippet is an anonymous declaration in VB. We can notice that the as key word is not required to declare it.
Visual Studio Editor is user-friendly to give us the IntelliSense as below. Once you declared the type then you can access all the properties.

When the above code reaches the complier these are the steps that are done internally.

1. Compiler automatically generates anonymous class.
2. It anonymously instantiate an object that contains the properties in anonymous type
3. The instantiated object is assigned to the anonymous type variable "Emp"

The anonymous type inherits from Object, it holds the properties specified in declaring the object.

Extension-methods

http://weblogs.asp.net/scottgu/archive/2007/03/13/new-orcas-language-feature-extension-methods.aspx

What are Extension Methods?

Extension methods allow developers to add new methods to the public contract of an existing CLR type, without having to sub-class it or recompile the original type.  Extension Methods help blend the flexibility of "duck typing" support popular within dynamic languages today with the performance and compile-time validation of strongly-typed languages.
Extension Methods enable a variety of useful scenarios, and help make possible the really powerful LINQ query framework that is being introduced with .NET as part of the "Orcas" release.

Simple Extension Method Example:

Ever wanted to check to see whether a string variable is a valid email address?  Today you'd probably implement this by calling a separate class (probably with a static method) to check to see whether the string is valid.  For example, something like:

string email = Request.QueryString["email"];

if ( EmailValidator.IsValid(email) ) {
   
}

Using the new "extension method" language feature in C# and VB, I can instead add a useful "IsValidEmailAddress()" method onto the string class itself, which returns whether the string instance is a valid string or not.  I can then re-write my code to be cleaner and more descriptive like so:

string email = Request.QueryString["email"];

if ( email.IsValidEmailAddress() ) {
   
}

How did we add this new IsValidEmailAddress() method to the existing string type?  We did it by defining a static class with a static method containing our "IsValidEmailAddress" extension method like below:

public static class ScottGuExtensions
{
    public static bool IsValidEmailAddress(this string s)
    {
        Regex regex = new Regex(@"^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$");
        return regex.IsMatch(s);
    }
}

Note how the static method above has a "this" keyword before the first parameter argument of type string.  This tells the compiler that this particular Extension Method should be added to objects of type "string".  Within the IsValidEmailAddress() method implementation I can then access all of the public properties/methods/events of the actual string instance that the method is being called on, and return true/false depending on whether it is a valid email or not.
To add this specific Extension Method implementation to string instances within my code, I simply use a standard "using" statement to import the namespace containing the extension method implementation:

using ScottGuExtensions;

The compiler will then correctly resolve the IsValidEmailAddress() method on any string.  C# and VB in the public "Orcas" March CTP now provide full intellisense support for extension methods within the Visual Studio code-editor.  So when I hit the "." keyword on a string variable, my extension methods will now show up in the intellisense drop-downlist:

The VB and C# compilers also naturally give you compile-time checking of all Extension Method usage - meaning you'll get a compile-time error if you mis-type or mis-use one.
[Credit: Thanks to David Hayden for first coming up with the IsValidEmailAddress scenario I used above in a prior blog post of his from last year.]

Extension Methods Scenarios Continued...

Leveraging the new extension method feature to add methods to individual types opens up a number of useful extensibility scenarios for developers.  What makes Extension Methods really powerful, though, is their ability to be applied not just to individual types - but also to any parent base class or interface within the .NET Framework.  This enables developers to build a variety of rich, composable, framework extensions that can be used across the .NET Framework.
For example, consider a scenario where I want an easy, descriptive, way to check whether an object is already included within a collection or array of objects.  I could define a simple .In(collection) extension method that I want to add to all objects within .NET to enable this.  I could implement this "In()" extension method within C# like so:

Note above how I've declared the first parameter to the extension method to be "this object o".  This indicates that this extension method should applied to all types that derive from the base System.Object base type - which means I can now use it on every object in .NET. 
The "In" method implementation above allows me to check to see whether a specific object is included within an IEnumerable sequence passed as an argument to the method.  Because all .NET collections and arrays implement the IEnumerable interface, I now have a useful and descriptive method for checking whether any .NET object belongs to any .NET collection or array.
I could use then use this "In()" extension method to see whether a particular string is within an array of strings:

I could use it to check to see whether a particular ASP.NET control is within a container control collection:

I could even use it with scalar datatypes like integers:

Note above how you can even use extension methods on base datatype values (like the integer value 42).  Because the CLR supports automatic boxing/unboxing of value-classes, extensions methods can be applied on numeric and other scalar datatypes directly. 
As you can probably begin to see from the samples above, extension methods enable some really rich and descriptive extensibility scenarios.  When applied against common base classes and interfaces across .NET, they enable some really nice domain specific framework and composition scenarios. 

Built-in System.Linq Extension Methods

One of the built-in extension method libraries that we are shipping within .NET in the "Orcas" timeframe are a set of very powerful query extension method implementations that enable developers to easily query data.  These extension method implementations live under the new "System.Linq" namespace, and define standard query operator extension methods that can be used by any .NET developer to easily query XML, Relational Databases, .NET objects that implement IEnumerable, and/or any other type of data structure. 
A few of the advantages of using the extension method extensibility model for this query support include:
1) It enables a common query programming model and syntax that can be used across all types of data (databases, XML files, in-memory objects, web-services, etc).
2) It is composable and allows developers to easily add new methods/operators into the query syntax. For example: we could use our custom "In()" method together with the standard "Where()" method defined by LINQ as part of a single query.  Our custom In() method will look just as natural as the "standard" methods supplied under the System.Linq namespace. 
3) It is extensible and allows any type of data provider to be used with it.  For example: an existing ORM engine like NHibernate or LLBLGen could implement the LINQ standard query operators to enable LINQ queries against their existing ORM implementation and mapping engines.  This will enable developers to learn a common way to query data, and then apply the same skills against a wide variety of rich data store implementations.
I'll be walking through LINQ much more over the next few weeks, but wanted to leave you with a few samples that show how to use a few of the built-in LINQ query extension methods with different types of data:

Scenario 1: Using LINQ Extension Methods Against In-Memory .NET Objects

Assume we have defined a class to represent a "Person" like so:

I could then use the new object Initializer and collection Initializer features to create and populate a collection of "people" like so:

I could then use the standard "Where()" extension method provided by System.Linq to retrieve a sequence of those "Person" objects within this collection whose FirstName starts with the letter "S" like so:

The new p => syntax above is an example of a "Lambda expression", which is a more concise evolution of C# 2.0's anonymous method support, and enables us to easily express a query filter with an argument (in this case we are indicating that we only want to return a sequence of those Person objects where the firstname property starts with the letter "S").  The above query will then return 2 objects as part of the sequence (for Scott and Susanne).
 I could also write code that takes advantage of the new "Average" and "Max" extension methods provided by System.Linq to determine the average age of the people in my collection, as well as the age of the oldest person like so:

Scenario 2: Using LINQ Extension Methods Against an XML File

It is probably rare that you manually create a collection of hard-coded data in-memory.  More likely you'll retrieve the data either from an XML file, a database, or a web-service.
Let's assume we have an XML file on disk that contains the data below:

I could obviously use the existing System.Xml APIs today to either load this XML file into a DOM and access it, or use a low-level XmlReader API to manually parse it myself.  Alternatively, with "Orcas" I can now use the System.Xml.Linq implementation that supports the standard LINQ extension methods (aka "XLINQ") to more elegantly parse and process the XML. 
The below code-sample shows how to use LINQ to retrieve all of the <person> XML Elements that have a <person> sub-node whose inner value starts with the letter "S":

Note that it uses the exact same Where() extension method as with the in-memory object sample.  Right now it is returning a sequence of "XElement" elements, which is an un-typed XML node element.  I could alternatively re-write the query to "shape" the data that is returned instead by using LINQ's Select() extension method and provide a Lambda expression that uses the new object initializer syntax to populate the same "Person" class that we used with our first in-memory collection example:

The above code does all the work necessary to open, parse and filter the XML in the "test.xml" file, and return back a strongly-typed sequence of Person objects.  No mapping or persistence file is necessary to map the values - instead I am expressing the shaping from XML->objects directly within the LINQ query above.
I could also use the same Average() and Max() LINQ extension methods as before to calculate the average age of <person> elements within the XML file, as well as the maximum age like so:

I do not have to manually parse the XML file.  Not only will XLINQ handle that for me, but it will parse the file using a low-level XMLReader and not have to create a DOM in order to evaluate the LINQ expression.  This means that it is lightening fast and doesn't allocate much memory.

Scenario 3: Using LINQ Extension Methods Against a Database

Let's assume we have a SQL database that contains a table called "People" that has the following database schema:

I could use the new LINQ to SQL WYSIWYG ORM designer within Visual Studio to quickly create a "Person" class that maps to the database:

I can then use the same LINQ Where() extension method I used previously with objects and XML to retrieve a sequence of strongly-typed "Person" objects from the database whose first name starts with the letter "S":

Note how the query syntax is the same as with objects and XML. 
I could then use the same LINQ Average() and Max() extension methods as before to retrieve the average and maximum age values from the database like so:

You don't need to write any SQL code yourself to have the above code snippets work.  The LINQ to SQL object relational mapper provided with "Orcas" will handle retrieving, tracking and updating objects that map to your database schema and/or SPROCs.  You can simply use any LINQ extension method to filter and shape the results, and LINQ to SQL will execute the SQL code necessary to retrieve the data (note: the Average and Max extension methods above obviously don't return all the rows from the table - they instead use TSQL aggregate functions to compute the values in the database and just return a scalar result).
Please watch this video I did in January to see how LINQ to SQL dramatically improves data productivity in "Orcas".  In the video you can also see the new LINQ to SQL WYSIWYG ORM designer in action, as well as see full intellisense provided in the code-editor when writing LINQ code against the data model.



Extension methods enable you to "add" methods to existing types without creating a new derived type, recompiling, or otherwise modifying the original type. Extension methods are a special kind of static method, but they are called as if they were instance methods on the extended type. 

Extension methods are defined as static methods but are called by using instance method syntax. Their first parameter specifies which type the method operates on, and the parameter is preceded by the this modifier. Extension methods are only in scope when you explicitly import the namespace into your source code with a using directive.

You can use extension methods to extend a class or interface, but not to override them. An extension method with the same name and signature as an interface or class method will never be called. At compile time, extension methods always have lower priority than instance methods defined in the type itself. In other words, if a type has a method named Process(int i), and you have an extension method with the same signature, the compiler will always bind to the instance method. When the compiler encounters a method invocation, it first looks for a match in the type's instance methods. If no match is found, it will search for any extension methods that are defined for the type, and bind to the first extension method that it finds. The following example demonstrates how the compiler determines which extension method or instance method to bind to.