何时放置业务逻辑DAL ADO.Net设计 [英] Wher to place the buiness logic DAL ADO.Net Design

问题描述

你好，
我想设计一个用于库存管理的应用程序VB.Net/MySQL.
在数据库上，我有两个表
库存(参考，数量))
变动(参考，日期，金额，输入/输出)
每当我有股票输入或输出时，我都必须在表格移动中插入相应的行.表Stock中的金额必须根据添加的行进行更新.

我有两种选择(据我所知)来设计此应用程序:
1-在表Movement上创建触发器，该触发器将更新表Stock的数量
2-使用本地功能(在我的.Net应用程序中)管理问题

第二个需求是确定什么是存货充足".
我必须具有一个将项目列表(参考，金额)作为输入的函数
并返回一个布尔值，以确定库存是否足够.

同样的问题:在何处放置业务逻辑"
1-PL/SQL中数据库中的函数
2-.Net应用程序中的本地功能(我必须加载整个库存表并在本地执行验证)

您认为最佳选择是什么?

预先感谢您的建议

Hello,
I want to design an application VB.Net / MySQL for inventory management.
On the database I have two tables
Stock (Ref, Quantity))
Movement (Ref, Date, Amount, input / output)
Every time I have an input or output of stock I have to insert the corresponding row in the table movement. The amounts in the table Stock must be updated in accordance with the lines added.

I have two choices (to my knowledge) to design this application:
1 - Create a trigger on the table Movement, which updates the amount of the table Stock
2 - Manage the problem with local functions (in my. Net application)

A second need is to determine is what the stock is "sufficient" or not.
I must have a function that has as input a list of items (Ref, Amount)
and returns a Boolean value to determine if the stock is sufficient or not.

The same concerns: where to place the "business logic"
1 - A function in the database in PL / SQL
2 - A local function in my .Net application (i have to load the entire stock table and performs the verification locally)

What choice you think is best?

Thank you in advance for your advice

推荐答案

我的建议是永远不要将业务逻辑放在数据库层中，而要放在代码中.避免触发器等，并保持数据库层干净.

这样您就可以了:
-更好地控制自己在做什么
-它更容易更改
-您不必在存储的proc中执行复杂的逻辑
-您未锁定供应商(以后可以更改数据库)
-在我的测试中，速度大约提高了20％(代码与MSSQL中的存储过程相比)

My advise is to never put your business logic in your database layer, put it in your code. Avoid triggers etc. and keep your db layer clean.

This way you have :
-more control over what you are doing
-it''s simpler to change
-you don''t have to do complex logic in stored procs
-you are not locked down in a vendor (you can change your db later)
-in my tests it''s about 20% faster (code vs stored procs in MSSQL)

您好，

感谢您的回答，我主要关心的是数据的一致性". (我不确定该称谓)，因为查询是从.Net应用程序传递到数据库服务器的时间.

例如，如果客户请求结帐，则购物车由列表(参考，金额)组成，操作如下:

1-结帐请求
2-验证足够数量的物品的可用性
3-更新表格(金额值)

当客户检查可用性并且在完成更新表之前，该问题就会出现，该表已被另一位用户修改，这将毫无意义地进行可用性检查.尤其是在业务逻辑位于客户端的情况下，就会出现此问题.因为时间t1和t3(案例1)之间有联系远程数据库服务器和检索数据所需的时间.而且考虑到数据库托管在Internet上，间隔可能会很大，这会使问题经常发生.

这个问题在下面的序列图中进行了图解说明.


但是，在服务器上使用PL/SQL过程(情况2)的情况下，时间间隔t2-t1相对较短，您可以锁定更多相关行.

您如何看待?

Hello,

Thank you for the answer, my major concern is the "coherence" of data. (I’m not sure of the appellation), due to the delivery time of the queries from the .Net application to the database server.

For example, if a customer request a checkout, the cart consists of a list(Reference, Amount), the operation proceeds as follows:

1-Checkout request
2-Verification of the availability of items in sufficient quantities
3-Update the table (The amount values)

The problem appears when a customer check availability and before the update tables is done , the table is modified by another user which will make availability checked above with no mean. This problem appears especially in cases where the business logic is at the client. Because the time t1 and t3 (Case 1) are separated by the time needed to contact the remote database server and retrieve the data. And given that the database is hosted on the internet the interval can be quite large which will make the problem happening often.

This is problem is schematized in the sequence diagram below.


Yet in the case PL/SQL procedures are used on the server (case2) the interval t2-t1 is relatively short and more you can lock the concerned line.

What do you think?

这篇关于何时放置业务逻辑DAL ADO.Net设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！