Tuesday, September 23, 2008

ADO.NET Data Services + Oracle

Wow - this is wicked cool stuff.

ADO.NET Data Services allow you to expose IQueryable objects as RESTful endpoints in ATOM, POX, and JSON. Here's my brain dump of how cool this can be used for RAD purposes...

ADO.NET Data Services is being plugged as just a endpoint for ADO.NET Entities, and thus just SQL Server's LINQ to SQL implementation. Microsoft themselves are doing most of that damage - but I want to point out how it's much bigger than that!

If you're like me, you have the pleasure (yes, pleasure) of working with Oracle instead of SQL Server. Many times this leaves you feeling high and dry with all this cool new LINQ stuff. There is no realy good LINQ to Oracle implementation out there - but the team at NHibernate has come to the rescue.

NHibernate is a .NET port of the years old Java Hibernate project - providing ORM for your software. The importance of this framework in the RAD world is big - it allows you to very easily map a database to objects.

LINQ to NHibernate is almost a year old now, and is in the process of being implemented in the main trunk of NHibernate (currently it's there, but throws a NotImplementedException after mocking you by showing you the SQL it knows that it should use). However, in the NContrib trunk, I have found a quite functional version of LINQ to NHibernate!

Why do I bring this up? Because some kind people in the project have even gone so far as to implement the System.Data.Services.IUpdateable interface within the LINQ.NHibernate project. This means fully functional ADO.NET Data Services for your Oracle database.

With only a couple lines of code, you can expose your NHibernate objects as queryable JSON, ATOM, POX RESTful services. Simply extend NHibernateContext, provide a ProvideSession() implementation, and expose public IQueryable properties on your object. Use this class as the type parameter of a new System.Data.Services.DataService class, and you'll be all set.

ADO.NET data services provides the RESTful interface which is flexible enough to pass pagnation, ordering, filtering, etc straight through the web layer to the database layer. So a javascript request for:

http://server/someobject?$filter=someid eq '1'&$orderby=somefield

produces SQL sent straight to Oracle of

select *
from someobject
where someid = 1
order by somefield

It's also fully updatable (through the IUpdateable implementation) which means that POST, PUT, DELETE requests work in a similar fashion.  It provides it in a JSON format, allowing you to use your database tables as javascript collections.... wow... Again, the RAD implications are huge.

The only issue that I have is with performance - NHibernate doesn't seem to stream resultsets (instead building the object array all at once), and the wordy ATOM/JSON formats don't suit some applications. ADO.NET Data Services is NOT pluggable with custom serialization - I've decompiled and searched for an entry point for literally DAYS.

One other point to make, is that referencing these new data services from VS2008 SP1 provides you with a client side LINQ object which translates linq queries to filtered HTTP requests (as in the filter/order/etc format above, which then translates to SQL... So, cool.

I'd provide some detailed examples and implementations - but that'd ruin all the fun ;)

10 comments:

Nick said...

How does this prevent SQL injection? I'm guessing you only allow read only access on certain tables.

kovalan Jayamurugan said...

Thanks for sharing such informative guide on .Net technology. This post gives me detailed information about the .net technology. I am working as trainer in leading IT training academy offering Dot Net Training in Chennai and i use your guide to educate my students.

Reeta said...

Really an amazing article.!! Thanks for taking time to provide us this exclusive information which makes me to get more information about search engine

Dotnet Training in Chennai

Amirtha rao said...

This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic.
Regards,
DOT NET Course in Chennai|.net training in chennai

Mathew Stephen said...

Awesome Post!!! With unique content, I really get interest to read this post. I hope this article help many of them who looking this pretty information.
Regards,
DOTNET course chennai | DOTNET Training Institute in Chennai

Rasool Bevi said...

As a fresher, I learned much precious information about .net from your blog, Thanks for taking time to discuss about the beauty of dot net language.
Regards,
DOT NET Training in Chennai|DOT NET Course in Chennai

Rekha J said...

This is my first visit to your blog, your post made productive reading, thank you. dot net training in chennai

Aptoinn said...

You post explain everything in detail and it was very interesting to read. Thank you. nata coaching centres in chennai

sivanesan said...

Excellent post. I have read your blog it's very interesting and informative. Keep sharing.
erp software in chennai

Shreeja K said...

Informative article, just what I was looking for.seo services chennai