Standards for using Stored Procedures as an interface

[Thomas R. Hummel]

Hi,

I've been tasked by a client to come up with documentation on
standards to follow in creating a data access layer implemented
through stored procedures. When talking about a data access layer,
they mean what will be interfacing with the .NET DAL. Upper
management, and the general consensus here is to stay away from a
strictly CRUD-based, auto-generated set of stored procedures.

In my own design work, I've identified some places to abstract out
entities that aren't based strictly in one table. I'm not sure how to
put that thought process into a more generic document though. How to
decide when to abstract, etc.

My question is, does anyone know of any good references or similar
documents that I could use as a starter to make sure that I don't
leave out any major areas that need to be addressed? All of my
searches come up with DALs that are strictly written from the point of
view of the .NET developer.

Thanks,
-Tom.

[Eric Isaacs]

Re: Standards for using Stored Procedures as an interface


The CRUD-based auto-generated set of stored procedures is a great
basis if you have a matching middle-tier. I would suggest coming up
with a middle-tier architecture that's generic (so that it can be auto-
generated) and flexible so that custom code can also be added to it.

If you have normalized databases, mimicking the structure of the
normalized tables in the middle-tier just makes sense. Every table
has a collection and an associated class including cross-reference
tables. For one to many relationships (parent to child relationships)
the parent class will have a collection of children within it, but
also the child class will have a parent property. By doing this, you
have the option of filling it from the top down or the bottom up.

The middle-tier is there as an interface to the database and as the
keeper of the business logic. But how you fill that middle-tier from
the database can be dynamic or customized.

With our generic auto-generated middle-tier design, we're able to fill
multiple layers (classes and collections) with just 1 to a few SQL
calls. We can even create custom fillers if the need arises, but now
we're coming up with new ways to fill our middle tier components with
generic SQL that's working nearly as efficiently as the custom fillers
(that take a lot more time to develop.) All in all, we're saving a
lot of development time now with our middle-tier design and that
design is making every developers code much more predictable and easy
to follow.

You'll still need to write custom code in the middle-tier, but if you
separate your generated generic code in separate partial class files,
you can focus your code development on just the more complicated
development that's business specific and you can regenerate the middle-
tier and associated auto-generated sprocs when database changes are
made.

There are ways to make this work. Doing it all by hand just doesn't
make sense anymore.

We all make a living out of automating processes, but software
development is going in the same direction. We're able to automate a
large portion of it now with custom scripts that are written as if our
own developers wrote the code entirely themselves. Also the generated
code will just work with very limited testing required, once you get
it right the first time which saves time in testing as well.

Look into CodeSmith as a tool to consider. Some of the templates
available for CodeSmith online will get you started on the right
path. You can customize your own scripts for generating your own code
to your own standards. Even if you only generate a small portion of
the code or the sprocs, a tool such as CodeSmith will pay for itself
very quickly. But you'll still need good developers to architect your
standards.

-Eric Isaacs
J Street Technology, Inc.