data retrieval method signatures

Here's a style question I haven't been able to find much of an answer to on the web at large...  When coding a data access layer, is it better to have a small number of generic methods, or numerous specific methods?  To be clear, I'm wondering in particular about an API used internally by a single in-house app, as opposed to one exposed externally to consumers...

For example (C#):

public DataSet GetData(string SQL) {...}

vs multiple varying methods like:

public DataSet GetProductsByCategory(Guid CategoryID) {...}

It seems like the generic approach requires less maintenance, but would clearly lead to inappropriate placement of SQL code in the calling layer...that could be mitigated with an Enum of query types and a parameter list though.  Whereas the second approach requires several procedures, all with very similar code, that must be updated every time the DB schema changes...

My current solution is to split the problem into two separate components.  One generic component, which is used only by a second component which exposes the more specific API methods.  I use the aforementioned QueryTypes enum in the generic component to keep the SQL as close to the DB server as possible, but then shield the UI app from all the DB-Where-Clause complexities with the nice wrapper API. 

Since my app communicates with the backend DB server via web services, this seems to work well.  The web service implements the generic component, and the client side DLL has the app-specific wrapper API.  It's nice cause I can update the app's API with a new client version revision if need be, without breaking older clients by changing the web service method signatures.

Anyway, that's what I've come up with, but I'd be interested to hear other people's approaches (and *constructive* criticism, hehe).

Joe
Friday, May 21, 2004

Think about how generic data interfaces, like ODBC are stuctured.  Although these are generic interfaces and abstract at a much lower level than your application does, they are also a combination of specific methods and properties and completely generalised methods.

There are specific methods to do that which is well known and understood, you can make connections to data sources, find table/queries and the rest of it, and you can have something like SQL passthrough which avoids all the specifics and lets you hit the virtual metal of the underlying database.

Similarly with an application, you're going to have well understood and well specified interfaces, that themselves use more generic ones and allowing direct access to those more generic interfaces means that your application isn't stalled whilst you work out how to create a new specific set of interfaces.

That generic interface will be your 'Get out of jail free' card at one time or another.

Simon Lucy
Friday, May 21, 2004

I did the same as you:

1)  low-level function, takes SQL as parameter, called by higher-level data-access functions

2)  mid-level data-access functions, have strongly-decorated application-domain names like GetProductsByCategory, take C++/C# types as a parameter, construct appropriate SQL and call the low-level function

3)  top-level application functions, call the mid-level data-access API, know nothing about SQL.

Christopher Wells
Friday, May 21, 2004

Some people might say it's better to not have SQL outside the database, and to have the SQL in DB stored procedures instead; if so, then I'd think that the DB stored procedures would replace your data-access layer.

Christopher Wells
Sunday, May 23, 2004

  Recent Topics

  Fog Creek Home