Doubt in research

Question

I have a Grid, but when I type something into the field textbox, the search is not done and nothing is displayed on Grid.

Follow the code made in 4 layers:

Form:

    private void txtPesquisa_TextChanged(object sender, EventArgs e)
    {


        UsuarioDTO usuDto = new UsuarioDTO();
        usuDto.Nome = txtPesquisa.Text;
        dgvListaUsuario.DataSource = UsuarioModel.PesquisaUsuario(usuDto);


    }

Class: Model

       public static IList<UsuarioDTO> PesquisaUsuario (UsuarioDTO usuDto)
    {

        try
        {
        return new UsuarioDAO().PesquisaUsuario(usuDto);
        }
        catch (Exception ex)
        {

            throw ex;
        }

    }

Class: DAO

public IList<UsuarioDTO> PesquisaUsuario(UsuarioDTO usuDto)// esperando um retorno do UsuarioDTO que é o listaUsuarioDTO
    {
        // fazer o try catch para o tratamento de erro
        try
        {

            SqlConnection CON = new SqlConnection();// conexão com o Banco de Dados SQL 
            CON.ConnectionString = Properties.Settings.Default.CSEmpilhadeiras; // criar uma String de conexão
            SqlCommand CM = new SqlCommand();//
            CM.CommandType = System.Data.CommandType.Text;

            CM.CommandText = "SELECT tbUsuario WHERE nome_usu LIKE %@nomeUsu%";
            CM.Parameters.Add("nomeUsu", System.Data.SqlDbType.VarChar).Value = usuDto.Nome;

            CM.Connection = CON;

            SqlDataReader ER;// colocar um dataread trazer o conteudo

            // listaUsuarioDTO este é o objeto que vou carregar e retornar
            IList<UsuarioDTO> listaUsuarioDTO = new List<UsuarioDTO>();

            CON.Open();// abrir a conexão
            ER = CM.ExecuteReader();// executa o comando
            if (ER.HasRows) // se tem alguma linha. teve algum retorno ?
            {
                while (ER.Read())// ler linha a linha 
                {
                    // criar um usuarioDTO
                    UsuarioDTO usuario = new UsuarioDTO();
                    usuario.Codigo = Convert.ToInt32(ER["cod_usu"]);
                    usuario.Nome = Convert.ToString(ER["nome_usu"]);
                    usuario.Login = Convert.ToString(ER["login_usu"]);
                    usuario.Senha = Convert.ToString(ER["senha_usu"]);
                    usuario.Email = Convert.ToString(ER["email_usu"]);
                    usuario.Status = Convert.ToChar(ER["status_usu"]);
                    usuario.DtCadastro = Convert.ToDateTime(ER["dtCadastro_usu"]);
                    usuario.Nivel = Convert.ToChar(ER["nivel_usu"]);

                  //  listaUsuarioDTO.Add(usuario);
                }
            }

            return listaUsuarioDTO;

        }
        catch (Exception ex)
        {
            // se cair o Banco, se estiver fora 
            throw ex; // devolve o erro para quem Chamou que é o formulario
        }

    }

Class: User Name

public class UsuarioDTO
{
    private int codigo;
    private String nome;
    private String login;
    private String senha;
    private String email;
    private Char status;
    private DateTime dtCadastro;
    private Char nivel;

    #region   Getters e Setters

    public int Codigo
    {
        get { return codigo; }
        set { codigo = value; }
    }
    public String Nome
    {
        get { return nome; }
        set { nome = value; }
    }


    public String Login
    {
        get { return login; }
        set { login = value; }
    }

    public String Senha
    {
        get { return senha; }
        set { senha = value; }
    }


    public String Email
    {
        get { return email; }
        set { email = value; }
    }

    public Char Status
    {
        get { return status; }
        set { status = value; }
    }


    public DateTime DtCadastro
    {
        get { return dtCadastro; }
        set { dtCadastro = value; }
    }

    public Char Nivel
    {
        get { return nivel; }
        set { nivel = value; }
    }
}

I’m not getting to identify the mistake!

error

An unhandled Exception of type 'System.Data.Sqlclient.Sqlexception' occurred in Htsisforklifts.Model.dll

Additional information: Invalid column name 'usuName'.

Invalid column name 'tbUsuario'.

Answer 1

Corrected.

The problem is that you didn’t run the query in the BD, one possibility would be (notice it’s just an example, I don’t know the name of the field in the table):

public IList<UsuarioDTO> PesquisaUsuario(UsuarioDTO usuDto)
{
    using(SqlConnection CON = new SqlConnection())
    {
        CON.ConnectionString = Properties.Settings.Default.CSEmpilhadeiras;
        SqlCommand CM = new SqlCommand();
        CM.CommandType = System.Data.CommandType.Text;
        CON.Open();

        CM.CommandText = "SELECT * FROM tbUsuario WHERE nome_usu LIKE @nomeUsu";
        CM.Parameters.Add("nomeUsu", System.Data.SqlDbType.Varchar).Value = "%" + usuDto.Nome + "%";
        CM.Connection = CON;

        var dr = CM.ExecuteReader();

        var usuarios = new List<UsuarioDTO>();

        while(dr.Read())
        {
                UsuarioDTO usuario = new UsuarioDTO();
                usuario.Codigo = Convert.ToInt32(dr["cod_usu"]);
                usuario.Nome = Convert.ToString(dr["nome_usu"]);
                usuario.Login = Convert.ToString(dr["login_usu"]);
                usuario.Senha = Convert.ToString(dr["senha_usu"]);
                usuario.Email = Convert.ToString(dr["email_usu"]);
                usuario.Status = Convert.ToChar(dr["status_usu"]);
                usuario.DtCadastro = Convert.ToDateTime(dr["dtCadastro_usu"]);
                usuario.Nivel = Convert.ToChar(dr["nivel_usu"]);

                usuarios.Add(usuario);
        }

        return usuarios;
    }
}

It’s just a snippet, you may want to handle multiple results, errors, etc.

On receipt of the datasource:

private void txtPesquisa_TextChanged(object sender, EventArgs e)
{
    UsuarioDTO usuDto = new UsuarioDTO();
    usuDto.Nome = txtPesquisa.Text;
    var source = new BindingSource();
    source.DataSource = UsuarioModel.PesquisaUsuario(usuDto);
    dgvListaUsuario.DataSource = source;
}

Change the method signature:

public static IList<UsuarioDTO> PesquisaUsuario(UsuarioDTO usuDto)

Answer 2

@Denilsoncarlos, the problem is in your query, but specifically in the condition LIKE.

You must concatenate the % with the variable, as in the example below.

DECLARE @tabela as table(
    guid uniqueidentifier primary key, 
    nome varchar(50)
);

INSERT INTO @tabela VALUES ('7290713f-c2d3-4bc3-89b2-e58685e594bb', 'Sarah Garcia');
INSERT INTO @tabela VALUES ('b3d0d2cf-089b-4684-8892-fc947375614a', 'Mark Thatcher');
INSERT INTO @tabela VALUES ('1145f4d6-8c47-4124-a48c-10a0610bbf39', 'Arthur Lake');
INSERT INTO @tabela VALUES ('d87cd5d1-2a9d-49de-8e37-0ff857e0e068', 'Kenneth Major');
INSERT INTO @tabela VALUES ('a8cdc2f8-2b90-459c-9eff-63ba410d1830', 'Sarah Robinson');
INSERT INTO @tabela VALUES ('2362e37c-6e4f-42e7-b265-9939c1d011e3', 'Adam Davies');
INSERT INTO @tabela VALUES ('cb350a4c-7701-4934-b37b-d6fb518db7d5', 'Kimberly Taylor');
INSERT INTO @tabela VALUES ('eb17d0ce-40fb-4d03-9d8f-c6580200fa05', 'Margaret Stevens');
INSERT INTO @tabela VALUES ('e3e1c825-1366-4759-804e-bd1b355936e1', 'Ursula Washington');

DECLARE @nome as varchar(50);
SET @nome = 'ar'

SELECT * FROM @tabela WHERE nome LIKE '%' + @nome + '%'

Answer 3

I believe that there are two situations that are causing this behavior:

CM.Parameters.Add("nameUsu", System.Data.Sqldbtype.Varchar). Value = usuDto.Name;

the correct would be:

CM.Parameters.AddWithValue("@nomeUsu", "%" + usuDto.Nome + "%");

and the other thing is that rescued users are not being added to the return list in the DAO class:

// listUsuarioDTO.Add(user);

you must uncomment this line.