将DataTable传递给存储过程中的表值参数不起作用

Question

因此，我需要将一个表值参数(由CheckBoxLists中的选定选项填充(传递给存储过程，以便检索与表值参数中的某些条件匹配的配方，但当我尝试这样做时，它不会返回任何内容。我在ASP.Net和C#上工作。

SQL Server存储过程

CREATE TYPE dbo.Filtros AS TABLE
(
Categoria NVARCHAR(50),
Dificuldade NVARCHAR(50),
Duracao NVARCHAR(50)
);
GO
ALTER PROCEDURE [dbo].[uspPesquisarReceita] (@TVP dbo.Filtros READONLY)
AS
BEGIN
SELECT R.NomeReceita, C.NomeCategoria, DF.Dificuldade, D.Duracao, R.ClassificacaoGeral, R.DataPublicacao, R.Foto, R.NumVisualizacoes, R.IDReceita
FROM dbo.Receitas R 
LEFT JOIN dbo.Categorias C ON R.IDCategoria = C.IDCategoria
LEFT JOIN dbo.Dificuldade DF ON R.IDDificuldade = DF.IDDificuldade
LEFT JOIN dbo.Duracao D ON R.IDDuracao = D.IDDuracao
WHERE R.IDEstado = 1 AND C.NomeCategoria IN (SELECT Categoria FROM @TVP) OR DF.Dificuldade IN (SELECT Dificuldade FROM @TVP) OR D.Duracao IN (SELECT Duracao FROM @TVP)
END

我用来填充用作TVP的DataTable的代码是正确的，因为我通过将其内容打印到标签来测试它。因此，DataTable正在接收并正确存储复选框列表中选择的选项。

C#用于存储过程和DataTable，以存储来自存储过程的结果数据

public SqlDataReader receitaPesquisada(DataConnection conn, string pesquisa, DataTable tabelaParam)
{
SqlDataReader drObterReceita = null;
try
{
string strCmmd = "uspPesquisarReceita";
SqlCommand command = new SqlCommand(strCmmd, conn.Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Pesquisa", SqlDbType.NVarChar).Value = pesquisa;
SqlParameter param = new SqlParameter("@TVP", tabelaParam);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.Filtros";
command.Parameters.Add(param);

drObterReceita = command.ExecuteReader();
return drObterReceita;
}
catch (Exception ex)
{
Console.Write("Algo correu mal no método" + ex.ToString());
return drObterReceita;
}
}
public DataTable imprimirReceitaPesquisada(string pesquisa, DataTable tabelaParam)
{
DataConnection conn = new DataConnection();
DataTable dtbReceitaPesquisada = new DataTable();
dtbReceitaPesquisada.Columns.Add("NomeReceita", typeof(string));
dtbReceitaPesquisada.Columns.Add("Categoria", typeof(string));
dtbReceitaPesquisada.Columns.Add("Dificuldade", typeof(string));
dtbReceitaPesquisada.Columns.Add("Duracao", typeof(string));
dtbReceitaPesquisada.Columns.Add("ClassificacaoGeral", typeof(string));
dtbReceitaPesquisada.Columns.Add("DataPublicacao", typeof(string));
dtbReceitaPesquisada.Columns.Add("Foto", typeof(string));
dtbReceitaPesquisada.Columns.Add("NumVisualizacoes", typeof(string));
try
{
conn.openConnection();
SqlDataReader drObterReceita = this.receitaPesquisada(conn, pesquisa, tabelaParam);
while (drObterReceita.Read())
{
dtbReceitaPesquisada.Rows.Add(drObterReceita[0].ToString(), drObterReceita[1].ToString(), drObterReceita[2].ToString(), drObterReceita[3].ToString(), Math.Round(double.Parse(drObterReceita[4].ToString()), 1).ToString(), drObterReceita[5].ToString(), drObterReceita[6].ToString(), drObterReceita[7].ToString());
}
}
catch (Exception ex)
{
Console.Write("Algo correu mal no método" + ex.ToString());
}
finally
{
conn.closeConnection();
}
return dtbReceitaPesquisada;
}

谢谢你的帮助！

Answer 1

正如@Alexander Petrov正确指出的那样，您正在声明一个名为dbo.filters 的表类型

CREATE TYPE dbo.Filters AS TABLE

但是在存储过程定义中，您传递的是dbo.filttros 类型的表变量

ALTER PROCEDURE [dbo].[uspPesquisarReceita] (@TVP dbo.Filtros READONLY)

它应该是dbo.filters类型。

此外，在没有括号的where子句中同时使用AND和OR运算符时，始终要非常小心；(("；，由于SQL中AND的优先级高于OR，您想要的条件可能不会给出预期的结果。

让我解释一下，下面是你使用的地方

WHERE R.IDEstado = 1 AND C.NomeCategoria IN (SELECT Categoria FROM @TVP) OR DF.Dificuldade IN (SELECT Dificuldade FROM @TVP) OR D.Duracao IN (SELECT Duracao FROM @TVP)

如果您运行查询，在结果集中，您将看到R.IDEstado=1和C.NomeCategoria的结果来自@TVP中的Categoria列，这两个值都必须为true在评估此AND之后，将评估其他OR条件。

但是，你可能想要第一个条件的结果

R.IDEstado = 1 

必须是真实的，并且的任何一个或多个条件

C.NomeCategoria IN (SELECT Categoria FROM @TVP) OR DF.Dificuldade IN (SELECT Dificuldade FROM @TVP) OR D.Duracao IN (SELECT Duracao FROM @TVP)

应该是真的，但是您并没有从上面解释的查询中清楚地得到这一点。

因此，您应该使用下面的where，括号中包含的所有OR条件：

WHERE R.IDEstado = 1 AND (C.NomeCategoria IN (SELECT Categoria FROM @TVP) OR DF.Dificuldade IN (SELECT Dificuldade FROM @TVP) OR D.Duracao IN (SELECT Duracao FROM @TVP))

注意：您可能在查询中使用了缩进的AND和OR条件，我只是解释在where子句中使用不带括号的AND和或条件的含义，这可能会也可能不会影响您的情况

Answer 2

更新：我解决了我的问题。问题是，在用户单击搜索按钮后，我从复选框中检索值，并将它们放入DataTable中。

然而，当用户点击搜索按钮时，它会将他重定向到另一个页面。在这个显示搜索结果的页面中，我调用了基于所选复选框列表选项填充DataTable的方法。但由于我将他重定向到了另一个页面，回发使我的带有checkboxlist值的DataTable变得毫无用处。

相反，我将DataTable分配给一个Session变量，并在重定向后的新页面中调用它。工作起来很有魅力！谢谢大家。