Very quick advice?

[Daniel]

Hey guys

I am moving all the sql code that is being done inside my programme out to
the database and having it all called through stored procedures in the
database. To me this is the best way to separate database coding from actual
programme coding. Right way forward? Yes, no? I think yes just want
confirmation before i start as its a big job.

Thanks

 

[Peter Bradley]

Yes

Peter

 

[Marc Gravell]

IMO, yes.

Historically, another big win was performance, but this has levelled a
bit since the RDBMS vendors are now better at caching and reusing
plans. It also removes the temptation to use string concatenation to
build your SQL (easy in C#, trickier [but very possible] in SQL) -
thus avoiding most injection vulnerabilities.

But for me - the main advantage is a layer of abstraction (via the SP
signature as an interface) between the layers; I can't accurately
count the number of times where I have been able to re-work the
database independently of the application by tweaking the SPs -
invaluable. Security is another biggie; your C# code can have access
to execute a reviewed, formalised SP, but not have (direct) access to
do *anything* on the same tables - makes it very hard to exploit via
direct DML operations. Of course, some "find" operations are best done
via dynamic SQL, which requires SELECT as a minimum.

Of course, this does mean you can't simply let DataSets roam rampant
anymore (default [self-building] adapters). For me this is a very good
thing. What I am interested in, however, is how this is going to hang
together with LINQ; as I understand it you can plumb LINQ all the way
to the database if you want... but personally, I don't *want* my
applications having this level of ability... I'm currently downloading
the CTP to see how it behaves in practice (at this early stage).

Marc

 

[Mark Rae]

Yes, no?

Most definitely!

 

[Jon Skeet [C# MVP]]

I am moving all the sql code that is being done inside my programme out to
the database and having it all called through stored procedures in the
database. To me this is the best way to separate database coding from actual
programme coding. Right way forward? Yes, no? I think yes just want
confirmation before i start as its a big job.

I would certainly avoid hard-coded SQL where possible, but I don't tend
to use SPs as much as ORM. There are definitely cases where SPs are the
way to go (in particular when you want manipulation done and it can all
be done within the database), along with the security point raised by
Marc, but for CRUD operations (especially where reading can involve
dynamic queries) ORM solutions can be fabulous.

(At this point I should say that almost all my experience with ORM is
using Hibernate for Java; however, it made what would have been an
almost impossible project feasible.)

 

[Marc Gravell]

Just an additional: interoperability. Some of my work is nice
standalone .Net (etc) systems, but I also support a large coroporate
monolith that has grown organically over time. Sometimes these systems
are neatly packaged, but often (where sub-systems have been upgraded
individually due to complexity) the same database operations must
support .Net 2.0 (smart, web and rich clients), VB6 (rich and web),
Centura and (gulp - until frighteningly recently) Gupta 16-bit (not
sure "rich" is the right term, but you get the idea...).

In such a scenario, the abstraction of SPs makes this (just about)
viable, where-as I wouldn't trust the VB6 or Centura ORM offerings to
play nicely side-by-side. I also acknowledge that both Java and .Net
provide many proven ORM offerings, and in a green-field scenario (i.e.
where I don't need to back-port to VB6 or below) I am happy to
re-evaluate what role things like NHibernate and LINQ have to play.

So cheers for your thoughts, Jon - always a pleasure.

Marc

 

[Jon Skeet [C# MVP]]

Just an additional: interoperability. Some of my work is nice
standalone .Net (etc) systems, but I also support a large coroporate
monolith that has grown organically over time. Sometimes these systems
are neatly packaged, but often (where sub-systems have been upgraded
individually due to complexity) the same database operations must
support .Net 2.0 (smart, web and rich clients), VB6 (rich and web),
Centura and (gulp - until frighteningly recently) Gupta 16-bit (not
sure "rich" is the right term, but you get the idea...).

In such a scenario, the abstraction of SPs makes this (just about)
viable, where-as I wouldn't trust the VB6 or Centura ORM offerings to
play nicely side-by-side. I also acknowledge that both Java and .Net
provide many proven ORM offerings, and in a green-field scenario (i.e.
where I don't need to back-port to VB6 or below) I am happy to
re-evaluate what role things like NHibernate and LINQ have to play.

One interesting thing is that this goes both ways. In the project I was
thinking of, we *did* have a load of stored procs for a lot of the
"background work". Now, we only had one client platform (Java) but we
had to work on both Oracle and SQL Server. We spent *much* more time
porting the procs from SQL Server to Oracle than we did getting the
queries right on both of them (and on HSQL, our unit testing database
platform).

So cheers for your thoughts, Jon - always a pleasure.

Ditto