LINQ - Retrieving Data Using Stored Procedures

Hi,

This blog post summarizes the overview of LINQ.

Below are the new features been introduced in Orcas

§ Automatic Properties

§ Object Initializers

§ Collection Initializers

§ Extesnion Methods

§ Lambda Expressions - p => expressions

§ Query Syntax

§ Anonymous Types

§ Var Keyword

These language features help in making query against relational data. This overall querying programming model is called as "LINQ" - which stands for .NET Language Integrated Query.

Using Stored Procedures

Best would be to read it from the given reference.

However, few importants, has mentoioned here.

Return Type:

When a SP is drag-dropped on LINQ to SQL Designer and saved below happens.

§ By default the method name is created on the DataContext class with the same name as the SPROC name.

§ The return type of the method will be an automatically created type that follows the "[SprocName]Result" naming pattern.

§ For example: the SPROC ‘Ten_Most_Expensive_Products’ would return a sequence of " Ten_Most_Expensive_ProductsResult" objects. We could optionally change the name of the method by selecting it in the designer and then use the property grid to rename it.

private void QueryProductCallSP()

{

NorthwindDataContext db = new NorthwindDataContext();

Ten_Most_Expensive_ProductsResult products = db.Ten_Most_Expensive_Products();

GridView1.DataSource = products;

GridView1.DataBind();

}

Mapping the Return Type of SPROC Methods to Data Model Classes

§ As above, The LINQ to SQL designer automatically defines a new " Ten_Most_Expensive_ProductsResult " class to represent the result.

§ We could alternatively choose to have the return result of a SPROC map to an existing data model class we have already defined in the LINQ to SQL designer (for example: an existing Product entity class).

§ To do this, Rather than just dropping the SPROC anywhere on the designer, though, instead drop the SPROC on top of the existing "Product" class in our data model designer.

Advanatge:

§ This gesture of dropping the SPROC onto the Product class tells the LINQ to SQL designer to have the "‘Ten_Most_Expensive_Products’" method return a sequence of "Product" objects as a return result rather than Ten_Most_Expensive_ProductsResult.

§ One of the cool things about having our SPROC return "Product" objects like above is that LINQ to SQL will automatically track the changes made to the returned Product objects just like it would Product objects returned via LINQ queries. When we call the "SubmitChanges()" method on our DataContext, the changes we have made to these objects will automatically be saved back to the database.

private void QueryProductCallSP()

{

NorthwindDataContext db = new NorthwindDataContext();

List<Product> products = db.Ten_Most_Expensive_Products().ToList();

GridView1.DataSource = products;

GridView1.DataBind();

}

private void QueryProductCallSP()

{

NorthwindDataContext db = new NorthwindDataContext();

List<Product> products = db.Ten_Most_Expensive_Products().ToList();

Foreach(Product prdct in products)

{

Prdct.ProductName = “XYZ”;

}

}

Handling SPROC Output Parameters

§ LINQ to SQL maps "out" parameters in SPROCs as reference parameters (ref keyword), and for value types declares the parameter as nullable.

private void QueryProductCallSP()

{

NorthwindDataContext db = new NorthwindDataContext();

List<Product> products = db.SPName(ref productName).ToList();

}

Handling Multiple Result Shapes from SPROCs

Please Follow Reference link.

Supporting User Defined Functions (UDFs)

§ In addition to SPROCS, LINQ to SQL also supports both scalar-valued and table-valued user defined functions (UDFs), as well as the in-line counterpart to both.

§ Once added to your DataContext as a method, you can use these UDF functions within your LINQ queries.

Please Follow Reference link.

Reference: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

Hope this helps.

Thanks & Regards,

Arun Manglick || Tech Lead