Overview

• The DataSets and DataTables mimic the structure of a relational database in memory in ADO.NET.
• A DataSet that is created after executing a query against a connected database can then be manipulated in memory, and the changes can be sent back by using a DataAdapter.
• The DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application.
• The DataSet contains a collection of one or more DataTable objects consisting of rows and columns of data, and also primary key, foreign key, constraint, and relation information about the data in the DataTable objects.

DataAdapter

• A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet.
• The DataAdapter also resolves changes made to the DataSet back to the data source.
• It uses the Connection object to connect to a data source, and it uses Command objects to retrieve data from and resolve changes to the data source.

using (var connection = new SqlConnection("..."))
{
    var dataAdpater = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", connection.ConnectionString)
    {
        UpdateCommand = new SqlCommand(
            "UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID", connection)
    };
    var parameter = dataAdpater.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int);
    parameter.SourceColumn = "CategoryID";
    parameter.SourceVersion = DataRowVersion.Original;

    dataAdpater.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");

    var categoryTable = new DataTable();
    dataAdpater.Fill(categoryTable);

    var categoryRow = categoryTable.Rows[0];
    categoryRow["CategoryName"] = "New Beverages";

    dataAdpater.Update(categoryTable);

    foreach (DataRow row in categoryTable.Rows)
        Console.WriteLine("{0}: {1}", row[0], row[1]);
}

Ordering of Inserts, Updates, and Deletes

DataTable table = dataSet.Tables["Customers"];  

// First process deletes.  
adapter.Update(table.Select(null, null, DataViewRowState.Deleted));  

// Next process updates.
adapter.Update(table.Select(null, null, DataViewRowState.ModifiedCurrent));  

// Finally, process inserts.
adapter.Update(table.Select(null, null, DataViewRowState.Added));

DataView

• A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data-binding applications.
• Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.
• A DataView provides a dynamic view of data in the underlying DataTable: the content, ordering, and membership reflect changes as they occur.
• This behavior differs from the Select method of the DataTable, which returns a DataRow array from a table based on a particular filter and/or sort order: this content reflects changes to the underlying table, but its membership and ordering remain static. The dynamic capabilities of the DataTable make it ideal for data-binding applications.
• There are two ways to create a DataView: You can use the DataView constructor, or you can create a reference to the DefaultView property of the DataTable.
• The DataView constructor can be empty, or it can take either a DataTable as a single argument, or a DataTable along with filter criteria, sort criteria, and a row state filter.
• Because the index for a DataView is built both when the DataView is created, and when any of the Sort, RowFilter, or RowStateFilter properties are modified, you achieve best performance by supplying any initial sort order or filtering criteria as constructor arguments when you create the DataView.
• Creating a DataView without specifying sort or filter criteria and then setting the Sort, RowFilter, or RowStateFilter properties later causes the index to be built at least twice: once when the DataView is created, and again when any of the sort or filter properties are modified.
• Note that if you create a DataView using the constructor that does not take any arguments, you will not be able to use the DataView until you have set the Table property.
• The following code example demonstrates how to create a DataView using the DataView constructor.

DataView custDV1 = new DataView(custDS.Tables["Customers"],   
    "Country = 'USA'",
    "ContactName",
    DataViewRowState.CurrentRows);

DataView custDV2 = custDS.Tables["Customers"].DefaultView;

LINQ to DataSet

• Data sources that implement the IEnumerable<T> generic interface can be queried through LINQ.
• Calling AsEnumerable on a DataTable returns an object which implements the generic IEnumerable<T> interface, which serves as the data source for LINQ to DataSet queries.