Getting rid of stored procedures

[philc]

I am looking at my project right now and I'm completely flabbergasted
at the number of stored procedures. Now I'm fine with some of the
more complex ones (with multiple joins and/or logic) but most of these
are simple CRUD (create, read, update, delete) from a single table
that should be generated automatically. I want to move away from
stored procedures for these cases because it's a maintanence
nightmare, has little if any performance gain, and is a waste of time.
There is a great discussion on this in this weblog entry:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

For these, I already have a strongly typed dataset so this should be
really easy - I pass in my table to function foo and it should get
filled (or updated, inserted, deleted).

I could create a wrapper around the SqlCommandBuilder to generate the
select statement along with the other three. This is done here:
http://www.codeproject.com/cs/database/CustomCommandBuilder.asp

Or I could use the generated embedded sql by configuring DataAdapters
(by dragging in a table from server explorer) but this can get nasty
and it generates a lot of ugly (but hidden) code.

Now there are an abundance of existing solutions that will create the
entire data access layer code for you. This includes:
LLBLGen - http://www.llblgen.com/
Entity Broker - http://www.thona-consulting.com/content/products/entitybroker.aspx
Norpheme (open source) - http://www.norpheme.com/
Wilson ORMapper - http://www.ormapper.net/
CodeProject example -
http://www.codeproject.com/useritems/xmlsqlpp.asp#xx797672xx

Now these are all great but I believe they are overkill for me. I
don't want an entire data access layer generated for me - I just want
to take my strongly typed datasets (and my connection object) and use
a very thin on-the-fly solution to perform the crud actions on them.

CustomCommandBuilder seems pretty on target with what I want. Is
there a reason it received mixed reviews or why most solutions out
there generate a complete data access layer for you? I love my
strongly typed datasets and I like my code lean and complexity free.

 

[William \(Bill\) Vaughn]

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

 

[William \(Bill\) Vaughn]

Without getting into a flame war, the arguments in this blog have been
thrashed around for some time. Consider that when polled, the developers
that attend my lectures (and this is from conferences all over the world),
almost universally use stored procedures. You're always going to find
arguments to move code out of the server and back into your applications. In
small shops this can work, but when three (or more) developers have to
support the application, SPs make more and more sense. Dedicated DBAs insist
on them. Managers who need to reign in costs demand them.
The CommandBuilder while attractive is a pit of problems. I discuss these in
an article (see http://www.betav.com/msdn_magazine.htm). It's simplistic
approach to problems is inefficient and ignores many other issues that
hobble productivity.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

 

[David Browne]

I am looking at my project right now and I'm completely flabbergasted
at the number of stored procedures. Now I'm fine with some of the
more complex ones (with multiple joins and/or logic) but most of these
are simple CRUD (create, read, update, delete) from a single table
that should be generated automatically. I want to move away from
stored procedures for these cases because it's a maintanence
nightmare, has little if any performance gain, and is a waste of time.
There is a great discussion on this in this weblog entry:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
.. . .
CustomCommandBuilder seems pretty on target with what I want. Is
there a reason it received mixed reviews or why most solutions out
there generate a complete data access layer for you? I love my
strongly typed datasets and I like my code lean and complexity free.

I think CustomCommandBuilder is right on the money. The worst thing about
the built-in CommandBuilders is that they will query the database metadata
catalog each time you use them. But getting this information from your
typed datasets removes the only serious problem with the CommandBuilder
concept.

Oh, and look at this thread for a tool I use to automate the extraction of
strongly-typed dataset schemas from a SqlServer database.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&[email protected]

David

 

[William Ryan eMVP]

I guess this is actually a serious post so I'll bite.

--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

I am looking at my project right now and I'm completely flabbergasted
at the number of stored procedures. Now I'm fine with some of the
more complex ones (with multiple joins and/or logic) but most of these
are simple CRUD (create, read, update, delete) from a single table
that should be generated automatically. I want to move away from
stored procedures for these cases because it's a maintanence
nightmare, has little if any performance gain, and is a waste of time.

What about Security? How do you permission your dynamic SQL? I'll grant
you performance isn't that big of a difference than using Paramaterized
queries but big deal.
ere is a great discussion on this in this weblog entry:

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Ok, so having to recompile your project and redistributue it just b/c you
want to add a field to a query is not a maintenance nightmare? I use Stored
procs on a regular basis for one app which allows me to fulfull customer
requests WHILE I'm on the phone with them - just hit the refresh button and
the changes are reflected instantaneously. That couldn't be done with
inline sql.

Besides - the number of lines of code is virtually identical. The only real
downside that I can see to stored procs is that you can't search through
their text easily, but it took me all of about 15 minutes to build a utility
to do that for me.

For these, I already have a strongly typed dataset so this should be
really easy - I pass in my table to function foo and it should get
filled (or updated, inserted, deleted).

I could create a wrapper around the SqlCommandBuilder to generate the
select statement along with the other three. This is done here:
http://www.codeproject.com/cs/database/CustomCommandBuilder.asp

Unless you need complex concurrency handling - btw, check out Bill Vaughn's
piece on Weaning Developers from the CommandBuilder over at www.betav.com ->
Articles -> MSDN .

Or I could use the generated embedded sql by configuring DataAdapters
(by dragging in a table from server explorer) but this can get nasty
and it generates a lot of ugly (but hidden) code.

Agreed but this is the case either way.

Now there are an abundance of existing solutions that will create the
entire data access layer code for you. This includes:
LLBLGen - http://www.llblgen.com/
Entity Broker - http://www.thona-consulting.com/content/products/entitybroker.aspx
Norpheme (open source) - http://www.norpheme.com/
Wilson ORMapper - http://www.ormapper.net/
CodeProject example -
http://www.codeproject.com/useritems/xmlsqlpp.asp#xx797672xx

You forgot Deklarit - www.deklarit.com an absolutely remarkable product as
is LLBLGen IMHO.

Now these are all great but I believe they are overkill for me. I
don't want an entire data access layer generated for me - I just want
to take my strongly typed datasets (and my connection object) and use
a very thin on-the-fly solution to perform the crud actions on them.

Ok, but what does that have to do with Stored Procs?

CustomCommandBuilder seems pretty on target with what I want. Is
there a reason it received mixed reviews or why most solutions out
there generate a complete data access layer for you?

Again, refer to the illustrious Mr. Vaughn's Article. CommandBuilders, in a
work, blow. The concurrency limitations are total dealbreakers in many
instances depending on the nature of your app. They are also performance
pigs. This will help
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp
I love my

 

[David Browne]

I guess this is actually a serious post so I'll bite.

--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

What about Security? How do you permission your dynamic SQL? I'll grant
you performance isn't that big of a difference than using Paramaterized
queries but big deal.

The same way you do your stored proceures. By creating roles and giving the
roles permission to perform operations and adding users to the roles.

ere is a great discussion on this in this weblog entry:
Ok, so having to recompile your project and redistributue it just b/c you
want to add a field to a query is not a maintenance nightmare? I use Stored
procs on a regular basis for one app which allows me to fulfull customer
requests WHILE I'm on the phone with them - just hit the refresh button and
the changes are reflected instantaneously. That couldn't be done with
inline sql.

The OP's point is that stored stored procedures have their place. But there
is no good reason to write stored procedure wrappers for insert, update and
delete from every table in your database.

Stored procedures should do multi-step or multi-table units of work, or hide
some bit of complexity from the application. For a simple insert, update or
delete against a table or view, stored procedures are just a headache to
update. Now if 90% of your data manipulation is done through stored
procedures for some good reason, then you might want to do the rest in
stored procedures too, since you're already commited to substantial amounts
of TSQL maintence. But if your applciation is designed to directly perform
inserts, updates and deletes against tables or better views, then then
there's no good reason to have a bunch of stored procedures.

Besides - the number of lines of code is virtually identical. The only real
downside that I can see to stored procs is that you can't search through
their text easily, but it took me all of about 15 minutes to build a utility
to do that for me.

.. . .

Again, refer to the illustrious Mr. Vaughn's Article. CommandBuilders, in a
work, blow. The concurrency limitations are total dealbreakers in many
instances depending on the nature of your app.

You have to deal with concurrency issues either way. The basic point is
that starting from a strongly-typed dataset, you have everything you need to
build a proper CRUD command.

And how many developers do you know who know who really understand error
handling and transactions in TSQL. Not many.

They are also performance pigs.

The OP was not talking about the built-in commandBuilders. Rather a
CustomCommandBuilder which generates the CRUD commands from the
strongly-typed DataTable's metadata, rather than the metadata catalog on the
server.

David

 

[William \(Bill\) Vaughn]

Like this?
SELECT Text from SYSCOMMENTS WHERE Charindex('Author',text) > 0



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

 

[William Ryan eMVP]

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

The same way you do your stored proceures. By creating roles and giving the
roles permission to perform operations and adding users to the roles.

And that's no more work than granting execute to a proc?

The OP's point is that stored stored procedures have their place. But there
is no good reason to write stored procedure wrappers for insert, update and
delete from every table in your database.

Stored procedures should do multi-step or multi-table units of work, or hide
some bit of complexity from the application. For a simple insert, update or
delete against a table or view, stored procedures are just a headache to
update. Now if 90% of your data manipulation is done through stored
procedures for some good reason, then you might want to do the rest in
stored procedures too, since you're already commited to substantial amounts
of TSQL maintence. But if your applciation is designed to directly perform
inserts, updates and deletes against tables or better views, then then
there's no good reason to have a bunch of stored procedures.

-I guess it's just a matter of opinion - I don't think this 'complexity' is
any issue at all.

. . .
in

You have to deal with concurrency issues either way. The basic point is
that starting from a strongly-typed dataset, you have everything you need to
build a proper CRUD command. -Ok

And how many developers do you know who know who really understand error
handling and transactions in TSQL. Not many.

-I'd agree that everyone doesn't understand these facets but that's hardly a
real issue for any professional developer using Sql Server. I don't have
any numbers either way, but if you are programming against SQL Server it's
something you should know - or learn in a hurry.

 

[philc]

I think CustomCommandBuilder is right on the money. The worst thing about
the built-in CommandBuilders is that they will query the database metadata
catalog each time you use them. But getting this information from your
typed datasets removes the only serious problem with the CommandBuilder
concept.

Oh, and look at this thread for a tool I use to automate the extraction of
strongly-typed dataset schemas from a SqlServer database.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&[email protected]

David

Right. Witht eh CustomCommandBuilder the performance is on par with
stored procs for the simple CRUD operations. I would still use stored
procs for the complex joins.

I just checked out your tool and it works really nicely! I'm going to
try to modify it to fetch the relations too. I have found that making
use of the ADO.NET relations frees me from having to use views as much
as before. Then this would be a perfect complement to the
CustomCommandBuilder

Philippe

 

[philc]

The same way you do your stored proceures. By creating roles and giving the
roles permission to perform operations and adding users to the roles.

That's what I'm planning on. I would set explicit permissions on each
item on a role by role basis. This is really simple and intuitive.

The OP's point is that stored stored procedures have their place. But there
is no good reason to write stored procedure wrappers for insert, update and
delete from every table in your database.

Stored procedures should do multi-step or multi-table units of work, or hide
some bit of complexity from the application. For a simple insert, update or
delete against a table or view, stored procedures are just a headache to
update. Now if 90% of your data manipulation is done through stored
procedures for some good reason, then you might want to do the rest in
stored procedures too, since you're already commited to substantial amounts
of TSQL maintence. But if your applciation is designed to directly perform
inserts, updates and deletes against tables or better views, then then
there's no good reason to have a bunch of stored procedures.

You are correct (again). In that blog entry (first link I provided)
which has a long discussion on the pros and cons of stored procs, most
people either used stored procs all the time or none at all. I'm
suggesting taking the best of both worlds in a hybrid system - stored
procs when needed but using code generation to handle the cases where
all you're doing are CRUD operations on a single & simple table.

When a database schema change is made, then you just have to update
the typed dataset. This takes almost no time especially with that
utility you provided.

 

[philc]

Ok, so having to recompile your project and redistributue it just b/c you
want to add a field to a query is not a maintenance nightmare? I use Stored
procs on a regular basis for one app which allows me to fulfull customer
requests WHILE I'm on the phone with them - just hit the refresh button and
the changes are reflected instantaneously. That couldn't be done with
inline sql.

If I add a new column to one of my tables, I would have to recompile
and redistribute but I'm not sure how I could avoid that anyways.
Well maybe that would be possible if you didn't use a strongly typed
dataset or you never explicitly reference specific columns on the
client side.

If we're talking about a more complicated view with joins, then I
would use a stored procedure but I would still need a recompile when
using strongly typed datasets. If we're talking about business logic,
that's usually in my business logic layer and not the database.


http://www.alternex.net/

 

[Miha Markic [MVP C#]]

Phil,

I recommend you to also take a look at CodeSmith, a free template based code
generator:
http://www.ericjsmith.net/codesmith/