MSDN Documentation

Microsoft Developer Network

DataSets

A DataSet is an in-memory representation of data. It is a collection of zero or more tables, relationships, and constraints that represent a structured set of data.

Key Characteristics

  • Disconnected Architecture: DataSets are designed for use in disconnected environments. This means that a DataSet can be populated with data from a data source, and then the connection to the data source can be closed. The application can then manipulate the data in the DataSet without maintaining an active connection.
  • In-Memory Representation: All data within a DataSet is held in memory. This allows for fast querying and manipulation of data without round trips to the database.
  • Tabular Structure: A DataSet contains one or more DataTables. Each DataTable represents a single table of data and contains Rows and Columns.
  • Relationships and Constraints: DataSets can define relationships between tables (similar to foreign key relationships in a relational database) and enforce constraints (like unique keys) on the data.

Core Components of a DataSet

  • DataTable: Represents a single table of data. It contains a collection of DataColumns and DataRows.
  • DataColumn: Defines a column in a DataTable, including its name, data type, and other properties.
  • DataRow: Represents a single row of data within a DataTable.
  • DataRelation: Defines a relationship between two tables in a DataSet, allowing you to navigate between related rows.
  • Constraint: Enforces rules on the data within a DataTable, such as UniqueConstraint and ForeignKeyConstraint.

Creating and Populating a DataSet

You can create a DataSet programmatically or by using a SqlDataAdapter to fill it with data from a database.


using System;
using System.Data;
using System.Data.SqlClient;

// Assume connectionString is defined
string connectionString = "Your_Connection_String_Here";
string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

DataSet dataSet = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    adapter.Fill(dataSet, "Customers"); // Fill the DataSet with data into a table named "Customers"
}

// Accessing data
if (dataSet.Tables.Contains("Customers"))
{
    DataTable customersTable = dataSet.Tables["Customers"];
    foreach (DataRow row in customersTable.Rows)
    {
        Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}, Contact: {row["ContactName"]}");
    }
}

Working with Data in a DataSet

  • Adding Rows: You can add new rows to a DataTable within a DataSet.
  • Modifying Rows: Existing rows can be updated.
  • Deleting Rows: Rows can be removed from a DataTable.
  • Accepting Changes: After making changes, you can accept them to mark the current state as the original.
  • Getting Changes: You can retrieve rows that have been added, modified, or deleted using the GetChanges() method.

When to Use DataSets

DataSets are particularly useful when:

  • You need to work with data offline or in a disconnected scenario.
  • You need to perform complex operations on data in memory, such as merging data from multiple sources or performing extensive filtering and sorting.
  • You are working with XML data, as DataSets have strong support for XML.
  • You need to represent a complex data structure with multiple related tables.
Tip: While DataSets are powerful, they can consume significant memory, especially with large datasets. For simpler scenarios or when dealing with very large amounts of data, consider using DataReader for forward-only, read-only access, or consider ORMs like Entity Framework for more object-oriented data access.

Related Topics