Contents
.NET 3.0 has now been released, so we should all know it by now shouldn't we. Jeez, doesn't seem like that long ago that .NET 2.0 came along. Well for those that dont realize .NET 3.0 actually contains quite a lot of new stuff, such as:
- Windows Workflow Foundation (WWF) : Managing object lifecycles / persistent object storage
- Windows Communication Foundation (WCF) : The new communication layer
- Windows Presentation Foundation (WPF) : The new presentation layer (XAML)
- Windows Cardspace : Which provides a standards-based solution for working with and managing diverse digital identities
So as you can see there is a lot to be learned right there. I'm in the process of learning WPF/WCF but I am also interested in a little gem called LINQ, that I believe will be part of .NET 3.5 and Visual Studio "Orcas" (as its now known). LINQ will add new features to both C# and VB.NET. LINQ has three flavours
- LINQ : Language Integrated Query for in memory objects (renamed in March 2007 Orcas CTP to LINQ to objects)
- DINQ : Language Integrated Query for databases (renamed in March 2007 Orcas CTP to LINQ to SQL)
- XLINQ : Language Integrated Query for XML (renamed in March 2007 Orcas CTP to LINQ to XML)
LINQ is pretty cool, and I have been looking into it as of late, so I thought I would write an article about what I have learned in the LINQ/DLINQ/XLINQ areas, in the hopes that it may just help some of you good folk. This article will be focussed on SLINQ, and is the second in a series of three proposed articles.
The proposed article series content will be as follows:
- Part1 : will be all about standard LINQ, which is used to query in memory data objects such as List, arrays, etc.
- Part2 (this article) : will be about using DLINQ, which is LINQ for database data
- Part3 : will be about using XLINQ, which is LINQ for XML data
To run the code supplied with this article you will need to install the May 2006 LINQ CTP which is available here, there is a new March 2007 CTP available, but its about 4GB for the full install (as its not just LINQ but the entire next generation of Visual Studio codenamed "Orcas") and quite fiddly, and probably going to change anyway, so the May 2006 LINQ CTP will be OK for the purpose of what this article is trying to demonstrate.
DLINQ itself allows developers to query data stored within a SQL Server database. Only SQL server is supported by DLINQ. In order for DLINQ to do this, certain entities must be created. These entities are placed into a code file, and should match the database schema. These entities are then annotated with some new DLINQ Attributes. These Attributes allows DLINQ identify the entities correctly, and for DLINQ to create the appropriate SQL commands to send to the database. For example there are Table, Column and Association attributes which all reflect the real database objects : Table, Field (or Column if you prefer) and Relationship.
From what I've read around this subject this is fairly similar to a technique called O/R Mapping (Object Relational Mapping)
"Object-Relational mapping (aka O/RM, ORM, and O/R mapping), is a programming technique for converting data between incompatible type systems in databases and Object-oriented programming languages. In effect, this creates a "virtual object database" which can be used from within the programming language. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to create their own ORM tools.
It's this promise that DLINQ is trying to deliver; it's really offering a layer of persistence where changes may be held, awaiting commitment to the underlying database. So you can think of it as an in memory version of the database.
Obviously there needs to be some way that LINQ knows the structure of the database (so that it can generate correct SQL commands that will give the correct results), this is where the Entity code file(s) fit in. By using the new Table, Column and Association DLINQ attributes, we are able to correctly annotate some fairly standard (albeit some new DLINQ classes such as EntitySet) .NET code to resemble the underlying database structure.
So once these entities exist, using DLINQ, one is able to query, delete, update entities or insert new entities. So if DLINQ is asked to delete an entity it will (under the hood) create the appropriate SQL command to delete the requested row from the requested database table.
As far as I know DLINQ and Entity files go hand in hand; you must have entity files to use DLINQ, and entity files are really only used when we are dealing with DLINQ. So I think it's fair to say that where there's DLINQ there's entity code somewhere that allows DLINQ to create the appropriate in-memory structure of the underlying database. Otherwise DLINQ simply will not know how to create the correct SQL commands.
There are probably people reading this, that think that O/R Mapping has been around a while, and some of you may have even used it before. I'm not going to get into that debate, as I personally have not used any O/R Mapping stuff in .NET, so can't possible say. I do know that DLINQ's approach is quite similar to Java's J2EE Enterprise Java Beans (which I've just done a reasonable large project in), except that Java supports most databases, not just SQL Server.
The rest of the article will focus on how to get started with DLINQ, and what steps you must carry out in order to generate entity files, and indeed DLINQ queries.
There are really only a couple of main steps to follow in order to get started with DLINQ, these are:
- Define your entity code files
- Create a DataContext
- Run queries
- Alter values and commit updates
That's really all that we need to do. So in the rest of the article I will be showing you how to do this.
Before we do anything with DLINQ we need to create an entity file.
Entity code files are simple. The object classes you will use to represent your application data.
So what do they look like, what does this all mean?
Well quite simply they mirror the design of the tables within the given database that you want to interact with using DLINQ.
I have been (mis?)fortunate enough to have done some Java J2EE Enterprise Java Bean development recently, so if you to have some Java experience, the best way to think about these Entity code files, is the same as Entity beans in Java.
If, however, you are a pure Microsoft enthusiast (I'm erring this way too don't worry, four years of Java has slowly bent my mind), then don't worry I'll go through what an entity file is right now.
Let's say we have a table in our database (say we're using the usual Northwind sample database that comes with the May 2006 LINQ CTP) and we want to create an entity class for this, we need only apply a custom attribute to the top of the class declaration. DLINQ defines the Table attribute for this purpose.
[Table(Name="Customers")]
public class Customer
{
public string CustomerID;
public string City;
}
The Table attribute has a Name property that you can use to specify the exact name of the database table. If no Name property is supplied DLINQ will assume the database table has the same name as the class. Only instances of classes declared as tables will be able to be stored in the database.
Instances of these types of classes are known as entities, and the classes themselves, entity classes.
In addition to associating classes to tables you will need to denote each field or property you intend to associate with a database column. For this, DLINQ defines the Column attribute.
[Table(Name="Customers")]
public class Customer
{
[Column(Id=true)]
public string CustomerID;
[Column]
public string City;
}
The Column attribute has a variety of properties you can use to customize the exact mapping between your fields and the database's columns. One property of note is the Id property. It tells DLINQ that the database column is part of the table's primary key.
As with the Table attribute, you only need to supply information in the Column attribute if it differs from what can be deduced from your field or property declaration. In this example, you need to tell DLINQ that the CustomerID field is part of the table's primary key yet you don't have to specify the exact name or type.
Only fields and properties declared as columns will be persisted to or retrieved from the database. Others will be considered as transient parts of your application logic.
Other Column Attributes
There are many more Column attributes, such as :
| Property | Type | Description |
| Name | String | The name of the column in the table or view. If not specified the column is assumed to have the same name as the class member |
| Storage | String | The name of the underlying storage. If specified it tells DLINQ how to bypass the public property accessor for the data member and interact with the raw value itself. If not specified DLINQ gets and sets the value using the public accessor. |
| DBType | String | The type of database column specified using database types and modifiers. This will be the exact text used to define the column in a T-SQL table declaration command. If not specified the database column type is inferred from the member type. The specific database type is only necessary if CreateDatabase() method is expected to be used to create an instance of the database. |
| Id | Bool | If set to true the class member represents a column that is part of the table's primary key. If more than one member of the class is designated as the Id, the primary key is said to be a composite of the associated columns. At least one member must have this attribute and must be mapped to the primary key or a unique key in the corresponding table/view. Table/view without unique key are not supported. |
| AutoGen | Boolean | Identifies that the member's column value is auto-generated by the database. Primary keys that are designated AutoGen=true should also have a DBType with the IDENTITY modifier. AutoGen members are synchronized immediately after the data row is inserted and are available after SubmitChanges() completes. |
| IsVersion | Boolean | Identifies the member's column type as a database timestamp or a version number. Version numbers are incremented and timestamp columns are updated every time the associated row is updated. Members with IsVersion=true are synchronized immediately after the data row is updated. The new values are visible after SubmitChanges() completes. |
| UpdateCheck | UpdateCheck | Determines how DLINQ implements optimistic concurrency conflict detection. If no member is designate as IsVersion=true detection is done by comparing original member values with current database state. You can control which members DLINQ uses during conflict detection by giving each member an UpdateCheck enum value.
Always - always use this column for conflict detection
Never - never use this column for conflict detection
WhenChanged – only use this column when the member has been changed by the application |
| IsDiscriminator | Boolean | Determines if the class member holds the discriminator value for an inheritance hierarchy. |
There are also many more custom attributes that may be used within DLINQ such as :
System.Data.DLinq.DatabaseAttribute System.Data.DLinq.TableAttribute (as we have seen above) System.Data.DLinq.AssociationAttribute (Relation in SQL speak) System.Data.DLinq.StoredProcedureAttribute System.Data.DLinq.FunctionAttribute System.Data.DLinq.ParameterAtttribute System.Data.DLinq.InheritenceMappingAttribute
I suggest the best place to look for this is the DLINQ overview document, that comes with the May 2006 LINQ CTP download
DLINQ relies on these attributes in order to correctly define an entire class. So as you can imagine for an entire database with lots of tables, this would be a lot of work. Luckily there are a number of cheat methods at our disposal. Lets look at them shall we.
Well when you install the May 2006 LINQ CTP you will get extra project templates in Visual Studio as well as new items, which you can add to your project. One such new item, is the DLinqObjects item. Which when added will add a new *.dlinq file to your project. This file is a DLINQ designer file.
When you click on the newly added *.dlinq file, you will get a new designer surface, from where you may add the following items to the work surface:
- New Class
- Association
- Inheritence
Where once added to the work surface, you may use tweak the controls on the work surface using the Visual Studio interaction method (click, right click)
The main thing that one would do using the designer is to add a new class (can be thought of as Table in SQL) and then add new properties (can be thought of as fields in Table).
Once you're happy that you have the strucrure you want, build the project, and the code will be auto-generated by Visual Studio and placed in the associated code behind file (.cs in my case) for the current *.dlinq file.
Isn't there a better way?
Well yes there is actually. We have a Server explorer in Visual Studio right? So why not use that to navigate to the correct database, and simply drag the required table on to the DLINQ designer work surface. So let's assume that I've navigated to my own local (yours will be your local) SQL Server (2005 express) installation, and pointed it to my installed Northwnd (the one that comes with the May 2006 LINQ CTP) and I've dragged a table to the DLINQ designer (say the customers table).
And then built the project. Well the code will be auto generated by Visual Studio and placed in the associated code behind file (.cs in my case) for the current *.dlinq file.
As shown below
So thats nice isn't it. However, I did read somewhere that the DLINQ designer is a bit buggy for large database structures. Also, I am not dead keen on designers in general, though you may love them. But I'm going to show you another method. Which is a little command line utility called SQLMetal.exe. So let's look at that now.
Luckily Microsoft did know how slow the designer was, and that it's simply not that great for large databases, so they gave us a command line EXE, which once you have the May 2006 LINQ CTP installed will be located at (provided you accepted default installation instructions that is) C:\Program Files\LINQ Preview\Bin\SqlMetal.exe.
SqlMetal.exe is a nice little utility that we can use to generate a entity wrapper class for a database. There are numerous options, so let's have a look at the options :
Though I imagine the most commonly used will be the "To generate source code from SQL metadata directly" option, which is the option that takes a database and examines it, to create a source code wrapper file. These files are known as entity code files, as mentioned earlier.
So how is this entity code file created?
Well its simply a case of supplying some details to SqlMetal.exe, such as
- /server:
- /database:
- /namespace:
- /code:
- /language:
So the full command line would be composed like:
sqlmetal /server:myserver /database:northwind /namespace:nwind /code:nwind.cs
/language:csharp
A full example for my own SQLExpress 2005 home installation, using the northwnd.mdf file, that the May 2006 LINQ CTP installs, is as follows:
sqlmetal.exe /server:BUDHA01\SQLExpress /database:"c:\program files\LINQ
Preview\data\northwnd.mdf" /namespace:nwind /code:nwind.cs /language:csharp
When this command line is run, the result is that I get a new C# file called nwind.cs in the same directory as the SqlMetal.exe tool, this should be C:\Program Files\LINQ Preview\Bin\ if you accepted the default May 2006 LINQ CTP install instructions.
So I can then copy this nwind.cs entity code file to my current project.
Before we move on shall we just try this again, with a smaller somewhat easier database, and go through the generated entity code file.
First let's look at a good old fashioned SQL Server Entity Relationship Diagram. We like them. We get them.
So let's say we have a setup like the one described above where we have two tables and a simple relationship, then the following SQL would be used to create the entire database.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE
_object_id = OBJECT_ID(N'[dbo].[Publisher]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Publisher](
[publisherId] [int] IDENTITY(1,1) NOT NULL,
[publisherName] [nvarchar](50) NULL,
[publisherEmail] [nvarchar](50) NULL,
[publisherContact] [nvarchar](50) NULL,
CONSTRAINT [PK_Publisher] PRIMARY KEY CLUSTERED
(
[publisherId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE
_object_id = OBJECT_ID(N'[dbo].[Book]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Book](
[bookId] [int] IDENTITY(1,1) NOT NULL,
[bookName] [nvarchar](50) NULL,
[bookAuthor] [nvarchar](50) NULL,
[pulisherId] [int] NULL,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[bookId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE
_object_id = OBJECT_ID(N'[dbo].[FK_Book_Publisher]')
AND parent_object_id = OBJECT_ID(N'[dbo].[Book]'))
ALTER TABLE [dbo].[Book] WITH CHECK ADD
_CONSTRAINT [FK_Book_Publisher] FOREIGN KEY([pulisherId])
REFERENCES [dbo].[Publisher] ([publisherId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Book] CHECK CONSTRAINT [FK_Book_Publisher]
So that's normal SQL syntax. So let's compare this to what we get when we generate an entity code file using SQLMetal.exe, for this simple two table database.
namespace simpDB {
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Reflection;
using System.Query;
using System.Expressions;
using System.Data;
using System.Data.DLinq;
public partial class SimpleDatabase : DataContext {
public Table<Book> Book;
public Table<Publisher> Publisher;
public SimpleDatabase(string connection) :
base(connection) {
}
public SimpleDatabase(System.Data.IDbConnection connection) :
base(connection) {
}
public SimpleDatabase(string connection, System.Data.DLinq.MappingSource
mappingSource) :
base(connection, mappingSource) {
}
public SimpleDatabase(System.Data.IDbConnection connection,
System.Data.DLinq.MappingSource mappingSource) :
base(connection, mappingSource) {
}
}
[Table(Name="Book")]
public partial class Book : System.Data.DLinq.INotifyPropertyChanging,
System.ComponentModel.INotifyPropertyChanged {
private int _BookId;
private string _BookName;
private string _BookAuthor;
private System.Nullable<int> _PulisherId;
private EntityRef<Publisher> _Publisher;
public Book() {
this._Publisher = default(EntityRef<Publisher>);
}
[Column(Name="bookId", Storage="_BookId",
DBType="Int NOT NULL IDENTITY", Id=true, AutoGen=true)]
public int BookId {
get {
return this._BookId;
}
set {
if ((this._BookId != value)) {
this.OnPropertyChanging("BookId");
this._BookId = value;
this.OnPropertyChanged("BookId");
}
}
}
[Column(Name="bookName", Storage="_BookName",
DBType="NVarChar(50)")]
public string BookName {
get {
return this._BookName;
}
set {
if ((this._BookName != value)) {
this.OnPropertyChanging("BookName");
this._BookName = value;
this.OnPropertyChanged("BookName");
}
}
}
[Column(Name="bookAuthor", Storage="_BookAuthor",
DBType="NVarChar(50)")]
public string BookAuthor {
get {
return this._BookAuthor;
}
set {
if ((this._BookAuthor != value)) {
this.OnPropertyChanging("BookAuthor");
this._BookAuthor = value;
this.OnPropertyChanged("BookAuthor");
}
}
}
[Column(Name="pulisherId", Storage="_PulisherId",
DBType="Int")]
public System.Nullable<int> PulisherId {
get {
return this._PulisherId;
}
set {
if ((this._PulisherId != value)) {
this.OnPropertyChanging("PulisherId");
this._PulisherId = value;
this.OnPropertyChanged("PulisherId");
}
}
}
[Association(Name="FK_Book_Publisher",
Storage="_Publisher", ThisKey="PulisherId",
IsParent=true)]
public Publisher Publisher {
get {
return this._Publisher.Entity;
}
set {
Publisher v = this._Publisher.Entity;
if ((v != value)) {
this.OnPropertyChanging("Publisher");
if ((v != null)) {
this._Publisher.Entity = null;
v.Book.Remove(this);
}
this._Publisher.Entity = value;
if ((value != null)) {
value.Book.Add(this);
}
this.OnPropertyChanged("Publisher");
}
}
}
public event System.ComponentModel.PropertyChangedEventHandler
PropertyChanging;
public event System.ComponentModel.PropertyChangedEventHandler
PropertyChanged;
protected virtual void OnPropertyChanging(string PropertyName) {
if ((this.PropertyChanging != null)) {
this.PropertyChanging(this, new
PropertyChangedEventArgs(PropertyName));
}
}
protected virtual void OnPropertyChanged(string PropertyName) {
if ((this.PropertyChanged != null)) {
this.PropertyChanged(this, new
PropertyChangedEventArgs(PropertyName));
}
}
}
[Table(Name="Publisher")]
public partial class Publisher : System.Data.DLinq.INotifyPropertyChanging,
System.ComponentModel.INotifyPropertyChanged {
private int _PublisherId;
private string _PublisherName;
private string _PublisherEmail;
private string _PublisherContact;
private EntitySet<Book> _Book;
public Publisher() {
this._Book = new EntitySet<Book>(new
Notification<Book>(this.attach_Book), new
Notification<Book>(this.detach_Book));
}
[Column(Name="publisherId", Storage="_PublisherId",
DBType="Int NOT NULL IDENTITY", Id=true, AutoGen=true)]
public int PublisherId {
get {
return this._PublisherId;
}
set {
if ((this._PublisherId != value)) {
this.OnPropertyChanging("PublisherId");
this._PublisherId = value;
this.OnPropertyChanged("PublisherId");
}
}
}
[Column(Name="publisherName",
Storage="_PublisherName", DBType="NVarChar(50)")]
public string PublisherName {
get {
return this._PublisherName;
}
set {
if ((this._PublisherName != value)) {
this.OnPropertyChanging("PublisherName");
this._PublisherName = value;
this.OnPropertyChanged("PublisherName");
}
}
}
[Column(Name="publisherEmail",
Storage="_PublisherEmail", DBType="NVarChar(50)")]
public string PublisherEmail {
get {
return this._PublisherEmail;
}
set {
if ((this._PublisherEmail != value)) {
this.OnPropertyChanging("PublisherEmail");
this._PublisherEmail = value;
this.OnPropertyChanged("PublisherEmail");
}
}
}
[Column(Name="publisherContact",
Storage="_PublisherContact", DBType=
"NVarChar(50)")]
public string PublisherContact {
get {
return this._PublisherContact;
}
set {
if ((this._PublisherContact != value)) {
this.OnPropertyChanging("PublisherContact");
this._PublisherContact = value;
this.OnPropertyChanged("PublisherContact");
}
}
}
[Association(Name="FK_Book_Publisher",
Storage="_Book", OtherKey="PulisherId")]
public EntitySet<Book> Book {
get {
return this._Book;
}
set {
this._Book.Assign(value);
}
}
public event System.ComponentModel.PropertyChangedEventHandler
PropertyChanging;
public event System.ComponentModel.PropertyChangedEventHandler
PropertyChanged;
protected virtual void OnPropertyChanging(string PropertyName) {
if ((this.PropertyChanging != null)) {
this.PropertyChanging(this, new
PropertyChangedEventArgs(PropertyName));
}
}
protected virtual void OnPropertyChanged(string PropertyName) {
if ((this.PropertyChanged != null)) {
this.PropertyChanged(this, new
PropertyChangedEventArgs(PropertyName));
}
}
private void attach_Book(Book entity) {
this.OnPropertyChanging(null);
entity.Publisher = this;
this.OnPropertyChanged(null);
}
private void detach_Book(Book entity) {
this.OnPropertyChanging(null);
entity.Publisher = null;
this.OnPropertyChanged(null);
}
}
}
OK it's a lot of code, but it's a lot of code that you do not have to write, or design using the DLINQ designer, and you can be safe in the knowledge that there is no errors in it (yet, well probably do that later, when we manually hack the file. Though I recommend using SqlMetal.exe, as this is it's job)
public Table<Book> Book;
...
[Table(Name="Book")]
public partial class Book : System.Data.DLinq.INotifyPropertyChanging,
System.ComponentModel.INotifyPropertyChanged {
private int _BookId;
[Column(Name="bookId", Storage="_BookId",
DBType="Int NOT NULL IDENTITY", Id=true, AutoGen=true)]
public int BookId {
get {
return this._BookId;
}
set {
if ((this._BookId != value)) {
this.OnPropertyChanging("BookId");
this._BookId = value;
this.OnPropertyChanged("BookId");
}
}
}
...
}
...
So hopefully you can see some common stuff in this C# code, theres a new Table type called Book. Theres also a new private field called _BookId, that matches the data type of the field in the actual SQL Server database table (it was also an int). And for each field (column) in the table there is Property with a get/set. OK there are some new DLINQ attributes they you'll need to learn, and there are some extra events such as:
public event System.ComponentModel.PropertyChangedEventHandler
PropertyChanging;
public event System.ComponentModel.PropertyChangedEventHandler
PropertyChanged;
But SqlMetal.exe does this all for free. Nice of it, no?
NOTE : If you are using SQL Server Express 2005 / SQL Server 2005, and your database contains diagrams, then there will be an additional sysDiagrams table generated, as SqlMetal.exe generates code for the entire database. So if you dont want this in the generated entity code file, you can manually delete the generated sysDiagrams stuff.
The DataContext is the main conduit by which you retrieve objects from the database and submit changes back. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply.
The purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results. The DataContext enables Language-Integrated Query by implementing the same operator pattern as the Standard Query Operators such as Where and Select.
For example, you can use the northwnd class (DataContext) which we generated using SQLMetal (and I've modified it, so that the database name is simply db, rather than the default one SQLMetal gives it) to retrieve all customer objects and bind this to a Winforms DataGridView, would be as follows:
nwind.db db;
...
try
{
db = new nwind.db(@"c:\program files\LINQ
Preview\data\northwnd.mdf");
}
catch(Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
...
dataGridView1.DataSource=(from customer in custs select customer).ToBindingList();
Isn't this easy. It's pratically the same for ASP.NET though you don't need to call the .ToBindingList(); when dealing with ASP .NET. You would simply do the following :
GridView1.DataSource = from customer in db.Customers
select customer;
Now that you have a northwnd class (DataContext) instantiated (db) running queries is very easy. We can simply run any LINQ standard query operator.
The following are but a few, for more standard query operators you could see my first article which was all about standard query operators
dataGridView1.DataSource=(from customer in custs select
customer).ToBindingList();
...
dataGridView1.DataSource=( from customer in db.Customers
where customer.Country == "USA"
orderby customer.CompanyName
select customer).ToBindingList();
...
dataGridView1.DataSource=( from customer in db.Customers
where customer.Country == "USA"
orderby customer.CompanyName
select new { customer.CustomerID,
customer.City }).ToBindingList();
...
dataGridView1.DataSource=( from customer in db.Customers
where customer.CustomerID.ToLower().StartsWith("a")
orderby customer.CompanyName
select customer).ToBindingList();
...
dataGridView1.DataSource=( from o in db.Orders
where o.OrderDate > Convert.ToDateTime("31/12/1997") &&
!(o.ShippedDate == null)
orderby o.OrderDate
select o).Take(50).ToBindingList();
...
dataGridView1.DataSource=(from c in db.Customers
join o in db.Orders on c.CustomerID equals
o.CustomerID
where o.CustomerID == "ALFKI"
select o).ToBindingList();
So that's just a taster of some of the sort of queries one can do with DLINQ. The following screen shot from the attached demo app, shows an example of one of these queries being bound to a Winforms DataGridView control
So far I've only shown you how to fetch (SELECT) from the database. But what we really want to be able to do, is change values, delete and add new values. So let's carry on and look at that.
Inserts
The DEMO app attached provides a two inserts, but it should be enough for you to get the picture.
INSERT A NEW CUSTOMER
This bit of code simply adds a new customer to the Customer's table, and then shows all the Customers within the DataGridView
try
{
Customers cust;
string id = "SACH1";
if ( (from c in db.Customers where c.CustomerID == id select c).Count()
> 0)
{
cust = db.Customers.Single(c => c.CustomerID == id);
db.Customers.Remove(cust);
db.SubmitChanges();
}
cust = new Customers {
CustomerID=id,CompanyName="SAS",ContactName="sacha
barber",ContactTitle="student" };
Table<Customers> custs = db.GetTable<Customers>();
custs.Add(cust);
db.SubmitChanges();
AllCustomers();
}
catch(Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
INSERT A NEW CUSTOMER AND A NEW ORDER
This bit of code simply adds a new customer to the Customers table, and a new Order for this Customer and then shows all the new Order within the DataGridView
try
{
Customers cust;
string id = "SACH2";
if ( (from c in db.Customers where c.CustomerID == id select c).Count()
> 0)
{
cust = db.Customers.Single(c => c.CustomerID == id);
db.Customers.Remove(cust);
db.SubmitChanges();
}
cust = new Customers {
CustomerID=id,CompanyName="SAS",ContactName="sacha
barber",ContactTitle="student" };
Table<Customers> custs = db.GetTable<Customers>();
custs.Add(cust);
Orders ord = new Orders {
CustomerID=id,ShipCity="Brighton",
ShipCountry="England", ShipName="=====",
OrderDate = DateTime.Now };
cust.Orders.Add(ord);
db.SubmitChanges();
dataGridView1.DataSource=(
from o in cust.Orders
select new { o.OrderID, o.CustomerID,
o.ShipCity,o.ShipCountry,
o.ShipName, o.OrderDate }
).ToBindingList();
}
catch(Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
Updates
The DEMO app attached provides a single update, but it should be enough for you to get the picture. In this example the user is prompted for a Customer ID that should be updated. They are then asked for a new ContactName which will be used to update the Customer with.
try
{
string id = GetUserValue("Update which customer< please specify a
CustomerID");
if (!string.IsNullOrEmpty(id))
{
if ( (from c in db.Customers where c.CustomerID == id select
c).Count() > 0)
{
string contactName = GetUserValue("Please enter the new
ContactName for CustomerID [" + id + "]");
if (!string.IsNullOrEmpty(contactName))
{
Customers cust = db.Customers.Single(c => c.CustomerID ==
id);
cust.ContactName = contactName;
db.SubmitChanges();
}
}
else
{
MessageBox.Show("Couldnt find a customer with that
CustomerID");
}
}
}
catch(Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
Deletes
The DEMO app attached provides a single delete, but it's a delete that should affect two tables, as it is deleting a Customer who has an open Order. So there is the matter of referential integrity to consider.
try
{
string id = GetUserValue("Enter a CustomerID to delete");
if (!string.IsNullOrEmpty(id))
{
if ( (from c in db.Customers where c.CustomerID == id select
c).Count() > 0)
{
if ( (from o in db.Orders where o.CustomerID == id select
o).Count() > 0)
{
db.Orders.Remove(db.Orders.Single(o => o.CustomerID ==
id));
}
Customers cust = db.Customers.Single(c => c.CustomerID == id);
db.Customers.Remove(cust);
db.SubmitChanges();
AllCustomers();
}
else
{
MessageBox.Show("Couldnt find a customer with that
CustomerID");
}
}
}
catch(Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
SQL Server 2005 Profiler
I don't actually have a full blown version of SQL 2005, I am using SQL SERVER EXPRESS 2005, so can't run SQL profiler on the generated SQL that DLINQ produces. But if you are lucky enough to have SQL Profiler for SQL SERVER 2005, I would recommend running it, so that you can see just what is auto-generated by DLINQ.
However, the entity class created by SQLMetal.exe does contain a nice property to help here too, its called Log. And simply by setting it as follows:
db.Log = Console.Out;
We can see the SQL generated in the Visual Studio output window. Which saves us having to need SQL Profiler, though SQL Profiler is the better analysis tool. But needs must and all that.
Well you've just seen how easy is was to do some standard SQL type operations. It's already a lot easier than ADO.NET I think. But what was nice about ADO.NET was that if we didn't like the UpdateCommand, or DeleteCommand (say from a wizard or by using the SqlCommandBuilder class) we could provide our own logic, either using custom SQL commands, or better still delegate this task to SQL server stored procedures and simply prvide the stored procedures with the relevant commands.
This is an SQL world that we probably all know and like. So can DLINQ do this, or are we forced to use the SQL that is auto-generated when we call SubmitChanges() method on the DataContext (Database entity class), to perform updates/deletes/inserts?
Luckily the LINQ team have thought about this, and actually given us the option to create our own logic. So how do we do that? Let's have a look shall we?
"When SubmitChanges() is called DLINQ generates and executes SQL commands to insert, update and delete rows in the database. These actions can be overridden by application developers and in their place custom code can be used to perform the desired actions. In this way, alternative facilities like database stored procedures can be invoked automatically by the change processor.
Consider a stored procedure for updating the units in stock for the Products table in the Northwind sample database. The SQL declaration of the procedure is as follows.
create proc UpdateProductStock
@id int,
@originalUnits int,
@decrement int
as
.....
"You can use the stored procedure instead of the normal auto-generated update command by defining a method on your strongly-typed DataContext. Even if the DataContext class is being auto-generated by the DLINQ code generation tool, you can still specify these methods in a partial class of your own. "
public partial class Northwind : DataContext
{
...
[UpdateMethod]
public void OnProductUpdate(Product original, Product current) {
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)
);
if (rowCount < 1)
throw new OptimisticConcurrencyException();
}
...
}
}
"The attribute UpdateMethod tells the DataContext to use this method in place of a generated update statement. The original and current parameters are used by DLINQ for passing in the original and current copies of the object of the specified type. The two parameters are available for optimistic concurrency conflict detection. Note that if you are overriding the default update logic, conflict detection is your responsibility.
The stored procedure UpdateProductStock is invoked using the ExecuteCommand() method of the DataContext. It returns the number of rows affected and has the following signature:"
public int ExecuteCommand(string command, params object[] parameters);
"The object array is used for passing parameters required for executing the command.
Similar to the update method, insert and delete methods may be specified using the InsertMethod and DeleteMethod attributes. Insert and delete methods take only one parameter of the entity type to be updated. For example, methods to insert and delete a Product instance can be specified as follows:"
[InsertMethod]
public void OnProductInsert(Product prod) { ... }
[DeleteMethod]
public void OnProductDelete(Product prod) { ... }
"The method names can be arbitrary but the attributes are required and the signatures must follow the specified patterns."
DLinq Overview For CSharp Developers.doc. Copyright ©Microsoft Corporation 2006
This qutoed section was taken from the document mentioned here, which comes with the May 2006 LINQ CTP installation. I just could not have said it better than this, so felt was OK to nick this one section.
Up until now we've just been doing queries that were static, that is, the full query is hard-coded into the attached DEMO app. Whilst this shows us how to use DLINQ, is probably not going to be what we have to do in our day to day programming jobs. Normally we would tailor a query using user specified parameters.
So let's have a look at how we can do this in DLINQ.
The first method is very simple, we just introduce some variables to limit the results that will be obtained by the query.
The variable can be put into the WHERE clause of the query. Let's see a simple example that returns all customers from the customers table, who live in the USA.
try
{
string country="USA";
dataGridView1.DataSource=(from emp in db.Employees
where emp.Country.Equals(country)
select emp).ToBindingList();
}
catch(Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
That's easy enough isn't it.
But there is another way, which is decribed at Matt Warrens blog The Wayward WebLog, and it is to use the IQueryable interface. From what Matt is saying this interface has been changing for the next version of LINQ, so there may be changed from what this article is stating, when the new version of LINQ is officially released. This article is based on the May 2006 LINQ CTP, so I'll try and show you what's what using what we have available right now.
Firstly, we need to define a couple of class instance fields. This example uses the Employee table of the northwind database
private readonly IQueryable<EmployeeView> baseQuery;
public class EmployeeView {
public string LastName;
public string FirstName;
public string City;
public string Country;
public string Extension;
public DateTime? HireDate;
}
...
baseQuery = from e in db.Employees select new EmployeeView { e.LastName,
e.FirstName, e.City, e.Country, e.Extension, e.HireDate };
So that's one part.
Next we need to create a method that will carry out a dynamic query that the user specifies (filter really; thinking of it like a filter is probably better).
The code is shown below. I'm hoping that you guys can follow this. It's fairly easy, it goes something like this:
- Show all employees
- Create a new ParameterExpression of type EmployeeView
- Create a new expression based on the filter string the user provided (OK so the DEMO app provides it in this case, but it could come from user input, it's just a string)
- Get the database to create a new query based on the new expression from step3
- We should now have a set of results that matched the filter
private void RunDynamicEmployeesQuery(string filter)
{
try
{
dataGridView1.DataSource=( from emp in db.Employees
select emp).ToBindingList();
var expression = baseQuery.Expression;
ParameterExpression expr = Expression.Parameter(typeof(EmployeeView),
null);
if (!String.IsNullOrEmpty(filter)) {
expression = QueryExpression.Where(expression,
QueryExpression.Lambda(filter, expr));
}
var query = db.CreateQuery<EmployeeView>(expression);
string res="";
foreach(EmployeeView ev in query)
{
res+="Name : " + ev.FirstName + " " +
ev.LastName + ", City : " + ev.City + ",
Country : " + ev.Country + ",
Extension : " + ev.Extension + ",
HireDate : " + ev.HireDate.Value.ToShortDateString() +
"\r\n";
}
MessageBox.Show(db.GetQueryText(query).ToString() +
"\r\n\r\n" + "User Defined Filter : " +
filter + "\r\n\r\n" + "Yielded " +
query.Count().ToString() + " employee results\r\n\r\n"
+ res + "\r\n\r\n" + "NOTE : May 2006 CTP (This
one) DOES NOT seem to allow IQueryable results to be bound to
DataGridView though\r\n" + "perhaps, .NET 3.5 proper
release or March 2007 CTP \"Orcas\" release will fix
this. As such the forms DataGridView\r\n" + "is
currently set to display all Employees, and not the result of the
filter. The results shown in this message box are\r\n" +
"correct, based on the filter. I just cant get the results
into the DataGridView.r\n\r\n" + "So if anyone knows
how to do that, please let me know");
}
catch(Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
And finally, we need to call this method, passing in a valid filter string. This is quite simple.
RunDynamicEmployeesQuery(@"city = 'Redmond'");
One Thing That Doesnt Work
The more eagle eyed among you may spot that that there is some code commented out. The code commented out is actually what should bind the reults of the now filtered query to the DataGridView. Unfortunatly, I could not get this to work. It should, it just wont do it. I did read somewhere that the May 2006 LINQ CTP has less binding features available than the final version will. So I imagine that the full realeased version of LINQ (whenever that is) should allow an IQueryable to be used as a DataSource for a DataGridView. It just doesn't seem to at the moment. Sorry about that folks.
I am obviously delaign with a Winforms app here, so am using a DataGridView to show the results of any queries. I only came across two things really:
- When trying to bind the results of a DLINQ query to the
DataGridView I had to use the undocumented .ToBindingList() on the end of a DLINQ query to be abe to set it as the datasource for the DataGridView - Normally a
DataGridView allows its bound contents to be sorted using a column click. Attempting this with DLINQ query bound to the DataGridView, causes an Exception "DataGridView control cannot be sorted if it is bound to an IBlindingList that does support sorting" IQueryable not being able to be used as a DataSource for a DataGridView, as just explained.
The 1st one is fair enough once you know about it.
The 2nd point, mmmm that's bad. So I hunted a bit and found this article:
Sortable bindinglist and DLinq which kind of says that in the May 2006 LINQ CTP version of LINQ there is limited binding support, and it's all going to get better in the future. So I guess we'll just have to wait for Visual Studio Orcas to be released officially.
I hope there are some of you have read the first part, and can see where this article picks up from Part1. I did not want to go over all the standard query operators again, as those that missed those can check out Part1
I am also hoping that this article has shown that DLINQ (Or LINQ over SQL as it will be known in future) is not that scary, and it's actually quite easy to use.
I would just like to ask, if you liked the article please vote for it, as it lets me know if the article was at the right level or not.
Also if you think that the next proposed article should include this much material or less material. Let me know, after all I want to write articles that actually help people out.
I have quite enjoyed constructing this article, and have been quite refreshed at just how easy DLINQ is to use. Though I still quite like stored procedures and can't help but think that they (them / Microsoft / the good / the bad / the brilliant ones) are just adding another level of code to the data access layer. I'm still not 100% decided about DLINQ, it's very easy to use. But you MUST maintain these entity classes, so your database design must be fairly locked down, though using SQLMetail its easy to generate these entity classes again. I do love how easy it is to update the database, and I thought DataAdaptors were cool, dataContext is sooooo (lots of o's basically) much better
v1.0 23/03/07 : Initial issue
- Lambda Expressions and Expression Trees: An Introduction
- Lambdas and Curry Notes. From Sussex university (my uni actually)
- Concepts behind the C# 3.0 language
- LINQ Project
- 101 LINQ Samples
- Building Queries
- The Wayward WebLog
- May 2006 LINQ CTP
- ScottGu's Blog
