Now, I may be doing this wrong, but at the moment, it works :)
I’m writing a service to communicate with a database, and provide a consistent front end for the applications that will be using it.
In the past that has meant writing tonnes of CRUD (Create Read Update Delete) code, usually via Stored Procs, which are then accessed by a ‘DbAccess’ helper class of some variety. Not to mention the added bonus of writing the classes to store the data as it comes out.
Fortunately the database I’m accessing is small, and so I though it to be a good candidate for messing around with LINQ to SQL, and right I was too! Onto teh meaty stuff….
Lets say I have a database with 3 tables:
To insert into this via the medium of SQL we’re looking at something like:
insert into Car (ID, Make, Model) values (1, 'Volvo', 'V40')
insert into Owner (CarId, Title, Surname) (1, 'Mr', 'Skardon')
insert into PreviousOwners(CarId, WhenBought, Title, Surname) values (1, '2003-02-02', 'Ms', 'Taylor')
insert into PreviousOwners(CarId, WhenBought, Title, Surname) values (1, '2001-02-02', 'Miss', 'Polton')
Which inevitably ends up becoming wrapped up in stored procs, probably one to Add the car and owner, and another to add previous owners. Eurgh.
Anyways, we all know that stuff, how about we LINQ it up…
LINQ to SQL – Really basic edition
First, we want a new VS2008 window…
Got it?
Ace – Let us proceed…. Create a new Console App, I’m calling mine ‘LinqToCar’, but you can call it whatever you like :)
Once we have that, we add a new item to the project, and select the ‘LINQ to SQL Classes’ type, naming it (again) as you like.
In our new ‘dbml’ window we have we’ll have an empty tableau, let’s get filling it… Open up the ‘Server Explorer’ window in Visual Studio (CTRL+ALT+S), and add a Data connection to the Car database. Open up the ‘Tables’ folder and select all the tables, dragging them into the Object Relational Designer pane. You should end up with something like this:
Now we can switch back to our Program.cs file and start querying the database…
But first! We need a datacontext… Luckily we’ve already got one: ‘CarsDataContext’. So, let’s simply enter the following code:
class Program
{
static void Main(string[] args)
{
CarsDataContext db = new CarsDataContext();
var allCars = from c in db.Cars
select c;
allCars.WriteCars();
Console.WriteLine("Press ENTER to exit.");
Console.ReadLine();
}
}
internal static class CarsExtensions
{
internal static void WriteCars(this IEnumerable<Car> cars)
{
foreach (var car in cars)
Console.WriteLine(car.ID + ", " + car.Make + ", " + car.Model + ", " + car.Owner.Surname);
}
}
The ‘CarExtensions’ class just adds an Extension method to be able to print the contents of an IEnumerable<Car> collection to the console. The important code is the actual selection, first we create our DataContext:
CarsDataContext db = new CarsDataContext();
Then we perform the query:
var allCars = from c in db.Cars
select c;
This (as the cunningly named variable suggests) gets us all the cars (nb, we could have written this in lambda stylee by doing:
var allCars = db.Cars.Where(c => c);
Anyhews, this is starting to come across as an introduction to LINQ to SQL – which it’s not meant to be. Needless to say, we can perform pretty much any query we want to on this now, experiment with it!
But where does the WCF stuff come into it?
Well, we have all our classes created by the designer - ‘Car’, ‘Owner’ and ‘PreviousOwner’, what would be great would be if we could have access to these via a service and not have to create ‘mapping’ classes.
Soooo… how do we go about doing that?
Converting the ConsoleApp to a WCFService
To be honest, the easiest thing to do here is actually start a new ‘WCF Service Library’ project and add a dbml to it in the same way we did above. So, lets do that. (remember to add the LINQ to SQL file as above!!!)
Assuming you are now staring at an ‘IService1.cs’ file in your VS window, what we should do is go to the Solution Explorer and rename the ‘IService1.cs’ file to something better - ‘ICarsService’ seems a good start. Once we’ve done that, let’s clear out the ‘CompositeType’ and the methods defined for us in the ICarsService interface. So we should end up with a file with the following contents:
[ServiceContract]
public interface ICarsService
{
}
Let’s add two methods, one to get a car, one to submit a car…
[ServiceContract]
public interface ICarsService
{
[OperationContract]
Car GetCar(int id);
[OperationContract]
void SubmitCar(Car car);
}
Ok, so now we need to go to the Service1.cs file and rename it to something like ‘CarsService’ and then open it for editing… Same deal as before, delete all teh methods given to us and implement the ones defined in the ICarsService.
public class CarsService : ICarsService
{
public Car GetCar(int id)
{
throw new System.NotImplementedException();
}
public void SubmitCar(Car car)
{
throw new System.NotImplementedException();
}
}
Let’s add some simple LINQ code here…
public class CarsService : ICarsService
{
private static readonly CarsDataContext _db = new CarsDataContext();
public Car GetCar(int id)
{
return _db.Cars.Where(c => c.ID == id).Single();
}
public void SubmitCar(Car car)
{
_db.Cars.InsertOnSubmit(car);
_db.SubmitChanges();
}
}
Cool, so… let’s press F5 and load up the WCF Test Client and invoke that bad boy…
‘GetCar’ – passing ID 1 (as I know it’s there)…
Gah! WebException…
‘An error occurred while receiving the HTTP response to http://localhost:8731/Design_Time_Addresses/CarsLibrary/Service1/. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down).’
Hmmm… Ah, wait a minute… Sure, we’re attempting to pass down a ‘Car’ object, but we’ve not defined it as a DataContract… Let’s get onto that!
We need to open the Cars.designer.cs file and modify the ‘Car’ class.
First, we need to add the ‘DataContract’ attribute to the class…
[DataContract]
[Table(Name="dbo.Car")]
public partial class Car : INotifyPropertyChanging, INotifyPropertyChanged {...}
Next, we need to modify the properties, so, let’s find the properties, (in our case ‘ID’, ‘Make’, ‘Model’, ‘PreviousOwners’ and ‘Owner’ and add the ‘DataMember’ attribute to them:
[DataMember]
[Column(Storage="_ID", DbType="Int NOT NULL", IsPrimaryKey=true)]
public int ID {...}
Ace, now we’re rocking, let’s try again…
Gah! No we’re not… same error… oh wait, ahh yes, some of our properties refer to other classes (Owner etc) so we’d best DataContract / DataMember those bad boys as well…
Oh noes! Still doesn’t work.. Let’s have a look at those classes a bit closer…
What we’ve blindly done (I admit, I made you do this so please forgive me :)) is add the [DataMember] attribute to all the properties. Now, for the ‘Car’ class that is fine, desired in fact.. But what about the other classes? Well, inside those we have properties which are classed as ‘Association’ properties, (look at the attributes). How do these work?
Well… Lets say we’ve done a query, and have a Car object..
Car car = _db.Cars.Where(c => c.ID == 1).Single();
If we now use intellisense we can start skipping through the class…
Get’s us the Owner of the car.. but wait! There’s more!!
car.Owner.Car
??? Eh? Well, that gets us the owner of the car’s car…. but wait!! There’s even more!!!!
car.Owner.Car.Owner.......
A never ending stream of accessibility…!!! This is great internally to the service, but causes an infinite loop when trying to send the data down, (which is why you’ll get a ‘timeout’ exception).. The solution? Remove the ‘DataMember’ attributes from the [Association] properties…
Done?
F5 and try once more…
Huzzah!
We have a winner!!!
So, we can now Get a LINQ to SQL object from the service to our client.
I think that’ll be enough for this post… :) The next post will cover the uploading of data to our service…