将COMPLEX存储过程转换为C＃& ADO.Net [英] Converting a COMPLEX stored procedure into C# & ADO.Net

问题描述

这不是SP的好坏的问题。还是用C＃编写SQL语句是好是坏。

This is NOT a question on whether SPs are good or bad. Or whether writing SQL statements in C# is good or bad.

很快，我们将开始一个新项目的工作，这是一个典型的库存/账单管理系统。这将使用.Net和C＃作为语言进行开发。该数据库尚未完成。

Very soon we start work on a new project which is a typical inventory/billing management system. This will be developed using .Net and C# as the language. The database is not yet finalized.

在此应用程序中不允许使用存储过程，并且存储过程将具有中等到复杂的数据库操作。现在我必须使用C＃和ADO.Net来编写可以轻松地使用SP编写的逻辑。

Use of stored procedures is NOT ALLOWED in this application and it will have medium to complex database operations. So logic that I could have easily written using and SP, is now something I will have to write using C# and ADO.Net.

现在拿一个非常假设的用例，客户已选择5项商品，并准备在柜台上产生账单…。通常将执行以下数据库操作：

Now taking a very HYPOTHETICAL USE CASE where a customer has selected 5 item and ready to get the bill generated at the counter …. this will typically have the following database operations:

• 检查库存表以查看是否有5个订单项都可用（数量充足）。


• 如果没有可用的项目–从帐单中删除该项目并更新BILL表


• 库存表中的数量字段将


• 警告/更新其他表格是指某项的阈值数量低于特定点。

• Checking the INVENTORY table to see if all the 5 line items are available (sufficient quantity).
• If any of the items is not available – drop the item from the bill and update the BILL table
• The quantity field of the INVENTORY table will be updated and the quantity field will be deducted with items which are sold.
• Alerting/Updating other table is threshold quantity of an item goes below certain point.

现在来看这种情况，在SP中可以轻松完成所有操作。它包含许多查询，IF条件，可能的循环等，看起来像是SP的良好竞争者。
我想从专家那里得到的是：

Now looking at this scenario this could have ALL been done easily in an SP. This contains a lot of queries, IF condition, possible loops etc and looks like a good contender for a SP. What I wanted from the experts is:

1. 在C＃中编写这种代码的最佳实践是什么？ ADO.Net。在C＃中，我们通常可以使用ADO.Net…重新创建相同的代码。但是这是唯一的方法还是正确的方法？


2. 我读过很多人说存储过程很糟糕……。但是当考虑到这样的复杂方案时……您不觉得存储过程更适合这种情况。

推荐答案

在C＃&中编写此类代码的最佳做法是什么？ ADO.Net。在C＃中，我们通常可以使用ADO.Net…重新创建相同的代码。但是这是唯一的方法还是正确的方法？

Are any best practices for writing such code in C# & ADO.Net. In C# we can typically recreate the same code using ADO.Net…. BUT IS THIS THE ONLY WAY OR THE CORRECT WAY?

所以我认为根据您的问题，使用类似以下内容会更快乐 Dapper 用于数据库访问。如果您不知道，Dapper是一个非常轻量级的数据库访问库，它甚至可以支持一些更复杂的需求（例如，在一次往返中返回多个结果集，然后将这些结果反序列化为POCO类）。

So I think based off of your question you would be much happier using something like Dapper for your database access. Dapper, in case you don't know, is an extremely lightweight database access library that supports even some of the more complex needs (like returning more than one result set in a single round-trip and then deserializing those results into POCO classes).

简而言之，借助Dapper，您可以灵活地查询数据库 您想要的方式 ，它是<

In short, with Dapper, you have the flexibility to query the database exactly how you want and it's ridiculously fast.

现在，对于一个更具体的示例，Dapper扩展了 IDbConnection 接口，因此与选择哪个数据库无关紧要：

Now, for a more concrete example, Dapper extends the IDbConnection interface, so it's not going to matter what database you choose:

该数据库尚未完成。

The database is not yet finalized.

它很容易参数化，因此不受 SQL注入：

it's easily parameterized, so it's not subject to SQL Injection:

var parms = new { ID = 1, Bar1 = "Hello" };
var foo = connection.Query<Foo>(
    "SELECT Bar1, Bar2 FROM Foo WHERE ID = @ID AND Bar1 = @Bar1",
    parms);

它支持事务，因此您仍可以将事务作为单个事务进行管理：

and it supports transactions, so you can manage the operation as a single transaction still:

var tran = connection.BeginTransaction();
var parms = new { ID = 1, Bar1 = "Hello" };
var foo = connection.Query<Foo>(
    "SELECT Bar1, Bar2 FROM Foo WHERE ID = @ID AND Bar1 = @Bar1",
    parms, tran);

并且说实话，尽管实体框架真的很受欢迎，并且微软正在倾销<大量资金投入他们的银行，使用Dapper之后，我不再购买它们。这就是为什么。一般而言，最好利用数据库来实现最佳性能，而Dapper允许这样做，它所要做的就是为您提供一种机制，通过该机制您可以 大幅减少 从ADO.NET中的数据库获取数据并对其执行所需的样板代码。

and to be perfectly honest, though the entity frameworks are really getting popular, and Microsoft is pouring a ton of money into theirs, I don't buy into them anymore after using Dapper. And here's why. Generally speaking it is much better to leverage the database for what it's best at, and Dapper allows that, all it's doing is giving you a mechanism by which you can drastically reduce the boiler plate code required to get data from and execute against a database in ADO.NET.

现在，这是进入存储过程参数的好方法。 / p>

Now, this is a good way to get into the argument of stored procedures.

我读过很多人说存储过程不好。但是当考虑这样的复杂情况时……您难道不认为存储过程更适合这种情况。

I have read a lot of people saying stored procedures are bad…. but when considering a complex scenario like this… don’t you feel that stored procedures are better suited in this case.

存储过程是 还不错。 而且大多数陈述这种观点的人可能来自两个角度之一：

Stored procedures are not bad. And most people that state that are probably coming from one of two angles:

1. 它们只是流行而已。


2. 它们不了解存储过程的目的和好处。 请注意： ，我不是说 愚蠢 ，而是说>

1. They are just jumping on the bandwagon.
2. They are ignorant to the purpose and benefits of a stored procedure. PLEASE NOTE: I did not say stupid, I said ignorant.

尽管人们普遍认为，存储过程仍然有效一个美好的目的。首先，要编译执行计划。其次，它们很容易被参数化以更快地进行更复杂和多级的查询-而视图和直接SQL则不是。 第三，它们更容易为DBA保护。

Stored procedures, despite popular belief, serve a wonderful purpose. First and foremost, the execution plan is compiled. Secondly, they are easily parameterized to make more complex and multi-level queries faster - whereas views and direct SQL are not. Third, they are more easily secured for DBA's.

我有一个查询，这是直接的SQL查询，因为我所在的公司当时我以为是完全相同的事情，所以我通过使它成为存储过程来将它的性能提高了98％。这为公司每月节省了2,000美元的准备购买的额外硬件！

I had a query, it was a direct SQL query because the company I worked for at the time thought the exact same thing, and I increased it's performance by 98% by making it a stored procedure. That saved the company $2,000 a month in additional hardware they were ready to buy!

正如我之前所说的， 使用数据库来发挥自己的优势。 我认为这经常会丢失，因为我看到了很多试图使用C＃和.NET框架对500,000个对象进行排序的人，他们不知道为什么它不够快- 他们应该已经在数据库服务器上对它们进行了排序！ 这是真的擅长的一件事！

As I stated before, use the database for what it's good at. I think this is often lost because I see a lot of people trying to sort 500,000 objects using C# and the .NET framework and they can't figure out why it's not fast enough - they should have sorted them on the database server! That's one thing it's really good at!

不要让人们带领您走上一条技术无用且不好的道路，因为很多人会告诉您触发器是不好的，所以但是当出于正确的原因使用触发器时，它们是完美的！

Don't let people lead you down the path that a technology is useless, and bad, because many will tell you triggers are bad, but when used for the right reason they are perfect!

这篇关于将COMPLEX存储过程转换为C＃&amp; ADO.Net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！