Query the database through a text box

1

Hello, I'm currently in a part of a project where I have to search the database through a text box. For example I searched for "Joseph" and returned all the data contained in the database with the name "Joseph". You could also search for "Traverse Street" for example. The goal is that after the query the results appear in a datagridview I am very inexperienced in SQL and I appreciate your help. Thank you. Regards.

    
asked by anonymous 15.05.2017 / 11:10

1 answer

4

To perform the query as the user types in the textBox, I use the TextChanged event associated with a timer, which will execute when the user stops typing.

In the Sql part, you only have to define in which columns the value you typed in the textBox will be searched for, and use OR between them.

I made a basic code to exemplify 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 part of the SQL command, prefer to use parameters, and do not concatenate directly in the string, as I did in the example.

And for text comparison, as you need, use the LIKE operator, otherwise the content of the column you are searching for should be exactly the same as that typed in the textBox by the user.

More information about LIKE: link

    
15.05.2017 / 13:28