如何使用 C# Ado.Net 在 sql 查询中查找参数 [英] How to find parameters in sql query using C# Ado.Net
问题描述
我得到了用户在运行时输入的这个查询.
I have got this query which is entered by user at run time.
SELECT * FROM Reports
WHERE ReportDate > DATEADD(d, @Days, getdate())
AND ReportCode = cast(@Reportcode as int)
在 C# .Net 或 SQL 中有什么方法可以从这个 sql 查询中检索参数名称 @Days 和 @ReportCode?
Is there any way in C# .Net or SQL to retrieve the parameter names @Days and @ReportCode from this sql query?
使用@ 字符进行正则表达式或字符串匹配并不是完全证明,因为参数名称可能以空格结尾,也可能不以空格结尾.它们可以紧跟逗号或括号等,名称本身可以包含特殊字符.
Regex or string matching using @ character is not full proof as parameter names may or may not end with a space. They can be immediately followed by a comma or parenthesis etc. and the name itself can contain a special character.
如果我在没有提供参数值的情况下执行这个查询,sql引擎会抛出异常必须声明标量变量@Days".我可以捕获异常并获取第一个参数名称,但是在查询中获取下一个参数将非常复杂.
If I execute this query without providing parameter values, the sql engine throws exception Must declare the scalar variable "@Days". I can catch the exception and get first parameter name, but then it would be very complex to get next parameter in query.
推荐答案
exec sp_describe_undeclared_parameters N'SELECT * FROM Reports
WHERE ReportDate > DATEADD(d, @Days, getdate())
AND ReportCode = cast(@Reportcode as int)'
输出:
parameter_ordinal name suggested_system_type_id suggested_system_type_name
----------------- ------------- ------------------------ ----------------------------
1 @Days 56 int
2 @Reportcode 56 int
(跳过更多列)
但是,这是一项 SQL Server 功能,而不是 ADO.NET 功能.
However, this is a SQL Server feature, not an ADO.NET one.
如果我在不提供参数值的情况下执行此查询,
If I execute this query without providing parameter values,
这里的关键技巧非常简单:提供正确的参数
The key trick here is remarkably simple: supply the correct parameters
注意:您也可以使用 @params
告诉它您已经知道的.有关更多详细信息,请查看文档.
Note: you can also use @params
to tell it about the ones you already know about. For more details, see the documentation.
这篇关于如何使用 C# Ado.Net 在 sql 查询中查找参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!