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