Republished with Permission - Original Article
I've blogged a few times about LINQ and DLINQ over the last
few weeks (and will be blogging a lot more about them in the future). You can
read my overview post on LINQ here,
and my introduction to DLINQ with ASP.NET here.
Sahil Malik has also been working on a great LINQ/DLINQ
tutorial series on his blog here (he
is also the author of the very popular Pro
ADO.NET 2.0 book). Recently he has posted several good posts about how to
call SPROCs and UDFs using LINQ and DLINQ. You can read them here:
How
to use SQLMetal to create DLINQ classes that call SPROCs
Calling
SPROCs from DLINQ that return Scalar Values
Calling
SPROCs from DLINQ that return a definite shape
Calling
SPROCs from DLINQ that return variable shapes
To quickly summarize at a high-level, to enable SPROCs with
DLINQ you should pass the "/sprocs" flag to SQLMetal when you create
your DAL classes:
"c:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:. /database:Northwind /pluralize /sprocs /code:Northwind.cs
Then assuming you have a SPROC defined like so in the
Northwind database like so:
CREATE PROCEDURE GetCustomersInCity
@City NVARCHAR(30)
AS
SELECT
CustomerID, ContactName, Address
FROM
Customers
WHERE City = @City
GO
You can invoke the SPROC and get back and print out a list
of name results using the below LINQ/DLINQ code:
Northwind db = new Northwind(connectionString);
var contactNames = from customer in db.GetCustomersInCity("London")
select customer.ContactName;
foreach (string contactName in contactNames) {
Response.Write("Contact: " + contactName + "<br>") ;
}
You could also obviously databind the list of customers to
an ASP.NET control like so as well (the below result will output a GridView
with 3 columns):
Northwind db = new Northwind(connectionString);
GridView1.DataSource = from customer in db.GetCustomersInCity("London")
select customer;
GridView1.DataBind();
Subscribe to Sahil's
blog to learn more. And if you haven't yet, make sure to read my LINQ
and DLINQ
overview posts for ASP.NET.
Hope this helps,
- Scott