SQL Query to C#

本文关键字：to Query SQL | 更新日期: 2023-09-27 18:06:48

我正在写这个查询，我想转换到c#，但我得到一个错误。

错误:

关键字'SELECT'附近语法错误。

'Products'附近语法错误。

这是我想要运行的查询。

Select    
      [ID]
      ,[ProductTypeID]
      ,[SeriesID]
      ,[PartNumber]
      ,[Title]
      ,[SEOFriendlyURLTitle]
      ,[HTMLDescription]
      ,[HTMLValueAdded]
      ,[RoHSCompliant]
      ,[ULCompliant]
      ,[CECompliant]
      ,[Series]
      ,[BUSINESS_UNIT]
      ,[PACKAGING_TYPE]
      ,[PACK_QTY]
      ,[MOQ]
      ,[ORDER_MULTIPLE]
      ,[LEAD_TIME_WEEKS]
      ,[INTERNATIONAL_HARMONIZE_CODE]
      ,[ECCN_NUMBER]
      ,[COUNTRY_OF_ORIGIN]
      ,[IS_PART_STATIC_SENSITIVE]
      ,[IS_PART_LEAD_PB_FREE]
      ,[MOISTURE_SENSITIVITY_LEVEL_MSL]
      ,[REGISTERABLE]
      ,[TAPE_WIDTH]
      ,[TAPE_MATERIAL]
      ,[QtyOnHand]
      ,[QtyOnSalesOrder]
      ,[QtyOnBackOrder]
      ,[ProductLine]
      ,[Reach138Compliant]
      ,[ConflictMinerals]
      ,[WebEnabled]
      ,[DateAdded]
      ,[UpdateDate]
      ,[Reviewed]
      ,[ReviewedBy]
      ,[Deleted]
      ,[Book]
      ,[CustomSort]
      ,[ONEK]
      ,[FIVEK]
      ,[TENK]
      ,[TWENTYFIVEK]   
      ,[Fifty]
      ,[OneHundred]
      ,[FiveHundred]                                 
FROM    Products.Products
Join
(SELECT Products.Prices.ProductID, 
    Max(IIf(Products.Prices.Code='ONEK',Products.Prices.Price,Null)) AS ONEK, 
    Max(IIf(Products.Prices.Code='FIVEK',Products.Prices.Price,Null)) AS FIVEK, 
    Max(IIf(Products.Prices.Code='TENK',Products.Prices.Price,Null)) AS TENK, 
    Max(IIf(Products.Prices.Code='TWENTYFIVEK',Products.Prices.Price,Null)) AS  TWENTYFIVEK,
    Max(IIf(Products.Prices.Code='Fifty',Products.Prices.Price,Null)) AS Fifty, 
    Max(IIf(Products.Prices.Code='OneHundred',Products.Prices.Price,Null)) AS OneHundred, 
    Max(IIf(Products.Prices.Code='FiveHundred',Products.Prices.Price,Null)) AS FiveHundred
FROM Products.Prices
GROUP BY Products.Prices.ProductID
) As pp
ON Products.Products.ID = pp.ProductID
下面是c#中的查询: 
y1.CommandText = "Select [ID], [PartNumber], [Book], [HTMLDescription], [HTMLValueAdded], [RoHSCompliant], [ULCompliant],[CECompliant], [Series], [BUSINESS_UNIT], [ONEK], [FIVEK], [TENK], [TWENTYFIVEK]" +
",[Fifty], [OneHundred], [FiveHundred], [PACKAGING_TYPE], [PACK_QTY], [MOQ], [ORDER_MULTIPLE], [LEAD_TIME_WEEKS], [INTERNATIONAL_HARMONIZE_CODE], [ECCN_NUMBER], [COUNTRY_OF_ORIGIN]" +
",[IS_PART_STATIC_SENSITIVE], [IS_PART_LEAD_PB_FREE], [MOISTURE_SENSITIVITY_LEVEL_MSL], [REGISTERABLE], [TAPE_WIDTH], [TAPE_MATERIAL], [Reach138Compliant], [ConflictMinerals], [WebEnabled]" +
"FROM Products.Products" +
"Join" +
"(SELECT Products.Prices.ProductID," +
    "Max(IIf(Products.Prices.Code='ONEK',Products.Prices.Price,Null)) AS ONEK," +
    "Max(IIf(Products.Prices.Code='FIVEK',Products.Prices.Price,Null)) AS FIVEK," +
    "Max(IIf(Products.Prices.Code='TENK',Products.Prices.Price,Null)) AS TENK," +
    "Max(IIf(Products.Prices.Code='TWENTYFIVEK',Products.Prices.Price,Null)) AS  TWENTYFIVEK," +
    "Max(IIf(Products.Prices.Code='Fifty',Products.Prices.Price,Null)) AS Fifty," +
    "Max(IIf(Products.Prices.Code='OneHundred',Products.Prices.Price,Null)) AS OneHundred," +
    "Max(IIf(Products.Prices.Code='FiveHundred',Products.Prices.Price,Null)) AS FiveHundred" +
"FROM Products.Prices" +
"GROUP BY Products.Prices.ProductID" +
") As pp" +
"ON Products.Products.ID = pp.ProductID" +
"where partnumber like '6%'";

请帮忙，谢谢。

SQL Query to C#

以以下片段为例:

"FROM Products.Products" +
"Join" +

当这些字符串连接在一起时，得到:

"FROM Products.ProductsJoin"

你需要包括空格

"FROM Products.Products " +
"Join" +

您在FROM, Join, (SELECT等前面缺少空格…所以当它们被输出时，你看到的是FROM Products.ProductsJoin。您还希望使用@作为前缀，以防止转义问题。想要修改:

y1.CommandText = @"Select [ID], [PartNumber], [Book], [HTMLDescription], [HTMLValueAdded], [RoHSCompliant], [ULCompliant],[CECompliant], [Series], [BUSINESS_UNIT], [ONEK], [FIVEK], [TENK], [TWENTYFIVEK]" +
",[Fifty], [OneHundred], [FiveHundred], [PACKAGING_TYPE], [PACK_QTY], [MOQ], [ORDER_MULTIPLE], [LEAD_TIME_WEEKS], [INTERNATIONAL_HARMONIZE_CODE], [ECCN_NUMBER], [COUNTRY_OF_ORIGIN]" +
",[IS_PART_STATIC_SENSITIVE], [IS_PART_LEAD_PB_FREE], [MOISTURE_SENSITIVITY_LEVEL_MSL], [REGISTERABLE], [TAPE_WIDTH], [TAPE_MATERIAL], [Reach138Compliant], [ConflictMinerals], [WebEnabled]" +
" FROM Products.Products" +
" Join" +
" (SELECT Products.Prices.ProductID," +
    "Max(IIf(Products.Prices.Code='ONEK',Products.Prices.Price,Null)) AS ONEK," +
    "Max(IIf(Products.Prices.Code='FIVEK',Products.Prices.Price,Null)) AS FIVEK," +
    "Max(IIf(Products.Prices.Code='TENK',Products.Prices.Price,Null)) AS TENK," +
    "Max(IIf(Products.Prices.Code='TWENTYFIVEK',Products.Prices.Price,Null)) AS  TWENTYFIVEK," +
    "Max(IIf(Products.Prices.Code='Fifty',Products.Prices.Price,Null)) AS Fifty," +
    "Max(IIf(Products.Prices.Code='OneHundred',Products.Prices.Price,Null)) AS OneHundred," +
    "Max(IIf(Products.Prices.Code='FiveHundred',Products.Prices.Price,Null)) AS FiveHundred" +
" FROM Products.Prices" +
" GROUP BY Products.Prices.ProductID" +
") As pp" +
" ON Products.Products.ID = pp.ProductID" +
" where partnumber like '6%'";

只需使用逐字字符串(@)，复制您的查询并创建如下:

      string query = 
      @"Select    
      [ID]
      ,[ProductTypeID]
      ,[SeriesID]
      ,[PartNumber]
      ,[Title]
      ,[SEOFriendlyURLTitle]
      ,[HTMLDescription]
      ,[HTMLValueAdded]
      ,[RoHSCompliant]
      ,[ULCompliant]
      ,[CECompliant]
      ,[Series]
      ,[BUSINESS_UNIT]
      ,[PACKAGING_TYPE]
      ,[PACK_QTY]
      ,[MOQ]
      ,[ORDER_MULTIPLE]
      ,[LEAD_TIME_WEEKS]
      ,[INTERNATIONAL_HARMONIZE_CODE]
      ,[ECCN_NUMBER]
      ,[COUNTRY_OF_ORIGIN]
      ,[IS_PART_STATIC_SENSITIVE]
      ,[IS_PART_LEAD_PB_FREE]
      ,[MOISTURE_SENSITIVITY_LEVEL_MSL]
      ,[REGISTERABLE]
      ,[TAPE_WIDTH]
      ,[TAPE_MATERIAL]
      ,[QtyOnHand]
      ,[QtyOnSalesOrder]
      ,[QtyOnBackOrder]
      ,[ProductLine]
      ,[Reach138Compliant]
      ,[ConflictMinerals]
      ,[WebEnabled]
      ,[DateAdded]
      ,[UpdateDate]
      ,[Reviewed]
      ,[ReviewedBy]
      ,[Deleted]
      ,[Book]
      ,[CustomSort]
      ,[ONEK]
      ,[FIVEK]
      ,[TENK]
      ,[TWENTYFIVEK]   
      ,[Fifty]
      ,[OneHundred]
      ,[FiveHundred]                                 
FROM    Products.Products
Join
(SELECT Products.Prices.ProductID, 
    Max(IIf(Products.Prices.Code='ONEK',Products.Prices.Price,Null)) AS ONEK, 
    Max(IIf(Products.Prices.Code='FIVEK',Products.Prices.Price,Null)) AS FIVEK, 
    Max(IIf(Products.Prices.Code='TENK',Products.Prices.Price,Null)) AS TENK, 
    Max(IIf(Products.Prices.Code='TWENTYFIVEK',Products.Prices.Price,Null)) AS  TWENTYFIVEK,
    Max(IIf(Products.Prices.Code='Fifty',Products.Prices.Price,Null)) AS Fifty, 
    Max(IIf(Products.Prices.Code='OneHundred',Products.Prices.Price,Null)) AS OneHundred, 
    Max(IIf(Products.Prices.Code='FiveHundred',Products.Prices.Price,Null)) AS FiveHundred
FROM Products.Prices
GROUP BY Products.Prices.ProductID
) As pp
ON Products.Products.ID = pp.ProductID";

(这将节省您找出错误在您的连接，并将更可读的IMO)

相关文章：