Query datasets

Applies to: Visual Studio Visual Studio for Mac

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.

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

Access related records

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.

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

• Dataset tools in Visual Studio