如何在C＃.NET中保存存储过程DRY？

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

我们对数据库的每个查询都使用存储过程。这似乎令人难以置信的不干:

Design the table

设计表格

Design CRUD operation SPs for that table

为该表设计CRUD操作SP

Design code (preferably a class) to fill parameters and execute CRUD SPs

设计代码(最好是一个类)来填充参数和执行CRUD SP

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.

如果我们添加单个列或更改数据类型,我们必须在.NET中的类中编辑表,一些SP和一些函数。

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:

结合Vinko的想法,我找到了这个。这里有一些代码(在C#中),我想出了那些需要它的代码:

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 */ }

8 个解决方案

#1

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.

避免修改至少SP的一个提示是将它们编写为使用“内省”,即从内部表或information_schema视图中推断出列名和数据类型。

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.

编写代码的代码更复杂,但每次表更改时都要避免修改它,并且可以在其余的SP中重用它。

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.

创建一个将为您描述表的SP,例如使用您将从其余SP调用的临时表(colname varchar,type varchar)。

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.

顺便说一句,如果您的查询很复杂,这可能变得非常复杂甚至不可行,但另一方面,如果它们不是,它可以为您节省很多麻烦。

#2

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

我建议使用代码生成工具(如NetTiers)生成CRUD层。

#3

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

OOP设计原则适用于过程代码,而不是声明性代码。特别是,重用SP是非常有问题的。

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.

基于CRUD生成器的UI设计是有名的。另一种明确将用户转变为数据录入员的方法。如果您使用这些,请确保在它们生成的内容和用户必须处理的内容之间有一个很好的抽象层。

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.

如果我们添加单个列或更改数据类型,我们必须在.NET中的类中编辑表,一些SP和一些函数。

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

当然,听起来您的数据库设计正在规定您的OOP要求。而是从另一个方向开始。

#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 ...

一旦SQL超出单个表,所有这些元查询方法就会在其轨道中消失。或者想要一个计算列。要么 ...

#5

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 :

以下是我处理数据库和crud对象设计的方法:

I create the data model

我创建了数据模型

I create a view for each table

我为每个表创建一个视图

I create insert, update, & delete procs for each table.

我为每个表创建插入,更新和删除过程。

All my C# code points to the views and procs.

我所有的C#代码都指向了视图和过程。

This allows me to :

这允许我:

Have a highly flexible query target (the view)

拥有高度灵活的查询目标(视图)

Query against the views in any manner I need without redundancy.

以任何我需要的方式查询视图而不需要冗余。

Prevent direct access to the tables via database security

防止通过数据库安全性直接访问表

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.

有一个代表目标表的视图可能会处理很多查询,即使它没有,最糟糕的情况是你需要专门为查询创建一个视图,这相当于为它创建一个proc,所以没有不好的一面。

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? ;-)

我听说人们建议使用存储过程来防止SQL注入攻击,但是如果在查询视图时使用参数化查询,这无论如何都不会成为问题。 ......我们总是以任何方式使用参数化查询......对吗? ;-)

#6

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.

您使用的“DeriveParameters”方法确实有效,但每个请求涉及2次数据库跳转。这种方法会受到性能影响。 Microsoft的数据访问应用程序块SqlHelper类将通过执行完全相同的“内省”来缓解此问题,但可选地缓存参数以供重用。这样您就可以创建单行SP调用而无需重复的“goo”设置参数等。

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

#7

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.

我不认为这真的属于DRY指南。这只是关于持久性,如果您手动执行#3,那么您应该考虑采用一种使这更容易的工具集。 LINQ to SQL是我个人的最爱,但有很多。

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.

您的#2也可以轻松实现自动化。减少整体工作所需的1)设计数据模型(概念)2)在持久层实现它(创建表,或添加列)3)在应用程序级别使用它。

#8

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.

有些表不具有CRUD,不应从您的应用程序访问,并且是数据库实现模型的工件。特别是,您的应用程序不应访问多对多链接表,它们应由数据库通过存储过程进行管理。

#1