DLinq: ADO.NET vNext a lap around
Today I would like to discuss on the DLinq the natural roadmap to ADO.NET vNext. Little walkthrough, I am going to explore here which I enjoyed a lot during my hands on.
Create LINQ Console application from your Visual Studio 2005.
Go to LINQ installation folder e.g., C:\Program Files\LINQ Preview\Bin. There you will find one exe with the name SqlMetal. Type /? to get the help information on all possible options. I am assuming that you have SQL Server 2000 Northwind database installed in your application.
Open Visual Studio 2005 Command prompt and type
/server:myServer\myDBInstance /database:Northwind /code:MyNorthwind.cs /language:csharp /namespace:MyProject /pluralize
This will create the MyNorthwind.cs code file in the folder from where executing. Next step would be to add that file to your LINQ Console application. Now your business layer is ready within even less that few seconds. Amazing!!!! “Thanks LINQ, thank you very much”, so you start watching your best TV show or read books go out because the SqlMetal has written 1200 lines of code for you. How much more luxury you want from life? It is beyond expectations.
Now inside your code under static void Main(string[] args)
Wish 0:
Let us have a little background on ObjectDumper.dll which comes with LINQ Project installation. This returns all IEnumerable<T> in fairly easy way. You do not need a foreach statement to iterate through the collection to print the output. And if you use Anonymous type, displaying the output is as easy as Printf in C days. This dll is being created some awesome power of Reflection. The installation folder contains the sample which has got the source code for this static class. Moreover you can easily decide the depth you iterate through. My demonstration will tell you how.
Wish 0+1:
The common task for all wish list is that you have to tell your business logic which database this will connect.
Northwind n = new Northwind
(@"Data Source=myServer\SQL2000;Initial Catalog=Northwind;Integrated Security=True");
//Get the customers
Table<Customer> customers = n.GetTable<Customer>();
Wish 1:
As you know SQL Server 2000 Northwind database has a table named Customer. Get all the customer first
var t = from c in customers
select c.CompanyName;
foreach(var t1 in t)
{
Console.WriteLine(t1);
}
This will pullout all the Company name field from customers table.
Wish 2:
Now you want to implement some filter there.
var t = from c in customers
where c.City == "London"
select c.CompanyName;
Wish 3:
Get two fields from there. Create a class and the property of each field, then initialize that class. Be little smart and use Anonymous types. Where CRL will create the class at runtime and will return it as typed collection (generics). This ensures the typed safety and the simplicity in terms of coding. If you have to declare class and related properties every time you want to get different set of results is not a very feasible solution. Friends we are here.
//Anonymous
var t = from c in customers
where c.City == "London"
select new{c.CompanyName, c.ContactName};
//using foreach
foreach(var t1 in t)
{
//Output: {CompanyName=Around the Horn, ContactName=Thomas Hardy}
Console.WriteLine(t1);
//Output: Around the Horn : Thomas Hardy
Console.WriteLine(t1.CompanyName + " : " + t1.ContactName);
}
//using ObjectDumper
//Output: CompanyName=Around the Horn ContactName=Thomas Hardy
ObjectDumper.Write(t);
Wish 4:
Let us join between customer and their orders.
//Join between Cutomers and Orders
var t = from c in customers
from o in c.Orders
where c.City == "London"
select new {c.CompanyName, o.OrderDate};
ObjectDumper.Write(t);
//Output:
/*
CompanyName=Around the Horn OrderDate=11/15/1996
CompanyName=Around the Horn OrderDate=11/15/1996
CompanyName=Around the Horn OrderDate=12/16/1996
CompanyName=Around the Horn OrderDate=2/21/1997
CompanyName=Around the Horn OrderDate=6/4/1997
*/
Wish 5:
Now apply filer in orders too.
var t = from c in customers
where c.City == "London"
select new {
c.CompanyName,
Orders =
from o in c.Orders
where o.ShipCity == "London"
select new {o.OrderDate, o.ShipCity}
};
ObjectDumper.Write(t);
//Output:
//CompanyName=Around the Horn Orders=...
//CompanyName=B's Beverages Orders=...
Since this has one level deep iteration for orders. We can now use the power of ObjectDumper to easily get the results will small modification.
ObjectDumper.Write(t,1);
//Output:
//CompanyName=Around the Horn Orders=...
//CompanyName=B's Beverages Orders=...
// Orders: OrderDate=8/26/1996 ShipCity=London
// Orders: OrderDate=3/11/1997 ShipCity=London
// Orders: OrderDate=3/24/1997 ShipCity=London
// Orders: OrderDate=5/15/1997 ShipCity=London
// Orders: OrderDate=5/16/1997 ShipCity=London
// Orders: OrderDate=6/24/1997 ShipCity=London
// Orders: OrderDate=7/15/1997 ShipCity=London
// Orders: OrderDate=3/11/1998 ShipCity=London
// Orders: OrderDate=3/13/1998 ShipCity=London
// Orders: OrderDate=4/14/1998 ShipCity=London
Wish 6:
Now last but not the least let us involve OrderDetails table to get the price of each order.
var t = from c in customers
where c.City == "London"
select new {
c.CompanyName,
Orders =
from o in c.Orders
from od in o.OrderDetails
select new {o.OrderDate,
TotalPrice =
(((float)od.Quantity * (float)od.UnitPrice)
- (float)od.Discount)}
};
ObjectDumper.Write(t,1);
/*Output:
CompanyName=Around the Horn Orders=...
Orders: OrderDate=11/15/1996 TotalPrice=90
Orders: OrderDate=11/15/1996 TotalPrice=390
Orders: OrderDate=12/16/1996 TotalPrice=96
Orders: OrderDate=12/16/1996 TotalPrice=195
Orders: OrderDate=12/16/1996 TotalPrice=608
Orders: OrderDate=2/21/1997 TotalPrice=152.9
Orders: OrderDate=2/21/1997 TotalPrice=299.9
Orders: OrderDate=6/4/1997 TotalPrice=237.5
Orders: OrderDate=6/4/1997 TotalPrice=1060
Orders: OrderDate=6/4/1997 TotalPrice=210
Orders: OrderDate=6/4/1997 TotalPrice=590.4
Orders: OrderDate=6/4/1997 TotalPrice=45
Orders: OrderDate=10/16/1997 TotalPrice=504
Orders: OrderDate=10/16/1997 TotalPrice=780
Orders: OrderDate=10/16/1997 TotalPrice=419.85
Orders: OrderDate=11/14/1997 TotalPrice=284.8
Orders: OrderDate=11/17/1997 TotalPrice=335.95
*/
Ohh!!! It is weekend. I crossed a day (lost another day J). Happy weekend.
Namoskar!!!