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:

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.DataSourcefrom 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

 

Published Sunday, June 18, 2006 10:24 PM by ScottGu
Filed under: , , , ,

Comments

# re: DLINQ with Stored Procedures

Monday, June 19, 2006 9:44 AM by Sahil Malik
Whoaa pleasure to be linked by Scott Guthrie :). And it was an utmost pleasure to meet you @ TechED too.

# re: DLINQ with Stored Procedures

Monday, June 19, 2006 7:36 PM by gozh2002
Hi Scott,

Can you please explain why we have to put
a select command in the end of the SQL query,
I am assuming we are doing this because the
concept or programming model is not
in the SQL mindset, then how should we
look at this LINQ stuff and in what perspective?

Regards,



# re: DLINQ with Stored Procedures

Tuesday, June 20, 2006 1:12 AM by Vikram
Hi

I still do not understand the logic of LINQ and the reason we shoulod use it. we can do these stuff without LINQ also.

Please explain

# re: DLINQ with Stored Procedures

Tuesday, June 20, 2006 3:28 AM by ScottGu
Hi Gozh2002,

The select statement in the LINQ query above enables you to control the "shape" of the data that is returned by the query.  In the first case it is returning just the name column, and in the second sproc example it is returning all three columns.

If you look at the LINQ and DLINQ overview articles I listed at the top of the article they will go into the syntax and shaping capabilities more.

Hope this helps,

Scott

# re: DLINQ with Stored Procedures

Tuesday, June 20, 2006 3:31 AM by ScottGu
Hi Vikram,

I'd recommend reading the first two articles I referenced in my blog post above.  They show off some of the power and flexibility of LINQ and DLINQ.  You can really do a lot without having to write more than a few lines of code with them.

Hope this helps,

Scott

# re: DLINQ with Stored Procedures

Tuesday, June 20, 2006 9:13 PM by gozh2002
Hi Scott,

Sorry, what I mean really is that SQL is using sytax like
" Select [column name] From [table name] Where [criteria]" for more than 20 years.

Why LINQ has to be so "smart" use a syntax which is smilar to SQL but put the select in the end of the query?

Will this change lead me to think the way I query data in a different perspective which is not relational query.

# re: DLINQ with Stored Procedures

Thursday, June 22, 2006 1:14 AM by ScottGu
Hi Gozh2002,

LINQ supports much richer data shaping/projection features than standard SQL syntax supports.  You can learn more about these features here: http://weblogs.asp.net/scottgu/archive/2006/06/04/Using-DLINQ-with-ASP.NET-_2800_Part-2-of-my-LINQ-series_2900_.aspx

Hope this helps,

Scott

# re: DLINQ with Stored Procedures

Monday, June 26, 2006 11:47 AM by Alon
Hi Scott

I was hoping to get more information about the performance issue between DLINQ and Standard Stored Procedures, could you add some examples?

Thanks, Alon

# re: DLINQ with Stored Procedures

Monday, June 26, 2006 6:44 PM by ScottGu
Hi Alon,

The performance using SPROCs with DLINQ should be about the same as calling SPROCs directly (they both use the same underlying ADO.NET data stack to execute against the database).

I don't have more data on exact performance yet though (since LINQ/DLINQ still hasn't shipped).

Hope this helps,

Scott

# re: DLINQ with Stored Procedures

Thursday, July 6, 2006 6:05 AM by Kamran Shahid

Isn't this mean's that Table name and Stored procedure name will have to be different?

That is no object[table,stored procedure,Viw] with the same name.

# re: DLINQ with Stored Procedures

Thursday, July 6, 2006 1:58 PM by ScottGu

Hi Kamran,

You can actually map the names however you want to types.  Your middle-tier naming scheme doesn't have to correspond to your DB structure.

Hope this helps,

Scott

# re: DLINQ with Stored Procedures

Friday, July 21, 2006 12:03 AM by christopher simmons
I was wondering, if linq talks to the database directly then don't you have to give the the sql user read/write permissions to the tables?

# Stored Procedures

Saturday, September 2, 2006 5:21 AM by Earnest Fantin
public DataTable GetAdvancedSearchResult(string category, string manufacturer, string desc, string descOpt, string partno, string partnoOpt) { string sql = "SELECT * FROM tbl_Product"; string whereClause = ""; string cat = "", man = "", des = "", par = ""; if (category.Trim().Length > 0) cat = "product_category='" + category + "'"; if (manufacturer.Trim().Length > 0) man = "product_Manufacturer='" + manufacturer + "'"; if (desc.Trim().Length > 0) des = GetDescription(desc, descOpt); if (partno.Trim().Length > 0) par = GetPartno(partno, partnoOpt); if (cat.Length > 0) whereClause = cat; if (man.Length > 0 && whereClause.Length > 0) whereClause += " AND " + man; else whereClause += man; if (des.Length > 0 && whereClause.Length > 0) whereClause += " AND " + des; else whereClause += des; if (par.Length > 0 && whereClause.Length > 0) whereClause += " AND " + par; else whereClause += par; if (whereClause.Length > 0) { sql += " WHERE " + whereClause; } return db.GetDataTable("tbl_Product", sql); //SqlCommand cmd = new SqlCommand("SP_SEARCH_ADVANCE"); //cmd.CommandType = CommandType.StoredProcedure; //DataTable dt = db.GetDataTable(cmd); //return dt; } i want to write this above functions in Stored Procedures.so give me some idea