C#SQL参数限制

本文关键字：参数 C#SQL | 更新日期: 2023-09-27 18:25:51

我的.Net应用程序中有以下SQL命令：

sqlCommand.Text = "DECLARE @method NVARCHAR(MAX);
                   SET @method = ' WITH selectRows AS (SELECT *, row=ROW_NUMBER() OVER(ORDER BY(SELECT 1)) FROM [' + @param + ']) SELECT ' + @param_2 + ' FROM selectRows WHERE row BETWEEN 0 AND 30;'
                   EXEC(@method);";
sqlCommand.Parameters.AddWithValue("@param", tableName);
sqlCommand.Parameters.AddWithValue("@param_2", columnString);

只要'@param_2'很长，查询就不会执行。假设列字符串如下所示：

[class],[partnr],[accessoriesidentifier],[canbelinedup],[certificate],[certificate_ce],[certificate_ul],[certificate_vde],[codeletter],[construction],[craftcooling],[craftelectro],[craftfluid],[crafthydraulic],[craftlubrication],[craftmechanic],[craftpneumatic],[craftprocess],[create],[depth],[depthspacingfront],[depthspacingrear],[description1],[description2],[description3],[discount],[ecabinetmacro],[erpnr],[externaldocument1],[externaldocument2],[externaldocument3],[externalplacement],[functiongroup],[graphicmacro],[groupnumber],[groupsymbolmacro],[height],[heightspacingabove],[heightspacingbelow],[identcode],[identtype],[isaccessory],[lastchange],[lifetime],[macro3d],[maintenance],[manufacturer],[mountinglocation],[mountingspace],[note],[ordernr],[packagingprice1],[packagingprice2],[packagingquantity],[picturefile],[piecetype],[priceunit],[productgroup],[productsubgroup],[producttopgroup],[purchaseprice1],[purchaseprice2],[quantityunit],[reportid],[salesprice1],[salesprice2],[snapheight],[snapheightmiddleoffset],[spare],[stress],[supplier],[terminal],[typenr],[uniqueid],[usage],[wear],[weight],[width],[widthspacingleft],[widthspacingright],[barcount],[bardistance],[bargeometry],[barmountingplatedistance],[bottompaneldepth],[bottompaneldistance],[bottompanelprojectionback],[bottompanelprojectionfront],[bottompanelprojectionleft],[bottompanelprojectionright],[busbarholderpartnr],[busbarholdervariant],[busbarrailpartnr],[busbarrailvariant],[deliverylength],[dooroffsetright],[dooroffsettop],[doorthickness],[doortrabbet],[doortype],[hingeposition],[insertpointoffsetx],[profiledepth],[profiledistance],[profileheight],[rearpaneldistance],[rearpaneldpepth],[rearpanelprojectionbottom],[rearpanelprojectionleft],[rearpanelprojectionright],[rearpanelprojectiontop],[sidepaneldepth],[sidepaneldistance],[sidepanelprojectionback],[sidepanelprojectionbottom],[sidepanelprojectionfront],[sidepanelprojectiontop],[toppaneldistance],[toppaneldpepth],[toppanelprojectionback],[toppanelprojectionfront],[toppanelprojectionleft],[toppanelprojectionright],[vprofiledepth],[vprofilewidth],[wallthickness],[widthbottom],[widthtop],[variant],[adjustrange],[adressrange],[advancecontacts],[airgap],[assemblyspreaded],[awgfrom],[awgtill],[bendingradius],[cabledesignation],[cabledisplayform],[cablelength],[cabletype],[cableweight],[coding],[color],[connection],[connectioncrosssection],[connectiondesignation],[connectionmethod],[contactarrangement],[contacttype],[coppernumber],[creepagedistance],[crosssectionfrom],[crosssectiontill],[currentcsa],[currentiec],[currentul],[degofprotection],[design],[doordepth],[doorheight],[doormountingspace],[doorwidth],[electricalcurrent],[electricalpower],[firmwareversion],[flow],[holdingpower],[innerdiameter],[intrinsicsafety],[material],[norm],[outerdiameter],[paneldepth],[panelheight],[panelmountingspace],[panelwidth],[pincount],[pipeclass],[plcdeviceid],[plcisbuscoupler],[plcisbusdistributor],[plciscpu],[plcispowersupply],[plcobjectdescription],[plctype],[powerdissipation],[pressure],[pressurelevel],[shortcircuitresistant],[standardinvers],[strokelength],[symbolfile],[symbolnr],[technicalcharacteristics],[thread],[triggercurrent],[voltage],[voltagecsa],[voltageiec],[voltagetype],[voltageul],[widthrating],[wirecount],[wirecrosssection],[wirecrosssectionanddiameter],[wirecrosssectionunit],[variant_1],[characteristics],[connectiondescription],[connectiondesignation_1],[description],[functiondefcategory],[functiondefgroup],[functiondefid],[hasled],[hasplugadapter],[idx1],[idx2],[intrinsicsafety_1],[nesteddevicetag],[pos],[safetyrelevant],[symbol],[symbolmacro],[terminalfunction],[terminalnr],[partnr_1],[variant_2],[count],[parentvariant],[pos_1],[22235.0],[22236.0],[22237.0],[22238.0],[22239.0],[22240.0],[22241.0],[22196.1],[22196.2],[22158.1],[22158.2],[22159.1],[22159.2],[22195.1],[22195.2],[22228.1],[22228.2],[22228.3],[22228.4],[22228.5],[22228.6],[22228.7],[22228.8],[22228.9],[22228.10]

查询不会在服务器上执行（SQL server 2005）。当我删除10列时，查询执行时不会出现任何问题。我使用sql探查器来查找sql服务器从我的应用程序接收到的任何问题，但没有发现任何问题。我真的很困惑为什么它不起作用。

我试图实现的是从数据库表中获得所有列的结果集，而不包含行列。因为如果我将SELECT@param_2更改为SELECT*，我将获得所有列加上显示行号的额外列。为了去掉这个行号，我想我只需要将所有列名发送到查询的SELECT语句中。然而，我现在遇到了上述问题。我希望有人知道一个解决方案，可以从上面解决问题，或者如何使用比我使用的更好的查询来获得除行号之外的所有列的结果集。

附言：（"失败"）列字符串的大小为3612个字符。我对SQL本身没有太多经验。如果我犯了愚蠢的错误，请原谅。

编辑：列名取决于用户，因此不能在查询中对其进行硬编码。

C#SQL参数限制

字符串文字和传递的参数本身都不是nvarchar(max)类型。因此，你会对这句关于字符串串联的话感到不满：

如果字符串串联的结果超过8000字节的限制，则结果将被截断。但是，如果连接的字符串中至少有一个是大值类型，则不会发生截断。

我可能要做的是将参数更改为明确的nvarchar(max):

sqlCommand.Parameters.Add("@param_2", SqlDbType.NVarChar,-1).Value =
     columnString;

或者你可以用来修复它

SET @method = CONVERT(nvarchar(max),
' WITH selectRows AS (SELECT *, row=ROW_NUMBER() OVER(ORDER BY(SELECT 1)) FROM [') + ...

也许这并不能直接回答这个问题，但它可能会有所帮助。为什么您需要使用dynamicoc sql？为什么你不能使用这个：

string sql = @"
WITH CTE AS 
( 
         SELECT   *, 
                  row=Row_number() OVER (ORDER BY (SELECT 1)) 
         FROM     dbo.TableName 
SELECT [class],[partnr],[accessoriesidentifier],[canbelinedup],[certificate],[certificate_ce],[certificate_ul],[certificate_vde],[codeletter],[construction],[craftcooling],[craftelectro],[craftfluid],[crafthydraulic],[craftlubrication],[craftmechanic],[craftpneumatic],[craftprocess],[create],[depth],[depthspacingfront],[depthspacingrear],[description1],[description2],[description3],[discount],[ecabinetmacro],[erpnr],[externaldocument1],[externaldocument2],[externaldocument3],[externalplacement],[functiongroup],[graphicmacro],[groupnumber],[groupsymbolmacro],[height],[heightspacingabove],[heightspacingbelow],[identcode],[identtype],[isaccessory],[lastchange],[lifetime],[macro3d],[maintenance],[manufacturer],[mountinglocation],[mountingspace],[note],[ordernr],[packagingprice1],[packagingprice2],[packagingquantity],[picturefile],[piecetype],[priceunit],[productgroup],[productsubgroup],[producttopgroup],[purchaseprice1],[purchaseprice2],[quantityunit],[reportid],[salesprice1],[salesprice2],[snapheight],[snapheightmiddleoffset],[spare],[stress],[supplier],[terminal],[typenr],[uniqueid],[usage],[wear],[weight],[width],[widthspacingleft],[widthspacingright],[barcount],[bardistance],[bargeometry],[barmountingplatedistance],[bottompaneldepth],[bottompaneldistance],[bottompanelprojectionback],[bottompanelprojectionfront],[bottompanelprojectionleft],[bottompanelprojectionright],[busbarholderpartnr],[busbarholdervariant],[busbarrailpartnr],[busbarrailvariant],[deliverylength],[dooroffsetright],[dooroffsettop],[doorthickness],[doortrabbet],[doortype],[hingeposition],[insertpointoffsetx],[profiledepth],[profiledistance],[profileheight],[rearpaneldistance],[rearpaneldpepth],[rearpanelprojectionbottom],[rearpanelprojectionleft],[rearpanelprojectionright],[rearpanelprojectiontop],[sidepaneldepth],[sidepaneldistance],[sidepanelprojectionback],[sidepanelprojectionbottom],[sidepanelprojectionfront],[sidepanelprojectiontop],[toppaneldistance],[toppaneldpepth],[toppanelprojectionback],[toppanelprojectionfront],[toppanelprojectionleft],[toppanelprojectionright],[vprofiledepth],[vprofilewidth],[wallthickness],[widthbottom],[widthtop],[variant],[adjustrange],[adressrange],[advancecontacts],[airgap],[assemblyspreaded],[awgfrom],[awgtill],[bendingradius],[cabledesignation],[cabledisplayform],[cablelength],[cabletype],[cableweight],[coding],[color],[connection],[connectioncrosssection],[connectiondesignation],[connectionmethod],[contactarrangement],[contacttype],[coppernumber],[creepagedistance],[crosssectionfrom],[crosssectiontill],[currentcsa],[currentiec],[currentul],[degofprotection],[design],[doordepth],[doorheight],[doormountingspace],[doorwidth],[electricalcurrent],[electricalpower],[firmwareversion],[flow],[holdingpower],[innerdiameter],[intrinsicsafety],[material],[norm],[outerdiameter],[paneldepth],[panelheight],[panelmountingspace],[panelwidth],[pincount],[pipeclass],[plcdeviceid],[plcisbuscoupler],[plcisbusdistributor],[plciscpu],[plcispowersupply],[plcobjectdescription],[plctype],[powerdissipation],[pressure],[pressurelevel],[shortcircuitresistant],[standardinvers],[strokelength],[symbolfile],[symbolnr],[technicalcharacteristics],[thread],[triggercurrent],[voltage],[voltagecsa],[voltageiec],[voltagetype],[voltageul],[widthrating],[wirecount],[wirecrosssection],[wirecrosssectionanddiameter],[wirecrosssectionunit],[variant_1],[characteristics],[connectiondescription],[connectiondesignation_1],[description],[functiondefcategory],[functiondefgroup],[functiondefid],[hasled],[hasplugadapter],[idx1],[idx2],[intrinsicsafety_1],[nesteddevicetag],[pos],[safetyrelevant],[symbol],[symbolmacro],[terminalfunction],[terminalnr],[partnr_1],[variant_2],[count],[parentvariant],[pos_1],[22235.0],[22236.0],[22237.0],[22238.0],[22239.0],[22240.0],[22241.0],[22196.1],[22196.2],[22158.1],[22158.2],[22159.1],[22159.2],[22195.1],[22195.2],[22228.1],[22228.2],[22228.3],[22228.4],[22228.5],[22228.6],[22228.7],[22228.8],[22228.9],[22228.10]
FROM   CTE
WHERE  row BETWEEN @StartRow AND  @EndRow;";
using(var sqlCommand = new SqlCommand(sql, con))
{
    sqlCommand.Parameters.Add("@StartRow", SqlDbType.int).Value = 1; 
    sqlCommand.Parameters.Add("@EndRow", SqlDbType.int).Value = 30;
    // ...
}

由于列名是由用户指定的，因此如果没有动态sql，就不能将它们作为参数添加。但是您知道所有有效的列名，所以您可以检查是否所有列名都有效。如果您不知道表中的所有列，请查看此处，并在应用程序启动时使用reader.GetSchemaTable加载所有列。

然后您可以简单地将其添加到sql:中

string sql = @"
WITH CTE AS 
( 
         SELECT   *, 
                  row=Row_number() OVER (ORDER BY (SELECT 1)) 
         FROM     dbo.TableName 
SELECT {0}
FROM   CTE
WHERE  row BETWEEN @StartRow AND  @EndRow;";
sql = string.Format(sql, string.Join(",", columnList));
相关文章：