In this tutorial we want to cover how to search filter an Mircosoft Access database with Windows Form ListView as our component. We search/filter on the server side using SQL statements.
This is much more efficient that loading all data and filtering from C# side.
We search filter based on multiple columns, not just one column. Users can search any of the 4 columns we work with in this example and data will be filtered appropriately.
Then when user clicks an item in the filtered listview, we show a messagebox with the filtered items.
Let's start.
We are Building a Vibrant YouTube Community
We have a fast rising YouTube Channel of friends. So far we've accumulated more than 2.6 million agreggate views and more than 10,000 subscribeers. Here's the Channel: ProgrammingWizards TV.
Please go ahead subscribe(free obviously) as well. If you have a question or a comment you can post there instead of in this site.People are suggesting us tutorials to do there so you can too.
Here's this tutorial in Video Format.
Create Table and Populate with Data
I'll assume you have created an ms access database with data in the table. We have a complete tutorial on how to do that from C# code. However you can also create it from MS Access interface.
Here's my access table with data.
Program.cs
Let's start.
Add Using Statements/Directives
We add them at the top of our file.
using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Windows.Forms;They include the following:
System- Probably the most important namespace as it contains the basic clases needed for creating any program like String, Exception and event args. Just try commenting out and see the compile time errors.System.Data- Provides us with the DataTable, which is a data structure that represents one table of in-memory data.System.Data.OleDb- This namespace provides with several classes needed to talk to MS Access database.These includeOleDbConnection,OleDbCommand,OleDbDataAdapteretc,System.Drawing- This namespace provides with several classes and structs needed especially for basic graphical features like Color,Font,Point and Size.System.Windows.Forms- This namespace allows us create windows forms components like TextBox and ListView.
How to Create Partial Classes in same Namespace.
A namespace allows us group classes into a single entity. Partial classes on the other hand allows us create classes that span multiple files.
Also it allows us create classes with similar name in the same namespace. These classes are just treated as a single class hence methods or data members of any class can be accessed by another.
namespace ListViewSearchAccess
{
partial class Program
{
...
}
partial class Program
{
...
}How to Define instance Fields, Constants and ReadOnly Fields
In a way all these three are instance fields. An instance field is a variable that belongs to the instance or object of a given class.
Normally you instantiate a class to get its instance or object.
private ListView myListView;
private TextBox searchTxt;
private Form myForm;Constants on the other hand are variables whose values cannot be changed. They are constant throughout the lifespan of the application. Constants get declared using the const modifier.
A good candidate for being marked constant is connection string. Basically a string that allows us connect to database, providing us with the database provider and path.
private const string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Hp/Documents/DataBases/spacecraftsDB.mdb;";We also have readonly fields.With readonly fields,assignments to the fields introduced by the declaration can only occur as part of the declaration or in a constructor in the same class.
readonly OleDbConnection con = new OleDbConnection(conString);
OleDbCommand cmd;
OleDbDataAdapter adapter;
readonly DataTable dt = new DataTable();What are the Roles of these Fields?
The fields we have defined above have the following main roles:
ListVew- To render our data from ms access database in a list with multiple columns.TextBox- To provide an interface for users to type data they want to search. Similar to how you type in google to search.Form- To hold all our windows forms components.Connection String- To provide basica configurations for connecting to our MS Access database. This includes the database provider as well as the data source.OleDbConnection- To allow us connect to MS Access database as well as close that connection.OleDbCommand- This is the SQL(Structured Query Language) statement that we want to execute against the database. It can also represents a stored procedure.OleDbDataAdapter- Contains commands that allow us to fill or update our data source.DataTable- Allow us to hold our ms access database data in an in-memory table for convenience.
How to create Windows Forms Components Programmatically
While you can definitely use windows designer, we all know that the best way to learn stuff is to create it yourself. This forces you to understand the details that will be hidden from you while provding you with absolute control of everything.
It's fairly easy to create simple interfaces programmatically from scratch in C#.
Our second partial class is responsible for creation of these components.
First to create a Label, which is normally used to render static text:
Label searchLabel = new Label
{
//set properties
}To create a TextBox, also just instantiate it:
searchTxt = new TextBox
{
//set properties
};The TextBox will be used for searching data from our ms access database.
To create a ListView, also just instantiate it:
myListView = new ListView { ...}You also set properties to our listview as well. We are using the View.Details as our View type. We also set the FullRowSelect property to true so that clicking a single cell results to full row selection.
Then to add columns to listview:
myListView.Columns.Add("ID", 30);
myListView.Columns.Add("NAME", 130);
myListView.Columns.Add("PROPELLANT", 130);
myListView.Columns.Add("DESTINATION", 130);We can specify the Column header as well as column width as we have done above.
Then to add the controls to the Form, we use the Add() method of the Form.Controls property.
myForm.Controls.Add(searchLabel);
myForm.Controls.Add(searchTxt);
myForm.Controls.Add(myListView);How to Populate ListView with Data
Let's say we have some data being injected to our method as parameters, as we want to add them to multiple columns.
private void populate(string id, string name, string propellant, string destination)
{
myListView.Items.Add(new ListViewItem(new[] { id, name, propellant, destination }));
}As you can see first we invoke the Add() method of the Items property of ListView class.
This expects a ListViewItem object. So we instantiate the ListViewItem passing in a string array containing the data passed to us as arguments.
How to Search MS Access Database and Fill ListView
We make a server side search againts MS Access database, then fill the ListView with search results.
First let's create a method that receives the query or search terms as a parameter:
private void search(String searchTerm)
{
//search searchTerm
}First we'll clear the ListView to avoid duplicates:
myListView.Items.Clear();Then we'll create an SQL statement that will search multiple columns using the LIKE operator. We are searching multiple columns so we'll use the OR operator:
string sql = "SELECT * FROM spacecraftsTB WHERE S_Name LIKE '%" + searchTerm + "%' OR S_Propellant LIKE '%" + searchTerm + "%' OR S_Destination LIKE '%" + searchTerm + "%'";Then we represent the SQL statement in an OleDbCommand object, passing the SQL statement as well as the OleDbConnection object to it's constructor:
cmd = new OleDbCommand(sql, con);We'll then create a try-catch block to perform this search against our database, while catching any possible exception:
try
{
//search database here...
}
catch (Exception ex)
{
con.Close();
MessageBox.Show(ex.Message);
}To search the database first we have to open a connection:
con.Open();I will need an OleDbCommand which will allow us to execute the OleDbCommand we talked about earlier, so we instantiate it:
adapter = new OleDbDataAdapter(cmd);Then we will fill our datatable with our MS Access data, via the Fill method of the OleDbDataAdapter class:
adapter.Fill(dt);Then we create a foreach loop to iterate the rows of our datatable:
foreach (DataRow row in dt.Rows)
{
//populate listview
}Then we populate our listview with data from the database records/cells:
populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString());Then we close our OleDbConnection:
con.Close();Then we clear our database so as to avoid duplicate data:
dt.Rows.Clear();How to Setup Register ListView and TextBox Event Handlers
We've provided the user with a textbox to search data against our Microsoft access database.
However we want to perform a realtime search/filter, so we need to listen to TextChanged events. The TextChanged is an event for TextBox that allows us raised events as the user types or deletes text in a textbox.
So first we create a method to set these events registering them to their event handlers;
private void setUpEventHandlers()
{
//register events here.
}We register the TextChanged event:
searchTxt.TextChanged += searchTxt_TextChanged;After searching, we want to show what the user has typed in a messagebox. So we need to listen to ItemSelectionChanged event.
myListView.ItemSelectionChanged += myListView_ItemSelectionChanged;
Here's the event handler for TextChanged event of our TextBox:
void searchTxt_TextChanged(object sender, EventArgs e)
{
search(searchTxt.Text);
}On the other hand when the ItemSelectionChanged event is raised, we will first check if an item is selected then, get them and show the data in a messagebox:
void myListView_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
{
//Check if item is actually selected.
//If so get the selected items and hold them in strings.
//Then show them in a messagebox.
}
How to Create a Windows Form Programmatically and Run it
To do that you instantiate the System.Windows.Forms.Form class and pass it properties.
myForm = new Form
{
//properties
};Then we can invoke the createComponents():
createComponents();Then search empty to retrieve everything from database:
search("");We will later on invoke Run() method of the Application class to run the Form:
...
Application.Run(myForm);To run the Program, just instantiate the Program class inside the Main() method and invoke the createForm() method:
public static void Main()
{
new Program().createForm();
}Here's the full code:
Full Code
using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Windows.Forms;
namespace ListViewSearchAccess
{
partial class Program
{
/*
* INSTANCE FIELDS
*/
private ListView myListView;
private TextBox searchTxt;
private Form myForm;
private const string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Hp/Documents/DataBases/spacecraftsDB.mdb;";
readonly OleDbConnection con = new OleDbConnection(conString);
OleDbCommand cmd;
OleDbDataAdapter adapter;
readonly DataTable dt = new DataTable();
/*
* How to Fill ListView with MS Access data
*/
private void populate(string id, string name, string propellant, string destination)
{
myListView.Items.Add(new ListViewItem(new[] { id, name, propellant, destination }));
}
/*
* How to search multiple columns of data in MS Access database
*/
private void search(String searchTerm)
{
myListView.Items.Clear();
string sql = "SELECT * FROM spacecraftsTB WHERE S_Name LIKE '%" + searchTerm + "%' OR S_Propellant LIKE '%" + searchTerm + "%' OR S_Destination LIKE '%" + searchTerm + "%'";
cmd = new OleDbCommand(sql, con);
cmd = new OleDbCommand(sql, con);
try
{
//How to open database connection and fill adapter
con.Open();
adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dt);
//How to loop through datatable and fill our ListView
foreach (DataRow row in dt.Rows)
{
populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString());
}
//How to close connection and clear rows from our datatable.
con.Close();
dt.Rows.Clear();
}
catch (Exception ex)
{
con.Close();
MessageBox.Show(ex.Message);
}
}
/*
* How to register ListView and TextBox event handlers
*/
private void setUpEventHandlers()
{
myListView.ItemSelectionChanged += myListView_ItemSelectionChanged;
searchTxt.TextChanged += searchTxt_TextChanged;
}
/*
* How to implement TextBox TextChanged event thus search data.
*/
void searchTxt_TextChanged(object sender, EventArgs e)
{
search(searchTxt.Text);
}
/*
* How to show selected ListView item in a messagebox
*/
void myListView_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
{
try
{
if (e.IsSelected)
{
string id = myListView.SelectedItems[0].SubItems[0].Text;
string name = myListView.SelectedItems[0].SubItems[1].Text;
string propellant = myListView.SelectedItems[0].SubItems[2].Text;
string destination = myListView.SelectedItems[0].SubItems[3].Text;
MessageBox.Show(
"ID : " + id + "n" +
"NAME : " + name + "n" +
"PROPELLANT : " + propellant + "n" +
"DESTINATION : " + destination,
"Spaceship Details",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
catch (ArgumentOutOfRangeException argumentOutOfRangeException)
{
MessageBox.Show(argumentOutOfRangeException.Message);
}
}
/*
* How to programmatically create a form and run it.
*/
private void createForm()
{
myForm = new Form
{
AutoScaleMode = AutoScaleMode.Font,
Text = "C# MS Access :: ListView - MultiColumn SQL Search/Filter",
ClientSize = new Size(520, 585),
BackColor = Color.CadetBlue
};
createComponents();
search("");
setUpEventHandlers();
Application.EnableVisualStyles();
Application.Run(myForm);
}
/*
* Main method
*/
public static void Main()
{
new Program().createForm();
}
}
/*
* Our Partial Program class
* Create WinForms components for us.
* Exists in the same namespace.
*/
partial class Program
{
/*
* How to create windows forms components programmatically.
*/
public void createComponents()
{
/*
* Create a Label,set its properties
*/
Label searchLabel = new Label
{
Location = new Point(57, 106),
Size = new Size(38, 19),
Font = new Font("Segoe UI", 12F, FontStyle.Bold, GraphicsUnit.Pixel),
Text = "Filter: "
};
/*
* Create TextBoxes, set its properties
*/
searchTxt = new TextBox
{
Location = new Point(146, 106),
Size = new Size(173, 23),
TabIndex = 0
};
/*
* Create ListView, set its properties, add columns
*/
myListView = new ListView { Location = new Point(57, 165), Size = new Size(400, 400), View = View.Details, FullRowSelect = true, Alignment = ListViewAlignment.SnapToGrid };
myListView.Columns.Add("ID", 30);
myListView.Columns.Add("NAME", 130);
myListView.Columns.Add("PROPELLANT", 130);
myListView.Columns.Add("DESTINATION", 130);
/*
* Add Components to Form
*/
myForm.Controls.Add(searchLabel);
myForm.Controls.Add(searchTxt);
myForm.Controls.Add(myListView);
}
}
}
Result
C# MS Access Database
C# MS Access Search
C# MS Access Search
How to Run
- Create a console application.
- Copy paste the code above. It's just in a single file.
- Right click your the references section of your program, then choose
Add Reference. A dialog pops up, searchSystem.Windows.FormsandSystem.Drawingand add them.
Best regards.
