Mapping objects to a relational database...

[hyperD]

I think SQL 2000 caches inline SQL now for performance, but I always use SPs for the reasons stated. And table order for transactions to minimize deadlocks.

I have to admit I've moved from a more purist OO to a more service level architecture since using .net.

I got a bit spooked at reading some of the earlier posts - makes me feel like a charlatan! Oooops, better not tell the clients....!

[HankWangford]

Inline

I won't let any developers talk to the db unless it's via sprocs for this reason.

Ah your one of these DBA's who treat their DB better than their offspring)


You can write inline sql to use parameters thus avoiding the injection issue.

Let me guess you also have one business object and dal method per sproc? ZzzzzzZzzzzz, life is too short to code that crap

How is the inline method a mess? Granted when you have "SELECT bottle, boot, shop from PUBS...etc etc" hardcoded but with entity broker et al this isnt the case. The business entities are mapped directly from the db, hence strongly typed objects, no room for error there, whereas hand cranking these yourself can be a nightmare (creating numerous manual sqlparameters and giving one an incorrect sql data type is very easy)

I bet you get more bugs with your procs than I do with the generated sql.

Inline sql quries are cached in sql 2000 +

Results pay, elegant, beautifull time consuming hand cranked proc / dal / business entity layers do not.

Agreed asp.net has turned into a novice camp, but the old posters on the architecture board thona, frans, rob howard, scott hanslemann etc are good.

[HankWangford]

All I need is reflector! No more humans - they are all scum!

i always obfuscate....

[Joe Black]

I go with Hank on this one.

Why slap another layer of code on top of a design, e.g. TSQL, stored procs etc (in yet another language), when 90%+ of the time most data layers just add/update a couple of records.

If you're talking about analysing 30+ years of financial records, then maybe stored procs, a 'coding to the machine' philosophy makes sense for performance issues. For anything else I want a design, which when I change the design the design is reflected everywhere, not that I have to rummage about and change code, permissions, logic in umpteen places and however many languages...where design says can be up to 128 characters, but stored proc accepts only 32, and provides nothing more than a warning after the fact that the data was truncated.

A DB is an engine, it shouldn't be any more than that...any more than a DBA should be saying "no you can't have something called 'Yearly Sales', it has to be 'Yearly_Sales', or 'YRLYSLES'"...

Rant over...until the next time some 'DBA' won't allow me to create a database which best suits the design.

[Spacecadet]

I'm sticking with CSWD

Best top security practice is
NO access to tables
Access only to views (select only, no insert or update) and SP's

Yes it adds an extra layer, but then the views and SP's are part of the data model.

Plus - if the data model changes or a table has a columns added its simpler to change an SP than to try and find the bit of complied SQL somewhere in an app which is inserting without a column list.

[Joe Black]

> Best top security practice is NO access to tables
Access only to views (select only, no insert or update) and SP's

if the data/business layer is compromised then likewise any SPs it has permission to access/execute are compromised as well, so it provides only a marginal gain

> Yes it adds an extra layer, but then the views and SP's are part of the data model.

they're not, the table properties and/or relationships form part of the design, SPs etc are merely the means of implementation

> Plus - if the data model changes or a table has a columns added its simpler to change an SP than to try and find the bit of complied SQL somewhere in an app which is inserting without a column list.

if the design is implemented correctly then it should either be automatically updated with the changes, or should throw an exception/not compile/indicate the source of any conflict...in any case it should be the driver driving the car, not the engine, the database should change with the design, not the other way round.

[Joe Black]

Edited because arguing this is like banging your head against a wall

[HankWangford]

Hmmmm

I think most of the people on here seem to work on hack job applications looking at some of the comments I've seen.

you complete cretin.......

[Cowboy Bob]

I'm a big Hibernate fan. It generates very clean and efficient SQL, and is very mature. It works on .NET as well as Java, so you Microsofties don't have to miss out on the fun anymore. Mix liberally with Spring to get full transactional integrity across database calls and you've got a killer framework.

Besides, I hate stored procs as they take too much power away from the developer and hand over too much power to the DBA. Giving the DBA too much power over a project is a *very* bad thing. Next thing you know, they'll be designing business logic instead of tweaking indexes and banging on about database security

[Joe Black]

But Bob, as any good DBA will tell you, the best place for business logic is in the stored procs...