Searching Data in the Datagridview Using C# with SQL Server

Searching Data in the Datagridview Using C# with SQL Server

Searching Data in the Datagridview Using C#

In this tutorial, I will teach you how to create an Autocomple in a Combobox using C#.net and SQL Server 2005. With this, it will suggest the records when you start to type in the combobox then, you don’t have to click anymore the dropdown or scroll down the list of records that you’re going to select.

Let’s begin:

Create a database and name it “dbcombo”.

After creating database, do the following query for creating a table in the database that you have created.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblemployee](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NULL,
 [Address] [nvarchar](50) NULL,
 [Contact] [nvarchar](50) NULL,
 [Emailadd] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblemployee] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Now , open Microsoft Visual Studio and create new Windows Form Application for C#. Then do the following design of a Form as follows.

After that, go to the Solution Explorer, double click the “View Code”  to display the code editor.

In the code editor, declare all the classes that are needed.

Note: Put using System.Data.SqlClient; above the namespace to access sql server library.

 //initialize all classes
 SqlConnection conn = new SqlConnection();
 SqlCommand cmd = new SqlCommand();
 SqlDataAdapter da = new SqlDataAdapter();
 DataTable dt = new DataTable();

After declaring the classes, establish a connection between SQL server and C#.net and retrieve the data in the database to display in the datagridview in the first load of the Form. 

private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection between SQL server and Visual C#
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=persondb;trusted_connection=true;";

 //opening connection
 con.Open();
 try
 { 
 //initialize a new instance of sqlcommand
 cmd = new SqlCommand();
 //set a connection used by this instance of sqlcommand
 cmd.Connection = con;
 //set the sql statement to execute at the data source
 cmd.CommandText = "Select * FROM tblemployee"; 
 
 //initialize a new instance of sqlDataAdapter
 da = new SqlDataAdapter();
 //set the sql statement or stored procedure to execute at the data source
 da.SelectCommand = cmd;
 //initialize a new instance of DataTable
 dt = new DataTable();
 //add or resfresh rows in the certain range in the datatable to match those in the data source.
 da.Fill(dt);
 //set the data source to display the data in the datagridview
 dataGridView1 .DataSource = dt;
 
 }
 catch (Exception ex)
 {
 //catching error 
 MessageBox.Show(ex.Message);
 }
 //release all resources used by the component
 da.Dispose();
 //dr.Close();
 //clossing connection
 con.Close();
 }

Now, go back to the design views, double click the TextBox and do the following codes for searching data in the datagridview.

 private void textBox1_TextChanged(object sender, EventArgs e)
 {
 //opening connection
 con.Open();
 try
 {
 //initialize a new instance of sqlcommand
 cmd = new SqlCommand();
 //set a connection used by this instance of sqlcommand
 cmd.Connection = con;
 //set the sql statement to execute at the data source
 cmd.CommandText = "Select * FROM tblemployee WHERE Name Like '%" + textBox1 .Text + "%'";

 //initialize a new instance of sqlDataAdapter
 da = new SqlDataAdapter();
 //set the sql statement or stored procedure to execute at the data source
 da.SelectCommand = cmd;
 //initialize a new instance of DataTable
 dt = new DataTable();
 //add or resfresh rows in the certain range in the datatable to match those in the data source.
 da.Fill(dt);
 //set the data source to display the data in the datagridview
 dataGridView1.DataSource = dt;

 }
 catch (Exception ex)
 {
 //catching error 
 MessageBox.Show(ex.Message);
 }
 //release all resources used by the component
 da.Dispose();
 //dr.Close();
 //clossing connection
 con.Close();
 }

Output:

For all students who need programmer for your thesis system or anyone who needs a sourcecode in any programming languages. You can contact me @ :
Email – [email protected]
Mobile No. – 09305235027 – tnt