Searching Records 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.