How to work with several parameters in C #

Question

How to work with several parameters in C #

Navigation

#1 by (3 votes)

-1

Good afternoon. I need a help, I have a combobox with 3 options, and I need to pass any of the three options as a parameter of a procedure in sql for a query, but as I am new to C # I have never passed more than one parameter to a query in sql server, now I am with this difficulty, that I need to pass the parameter chosen in the combobox, and either CHEKLIST or SUPPLIER or CNPJ. The procedure is already working in sql.

Follow my screen

followmycode

privatevoidbtn_consultar_Click(objectsender,EventArgse){SqlCommandxml=newSqlCommand("usp_consultachkrecebimento", conexaoUSUARIOS(true));
        xml.Parameters.AddWithValue("@CHEKLIST", this.txt_consultar.Text);
        xml.Parameters.AddWithValue("@FORNECEDOR", this.txt_consultar.Text);
        xml.Parameters.AddWithValue("@CNPJ", this.txt_consultar.Text);
        xml.CommandType = CommandType.StoredProcedure;
        xml.ExecuteNonQuery();

        try
        {
            SqlDataAdapter dados = new SqlDataAdapter(xml);
            DataTable dtLista = new DataTable();
            dados.Fill(dtLista);

            dgw_consultaxml.DataSource = dtLista;
        }
        catch (Exception)
        {
            MessageBox.Show("Não existem dados a retornar por favor verifique como o Administrador do sistema");
            return;
        }
    }

follows the procedure

ALTER PROCEDURE [dbo].[usp_consultachkrecebimento]

     @CHEKLIST    AS VARCHAR(10)
    ,@FORNECEDOR  AS VARCHAR(MAX)
    ,@CNPJ        AS VARCHAR(20)

    AS
    BEGIN

    SET NOCOUNT ON;

    SELECT DISTINCT 
    R.ID_CKCLIST,
    R.CHV_NFE,
    R.N_FONEC,
    R.N_NOTA,
    R.N_CNPJ,
    R.N_IE,
    CONVERT(VARCHAR(10), CAST(R.DT_RECEBIM AS DATE), 103) AS RECEBIMENTO
    FROM tbl_chkrecebimento AS R
    WHERE (@CHEKLIST IS NULL      OR ID_CKCLIST = @CHEKLIST)
    AND   (@FORNECEDOR IS NULL    OR N_FONEC LIKE '%' + @FORNECEDOR + '%')
    AND   (@CNPJ IS NULL          OR N_CNPJ = @CNPJ)
END

c#

asked by anonymous 26.07.2018 / 17:25

1 answer

How to do an if-type validation in a set of options? how do I solve this exercise in c / c ++

score 3 · Accepted Answer

It really depends on how your procedure is, but in the current scenario with the data provided, you could make a condition to determine which parameter will be sent according to the selection of your DropDownList , which as you do not have your id I'll call it ddl_Filtro

private void btn_consultar_Click(object sender, EventArgs e)
{
    SqlCommand xml = new SqlCommand("usp_consultachkrecebimento", conexaoUSUARIOS(true));

    switch(this.ddl_Filtro.SelectedValue){
        case "CHEKLIST" :
            xml.Parameters.AddWithValue("@CHEKLIST", this.txt_consultar.Text);
            break;
        case "FORNECEDOR" :
            xml.Parameters.AddWithValue("@FORNECEDOR", this.txt_consultar.Text);
            break;
        case "CNPJ":
            xml.Parameters.AddWithValue("@CNPJ", this.txt_consultar.Text);
            break;
    }
    xml.CommandType = CommandType.StoredProcedure;
    xml.ExecuteNonQuery();

    try
    {
        SqlDataAdapter dados = new SqlDataAdapter(xml);
        DataTable dtLista = new DataTable();
        dados.Fill(dtLista);

        dgw_consultaxml.DataSource = dtLista;
    }
    catch (Exception)
    {
        MessageBox.Show("Não existem dados a retornar por favor verifique como o Administrador do sistema");
        return;
    }
}

In your procedure add default values in the parameter declaration

ALTER PROCEDURE [dbo].[usp_consultachkrecebimento]

     @CHEKLIST    AS VARCHAR(10) = NULL
    ,@FORNECEDOR  AS VARCHAR(MAX) = NULL
    ,@CNPJ        AS VARCHAR(20) = NULL

AS
BEGIN

    SET NOCOUNT ON;

    SELECT DISTINCT 
    R.ID_CKCLIST,
    R.CHV_NFE,
    R.N_FONEC,
    R.N_NOTA,
    R.N_CNPJ,
    R.N_IE,
    CONVERT(VARCHAR(10), CAST(R.DT_RECEBIM AS DATE), 103) AS RECEBIMENTO
    FROM tbl_chkrecebimento AS R
    WHERE (@CHEKLIST IS NULL      OR ID_CKCLIST = @CHEKLIST)
    AND   (@FORNECEDOR IS NULL    OR N_FONEC LIKE '%' + @FORNECEDOR + '%')
    AND   (@CNPJ IS NULL          OR N_CNPJ = @CNPJ)
END