by
Granville Barnett | 15 September 2007
Language Integrated Query (LINQ) comes as part of the
future revisions of both the C# and VB.NET compilers,
introducing a standard set of operators that can be used to
query several different data stores such as SQL Server and
XML.
In the past developers have had to spend many hours creating
trivial data access layers like that in the listing below so
that applications can interact more fluently with their
chosen data store:
- //...
- public
List<Book>
GetBooks()
- {
- List<Book>
bookList
=
new List<Book>();
- using
(SqlConnection
sqlConn
=
- new
SqlConnection(_conn))
- using
(SqlCommand
cmd
=
new SqlCommand("SelectBooks",
sqlConn))
- {
- cmd.CommandType
=
- CommandType.StoredProcedure;
- sqlConn.Open();
- SqlDataReader
sqlRdr
=
cmd.ExecuteReader();
- while
(sqlRdr.Read())
- {
- bookList.Add(new
Book((int)sqlRdr.GetInt32(0),
-
(string)sqlRdr.GetString(1),
-
(int)sqlRdr.GetInt32(2),
-
(string)sqlRdr.GetString(3)));
- }
- }
- return
bookList;
- }
- // ...
Typically these data access layers would use SQL queries
and map their results to a collection of custom types. This
was fun the first time we did it many years ago, but quite
frankly we are now starting to question our own sanity –
mention a data access layer (DAL) again, and we might just
scream!
In this article we will discuss how LINQ for SQL can help
us create more intuitive, flexible DAL’s with a lot less
code. Unfortunately we cannot just dive in without first
setting the scene.
By the time you have read this article you will be
familiar with key concepts introduced in LINQ and LINQ to
SQL. With this new-found knowledge, you will be able to
construct more powerful DAL’s for your applications to
interact with.
Many developers have been intimidated by complex Object
Relation Mapping (ORM) software due to the complexities of
replicating a conceptual model of their database schema. One
of the major advantages of using LINQ to SQL is that the
complexity is taken away from the developer; new types have
been included in ADO.NET Next (the next version of ADO.NET),
which easily allow you to express relationships between
entities. Developers will benefit from being able to step
through queries while debugging, and get intellisense when
constructing queries, resulting in a more comfortable and
robust programming environment.
Furthermore, the public May CTP of LINQ includes a number
of tools for us to use which automate the process of
constructing our conceptual entity view of our database
schema.
For more information on mapping to relational databases
using objects refer to Patterns of Enterprise Application
Architecture by Martin Fowler.
Before we apply LINQ queries to entities we must first
familiarize ourselves with exactly what LINQ is. In this
section we will look at code examples of LINQ in action as
well as provide a core understanding of the new features in
C# 3.0 which LINQ uses extensively.
In the following diagram we can see that there are several
components to LINQ, the middle tier of technologies which
include LINQ, LINQ for XML, and LINQ for SQL can be utilized
by both C# and VB.NET and potentially other languages.
[ The structure of LINQ ]
Each middle tier component provides us with the required
functionality to interact with that component’s data store.
Getting up and running with LINQ is simple – it requires a
one-off
download of the LINQ May CTP. The CTP includes:
- C# 3.0/VB.NET 9.0 compiler (includes LINQ, LINQ for
SQL and LINQ for XML namespaces)
- Language reference update for Visual Studio 2005
- Project Templates for Visual Studio (includes
Console Application, Win Forms, WPF, and Class Library)
- Two tools for automating the creation of entities
(LINQ for Relational Data (DLINQ) recently renamed to
LINQ for SQL), these include the DLINQ objects item
(visual designer for Visual Studio 2005) and SqlMetal
which is a command line utility.
- Sample applications
The May CTP of LINQ is a required installation for
running any code in this article - especially using Visual
Studio 2005. If you have the patience, you can also download
and install the
Visual Studio 2008 Beta edition, but some of the steps
will be different if you try to follow the instructions
found in this article.
In the next page let's get started by creating a simple
LINQ application.
Onwards to the
next page.
|