Query datasets

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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

    Dim customersRow As 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");
}

    Dim s As String = "primaryKeyValue"
Dim foundRow As DataRow = DataSet1.Tables("AnyTable").Rows.Find(s)

If foundRow IsNot Nothing Then
    MsgBox(foundRow(1).ToString())
Else
    MsgBox("A row with the primary key of " & s & " could not be found")
End If

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%'");

    Dim foundRows() As Data.DataRow
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());

    Dim customerID As String = "ALFKI"
Dim orders() As NorthwindDataSet.OrdersRow

orders = CType(NorthwindDataSet.Customers.FindByCustomerID(customerID).
    GetChildRows("FK_Orders_Customers"), NorthwindDataSet.OrdersRow())

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

    Dim orderID As Integer = 10707
Dim customer As NorthwindDataSet.CustomersRow

customer = CType(NorthwindDataSet.Orders.FindByOrderID(orderID).
    GetParentRow("FK_Orders_Customers"), NorthwindDataSet.CustomersRow)

MessageBox.Show(customer.CompanyName)

See also