How to work with various parameters in c#

Question

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 since I’m new in c# I never passed more than one parameter for a query in sql server, now I’m having this difficulty, that I need to pass the chosen parameter in the combobox, and either CHEKLIST or SUPPLIER or CNPJ. Procedure is already working on sql.

Follow my screen

follows my code

private void btn_consultar_Click(object sender, EventArgs e)
    {
        SqlCommand xml = new SqlCommand("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 trial

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

Answer 1

It actually depends on how your trial is going, but in the current scenario with the data provided, you could make a condition to determine which parameter will be sent according to your selection DropDownList, that as you do not present your id I will call you 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;
    }
}

Already in your process add default values in the parameters 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