C# System.ArgumentException: 表类型参数必须具有有效的类型名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17817997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
System.ArgumentException: The table type parameter must have a valid type name
提问by Madam Zu Zu
I am trying to pass in a user defined table type into a query in C#.
我正在尝试将用户定义的表类型传递到 C# 中的查询中。
the type is defined with 2 columns (org and sub org)
类型定义为 2 列（组织和子组织）
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?
我是否错误地传递了参数？
when i do it in SQL server like so:
当我在 SQL 服务器中这样做时：
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.
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...?
可能是我无法将它传递给 SQL 命令，我在另一个地方有类似的代码，它与存储过程一起使用效果很好......？
回答by volody
Set mapping to your type in SqlServer using TypeNameproperty that: Gets or sets the type name for a table-valued parameter, that has to fix .
使用TypeName属性在 SqlServer 中设置映射到您的类型： 获取或设置表值参数的类型名称，必须修复 .
p.TypeName = "dbo.MyType";
Check as well Table-Valued Parameterspost
也检查表值参数帖子
回答by trueboroda
You can get this error also when you wanna pass table params into stored procedure. There is happen if you use entity famework Context.Database.SqlQuery(). You must necessary set TypeName property for your table params.
当您想将表参数传递到存储过程时，您也会收到此错误。如果您使用实体成名 Context.Database.SqlQuery()，就会发生这种情况。您必须为表参数设置必要的 TypeName 属性。
SqlParameter codesParam = new SqlParameter(CODES_PARAM, SqlDbType.Structured);
SqlParameter factoriesParam = new SqlParameter(FACTORIES_PARAM, SqlDbType.Structured);
codesParam.Value = tbCodes;
codesParam.TypeName = "[dbo].[MES_CodesType]";
factoriesParam.Value = tbfactories;
factoriesParam.TypeName = "[dbo].[MES_FactoriesType]";
var list = _context.Database.SqlQuery<MESGoodsRemain>($"{SP_NAME} {CODES_PARAM}, {FACTORIES_PARAM}"
, new SqlParameter[] {
codesParam,
factoriesParam
}
).ToList();
回答by bvoyelr
Note that this may also happen when you're executing a stored procedure and you don't have the SqlCommand.CommandType set to CommandType.StoredProcedure, as such:
请注意，当您执行存储过程并且没有将 SqlCommand.CommandType 设置为 CommandType.StoredProcedure 时，也可能会发生这种情况，如下所示：
using (SqlCommand cmd = new SqlCommand("StoredProcName", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
