Query the database through a text box

Question

Hello, right now I’m in a part of a project where I have to do a database search through a text box. For example, he searched for "José" and returned all the data contained in the database with the name "José". You could also search for "Rua da Travessia" for example. The goal is that after the query the results appear in a datagridview I am very inexperienced in SQL and would appreciate your help. Thank you. Regards.

Answer 1

To perform the query while the user type in the textbox, I use the Textchanged event associated with a timer, which will run when the user stops typing.

In the Sql part, just define in which columns will be searched the value you typed in the textbox, and use OR between them.

I made a basic code to show you:

 public partial class FormConsulta : Form
    {
        public FormConsulta()
        {
            InitializeComponent();
            timer1.Interval = 400;
        }

    private void textBox1_TextChanged(object sender, EventArgs e)
    {
        //Ao alterar o texto do TextBox, reseta o timer
        timer1.Enabled = false;
        timer1.Enabled = true;
    }

    private void timer1_Tick(object sender, EventArgs e)
    {
        //Ao disparar o envento do timer, executa a consulta
        timer1.Enabled = false;
        Consultar();
    }

    private void Consultar()
    {
        //Executa consulta e define o datasource do gridview
        string sql = @"select * from tabela where upper(nome) like '%"+textBox1.Text.ToUpper()+"%' or upper(endereco) like '%"+textBox1.Text.ToUpper()+"%';";

        SqlConnection conn = new SqlConnection("sua string de conexao");
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();

        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        da.Fill(dt);
        conn.Close();
        da.Dispose();


        dataGridView1.DataSource = dt;

    }
}

In the SQL part of the command, prefer to use parameters, and not concatenate directly into the string, as I did in the example.

And for comparison of Texts, as you need, use the LIKE operator, otherwise the content of the column you are searching should be exactly equal to the one typed in the textbox by the user.

More information about LIKE: https://msdn.microsoft.com/pt-br/library/ms179859.aspx