SqlDataAdapter Query with Parameters

Question

SqlDataAdapter Query with Parameters

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I'm having difficulty returning a query in the database using SqlDataAdapter with parameters.

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

In this same View you have a field to search and filter in this same GridView , updating it with this filtering, and using the same method. So that gives the error, I used some examples that I found in the net but it is not working.

When you enter the line inside the if that has the following code, enter 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 correctly?

Follow 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();
    }
}

c# .net asp.net-mvc ado.net

asked by anonymous 05.06.2014 / 20:22

2 answers

1

I believe you have to by that line before if :

    adapter.SelectCommand = sqlCmd;

Because adapter.SelectCommand , it should still be null when you try to access it.

It would look 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();

I have not used SqlCommand for some time, but I think that's all.

05.06.2014 / 20:24

Protect pages that are loaded inside the template - Kohana Drivers for bank connection via Classic ASP

score 0 · Accepted Answer

Well, I ended up getting help from a friend and he helped me with the code, in the end I did not 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
    }
}