DLINQ with Stored Procedures
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 = @CityGO
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