c# - 系统参数异常 : The table type parameter must have a valid type name

Question

我正在尝试将用户定义的表类型传递到 C# 中的查询中。

类型定义为 2 列(组织和子组织)

我的代码是这样的:

DataSet ds = new DataSet();
try
{

    DataTable FilteredOrgSubOrg = new DataTable("OrgSubOrgValueType");
    FilteredOrgSubOrg.Columns.Add("org", typeof(string));
    FilteredOrgSubOrg.Columns.Add("subOrg", typeof(string));
    FilteredOrgSubOrg.Rows.Add(org, orgsub);
    using (SqlConnection conn = new SqlConnection(cCon.getConn()))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = 
                "select * from myTable ex where year = @year' and qtr = @qtr" +
                " and EXISTS(SELECT 1 FROM @OrgSubOrg tt  WHERE ex.org like tt.org" +
                " AND ex.orgsub = tt.suborg  )"+
                " order by ex.org,year, qtr DESC";
            // 2. set the command object so it knows
            // to execute a stored procedure

            // 3. add parameter to command, which
            // will be passed to the stored procedure
            cmd.Parameters.Add(new SqlParameter("@OrgSubOrg", FilteredOrgSubOrg));
            cmd.Parameters.Add(new SqlParameter("@year", year));
            cmd.Parameters.Add(new SqlParameter("@qtr", qtr));


            conn.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();

            sqlDA.SelectCommand = cmd;
            sqlDA.Fill(ds);

        }
    }

我是不是传错了参数？

当我在 SQL Server 中这样做时:

declare @OrgSubOrg OrgSubOrgValueType
insert into @OrgSubOrg  values ('05%','00000000')
insert into @OrgSubOrg values ('03%','00000000')


------------ complete -----------------------------------
select * from myTable ex
where 
year = '2013' and qtr = '1' 
and EXISTS(
               SELECT 1 
               FROM @OrgSubOrg tt               
               WHERE ex.org like tt.org
                 AND ex.orgsub = tt.suborg  )
order by ex.org,year, qtr DESC

everything works like it should.

我也试过像这样传递它:

  SqlParameter p = cmd.Parameters.Add(new SqlParameter("@OrgSubOrg", SqlDbType.Structured));
                     p.Value = FilteredOrgSubOrg;

但我得到了同样的错误

The table type parameter '@OrgSubOrg' must have a valid type name.

会不会是我不能将它传递给 SQL 命令，我在另一个地方有类似的代码，它与存储过程配合得很好......？

Answer 1

使用 TypeName 在 SqlServer 中将映射设置为您的类型属性:获取或设置必须修复的表值参数的类型名称。

p.TypeName = "dbo.MyType";

同时检查Table-Valued Parameters发布