How to Search Data in the ListView Using C# and SQL Server

How to Search Data in the ListView Using C# and SQL Server

Today, you will learn how to Search Data in the ListView Using a TextBox in C#.net and SQL Server 2005 Express Edition. This functionality will help you search records in the ListView. I’m going to use here, Microsorf Visual Studio 2008 for creating this project.

Let’s begin:

1. Create a database and name it “dbsubject”.

2. Create a table in the database that you have created.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblsubject](
 [SUBID] [int] IDENTITY(1,1) NOT NULL,
 [SUBJCODE] [nvarchar](50) NULL,
 [SUBDESC] [nvarchar](50) NULL,
 [UNIT] [int] NOT NULL,
 [YR] [nvarchar](50) NULL,
 [AY] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblemployee] PRIMARY KEY CLUSTERED 
(
 [SUBID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

3. Do the following query for inserting data in the table that you have created.

INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Theology 1', 'Faith and Creed and Basic Catholic Doctrine', 3, 'Grade 7', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 0', 'English Plus ', 3, 'Grade 7', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 1', 'Developmental Reading', 3, 'Grade 7', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Theology 1', 'Faith and Creed and Basic Catholic Doctrine', 3, 'Gade 8', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('History 1', 'Philippine History and Culture', 3, 'Grade 7', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Psychology 1a', 'General Psychology with Moral Regen & Drug Abuse Ed.', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Chem. 1', 'General and Organic Chemistry', 5, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philosophy 1', 'Introduction to Philosophy', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('PE 1', 'Physical Education', 2, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('NSTP 1', 'NSTP', 3, 'Grade 7', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Theology 2', 'Bible and Salvation History ', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 1', 'Study and Thinking Skills ', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 02', 'Developmental Reading 2', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Math 2', 'Modern College Algebra ', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Nat. Sci. 3', 'Physical Science', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 3', 'Logic', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Educ. 2', 'Child and Adolescent Development', 2, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('PE 2', 'Rhythmic Activities', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('NSTP 2', 'NSTP', 3, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Theology 3', 'Liturgy and Sacraments in General', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 2 ', 'Writing in the Decipline', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 3', 'Speech Communication', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 4', 'Interactive English', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Filipino 1', 'Komunikasyon sa Akademikong Filipino', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Math 2', 'Plane and Spherical Trig.', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 5', 'Philosophy of being', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 2', 'General Ethics', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('PE 3', 'Individual/ Dual Sports/ Games', 2, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Theology 4', 'Commandments and Christian Morality ', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 15', 'Philippine Literature', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('English 7a', 'Effective Writing', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Filipino 2', 'Pagbasa at Pagsulat Tungo sa Pananaliksik', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Nat. Sci. 4', 'Ecology', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 6', 'Philosophy of Nature', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 17', 'Special Ethics', 3, '23', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('PE 4', 'Team Sports and Games', 2, '22', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Theology 5', 'Church History and Church Today', 3, '24', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Filipino 3', 'Masining na Pagpapahayag', 3, '24', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Nat. Sci. 2 ', 'Biological Science', 3, '24', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 4', 'Theodicy', 3, '24', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 13', 'History of Ancient Philosophy', 3, '24', '2013-2014')
INSERT INTO tblsubject
 (SUBJCODE,SUBDESC, UNIT, YR, AY) 
 VALUES('Philo. 8a', 'Philosophical Pyschology', 3, '24', '2013-2014')

4. Open Microsoft Visual Studio 2008 and create new Windows Form Application for C#. Then, do the Form just like this.

5. Go to the Solution Explorer, click the “View Code”  to fire the code editor.

6. 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();

7. Create a method for displaying data in the ListView.

 private void LoadListView()
 {
 //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 tblsubject";

 //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);

 //add the data source to display the data in the ListView
 listView1.Items.Clear();
 foreach (DataRow r in dt.Rows)
 {
 var list = listView1.Items.Add(r.Field<int>(0).ToString());
 list.SubItems.Add(r.Field<string>(1));
 list.SubItems.Add(r.Field<string>(2));
 list.SubItems.Add(r.Field<int>(3).ToString());
 list.SubItems.Add(r.Field<string>(4));
 list.SubItems.Add(r.Field<string>(5));
 }
 }
 catch (Exception ex)
 {
 //catching error 
 MessageBox.Show(ex.Message);
 }
 //release all resources used by the component
 da.Dispose();
 //dr.Close();
 //clossing connection
 con.Close();
 }

8. Establish a connection between SQL server and C#.net and retrieve the data in the database to display in the ListView. 

 private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection between SQL server and Visual C#
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=dbsubject;trusted_connection=true;";
//call a method that you have created.
 LoadListView();
 }

9. Go back to the design views, double click the TextBox and do the following codes for searching data in the ListView.

 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 tblsubject WHERE SUBJCODE 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);
 //add the data source to display the data in the ListView
 listView1.Items.Clear();
 foreach (DataRow r in dt.Rows)
 {
 var list = listView1.Items.Add(r.Field<int>(0).ToString());
 list.SubItems.Add(r.Field<string>(1));
 list.SubItems.Add(r.Field<string>(2));
 list.SubItems.Add(r.Field<int>(3).ToString());
 list.SubItems.Add(r.Field<string>(4));
 list.SubItems.Add(r.Field<string>(5));
 }
 }
 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

Download Sourcecode