Knowledge Base

How to implement a searchable DataGrid control by using ADO.NET and Windows Forms in Visual C++

Article ID: 815679

Article Last Modified on 5/16/2007

APPLIES TO

Microsoft Visual C++ .NET 2003 Standard Edition
Microsoft ADO.NET 1.1
Microsoft ADO.NET 1.0
Microsoft Visual C++ 2005 Express Edition

For a Microsoft Visual C# .NET version of this article, see 815680.

For a Microsoft Visual Basic .NET version of this article, see 308070.

IN THIS TASK

SUMMARY
- Description of the technique
- Create the sample
REFERENCES

SUMMARY

This article describes how to search for data in a Microsoft Windows Forms 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 to move the record pointer to the correct row in the DataGrid control. This sample also indicates if the search cannot locate a record.

back to the top

Requirements

This article assumes that you are familiar with the following topics:

Microsoft ADO.NET
Microsoft Windows Forms controls

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

Microsoft Visual C++ .NET
Microsoft Visual C++ 2005

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 column header in the DataGrid control, the DataGrid control sets the sort order for you. The sample uses syntax to set the sort order programmatically. This syntax is similar to the SQL ORDER BY syntax.

//Examples: Set the default sort order.
tblAuthors->DefaultView->Sort = "au_id";
tblAuthors->DefaultView->Sort = "au_id DESC";
tblAuthors->DefaultView->Sort = "f_name,l_name";   // multicolumn sort/find

You can also change the sort order visually by clicking the column headers in the DataGrid control. When you click Find, you use that column sort order to perform your search.

Note The arrow in the column header indicates the current sort order. 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 to update the Label text, read the DataView.Sort property as follows.

lblFind->Text = System::String::Concat("Enter Search Criteria ",dv->Sort); //Used in this example to set the label.

back to the top

Position the record pointer in a DataGrid control

In this sample, you use the DataView.Find method to retrieve the record position and to update the CurrencyManager object. This action synchronizes the record pointer in the DataGrid control. If you want to select the row, 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 object. All data binding is performed through DataView classes. To synchronize your controls and the CurrencyManager object, the controls must be bound to the same DataView class.

// Search for the record in the DefaultView.
// If the record is found, move the record pointer to the correct record in the grid.
int i;
i = dv->Find(txtFind->Text);// Locates record in DefaultView.
                                                        // Does not move the record pointer or the CurrencyManager object.
if(i > dv->Table->Rows->Count || i < 0)
    MessageBox::Show("Record Not found");
else
    CM->Position = i;  // Synchronizes the CurrencyManager object and the record pointer.

back to the top

Create the sample

Start Microsoft Visual Studio .NET 2003 or Microsoft Visual Studio 2005.
On the File menu, point to New, and then click Project.
Under Project Types, click Visual C++ Projects.

Note In Visual Studio 2005, click Visual C++ under Project Types.
Under Templates, click Windows Forms Application (.NET).

Note In Visual Studio 2005, click Windows Forms Application under Templates.
In the Name box, type 815679, and then click OK. By default, the Form1 form is created.
Add a DataGrid control, a Button control, a Label control, and a TextBox control to the form.
In the Properties window, set the properties for these controls as follows:
- Form control
  Name: frmFind
  Size: 800, 520
  Text: Search 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
- DataGrid control
  Name: grdFind
  Location: 16, 8
  Size: 760,432
Use the using directive 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 class frmFind.
```
using namespace System::Data::SqlClient;
```
Add the following variable declarations to the frmFind class before the existing code.
```
SqlConnection* conn;
SqlDataAdapter* daAuthors;
DataSet* ds;
DataView* dv;
CurrencyManager* CM;
```
Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile this code sample. To do this, follow these steps:
1. Click Project, and then click ProjectName Properties.
  
  Note ProjectName represents the name of the project.
2. Expand Configuration Properties, and then click General.
3. Click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project setting on the right pane, click Apply, and then click OK.
For more information about the common language runtime support compiler options, visit the following Microsoft Web site:

/clr (Common Language Runtime Compilation)
http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx

These steps apply to the whole article.

Add the following code to the frmFind_Load event.

Note You must modify the connection string to connect correctly to your computer that is running Microsoft SQL Server.

//Modify this string to connect correctly to your computer that is running SQL Server.
conn = new SqlConnection("server=<YourServerName>;uid=<YourUserId>;pwd=<YourPassword>;database=pubs");
daAuthors = new SqlDataAdapter("Select * From Authors", conn);
ds = new DataSet();

    //Retrieve data from the Authors table.
daAuthors->Fill(ds, "Authors");
    dv = new DataView(ds->Tables->get_Item("Authors"));
    //Bind the data to the DataGrid control.
    grdFind->DataSource = dv;
    //Set the default sort order.
    dv->Sort = "au_id";
    // Initialize CurrencyManager to hold an instance of the form's CurrencyManager object.
    CM = __try_cast<System::Windows::Forms::CurrencyManager *>(grdFind->BindingContext->get_Item(dv));
//Register the ListChanged event of the DataView class.
dv->ListChanged += new System::ComponentModel::ListChangedEventHandler(this, dv_ListChangedEvent);

Add the following code to the btnFind_Click event handler.

// Verify that the user typed text to search for.
if(txtFind->Text->Equals(String::Empty))
{
    MessageBox::Show("Enter some criteria to find.");
    txtFind->Focus();
}
else
{
    // Search for the record in the DefaultView.
    // If the record is found, move the record pointer to the correct record in the grid.
    int i;
    i = dv->Find(txtFind->Text);// Locates record in DefaultView.
    if(i > dv->Table->Rows->Count || i < 0)
        MessageBox::Show("Record Not found");
    else
        CM->Position = i;  // Synchronizes the CurrencyManager object and the record pointer.
}

Add the dv_ListChangedEvent event handler.

private: void dv_ListChangedEvent(Object* sender, System::ComponentModel::ListChangedEventArgs* args)
{
    // This code permits the Label2.Text to be displayed correctly.
    if(dv->Sort->Substring((dv->Sort->Length - 4), 4) == "DESC")
        lblFind->set_Text(System::String::Concat("Enter Search Criteria ",dv->Sort->Substring(0, dv->Sort->Length - 5)));
    else
        lblFind->set_Text(System::String::Concat("Enter Search Criteria ",dv->Sort));
}

Open the source file Form1.cpp, and then modify the code of the Application::Run method to reflect the new name of the form.
```
Application::Run(new frmFind());
```
Press CTRL+SHIFT+S to save the project.
Press CTRL+SHIFT+B to build the solution.
Press CTRL+F5 to run the project.
Click the DataGrid header to change the sort order, type some search criteria in the text box, and then click Find. Notice that the record pointer is positioned correctly in the DataGrid control.

back to the top

REFERENCES

For more information about ADO.NET, about the DataSet object, about the methods in this article, and about SQL Server, visit the following Microsoft Developer Network (MSDN) Web sites:

Energize Your Data Retrieval Code with ADO.NET Objects
http://msdn2.microsoft.com/en-us/library/ms810293.aspx

ADO.NET for the ADO Programmer
http://msdn2.microsoft.com/en-us/library/ms973217.aspx

Consumers of Data on Windows Forms
http://msdn2.microsoft.com/en-us/library/aa983578(VS.71).aspx

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

168336 HOWTO: Open ADO Connection and Recordset objects

266654 HOWTO: Implement dynamic search functionality to locate records in a DataGrid that is bound to a large ADO Recordset

back to the top

Additional query words: datagrid ado.net searchable winfrom winform cnet

Keywords: kbwindowsforms kbcode kbhowtomaster KB815679

Microsoft KB Archive/815679

Contents

How to implement a searchable DataGrid control by using ADO.NET and Windows Forms in Visual C++

IN THIS TASK

SUMMARY

Requirements

Description of the technique

Set the sort order and find the records

Retrieve the current sort order programmatically

Position the record pointer in a DataGrid control

Create the sample

REFERENCES