DLinq: ADO.NET vNext a lap around

DLinq: ADO.NET vNext a lap around

  • Comments 1

 

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

 

 

 

Leave a Comment
  • Please add 1 and 2 and type the answer here:
  • Post
  • LINQ to SQL (aka DLinq) generates SQL queries for us at the back. SqlMetal.exe does not contain any SQL

Page 1 of 1 (1 items)