从参数化动态查询中获取参数类型

本文关键字：参数 获取 类型 动态 查询 | 更新日期: 2023-09-27 18:36:12

我正在编写一个接口来提供有关查询的最终用户输入。管理员定义查询，例如：

SELECT col1, col2, col3, col4 + col5 AS sumcol
FROM mytable
WHERE col10 = @parm1 AND somethingelse > @parm2

现在我可以在 C# 中解析该查询，获取所有返回的列名

using (myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo))
{
    //Retrieve column schema into a DataTable.
    schemaTable = myReader.GetSchemaTable();
    //For each field in the table...
    foreach (DataRow field in schemaTable.Rows)
    {
        // Display one-liner of important fields
        Console.WriteLine("{0} - Base Table: [{1}]  Data Type: [{2}]", 
            field[schemaTable.Columns["ColumnName"].ToString()].ToString(),
            field[schemaTable.Columns["BaseTableName"].ToString()].ToString(),
            field[schemaTable.Columns["DataType"].ToString()].ToString()
            );
    }

但我也想获取参数@parm1和@parm2所期望的数据类型。我可以解析字符串以获取参数名称，但我想向最终用户提供 2 个参数的输入框，并在执行查询以获取结果之前检查它们是否正确类型。不一定有我可以检查的特定列名，例如它总是 colx = @parm1然后我可以检查 colx 的类型并假设@parm1将是相同的类型，但子句可能通常比这更复杂。我无法控制管理员创建的查询，但我想尝试为最终用户提供尽可能万无一失的证明，提示他们输入正确的参数类型，因为我不确定管理员是否会为参数提供合理的名称以指示查询的期望。当我将查询传递给 SqlServer 以获取返回列类型时，它是否还可以告诉我参数类型可能是什么？我希望这是有道理的。

从参数化动态查询中获取参数类型

如果您使用的是 SQL Server 2012 及更高版本，则可以使用sp_describe_undeclared_parameters存储过程来猜测参数类型。

例如，在罗斯文示例数据库上执行以下查询：

EXEC sp_describe_undeclared_parameters 
        N'SELECT * FROM Customers WHERE CustomerID = @CustomerID'

为您提供以下结果：

+-------------------+-------------+--------------------------+----------------------------+----------------------+---------------------+-----------------+------------------------+------------------------------+----------------------------+--------------------------+----------------------------------------+-----------------------------+-----------------------------------+---------------------------------+-------------------------------+---------------------------+-----------------------------+------------------------------------+--------------------+---------------------+-----------------------+-----------------------+----------------------+
| parameter_ordinal |    name     | suggested_system_type_id | suggested_system_type_name | suggested_max_length | suggested_precision | suggested_scale | suggested_user_type_id | suggested_user_type_database | suggested_user_type_schema | suggested_user_type_name | suggested_assembly_qualified_type_name | suggested_xml_collection_id | suggested_xml_collection_database | suggested_xml_collection_schema | suggested_xml_collection_name | suggested_is_xml_document | suggested_is_case_sensitive | suggested_is_fixed_length_clr_type | suggested_is_input | suggested_is_output | formal_parameter_name | suggested_tds_type_id | suggested_tds_length |
+-------------------+-------------+--------------------------+----------------------------+----------------------+---------------------+-----------------+------------------------+------------------------------+----------------------------+--------------------------+----------------------------------------+-----------------------------+-----------------------------------+---------------------------------+-------------------------------+---------------------------+-----------------------------+------------------------------------+--------------------+---------------------+-----------------------+-----------------------+----------------------+
|                 1 | @CustomerID |                      239 | nchar(5)                   |                   10 |                   0 |               0 | NULL                   | NULL                         | NULL                       | NULL                     | NULL                                   | NULL                        | NULL                              | NULL                            | NULL                          |                         0 |                           0 |                                  0 |                  1 |                   0 | NULL                  |                   239 |                   10 |
+-------------------+-------------+--------------------------+----------------------------+----------------------+---------------------+-----------------+------------------------+------------------------------+----------------------------+--------------------------+----------------------------------------+-----------------------------+-----------------------------------+---------------------------------+-------------------------------+---------------------------+-----------------------------+------------------------------------+--------------------+---------------------+-----------------------+-----------------------+----------------------+
相关文章：