DLinq at MVP Summit


Just over a week ago, we had a Microsoft MVP summit here in Redmond. It was great to meet with such a smart and energized group of people who really get what we do and even better, tell us when we don’t get it! Their dedication was really admirable – imagine showing up on Saturday morning at 8:30 am after a Friday night party.


We had a series of breakout sessions – each repeated four times to ensure small group discussion. It was a bit exhausting but very rewarding. Here is one thing I had promised to MVPs – now available for all the LINQ users out there. Final version of my demo code. This is an adpatation of my PDC demo – with stored procedure (sproc) support shown. The sproc part is an alternative to dynamically generated SQL. So comment it out if you don’t care about it (and uncomment the non-sproc query).


Please note that the code below was built from scratch with the intention of fitting most of it on one screen for demo and explaining every line in about ten minutes. In your app, you should add more code where appropriate (e.g. use of properties, exception handling etc.) and follow the best practices.


using System;


using System.Collections.Generic;


using System.Query;


using System.Data.DLinq;


 


[Table(Name = “Products”)]


class Product


{


    [Column(Id=true)]


    public int ProductID;


    [Column]


    public short UnitsInStock;


    [Column]


    public int SupplierID;


    [Column]


    public string ProductName;


}


 


class Northwind : DataContext


{


    public Table<Product> Products;


 


    public Northwind(string s) : base(s) { }


 


    // Delete the following if you want DLinq to generate update command


    [UpdateMethod]


    public void OnProductUpdate(Product original, Product current)


    {


        // Execute the stored procedure for UnitsInStock update


        if (original.UnitsInStock != current.UnitsInStock)


        {


            int rowCount = this.ExecuteCommand(


                “exec UpdateProductStock “ +


                “@id={0}, @originalUnits={1}, @decrement={2}”,


                original.ProductID,


                original.UnitsInStock,


                (original.UnitsInStock – current.UnitsInStock)


            );


        }


    }       


   


}


 


class Driver


{


    static void Main(string[] args)


    {


        Northwind db = new Northwind(


            @”Server=.\SQLExpress;Database=c:\Northwind\Northwnd.mdf”);


       


        db.Log = Console.Out;


        


        // Use the following commented code or the sproc code below      


/*


        var query = from p in db.Products


                    where p.SupplierID == 3


                    orderby p.UnitsInStock


                    select p;


 


        Product sauce = query.First();


*/


 


        IEnumerable<Product> query = db.ExecuteQuery<Product>(


            “exec ProductsBySupplier 3”);


 


        Product sauce = query.Where(p => p.ProductID == 8).First();          


 


 


        Console.WriteLine(“\nID={0}\tStock={1}\tName={2}\n”,


                    sauce.ProductID, sauce.UnitsInStock, sauce.ProductName);


 


        sauce.UnitsInStock += 10;


 


        db.SubmitChanges();


 


        Console.WriteLine(“——–\n”);


        Console.WriteLine(“Post-SubmitChanges() results\n”);


 


        foreach (var p in query)


            Console.WriteLine(“\nID={0}\tStock={1}\tName={2}\n”,


                    p.ProductID, p.UnitsInStock, p.ProductName);


 


           


    }


}


The sproc code for addition to Northwind is given below:


create proc ProductsBySupplier


       @supplier     int


as


       select ProductID, UnitsInStock, SupplierID, ProductName


       from Products


       where SupplierID = @supplier


Go


 


create proc UpdateProductStock


       @id           int,


       @originalUnits       int,


       @decrement    int


as


       if (@originalUnits >= @decrement)


              Update Products


              Set UnitsInStock = UnitsInStock – @decrement


              Where ProductId = @id


              and   UnitsInStock = @originalUnits


Go  


Comments (3)

  1. Bill says:

    Thanks Dinesh – I appreciate it!

  2. mabster says:

    Dinesh,

    This looks great, but I am wondering how deletions to the data work.

    Would I have to build the "delete" logic myself? Like, create a "deleted" bool member variable in the Product class, or something?

    Cheers,

    Matt

  3. Alexander Christov says:

    Simple! Looks really nice.

    How do you handle master-details relationship with sprocs? I mean EntitySet and EntityRef but without dynamic SQL.

    If I missed something form the CP documentation, please point it out.

    Thanks in advance.