How to create generic WCF OperationContract to retrieve data from a SQL table.

Question

I've been given the task of creating a .net interface for a table that will be retrieved a database.

One of the obvious things that the interface must do is retrieve data from the table.

So.... I could write a function that simply does Select * from MyTable; and then returns the result. However, the table has ten columns and someone using the interface might want to filter the data by one or more columns. I might need something like Select * from MyTable where LastName like 'A%';. How can I design my interface to be a flexible single point of entry for downloading data from the table?

I've been told that any solution that involves pulling down the entire table from the server and then filtering the results in the .net code is unacceptable due to bandwidth concerns.

Note: I would also like to add that the code retrieving the data from the SQL table is called as a WCF service; however the data is retrieved, it must be returned to the caller as serializable data, not as any kind of reference.

Clarification: By "interface", I simply meant a function that is called to retrieve data from a SQL table. I realize that the term "interface" is a bit confusing. There is a word for exactly what I'm doing, but I don't know what it is.

Answer 1

I recommend using LINQ to Entities. It already has all the bandwidth efficiency concerns worked out.

Update (for WCF): In this case, I recommend WCF Data Services (formerly called ADO.NET Data Services, formerly called .NET RIA Services, formerly called Astoria). They use a REST protocol called oData in an interesting fashion. Essentially, you implement the service by providing an IQueryable<T> (e.g., using LINQ to Entities), and then you can consume the service by using "LINQ to oData" (I just made up that term, but that's the idea).

In this way, your clients can do something like:

var beverages = from product in myDataService.Products
                where product.CategoryName = "Beverages"
                select product;

(assuming myDataService is a DataServiceContract that points to your WCF Data Service). That query is transformed to an oData call, something like http://myHost/myWCFService.svc/Products?$filter=CategoryName%20eq%20'Beverages'. The oData query string is interpreted by the WCF data service, and passed to your IQueryable<T> implementation. If this is LINQ to Entities, then the query is passed through to the database, so the actual SQL executed is something like SELECT * FROM [Products] WHERE [Products].[CategoryName] = 'Beverages'.

As you can see, this is an incredibly powerful concept that allows very efficient data querying without the danger of SQL injection. There is a downside: since any kind of query is allowed, this approach can make database optimization more difficult. It also opens up the possibility of easier DoS attacks, though these can be limited. So it makes our lives as programmers very easy, but some DBAs are hesitant to accept it.

Answer 2

I'd place 10 text boxes on a form, and then build a query like:

string query = "select top 100 * from MyTable where 1=1 ";
if (!string.IsNullOrEmpty(txtLastName.Text))
    query += string.Format("and LastName like '%{0}%' ", txtLastName.Text);
if (!string.IsNullOrEmpty(txtFirstName.Text))
    query += string.Format("and FirstName like '%{0}%' ", txtFirstName.Text);

The top clause makes sure this will never download more than 100 rows.

Make sure you execute the resulting query using a low privilege user, or ensure the user doesn't enter a single quote ' in a textbox :)

Answer 3

NHibernate, SubSonic, LINQ 2 SQL, Entity Framework, LLBLGen, Codus to name a few.

I personally like NHibernate, with Fluent mappings, but it might be considered overkill and has a tad of a learning curve.

Libraries such as Sh#rp Architecture, Spring.NET and Castle Active Record abstract the implementation details NHibernate away so you can focus on Pocos and business without worrying about maintaining a DAL.