在C#和MySQL中对变量使用查询

Using query with variables in c# and mysql
2020-10-17
  •  译文(汉语)
  •  原文(英语)

我需要执行以下语句集,当前给出致命错误我需要以下查询的查询输出,该查询使用两个变量

数据库是MySQL,语言是C#,我正在尝试使用MyReader = new MySQLCommand(此查询,连接对象)在c#代码中使用此代码.ExecuteReader()

SET @lastItem := 0, @lastValue := 0; 
SELECT CONCAT (
  DATE (t5.InventoryDate)
  ,t5.SKUorItem
  ) AS PK,
    t5.CustomerID, t5.entityID, t5.inventoryDate, t5.SKUorItem, t5.Category, t5.inventory FROM
(
 SELECT 
 '3' as CustomerID,
 '90' as entityID,
 t1.InventoryDate as inventoryDate, 
 t1.idItem as SKUorItem, 
 t4.categoryInventary as Category,
 ifnull(t1.itemQty,0) as sales, ifnull(t2.buyQty,0) as StockMove,
 @lastValue := if( @lastItem = t1.idItem, @lastValue + ifnull(t2.buyQty,0) - ifnull(t1.itemQty,0), ifnull(t2.buyQty,0) - ifnull(t1.itemQty,0) ) as inventory,
 @lastItem := t1.idItem
 FROM
 (
 select date(date) as inventorydate, idItem, sum(quantity) as itemqty 
 from subway.saleitem 
 group by idItem, date(date)
 ) as t1
 LEFT OUTER JOIN
 (
 select date(date) as InventoryDate, idItem, Sum(initialBuyQuantity) as buyqty
 FROM Subway.InvoiceStock
 GROUP BY idItem, date(date)
 ) as t2
 ON t1.InventoryDate = t2.InventoryDate and t1.idItem = t2.idItem
 INNER JOIN subway.Item as t3 on t1.iditem = t3.iditem
 LEFT JOIN subway.CategoryInventary as t4 on t3.idCategoryInventary = t4.idCategoryInventary
 order by t1.iditem, t1.InventoryDate
) t5
解决过程1

将这些查询包装为Stored ProcedureUDF.只需从c#代码中调用即可.

I need a following set of statements executed , currently it gives Fatal Error I need the query output of following query which uses two variables

Database is MySQL and language is C# I am trying to use this in c# code using MyReader=new MySQLCommand(this query ,connection object).ExecuteReader()

SET @lastItem := 0, @lastValue := 0; 
SELECT CONCAT (
  DATE (t5.InventoryDate)
  ,t5.SKUorItem
  ) AS PK,
    t5.CustomerID, t5.entityID, t5.inventoryDate, t5.SKUorItem, t5.Category, t5.inventory FROM
(
 SELECT 
 '3' as CustomerID,
 '90' as entityID,
 t1.InventoryDate as inventoryDate, 
 t1.idItem as SKUorItem, 
 t4.categoryInventary as Category,
 ifnull(t1.itemQty,0) as sales, ifnull(t2.buyQty,0) as StockMove,
 @lastValue := if( @lastItem = t1.idItem, @lastValue + ifnull(t2.buyQty,0) - ifnull(t1.itemQty,0), ifnull(t2.buyQty,0) - ifnull(t1.itemQty,0) ) as inventory,
 @lastItem := t1.idItem
 FROM
 (
 select date(date) as inventorydate, idItem, sum(quantity) as itemqty 
 from subway.saleitem 
 group by idItem, date(date)
 ) as t1
 LEFT OUTER JOIN
 (
 select date(date) as InventoryDate, idItem, Sum(initialBuyQuantity) as buyqty
 FROM Subway.InvoiceStock
 GROUP BY idItem, date(date)
 ) as t2
 ON t1.InventoryDate = t2.InventoryDate and t1.idItem = t2.idItem
 INNER JOIN subway.Item as t3 on t1.iditem = t3.iditem
 LEFT JOIN subway.CategoryInventary as t4 on t3.idCategoryInventary = t4.idCategoryInventary
 order by t1.iditem, t1.InventoryDate
) t5
Solutions1

Wrap these queries into an Stored Procedure or UDF. Simply call that from c# code.

转载于:https://stackoverflow.com/questions/30816827/using-query-with-variables-in-c-sharp-and-mysql

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入