Eliminating CRUD procedures

  • Thread starter Thread starter Colin Robinson
  • Start date Start date
C

Colin Robinson

Your know the ones, you have a table and it has Create,Read,Update and
Delete Stored procedures and the app always uses these to talk to the DB
eliminating dynamic sql in your apps.

Makes perfect sense when you have a table however when you have 500 tables
its not so clever (thats 2000 stored procs to maintain)
Shouldnt the framework and sql guys talk to each other and come up with a
single proc that accepts the CRUD as an argument to a single UpdateMyTable
proc for each table. Then Hey presto 500 tables = 500 procs

It would be nice if i didnt have to code it myself and the dataset wizard
just happened to offer this option in the next framework version!

Colin Robinson.
 
In my opinion having a single proc to update all 500 tables is not a very
good idea. For one you would never get plan reuse so the performance would
suck on a busy system. Besides how many are pure update statements with no
logic otherwise. You can find a good selection of code generators on the web
for free that create all the CRUD sps that you want automatically. Even the
Dataset wizard built into VS will do this as well.
 
Realistically, when one sells a pair of shoes you might hit 5 or 50 tables
in the database as you check stock, move stock, update the customer's
invoice, the shipping records, the credit records, the billing records, the
QC records and the audit trail. Except in the simplest of cases, ordinary
CRUD code generated by the best of the OR mappers can't deal with the litany
of business rules that need to be exercised when working the transaction(s)
involved. The VS code generators (aka TableAdapter and DataAdapter which are
driven from the CommandBuilder fall short--again unless your operations are
very simple.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Your know the ones, you have a table and it has Create,Read,Update and
Delete Stored procedures and the app always uses these to talk to the DB
eliminating dynamic sql in your apps.

Makes perfect sense when you have a table however when you have 500 tables
its not so clever (thats 2000 stored procs to maintain)
Shouldnt the framework and sql guys talk to each other and come up with a
single proc that accepts the CRUD as an argument to a single UpdateMyTable
proc for each table. Then Hey presto 500 tables = 500 procs

It would be nice if i didnt have to code it myself and the dataset wizard
just happened to offer this option in the next framework version!

Colin Robinson.
Click to expand...

Interesting idea ... but right now you can use alternatives like SubSonic
to generate for you code and classes to do CRUD operations on your assorted
tables
 
come up with a single proc that accepts the CRUD as an argument to a single UpdateMyTable proc for each table. <<

Hey, why not replace the **entire** application with one procedure
call that takes a zillion parameters and decides what to do based on
bit flags? Of course, you cannot maintain such stupid code but you're
a cowboy coder who doesn't care about what happens after you sneak
past QA.

You have never read a book on basic Software Engineering, have you?
Do so, before you hurt someone. In particular, look up coupling and
cohesion among code modules. Then read it again before you next post,
so you will not wind up as an example of bad programming in one of my
books.
 
Now, now Joe... let's play nice. ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Now, now Joe... let's play nice. ;) <<

Admit it! You were dying to say the same thing. I was waiting for
Colin to discover the OTLT for the datas to go with his "Briteny
Spears, Squids and Automobiles" procedure.
 
Yes... but I try to be a bit more diplomatic. ;)
You can understand their frustration though. MS makes it seem so easy. I
hear they're offering a new consumer product "Brain Surgery For Everyone".
All it takes is a sharp knife, a skill saw and the bathroom mirror.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
ok guys so youve had your fun.

I never suggested one proc proc per db, or any horrendous logic, I never
even suggested business logic in the Crud procedures. I simply suggested the
framework offered the opportunity for a more manageable and maintainable
number of stored procs per table. 1:1 is about as closely coupled as you can
get.

Im aware of code generators orm mappers etc, of course I am and they are
great for new systems. but would you seriously point them at an existing
unknown Database thats been in production for some time and expect to get
away with it.

Bill even if the business logic was in the stored procs id rather find it in
one place than 4!. Ive lost count the number of times the update logic was
out of step with the insert logic as the business rules changed over time.

Thanks for the support ...
 
Ahahahaha ... sorry, I was reading this topic from the .adonet forum and
when I looked up to see where it was cross-posted, I instantly looked to see
if there was a --CELKO-- response. Priceless.
 
William (Bill) Vaughn said:
Realistically, when one sells a pair of shoes you might hit 5 or 50 tables
in the database as you check stock, move stock, update the customer's
invoice, the shipping records, the credit records, the billing records,
the QC records and the audit trail. Except in the simplest of cases,
ordinary CRUD code generated by the best of the OR mappers can't deal with
the litany of business rules that need to be exercised when working the
transaction(s) involved. The VS code generators (aka TableAdapter and
DataAdapter which are driven from the CommandBuilder fall short--again
unless your operations are very simple.
Click to expand...

TableAdapters and all OR Mappers just allow you to code your transactions in
the 3GL of your choice, instead of coding them with SQL.

Whether that's a good thing or a bad thing is another question.

David
 
I put my basic insert, change, and delete queries in one SP and then call
it with a parameter (that is similar to the RowState used by ADO.Net)
telling it which one to perform. Since all of my applications are written
with business objects, this is fairly simple to do, and makes it easier to
keep all of them updated and in sync.

Robin S.
 
I'm with Robin on this. I have a single SP, written by an SP, per
table
that does the basic things, insert, update, read all, read one,
delete,
update archive bit, read by aduit create date, read the structure
of the table, and a dynamic sql. Al based on a mode parameter.

Specialized SPs like Bill Vaughn mentioned are always going to be
something else you deal with.
 
William said:
Realistically, when one sells a pair of shoes you might hit 5 or 50
tables in the database as you check stock, move stock, update the
customer's invoice, the shipping records, the credit records, the
billing records, the QC records and the audit trail. Except in the
simplest of cases, ordinary CRUD code generated by the best of the OR
mappers can't deal with the litany of business rules that need to be
exercised when working the transaction(s) involved.
Click to expand...

Err... every o/r mapper who's worth something uses transactions, often
available to you on the BL level so you can have transactions
in-memory, in-memory+db or solely db.

Why don't you read up on the material more, Bill (and I don't mean
about SQL, but about o/r mapper frameworks) ? Entity persistence
systems do far more than just generating SQL.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Hey, take it easy. At least they are offering a Step-by-Step book to go with it ... <<

But they will not have the bugs out until the third version ...
 
Okay, granted that I'm not the most up to date on these OR tools, but the OR
Mappers I've seen from MS so far fall far short of the sophisticated logic
in real-world SP that handle a litany of business rules and operational
details.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Back
Top