How to implement a searchable DataGrid by using ADO.NET and Windows Forms by using Visual C# .NET or Visual C# 2005

Article translations Article translations
Article ID: 815680 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

This article describes how to search for data in a Windows Form DataGrid control and how to synchronize the grid with the search results. In this sample, you programmatically retrieve data into a DataView class, and then bind the DataView to the DataGrid. You can then type criteria in a text box, and then click Find to search for the record and move the pointer to the correct row in the DataGrid. This sample also indicates failure to locate a record.

back to the top

Description of the Technique

Set the Sort Order and Find the Records

To use the DataView.Find method to find a record, you must set the sort order. In this example, when you click the DataGrid column header, the DataGrid sets the sort order for you. The sample uses syntax to set the sort order programmatically. This is similar to the SQL ORDER BY syntax.
//Examples: Set the default sort order.
tblAuthors.DataView.Sort = "au_id";
tblAuthors.DefaultView.Sort = "au_id DESC";
tblAuthors.DefaultView.Sort = "f_name,l_name";   // multi-column sort/find
Users can also change the sort order visually by clicking the column headers in the DataGrid, so that when users click Find, they use that column sort order to perform their search.

Note The arrow in the column header indicates the current sort order, and the direction of the arrow indicates whether the order is ascending or descending.

back to the top

Retrieve the Current Sort Order Programmatically

To retrieve the current sort order and update the Label text, read the DataView.Sort property as follows:
lblFind.Text = "Enter Search Criteria " + dv.Sort; //Used in this example to set out label.
back to the top

Position the Record Pointer in a DataGrid

In this sample, you use the DataView.Find method to retrieve the record position, and to update the CurrencyManager object. This synchronizes the row pointer in the DataGrid. If you want to select the row beyond this, you can use the DataGrid.Select method.

This sample uses the CurrencyManager object to work with currency. You can also use the BindingManagerBase class. The BindingManagerBase class is the base class for the CurrencyManager. All data binding is performed through DataView classes. To synchronize your controls and the CurrencyManager, the controls must be bound to the same DataView.
// Search for the record in the DefaultView.
// If found, move the pointer to the correct record in the grid.
 int i;   
 i = dv.Find(txtFind.Text); // Locates record in DefaultView.
 // Does not move grid pointer or Currency Manager.
 if (i > dv.Table.Rows.Count || i < 0)
     MessageBox.Show("Record Not found");
 else
   // CM - CurrentlyManager
   CM.Position = i;  // Synchronizes Currency Manager and Grid Pointer.
back to the top

Create the Sample

  1. Start Microsoft Visual Studio .NET or Microsoft Visual Studio 2005.
  2. On the File menu, point to New, and then click Project.
  3. Click Visual C# Projects under Project Types, and then click Windows Application under Templates.

    Note In Visual Studio 2005, click Visual C# under Project Types.
  4. Drag a DataGrid control, a Button control, a Label control, and a TextBox control to the form.
  5. In the Properties pane, set the properties for the controls as follows:

    Form Control
    Name: frmFind
    Size: 800, 520
    Text: Search Form

    Form Label Control
    Name: lblFind
    AutoSize: True
    Location: 168, 456
    Text: Enter Search Criteria au_id

    TextBox Control
    Name: txtFind
    Location: 328, 456
    Size: 216, 20
    Text: Button

    Button Control
    Name: btnFind
    Location: 560, 456
    Size: 75, 32
    Text: Find

    DataGrid1 Control
    Name: grdFind
    Location: 16, 8
    Size: 760,432
  6. Use the using statement on the System.Data.SqlClient namespace so that you do not have to qualify declarations for this namespace later in your code. You must use this statement before Public Class frmFind, and before any other declarations. Add the following code to the frmFind declarations section:
    // Create namespace(s).
    using System.Data.SqlClient;
    
  7. Add the following variable declarations to frmFind after Public Class frmFind and before the rest of the code:
    //Modify this string to correctly connect to your SQL Server.
    static SqlConnection con = new SqlConnection("server=YourServerName;uid=YourUserId;pwd=YourPassword;database=pubs");
    SqlDataAdapter daAuthors = new SqlDataAdapter("Select * From Authors", con);
    DataSet ds = new DataSet();
    DataView dv;
    CurrencyManager CM;
  8. Add the following code to the frmFind_Load event:
    //Retrieve data from the Authors table.
    daAuthors.Fill(ds, "Authors");
    dv = new DataView(ds.Tables["Authors"]);
    //Bind the data to the DataGrid.
    grdFind.DataSource = dv;
    //Set the default sort order.
    dv.Sort = "au_id";
    // Initialize CurrencyManager to hold an instance of the form's CurrencyManager.
    CM = (System.Windows.Forms.CurrencyManager)grdFind.BindingContext[dv];
  9. Add the following code to the btnFind_Click event:
    // Verify that the user typed test to search for.
     if(txtFind.Text == "")
    {
       MessageBox.Show("Enter some criteria to find.");
       txtFind.Focus();
    }
    else
    {
    	// Search for the record in the DefaultView.
    	// If found, move the pointer to the correct record in the grid.
    	int i;   
    	i = dv.Find(txtFind.Text); // Locates record in DefaultView.
                                                 // Does not move grid pointer or CM.
     if(i > dv.Table.Rows.Count || i < 0)
     	MessageBox.Show("Record Not found");
     else
     	CM.Position = i;  // Synchronizes Cm and Grid Pointer.
    }
    
  10. Add the ListChanged event handler in the frmFind_Load function. The code looks as follows:
    ...
    dv = new DataView(ds.Tables["Authors"]);
    dv.ListChanged += new ListChangedEventHandler(dv_ListChangedEvent);
    ...
  11. Add the dv_ListChangedEvent event handler method. The code looks as follows:
    private void dv_ListChangedEvent(object sender, ListChangedEventArgs e)
    {
    }
  12. Add the following code to the dv_ListChangedEvent event handler method:
    // This allows the Label2.Text to be correctly displayed.
    if(dv.Sort.Substring((dv.Sort.Length - 4), 4) == "DESC")
    	lblFind.Text = "Enter Search Criteria " + dv.Sort.Substring(0, dv.Sort.Length - 5);
    else
    	lblFind.Text = "Enter Search Criteria " + dv.Sort;
    
  13. In Solution Explorer, select the project to set frmFind as the startup form.
    Note By default, the project is named WindowsApplication1. To rename the form, on the Project menu, click Properties. In the Startup Object list box, click frmFind, and then click OK to save your changes.
  14. Save, and then run the application.
  15. Click the DataGrid header to change the sort order. Type criteria in the text box, and then click Find. Notice that the record pointer is correctly positioned in the DataGrid.
back to the top

REFERENCES


For more information about ADO.NET, the DataSet object, the methods in this article, and SQL Server, visit the following Microsoft Developer Network (MSDN) Web sites:
Energize Your Data Retrieval Code with ADO.NET Objects
http://msdn.microsoft.com/columns/
ADO.NET for the ADO Programmer
http://msdn2.microsoft.com/en-us/library/ms973217.aspx
BindingManagerBase Class
http://msdn2.microsoft.com/en-us/library/system.windows.forms.bindingmanagerbase(vs.71).aspx
CurrencyManager Class
http://msdn2.microsoft.com/en-us/library/system.windows.forms.currencymanager(vs.71).aspx
PropertyManager Class
http://msdn2.microsoft.com/en-us/library/system.windows.forms.propertymanager(vs.71).aspx
DataView Class
http://msdn2.microsoft.com/en-us/library/system.data.dataview(vs.71).aspx
MSDN Online .NET Developer Center
http://msdn2.microsoft.com/en-us/netframework/default.aspx
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
168336 HOW TO: Open ADO Connection and Recordset Objects

For a Microsoft Visual Basic 6.0 version of this article, see 266654.
back to the top

Properties

Article ID: 815680 - Last Review: May 13, 2007 - Revision: 3.5
APPLIES TO
  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
  • Microsoft Visual C# 2005 Express Edition
Keywords: 
kbdataview kbhowtomaster kbhowto KB815680

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com