Sqldataadapter Query with Parameters

Question

I’m having difficulties in returning a query in the database using SqlDataAdapter with parameters.

Well, I have a layered application in my View when I first access a GridView with a query (this part is ok)!

In this same View has a field to search and filter in this same GridView, updating it with this filtering, and that uses the same method. Then that gives the error, I used some examples that I found on the net but not working.

When it enters the line in the if that has the following code, enter the catch:

adapter.SelectCommand.Parameters.Add(new SqlParameter("@nome", pessoa.Nome));

The error reported in catch is as follows:

{System.NullReferenceException: Referência de objeto não definida para uma instância de um objeto.}

How could I add this parameter the right way?

Follows the code:

public DataTable listarPessoas(PessoaModel pessoa = null)
{
    try
    {
        conectar();

        queryString = "SELECT * FROM [crud].[dbo].[Pessoas]";

        if (pessoa.Nome != null)
            queryString += " WHERE pess_nome LIKE '%@nome%'";

        SqlCommand sqlCmd = new SqlCommand(queryString, conn);
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();

        if (pessoa.Nome != null)
            adapter.SelectCommand.Parameters.Add(new SqlParameter("@nome", pessoa.Nome));

        conn.Open();
        adapter.SelectCommand = sqlCmd;
        adapter.Fill(ds);
        DataTable dt = ds.Tables[0];

        return dt;
    }
    catch (Exception erro)
    {
        throw erro;
    }
    finally
    {
        conn.Close();
    }
}

Answer 1

I believe you have to put that line before the if:

    adapter.SelectCommand = sqlCmd;

For adapter.SelectCommand, must still be null when you try to access it.

Would something like this:

    SqlCommand sqlCmd = new SqlCommand(queryString, conn);
    DataSet ds = new DataSet();
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = sqlCmd; // essa linha antes, para não deixa a instancia nula

    if (pessoa.Nome != null)
        adapter.SelectCommand.Parameters.Add(new SqlParameter("@nome", pessoa.Nome));

    conn.Open();

Long time no use SqlCommand, but I believe that’s all.

Answer 2

Well, I ended up getting help from a friend and he helped me with the code, in the end I didn’t use Sqldataadapter, thus getting the code:

public DataTable listarPessoas(PessoaModel pessoaModel)
{
    string queryString = "SELECT * FROM [Crud].[dbo].[Pessoas]";

    if (pessoaModel != null)
        if (pessoaModel.Id != 0)
            queryString += " WHERE pess_id = @id";
        else if(!string.IsNullOrEmpty(pessoaModel.Nome))
             queryString += " WHERE pess_nome LIKE @nome";

    try
    {
        conectar();

        SqlCommand command = new SqlCommand(queryString, conn);

        if (pessoaModel != null)
            if (pessoaModel.Id != 0)
                command.Parameters.AddWithValue("@id", pessoaModel.Id);
        else if (!string.IsNullOrEmpty(pessoaModel.Nome))
            command.Parameters.AddWithValue("@nome", '%' + pessoaModel.Nome + '%');

        SqlDataReader reader = command.ExecuteReader();
        DataTable dt = new DataTable();

        dt.Load(reader);
        reader.Close(); // Fecha o DataReader

        return dt;
    }
    catch (SqlException)
    {
        return null; // em caso de Exceções retorna null 
    }
    finally
    {
        conn.Close(); // Fecha a conexão com o banco de dados
    }
}