Querying Web Services with SQL

Question

I had a bit of a shock recently when thinking about combining a service oriented architecture with a brilliant UI which leverages SQL to optimize performance when querying data.

The DevExpress grid view for ASP.NET, for example, is so cool that it delegates all filtering, sorting and paging logic to the database server. But this presumes that the data is retrieved from a SQL-able database server.

What if I want to introduce a web service layer between the database and UI layers, and to have the UI use the web services to query the data?

• How can I design the web services and the UI such that I can pass filtering requests from the UI via the web services to the database?
• Do I need to provide a List QueryData(string sqlQuery) style web service and have to parse the SQL string on my own to guarantee security/access restriction?
• Or is there any good framework or design guideline that takes this burden from me?

This must be a very common problem, and I am sure that it has been solved relatively adequately already, has it?

I am mainly interested in a .NET/C#-based or -compatible solution.

Edit: I've found OData and Microsoft WCF Data Services. If I got it right, an OData-based application could look as follows:

1. User ---/Give me Page 1 (records 1..10)/---> ASP.NET Server Control (of course, via HTTP)
2. ASP.NET Server Control ---/LINQ Query/---> Data service client
3. Data service client ---/OData Query/---> WCF Data Service
4. WCF Data Service ---/LINQ Query/---> Entity Framework
5. Entity Framework ---/SQL Query/---> Database

If I got this right, my DevExpress server control should be able to delegate a filtering request (e.g. give me the top 10 only) through all these layers down to the database which then applies its indexes etc. in order to perform that query.

Is that right?

Edit: It is a joy to see this thread coming to life :-) It is hard to decide on what answer to accept because all seem equally good to me...

Answer 1

Really interesting question! I don't think there's a right or wrong answer, but I think you can establish some architectural principles.

Firstly, "Service Oriented Architecture" is an architectural style that requires you to expose business services for consumption by other applications. Running a database query is not a service - in my opinion at least. In fact, providing a web service to execute arbitrary SQL is probably an anti-pattern - you would bypass the security model most database servers provide, you'd have no control over the queries - it's relatively easy to write a syntactically correct "select" query which cripples your database (Cartesian joins are my favourite), and the overhead of the web service protocol would make this approach several times slower than just querying the database through normal access routes - LINQ or whatever.

So, let's assume you accept that point of view - what is the solution to the problem?

Firstly, if you want the productivity of using the DevExpress grid, you probably should work in the way DevExpress want you to work - if that means querying the database directly, that's by far the best way to go. If you want to move to a SOA, and the DevExpress grid doesn't support that, it's time to find a new grid control, rather than tailor your entire enterprise architecture to a relatively minor component.

Secondly - structurally, where should you do your sorting, filtering etc? This is an easy concept in SQL, but rather unpleasant when trying to translate it to a web service specification - you quickly end up with an incomprehensible method signature ("getAccountDataForUser(userID, bool sortByDate, bool sortByValue, bool filterZeros, bool filterTransfers)"). On the other hand, performing the filtering and sorting on the client is messy and slow.

My recommendation would be to look at the Specification Pattern - this allows you to have clean method signatures, but specify the desired sorting and ordering in a consistent way.