| title | description | ms.date | ms.topic | dev_langs | author | ms.author | manager | ms.subservice | ||
|---|---|---|---|---|---|---|---|---|---|---|
Query ADO.NET datasets (.NET Framework) |
Query ADO.NET datasets in .NET Framework application development with Visual Studio and find a specific row, rows by column values, and related records. |
11/01/2023 |
how-to |
|
ghogen |
ghogen |
mijacobs |
data-tools |
[!INCLUDE Data access tech note]
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.
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 xref:System.Data.DataSet.CaseSensitive%2A 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 xref:System.Data.DataTable.CaseSensitive%2A property.)
-
To locate a row, call the strongly typed
FindBymethod that uses the table's primary key.In the following example, the
CustomerIDcolumn is the primary key of theCustomerstable. This means that the generatedFindBymethod isFindByCustomerID. The example shows how to assign a specific xref:System.Data.DataRow to a variable by using the generatedFindBymethod.:::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataEditing/CS/Form1.cs" id="Snippet18":::
-
Call the xref:System.Data.DataRowCollection.Find%2A method of a xref:System.Data.DataRowCollection collection, passing the primary key as a parameter.
The following example shows how to declare a new row called
foundRowand assign it the return value of the xref:System.Data.DataRowCollection.Find%2A method. If the primary key is found, the contents of column index 1 are displayed in a message box.:::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataEditing/CS/Form1.cs" id="Snippet19":::
-
Data tables are created with the xref:System.Data.DataTable.Select%2A method, which returns an array of xref:System.Data.DataRows based on the expression passed to the xref:System.Data.DataTable.Select%2A method. For more information about creating valid expressions, see the "Expression Syntax" section of the page about the xref:System.Data.DataColumn.Expression%2A property.
The following example shows how to use the xref:System.Data.DataTable.Select%2A method of the xref:System.Data.DataTable to locate specific rows.
:::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataEditing/CS/Form1.cs" id="Snippet20":::
When tables in a dataset are related, a xref:System.Data.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 xref:System.Data.DataRelation object to locate related records by calling the xref:System.Data.DataRow.GetChildRows%2A method of a xref:System.Data.DataRow in the parent table. This method returns an array of related child records. Or, you can call the xref:System.Data.DataRow.GetParentRow%2A method of a xref:System.Data.DataRow in the child table. This method returns a single xref:System.Data.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 xref:System.Data.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
NorthwindDataSetwith aCustomerstable. -
An
Orderstable. -
A relationship named
FK_Orders_Customersrelating the two tables.
Additionally, both tables need to be filled with data for any records to be returned.
-
Call the xref:System.Data.DataRow.GetChildRows%2A method of a specific
Customersdata row, and return an array of rows from theOrderstable::::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataDatasets/CS/Form1.cs" id="Snippet6":::
-
Call the xref:System.Data.DataRow.GetParentRow%2A method of a specific
Ordersdata row, and return a single row from theCustomerstable::::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataDatasets/CS/Form1.cs" id="Snippet7":::