by October 9, 2006 0 comments



One of the mainstays of being a Web developer-no matter the platform-is
being able to connect to a database and store and retrieve data. And since all
databases use the SQL in some form or another, these skills are easily
transferable between different projects. Vendors of development tools also try
to leverage this by adding functionality that reduce the amount of SQL you need
to write. For instance, in ASP.NET 2.0 you can do
almost all database related tasks without needing to write any SQL by hand.

However, using queries-either directly or from stored procedures- have
their own advantages. You can fine grain the query structure or compile them
within the database engine for added performance. The problem with this is that
SQL is a completely non-object oriented language and in these days of OOP
paradigms, it sits completely out of place.

Direct
Hit!
Applies
to:
Web developers
USP:
Map DB tables to objects that writes your SQL queries
Links:
http://msdn.microsoft.com/data/ref/linq 
Google
keywords:
DLINQ,LINQ

This is where a new data query technology from Microsoft comes in. The .NET
Language Integrated Query or LINQ is a set of new object oriented query
operators that work with any enumerable data. Enumerable data, in this context,
means anything that can be represented as rows and columns of data. Some
examoples of enumerable data are XML data, RDBMS Tables, single and double
dimensional arrays and generics based objects. LINQ can work with all of these
very easily using a common data query mechanism.

LINQ takes care of the correct query type of the system being queried-for
instance when querying a database, SQL will automatically be generated. This
means using the same query syntax, you can query XML, database tables, etc. LINQ
will use XQuery/XPath or SQL
respectively for doing the actual query.

XLinq and DLinq are subsets of the LINQ framework that allow developers to
use specific mappings to XML or databases that use the LINQ syntax for queries.
This means that if you are using, say DLinq, for querying some data from a
database, instead of creating the mappings of LINQ objects to the database
tables yourself, you can do this in a graphical method by mapping tables to
objects. DLinq with ASP.NET 2.0 gives rise to many opportunities. But before we
look at using DLinq and ASP.NET, let us first create a simple application that
works with LINQ to query data.

Imports System
Imports System.Collections.Generic
Imports System.Query ‘ The LINQ entry point

Module Module1

‘ A class for author structure
Class Author
Public FirstName As String
Public LastName As String
Public Email As String
Public External As Boolean
End Class

Sub Main()
‘ A list of Authors
Dim PCQAuthors As List(Of Author) = New List(Of Author)

‘ Let’s create some authors for testing
Dim a As Author = New Author
a.FirstName = "Vinod"
a.LastName = "Unny"
a.Email = "v@u.com"
a.External = True
PCQAuthors.Add(a)
.
.
‘ Here comes the LINQ query
Dim extAuthors As IEnumerable(Of Author) = _
From au In PCQAuthors _
Where au.External = True _
Select au

‘ Show the list on the console
For Each ab As Author In extAuthors
Console.WriteLine(ab.FirstName)
Next
End Sub
End Module

This is a small and simple console application that does a couple of things.
It creates a small Authors class and in the code creates a generics list of
PCQuest authors. The main LINQ line of code is:

Dim extAuthors As IEnumerable(Of Author) = _
From au In PCQAuthors _
Where au.External = True _
Select au

Creating a DLINQ class library is as simple as dropping tables onto the designer and changing the names of the properties as you want

The LINQ query allows you to use the generics list as a queriable object and
filter out only external authors-just like you would in SQL-albeit with full
object orientation and IntelliSense built in. In fact, while writing this code,
we almost never needed to type anything other than the actual value of ‘True’.

Simply pressing Tab for the IntelliSense to use the default was enough! This
same kind of query can be used for any enumerable object-whether it be XML,
database or arrays. So your source of data can change (not just the database
type) but your query doesn’t when using LINQ.

Let’s now move on to using DLINQ-where we map a database table
graphically to classes. First we’ll need to create the LINQ class library that
maps to our database. Create a new project in VS2005 and select ‘LINQ Class
Library’ as the project type. In this project open the .LINQ file that is
created and using server explorer drop the tables that you wish to create
classes from.

As you drop the tables, classes get generated automatically for all of them.
Relationships between tables are also read from the database and
generated. Since these are classes, you can rename the properties in them to
whatever you want but the link to the database remains. Now add a new ASP.NET
Linq website project and add reference to the class library created above. Add a
new ASPX page to the project and drop a GridView control on the page. Double
click on the page to write the following code in the Page_Load event.

Dim pubs As DataClass.pubsDataContext = New
DataClass.pubsDataContext()
GridView1.DataSource = _
From p In pubs.BookAuthors _
Select p.Author
GridView1.DataBind()

Taking a look at this code reveals a number of nice things. The standard
ASP.NET control works with DLINQ queries directly.

Also the query is interesting in that the tables are represented as contained
objects. For instance in the query above, the object ‘p’ contains the table
objects with the relationships also defined. Which is why it is possible to ‘select
p.Author’. When the code is executed, the properties of the author that are
defined in the DLINQ class will bind to the GridView. Let’s now modify the
query above a little like this:

GridView1.DataSource = _
From p In pubs.BookAuthors _
Where p.Author.LastName = "Green" _
Select p.book

As you can see, the object can reveal the properties within the object as
well-in this case I get a list of books written by the author who has the last
name ‘Green’-in essence performing a join automatically.

There is a lot of more things that can be done in LINQ and will be an
interesting technology to watch and learn in the coming months.

No Comments so far

Jump into a conversation

No Comments Yet!

You can be the one to start a conversation.

Your data will be safe!Your e-mail address will not be published. Also other data will not be shared with third person.