System.ArgumentException：该表类型参数必须有一​​个有效的类型名称 [英] System.ArgumentException: The table type parameter must have a valid type name

问题描述

我想传递一个用户定义的表类型到C＃中的查询。

I am trying to pass in a user defined table type into a query in C#.

的类型被定义2列（有机和子组织）

the type is defined with 2 columns (org and sub org)

这是我的code是这样的：

this is what my code looks like:

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);

        }
    }

我在传递参数不正确？

am i passing the parameters in incorrectly?

当我这样做在SQL服务器像这样：

when i do it in SQL server like so:

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.

我也尝试过它，像这样：

i also tried passing it in like so:

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

但我得到同样的错误

but am getting the same error

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

那也许是因为我不能把它传递到SQL命令，我也有类似的code在另一个地方，这与存储过程的伟大工程...？

could it be that i can't pass it to a SQL command, i have similar code in another place, that works great with a stored procedure...?

推荐答案

设置映射到你的类型，在SQLServer中使用<一个href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.typename.aspx">TypeName属性：获取或设置类型名称为表值参数，具有修复

Set mapping to your type in SqlServer using TypeName property that: Gets or sets the type name for a table-valued parameter, that has to fix .

p.TypeName = "dbo.MyType";

检查以及表值参数在.NET 交

这篇关于System.ArgumentException：该表类型参数必须有一​​个有效的类型名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！