How to keep your Stored Procedures DRY in C#.NET?

Question

We're using Stored Procedures for every query to the DB. This seems incredibly un-DRY:

1. Design the table
2. Design CRUD operation SPs for that table
3. Design code (preferably a class) to fill parameters and execute CRUD SPs

If we add a single column, or change a datatype, we have to edit the table, a handful of SPs, and a handful of functions in a class within .NET.

What are some tips for reducing this duplication?

UPDATE:

In combination with Vinko's idea, I found this. Here's a bit of code (in C#) that I came up with for those that need it:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySQLConnString"].ConnectionString);
SqlCommand comm = new SqlCommand("NameOfStoredProcedure", conn);
comm.CommandType = CommandType.StoredProcedure;

conn.Open();
SqlCommandBuilder.DeriveParameters(comm);
conn.Close();

foreach (SqlParameter param in comm.Parameters)
{ /* do stuff */ }

Answer 1

I suggest using a code-generation tool, such as NetTiers to generate your CRUD layer.

Answer 2

OOP design principles are for procedural code, not declarative code. In particular, reusing SP's is highly problematic.

UI designs based on CRUD generators are well-named. Another way to explicitly turn users into data entry clerks. If you employ these, make sure you have a great abstraction layer between what they produce, and what the users have to deal with.

If we add a single column, or change a datatype, we have to edit the table, a handful of SPs, and a handful of functions in a class within .NET.

Sure sounds like your database design is dictating your OOP requirements. Start from the other direction instead.

Answer 3

One tip to avoid modification of at least the SPs is writing them to use 'introspection', that is, deducing the column names and datatypes from the internal tables or the information_schema views.

It's more complex code to write, but it'll avoid having to modify it each time the table changes, and it can be reused in the rest of the SPs.

Create a single SP that will describe the tables for you, for instance using a temp table (colname varchar, type varchar) that you'll call from the rest of SPs.

By the way, this can get very complex and even unfeasible if your queries are complex, but on the other hand, if they are not, it can save you a lot of trouble.

Answer 4

All these metaquery approaches die in their tracks as soon as the SQL gets beyond a single table. Or want a calculated column. Or ...

Answer 5

I don't think this really falls under the DRY guideline. This is simply about persistence, and if you're doing #3 manually then you should look at adopting one of the toolsets that make this easier. LINQ to SQL is my personal favorite, but there are many.

Your #2 can easily be automated as well. Reducing your overall work required to 1) design the data model (in concept) 2) implement it at the persistence layer (create your table, or add your column) 3) make use of it at the application level.

Answer 6

Personally, I'm not a big fan of putting my querying code into stored procedures. With the exception of highly complex things, it just seems like redundant overkill.

Here's how I handle my database and crud object design :

1. I create the data model
2. I create a view for each table
3. I create insert, update, & delete procs for each table.
4. All my C# code points to the views and procs.

This allows me to :

1. Have a highly flexible query target (the view)
2. Query against the views in any manner I need without redundancy.
3. Prevent direct access to the tables via database security
4. Abstract the data model in the event I ever need to refactor the underlying data model without breaking my code (I know, this could have performance costs)

Having one view representing the target table will probably handle many queries, and even if it doesn't, the worst that will happen is you need to create a view specifically for the query, which is the equivalent to creating a proc for it, so there's no down side.

I hear people recommending using stored procedures to prevent SQL Injection attacks, but if you use parameterized queries when querying your view, this won't be an issue anyway. ... and we always use parameterized queries any way ... right? ;-)

Answer 7

There are some tables which will not have CRUD, and should not be accessible from your application and are artifacts of the database implementation model. Particularly, many-to-many link tables should not be accessed by your application, they should be managed by the database through stored procedures.

Answer 8

The 'DeriveParameters' approach you use does work, but involves 2 database trips for each request. There will be a performance hit with this approach. Microsoft's Data Access Application Block SqlHelper class will mitigate this by doing the exact same 'introspection', but optionally caching parameters for re-use. This will let you create single-line SP calls with no repetitive "goo" setting up parameters, etc.

http://msdn.microsoft.com/en-us/library/cc309504.aspx