Passing Datatable to a table-Valued Parameter in a stored SQL file does not work

Asked

Viewed 23 times

1

I’m creating a revenue website. On my Masterpage (I am working on ASP.net), I have a search icon and when the user clicks on that icon, I will read three checkboxlists and pass the selected values of each checkboxlist to a Datatable.

The idea is to pass this Datatable as a parameter for a stored process that will later return the recipes that meet the criteria in Datatable. Although the code is working in structural terms, no results are shown.

Stored Procedure and Table Type SQL

 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

C# to fill the TVP passed as parameter for Stored Procedure

public DataTable verFiltros()
        {
            DataTable dtFiltros = new DataTable();
            dtFiltros.Columns.Add("Categoria", typeof(string));
            dtFiltros.Columns.Add("Dificuldade", typeof(string));
            dtFiltros.Columns.Add("Duracao", typeof(string));

            foreach (ListItem item in CheckBoxList1.Items)
            {
                if (item.Selected)
                {
                    DataRow dr = dtFiltros.NewRow();
                    dr["Categoria"] = item;
                    dtFiltros.Rows.Add(dr);
                }
            }

            foreach (ListItem item in CheckBoxList2.Items)
            {
                if (item.Selected)
                {
                    DataRow dr = dtFiltros.NewRow();
                    dr["Dificuldade"] = item;
                    dtFiltros.Rows.Add(dr);
                }
            }

            foreach (ListItem item in CheckBoxList3.Items)
            {
                if (item.Selected)
                {
                    DataRow dr = dtFiltros.NewRow();
                    dr["Duracao"] = item;
                    dtFiltros.Rows.Add(dr);
                }
            }

            return dtFiltros;
        }

Stored Procedure and Datatable used to store recipes that Stored Procedure should return

public SqlDataReader receitaPesquisada(DataConnection conn, DataTable tabelaParam)
        {
            SqlDataReader drObterReceita = null;

            try
            {
                string strCmmd = "uspPesquisarReceita";
                SqlCommand command = new SqlCommand(strCmmd, conn.Connection);
                command.CommandType = CommandType.StoredProcedure;
                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(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, 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;
        }

Code Behind of the ASP.net page that should show the search result (and currently does not return anything)

public partial class aspnet_ResultadoPesquisa : System.Web.UI.Page
    {
        Receitas novaReceita = new Receitas();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DataList();
            }
        }

        public void DataList()
        {
            DataTable dtReceitas = new DataTable();
            dtReceitas = novaReceita.imprimirReceitaPesquisada(Master.verFiltros());
            DataList1.DataSource = dtReceitas;
            DataList1.DataBind();
        }
    }

Thanks for the help!

  • its construction is strange, its table has 3 columns, but only fills one at a time, in practice it is passing 3 lists and not a table row, this construction is half meaningless. It could create a type with only 1 column with NVARCHAR(50), and the process receive 3 parameters of this type (@categoria dbo.Filtros, @dificuldade dbo.Filtros, @duracao dbo.Filtros) which would make more sense, because of the 3 foreach and the where would be clearer. Or it would be even easier to pass 3 parameters NVARCHAR, pq actually your code passes only 1 value for each combo, the selected

  • to call the trial is complicating too much something that would be very simple :)

  • Hello @Ricardopunctual! I see. A good idea with the question of using only one column in Type. In fact, I just need a column with all the selected values and then always look the same. However, the question of using only 3 parameters is not feasible, because the user can select more than one option in each checkboxlist1 (I have no way of knowing how many in all). So I really need a Valued Parameter table. But q matter of creating only one column seems interesting!

  • How could I improve the call the Procedure?

  • I get the idea, so it really needs to be a table, but I think it’s simpler if you pass 3 table parameters, one for each "checkbox". The rest of the code looks good, just capturing the SQL command to see what the problem is, maybe using sql profiler

  • 1

    Ah as for the problem. However, I already solved :) the problem was that I was filling the TVP with a method when the user clicked on a "Search" button. The question is that this button redirects to another page, so when I called the method on this other page, the Datatable came empty because the checkboxlist had nothing due to Postback. When I passed Datatable to a Session variable that already solved! Thanks for the help, and the suggestion, I will try ;)

Show 1 more comment
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.