更正使用表值函数的选择查询的参数类型 [英] Correct parameter types for select queries that use table valued functions

问题描述

我的 SQL Server 2012 包含一个函数 Get_Schedule:

My SQL Server 2012 contains a function Get_Schedule:

CREATE FUNCTION Get_Schedule(
    @pProject_Id   AS INT,
    @pPeriod_Start AS DATETIME2,
    @pPeriod_End   AS DATETIME2)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN (
        SELECT
            42     AS Val1,
            'Foo'  AS Val2);

Visual Studio 2015 的 SQL Server 对象资源管理器显示具有定义类型的函数参数.

Visual Studio 2015's SQL Server Object Explorer shows the function parameters with the types as defined.

然而，当将函数拖到强类型数据集设计器时，DATETIME2 参数更改为 ANSISTRING 而不是 System.DateTime 或 >System.Data.SqlDbType.DateTime2.

However when dragging the function to the strongly typed dataset designer the DATETIME2 parameters are changed to ANSISTRING instead of System.DateTime or System.Data.SqlDbType.DateTime2.

int 参数，@pProject_Id 确实具有正确的类型.

The int parameter, @pProject_Id does have the correct type.

在使用表适配器的 select 语句访问数据库和创建新的存储过程时都会发生这种情况(过程本身确实有预期的 DATATIME2 作为参数，但生成的表适配器有ansistring).

This happens both when using a select statement for the table adapter to access the database and when creating new a stored procedure (the procedure itself does have the expected DATATIME2 as parameter but the generated table adapter has ansistring).

如何在生成的表适配器中拥有正确的参数类型?

How to have the correct parameter types in the generated table adapters?

推荐答案

添加查询及其表适配器后:

After adding the query and its table adapter:

• 打开表适配器的属性；
• 展开 SelectCommand;并且，
• 将Parameters 编辑为正确的类型.

• open the table adapter's properties;
• expand SelectCommand; and,
• edit the Parameters tot the correct type.

这篇关于更正使用表值函数的选择查询的参数类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！