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