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

 

13 Comments

  • Whoaa pleasure to be linked by Scott Guthrie :). And it was an utmost pleasure to meet you @ TechED too.

  • 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,

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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

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

  • 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

  • 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?

  • 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

Comments have been disabled for this content.