[dba-SQLServer] Article: LINQ and stored procedures

Hi Arthur

I think that law of yours is up for revision, if for nothing else because it neglects a middle-tier in a three- or more tier system.

If it is very important for a setup that "anything" can communicate safely with the database, that "something" should be isolated from the back end completely by offering a neutral API to the outside world.
A good example of this are web-services. The interface is fully standardised. You can access it from any application which understands to communicate with a web-service and these applications may be programmed with any language. What is behind the web-service is of no importance - in fact it can be replaced completely as long as the new back end system provides the same information.

In my opinion the mechanics of the back end should be limited to the basics which will "never" change, like autoincrement/random/GUID generated IDs, relations and enforced referential integrity, rules like a start date cannot be later than an end date and a month value is between 1 and 12, materialized or aggregated tables for lookup or statistics etc.
On the other hand, rules that will change - and that is the business logic - should be kept in a middle-tier or - when this is not present - the front end. After all this is how most of us built front end/back end systems with Access.

But I only regard this as general rules. Most systems have a twist of some sort.

/gustav

>>> fuller.artful at gmail.com 29-03-2008 14:57 >>>
IMO we are coming to a crossroad here. I ought to indicate my perspective on
this at once, so that anything I write subsequently can be understood as
coming from said perspective.

Fuller's Sixth Law states "Anything the database can do, the database
shoulddo." This law applies to every db from dBASE to Access to MS SQL
to DB2 to
Oracle to Objectivity and so on.

To rephrase it in practical terms, this means that the FE ought to be
stripped of everything the db engine itself can do. This would include FK
constraints, to name just one thing. The FE should never never never touch
tables directly, but instead always go through a query (in Access) or a view
(in MS SQL and others) or a table UDF (in MS SQL). Nobody but God (i.e. me)
should be able to touch the tables directly.

Db-oriented logic encased in the FE is a bad idea for several reasons.
Perhaps the most notable is that said logic would have to be encased in
every FE that touches the db, which is career suicide. On the opposite
extreme, if everything is in the db itself then all the FE has to do is fire
a sproc and pass the appropriate parms. It is not trivial to translate a
call from VB6 or Delphi or .NET or PHP or Python into any other of these
languages, but it is much less work than to rebuild the logic in any new
language. A call to a sproc is basically a one-liner in any language.
Populate the parms, call the sproc, deal with the result set.

Ok, that's my take. Linq is going in an entirely new direction, and to be
fair, I've read the docs (well some of them) and been impressed by some of
the new language techniques, and also been thankful that one can use Linq to
call sprocs. But Linq overall seems to me a method of eliminating the
profession of SQL Developer. It attempts and suggests to move increasing
amounts of business logic upwards, to either the middle tier or the FE, and
in both cases I deem this incorrect. The push ought to be down not up, IMO.
I want the db to be a magic black box and the FE to simply follow some basic
communication rules, and everything will automagically happen. Moving the
logic of result-set-handling up to the MT or the FE is a bad choice, IMO.

All this said, I am still investigating Linq big-time. When I noticed that
one did not have to construct the SQL statements inside it, but could
instead call a sproc or table-udf, I was much relieved and much more
intrigued. So I shall pursue this path but also stick to my previously
stated law until it has been refuted. I will pursue the path of setting up
parms and calling sprocs, and totally ignore the idea of dynamically
constructing SQL statements within Linq.

And finally, I think that any investment in Linq for SQL is a dead end. Linq
for Entities is clearly the future. Linq for SQL is dead in the water,
subsumed completely by Linq for Entities. I can see some future there, for
sure. The idea that anything is encapsulated as a data source has promise.
But I still want the real logic and business in the back end.

A.

On 3/29/08, Gustav Brock <Gustav at cactus.dk> wrote:
>
> As requested recently by JC:
>
> >From the cover story "The Next Generation: Visual Studio 2008":
>
> <quote>
> LINQ speeds up application development by providing immediate fedback
> through IntelliSense and compile time error checking that wasn't possible
> using the older dual-mode ADO.NET and T-SQL development methodology.
> </quote>
>
> /gustav