Edit

Query datasets in .NET Framework applications

  • Article

Note

Datasets and related classes are legacy .NET Framework technologies from the early 2000s that enable applications to work with data in memory while the applications are disconnected from the database. They are especially useful for applications that enable users to modify data and persist the changes back to the database. Although datasets have proven to be a very successful technology, we recommend that new .NET applications use Entity Framework Core. Entity Framework provides a more natural way to work with tabular data as object models, and it has a simpler programming interface.

To search for specific records in a dataset, use the FindBy method on the DataTable, write your own foreach statement to loop over the table's Rows collection, or use LINQ to DataSet.

Dataset case sensitivity

Within a dataset, table and column names are case-insensitive by default — that is, a table in a dataset called "Customers" can also be referred to as "Customers." This matches the naming conventions in many databases, including SQL Server. In SQL Server, the default behavior is that the names of data elements cannot be distinguished only by case.

Note

Unlike datasets, XML documents are case-sensitive, so the names of data elements defined in schemas are case-sensitive. For example, schema protocol allows the schema to define a table called Customers and a different table called customers. This can result in name collisions when a schema that contains elements that differ only by case is used to generate a dataset class.

Case sensitivity, however, can be a factor in how data is interpreted within the dataset. For example, if you filter data in a dataset table, the search criteria might return different results depending on whether the comparison is case-sensitive. You can control the case sensitivity of filtering, searching, and sorting by setting the dataset's CaseSensitive property. All the tables in the dataset inherit the value of this property by default. (You can override this property for each individual table by setting the table's CaseSensitive property.)

Locate a specific row in a data table

To find a row in a typed dataset with a primary key value

  • To locate a row, call the strongly typed FindBy method that uses the table's primary key.

    In the following example, the CustomerID column is the primary key of the Customers table. This means that the generated FindBy method is FindByCustomerID. The example shows how to assign a specific DataRow to a variable by using the generated FindBy method.

    NorthwindDataSet.CustomersRow customersRow = 
    northwindDataSet1.Customers.FindByCustomerID("ALFKI");

To find a row in an untyped dataset with a primary key value

  • Call the Find method of a DataRowCollection collection, passing the primary key as a parameter.

    The following example shows how to declare a new row called foundRow and assign it the return value of the Find method. If the primary key is found, the contents of column index 1 are displayed in a message box.

    string s = "primaryKeyValue";
DataRow foundRow = dataSet1.Tables["AnyTable"].Rows.Find(s);

if (foundRow != null) 
{
    MessageBox.Show(foundRow[0].ToString());
}
else
{
    MessageBox.Show("A row with the primary key of " + s + " could not be found");
}

Find rows by column values

To find rows based on the values in any column

  • Data tables are created with the Select method, which returns an array of DataRows based on the expression passed to the Select method. For more information about creating valid expressions, see the "Expression Syntax" section of the page about the Expression property.

    The following example shows how to use the Select method of the DataTable to locate specific rows.

    DataRow[] foundRows;
foundRows = dataSet1.Tables["Customers"].Select("CompanyName Like 'A%'");

When tables in a dataset are related, a DataRelation object can make the related records available in another table. For example, a dataset containing Customers and Orders tables can be made available.

You can use a DataRelation object to locate related records by calling the GetChildRows method of a DataRow in the parent table. This method returns an array of related child records. Or, you can call the GetParentRow method of a DataRow in the child table. This method returns a single DataRow from the parent table.

This page provides examples using typed datasets. For information about navigating relationships in untyped datasets, see Navigating DataRelations.

Note

If you are working in a Windows Forms application and using the data-binding features to display data, the designer-generated form might provide enough functionality for your application. For more information, see Bind controls to data in Visual Studio. Specifically, see Relationships in Datasets.

The following code examples demonstrate how to navigate up and down relationships in typed datasets. The code examples use typed DataRows (NorthwindDataSet.OrdersRow) and the generated FindByPrimaryKey (FindByCustomerID) methods to locate a desired row and return the related records. The examples compile and run correctly only if you have:

  • An instance of a dataset named NorthwindDataSet with a Customers table.

  • An Orders table.

  • A relationship named FK_Orders_Customersrelating the two tables.

Additionally, both tables need to be filled with data for any records to be returned.

To return the child records of a selected parent record

  • Call the GetChildRows method of a specific Customers data row, and return an array of rows from the Orders table:

    string custID = "ALFKI";
NorthwindDataSet.OrdersRow[] orders;

orders = (NorthwindDataSet.OrdersRow[])northwindDataSet.Customers.
    FindByCustomerID(custID).GetChildRows("FK_Orders_Customers");

MessageBox.Show(orders.Length.ToString());

To return the parent record of a selected child record

  • Call the GetParentRow method of a specific Orders data row, and return a single row from the Customers table:

    int orderID = 10707;
NorthwindDataSet.CustomersRow customer;

customer = (NorthwindDataSet.CustomersRow)northwindDataSet.Orders.
    FindByOrderID(orderID).GetParentRow("FK_Orders_Customers");

MessageBox.Show(customer.CompanyName);

Related content