ASP。NET在存储过程中或在Gridview中的Databind中执行计算

Hey so I have a stored procedure populating a gridview

我有一个填充gridview的存储过程

and in my stored procedure I have two columns such as

在存储过程中，我有两列

Total_Price DataType Money

and

和

Discount DateType decimal(18, 0)

and I want to display a derived column called Actual Price which is

我想显示一个衍生列，叫做实际价格

Total_Price - Discount

is it best to create this column in the stored procedure or calculate on load in the gridview ?

最好是在存储过程中创建此列，还是在gridview中计算负载?

Heres my stored procedure at the moment

下面是我的存储过程

    SELECT     f.supplier_name,d.product_ID_key, d.product_name AS productName, d.packsize, d.tradePrice, d.IPU_code, d.EAN_code, c.discount, e.stock_indicator
FROM         aw_customer AS a INNER JOIN
                      aw_cust_contract AS b ON a.cust_ID_key = b.cust_ID_key INNER JOIN
                      aw_contract_line AS c ON b.contract_ID_key = c.contract_ID_key INNER JOIN
                      aw_product AS d ON c.product_ID_key = d.product_ID_key INNER JOIN
                      aw_stock AS e ON d.product_ID_key = e.product_ID_key INNER JOIN
                      aw_supplier AS f ON d.supplier_ID_key = f.supplier_ID_key
WHERE     (a.cust_ID_key = @customerId)
ORDER BY d.product_name

2 个解决方案

#1

I would calculate this at Stored Procedure level, so that any calls to this Stored Procedure would return the same results (you haven't got to do the math on other gridviews etc in the future if they are calling the same Stored Procedure)

我将在存储过程级别上计算它，以便对这个存储过程的任何调用都将返回相同的结果(如果将来调用相同的存储过程，您不需要对其他gridview等进行计算)

In situations like this, I've often created a SQL View so that the calculations are done within the view, and then multiple Stored Procedures can call this View and display the data like:

在这种情况下，我经常创建一个SQL视图，以便在视图中进行计算，然后多个存储过程可以调用此视图并显示如下数据:

SELECT Total_Price
       ,Discount
       ,Actual_Price
FROM [v_TableA]

http://msdn.microsoft.com/en-us/library/aa214068%28v=sql.80%29.aspx

See this article for details of Indexing Views, which will also improve performance:

有关索引视图的详细信息，请参阅本文，这也将提高性能:

http://technet.microsoft.com/en-us/library/cc917715.aspx

#2

Any way doing this on the data base level (in SP) will significantly improve a performance of data calculation but on other hand adding a new column increases a network load due to increased data amount.

在数据基级别上(在SP中)这样做将显著提高数据计算的性能，但另一方面，增加一个新列会增加网络负载，因为数据量增加了。

So decide yourself what is more important for your application.

所以，你要决定什么对你的申请更重要。

#1