Searching Records in ADO.NET

In this article you will learn to search records in Google style in ADO.NET.

Introduction

In this article, I am describing searching in the manner of the Google Search style. I am searching student records by matching character of student name. I am performing this operation in a Window Forms Application.

At first we should have a database with some records to be searched. I have database tables "student" and "student_detail". I am showing the records of database table so that it become easy to understand.



Create a Windows Forms Application. Take some UI controls and arrange them as in the following figure.


 
Write the following code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace SearchingInAdoDotNet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }      
        private void Form1_Load(object sender, EventArgs e)
        {
            txtsearch.Focus();
            lblrollno.Visible = false;
            dgvshow.Visible = false;
           
            dadapter = new SqlDataAdapter(sqlstate, connstring);
            dset = new DataSet();
            dadapter.Fill(dset);
        }
        SqlDataAdapter dadapter;
        DataSet dset;
        DataView dview;
        string connstring = "database=student;server=.;user=sa;password=wintellect";
        string sqlstate = "select name,rollno from student_detail";
 
        private void txtsearch_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.KeyCode != Keys.Enter)
            {
                dview = new DataView(dset.Tables[0]);
                dview.RowFilter = "name like '" + txtsearch.Text + "%' ";
                if (dview.Table.Rows.Count > 0)
                {
                    dgvshow.Visible = true;
                }
 
                dgvshow.DataSource = dview;
                dgvshow.Columns["rollno"].Visible = false;
 
                if (e.KeyCode == Keys.Down)
                {
                    dgvshow.Focus();
                } 
            }
        }
        private void dgvshow_KeyPress(object sender, KeyPressEventArgs e)
        {
           
        }
        private void dgvshow_KeyUp(object sender, KeyEventArgs e)
        {
            txtsearch.Text = dgvshow.Rows[dgvshow.CurrentCell.RowIndex].Cells[dgvshow.CurrentCell.ColumnIndex].Value.ToString();
            lblrollno.Text = dgvshow.Rows[dgvshow.CurrentCell.RowIndex].Cells[dgvshow.CurrentCell.ColumnIndex + 1].Value.ToString();
        }
        private void dgvshow_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                e.SuppressKeyPress = true;
                txtsearch.Text = dgvshow.Rows[dgvshow.CurrentCell.RowIndex].Cells[dgvshow.CurrentCell.ColumnIndex].Value.ToString();
                lblrollno.Text = dgvshow.Rows[dgvshow.CurrentCell.RowIndex].Cells[dgvshow.CurrentCell.ColumnIndex + 1].Value.ToString();
                showdetail();
            } 
        }
        private void bntgo_Click(object sender, EventArgs e)
        {
            showdetail();
        }
        void showdetail()
        {
            dadapter = new SqlDataAdapter("select * from student_detail where rollno=" + lblrollno.Text + "", connstring);
            dset = new DataSet();
            dadapter.Fill(dset);
            if (dset.Tables[0].Rows.Count > 0)
            {
                txtrollno.Text = dset.Tables[0].Rows[0][0].ToString();
                txtname.Text = dset.Tables[0].Rows[0][1].ToString();
                txtcourse.Text = dset.Tables[0].Rows[0][2].ToString();
                txtcity.Text = dset.Tables[0].Rows[0][3].ToString();
            }
            else
            {
                MessageBox.Show("Nothing to Show.....");
            }
        }
       private void dgvshow_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            txtsearch.Text = dgvshow.Rows[dgvshow.CurrentCell.RowIndex].Cells[dgvshow.CurrentCell.ColumnIndex].Value.ToString();
            lblrollno.Text = dgvshow.Rows[dgvshow.CurrentCell.RowIndex].Cells[dgvshow.CurrentCell.ColumnIndex + 1].Value.ToString();
        }      
    }
}
 
Run the application

 

Enter a name in textbox. You will note that it will show all matching names depending on the given input.



You can press the down arrow key to select a different name to search or can select by mouse.



Click the "Go" button. It will show the full record of that student.