DataSet Class

Definition

Represents an in-memory cache of data.

public class DataSet : System.ComponentModel.MarshalByValueComponent, System.ComponentModel.IListSource, System.ComponentModel.ISupportInitializeNotification, System.Runtime.Serialization.ISerializable, System.Xml.Serialization.IXmlSerializable
Inheritance
Implements

Inherited Members

System.ComponentModel.MarshalByValueComponent

System.Object

Examples

The following example consists of several methods that, combined, create and fill a DataSet from the Northwind database.

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

namespace Microsoft.AdoNet.DataSetDemo
{
    class NorthwindDataSet
    {
        static void Main()
        {
            string connectionString = GetConnectionString();
            ConnectToData(connectionString);
        }

        private static void ConnectToData(string connectionString)
        {
            //Create a SqlConnection to the Northwind database.
            using (SqlConnection connection =
                       new SqlConnection(connectionString))
            {
                //Create a SqlDataAdapter for the Suppliers table.
                SqlDataAdapter adapter = new SqlDataAdapter();

                // A table mapping names the DataTable.
                adapter.TableMappings.Add("Table", "Suppliers");

                // Open the connection.
                connection.Open();
                Console.WriteLine("The SqlConnection is open.");

                // Create a SqlCommand to retrieve Suppliers data.
                SqlCommand command = new SqlCommand(
                    "SELECT SupplierID, CompanyName FROM dbo.Suppliers;",
                    connection);
                command.CommandType = CommandType.Text;

                // Set the SqlDataAdapter's SelectCommand.
                adapter.SelectCommand = command;

                // Fill the DataSet.
                DataSet dataSet = new DataSet("Suppliers");
                adapter.Fill(dataSet);

                // Create a second Adapter and Command to get
                // the Products table, a child table of Suppliers. 
                SqlDataAdapter productsAdapter = new SqlDataAdapter();
                productsAdapter.TableMappings.Add("Table", "Products");

                SqlCommand productsCommand = new SqlCommand(
                    "SELECT ProductID, SupplierID FROM dbo.Products;",
                    connection);
                productsAdapter.SelectCommand = productsCommand;

                // Fill the DataSet.
                productsAdapter.Fill(dataSet);

                // Close the connection.
                connection.Close();
                Console.WriteLine("The SqlConnection is closed.");

                // Create a DataRelation to link the two tables
                // based on the SupplierID.
                DataColumn parentColumn =
                    dataSet.Tables["Suppliers"].Columns["SupplierID"];
                DataColumn childColumn =
                    dataSet.Tables["Products"].Columns["SupplierID"];
                DataRelation relation =
                    new System.Data.DataRelation("SuppliersProducts",
                    parentColumn, childColumn);
                dataSet.Relations.Add(relation);
                Console.WriteLine(
                    "The {0} DataRelation has been created.",
                    relation.RelationName);
            }
        }

        static private string GetConnectionString()
        {
            // To avoid storing the connection string in your code, 
            // you can retrieve it from a configuration file.
            return "Data Source=(local);Initial Catalog=Northwind;"
                + "Integrated Security=SSPI";
        }
    }
}
Option Explicit On
Option Strict On

Imports System.Data
Imports system.Data.SqlClient

Public Class NorthwindDataSet

    Public Shared Sub Main()
        Dim connectionString As String = _
            GetConnectionString()
        ConnectToData(connectionString)
    End Sub

    Private Shared Sub ConnectToData( _
        ByVal connectionString As String)

        ' Create a SqlConnection to the Northwind database.
        Using connection As SqlConnection = New SqlConnection( _
           connectionString)

            ' Create a SqlDataAdapter for the Suppliers table.
            Dim suppliersAdapter As SqlDataAdapter = _
               New SqlDataAdapter()

            ' A table mapping names the DataTable.
            suppliersAdapter.TableMappings.Add("Table", "Suppliers")

            ' Open the connection.
            connection.Open()
            Console.WriteLine("The SqlConnection is open.")

            ' Create a SqlCommand to retrieve Suppliers data.
            Dim suppliersCommand As SqlCommand = New SqlCommand( _
               "SELECT SupplierID, CompanyName FROM dbo.Suppliers;", _
               connection)
            suppliersCommand.CommandType = CommandType.Text

            ' Set the SqlDataAdapter's SelectCommand.
            suppliersAdapter.SelectCommand = suppliersCommand

            ' Fill the DataSet.
            Dim dataSet As DataSet = New DataSet("Suppliers")
            suppliersAdapter.Fill(dataSet)

            ' Create a second SqlDataAdapter and SqlCommand to get
            ' the Products table, a child table of Suppliers. 
            Dim productsAdapter As SqlDataAdapter = _
                New SqlDataAdapter()
            productsAdapter.TableMappings.Add("Table", "Products")

            Dim productsCommand As SqlCommand = New SqlCommand( _
               "SELECT ProductID, SupplierID FROM dbo.Products;", _
               connection)
            productsAdapter.SelectCommand = productsCommand

            ' Fill the DataSet.
            productsAdapter.Fill(dataSet)

            ' Close the connection.
            connection.Close()
            Console.WriteLine("The SqlConnection is closed.")

            ' Create a DataRelation to link the two tables
            ' based on the SupplierID.
            Dim parentColumn As DataColumn = _
               dataSet.Tables("Suppliers").Columns("SupplierID")
            Dim childColumn As DataColumn = _
               dataSet.Tables("Products").Columns("SupplierID")
            Dim relation As DataRelation = New _
               System.Data.DataRelation("SuppliersProducts", _
               parentColumn, childColumn)
            dataSet.Relations.Add(relation)

            Console.WriteLine( _
               "The {0} DataRelation has been created.", _
               relation.RelationName)
        End Using

    End Sub

    Private Shared Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file.
        Return "Data Source=(local);Initial Catalog=Northwind;" _
           & "Integrated Security=SSPI;"
    End Function
End Class

Remarks

The DataSet, which is an in-memory cache of data retrieved from a data source, is a major component of the ADO.NET architecture. The DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects. You can also enforce data integrity in the DataSet by using the UniqueConstraint and ForeignKeyConstraint objects. For further details about working with DataSet objects, see DataSets, DataTables, and DataViews.

Whereas DataTable objects contain the data, the DataRelationCollection allows you to navigate though the table hierarchy. The tables are contained in a DataTableCollection accessed through the Tables property. When accessing DataTable objects, note that they are conditionally case sensitive. For example, if one DataTable is named "mydatatable" and another is named "Mydatatable", a string used to search for one of the tables is regarded as case sensitive. However, if "mydatatable" exists and "Mydatatable" does not, the search string is regarded as case insensitive. For more information about working with DataTable objects, see Creating a DataTable.

A DataSet can read and write data and schema as XML documents. The data and schema can then be transported across HTTP and used by any application, on any platform that is XML-enabled. You can save the schema as an XML schema with the WriteXmlSchema method, and both schema and data can be saved using the WriteXml method. To read an XML document that includes both schema and data, use the ReadXml method.

In a typical multiple-tier implementation, the steps for creating and refreshing a DataSet, and in turn, updating the original data are to:

  1. Build and fill each DataTable in a DataSet with data from a data source using a DataAdapter.

  2. Change the data in individual DataTable objects by adding, updating, or deleting DataRow objects.

  3. Invoke the GetChanges method to create a second DataSet that features only the changes to the data.

  4. Call the Update method of the DataAdapter, passing the second DataSet as an argument.

  5. Invoke the Merge method to merge the changes from the second DataSet into the first.

  6. Invoke the AcceptChanges on the DataSet. Alternatively, invoke RejectChanges to cancel the changes.

Note

The DataSet and DataTable objects inherit from MarshalByValueComponent, and support the ISerializable interface for remoting. These are the only ADO.NET objects that can be remoted.

Note

Classes inherited from DataSet are not finalized by the garbage collector, because the finalizer has been suppressed in DataSet. The derived class can call the ReRegisterForFinalize method in its constructor to allow the class to be finalized by the garbage collector.

Constructors

Data<wbr>Set()

Initializes a new instance of the DataSet class.

Data<wbr>Set(String)

Initializes a new instance of a DataSet class with the given name.

Data<wbr>Set(Serialization<wbr>Info, Streaming<wbr>Context)

Initializes a new instance of a DataSet class that has the given serialization information and context.

Data<wbr>Set(Serialization<wbr>Info, Streaming<wbr>Context, Boolean)

Initializes a new instance of the DataSet class.

Properties

Case<wbr>Sensitive

Gets or sets a value indicating whether string comparisons within DataTable objects are case-sensitive.

Data<wbr>Set<wbr>Name

Gets or sets the name of the current DataSet.

Default<wbr>View<wbr>Manager

Gets a custom view of the data contained in the DataSet to allow filtering, searching, and navigating using a custom DataViewManager.

Enforce<wbr>Constraints

Gets or sets a value indicating whether constraint rules are followed when attempting any update operation.

Extended<wbr>Properties

Gets the collection of customized user information associated with the DataSet.

Has<wbr>Errors

Gets a value indicating whether there are errors in any of the DataTable objects within this DataSet.

Is<wbr>Initialized

Gets a value that indicates whether the DataSet is initialized.

Locale

Gets or sets the locale information used to compare strings within the table.

Namespace

Gets or sets the namespace of the DataSet.

Prefix

Gets or sets an XML prefix that aliases the namespace of the DataSet.

Relations

Get the collection of relations that link tables and allow navigation from parent tables to child tables.

Remoting<wbr>Format

Gets or sets a SerializationFormat for the DataSet used during remoting.

Schema<wbr>Serialization<wbr>Mode

Gets or sets a SchemaSerializationMode for a DataSet.

Site

Gets or sets an ISite for the DataSet.

Tables

Gets the collection of tables contained in the DataSet.

Methods

Accept<wbr>Changes()

Commits all the changes made to this DataSet since it was loaded or since the last time AcceptChanges() was called.

Begin<wbr>Init()

Begins the initialization of a DataSet that is used on a form or used by another component. The initialization occurs at run time.

Clear()

Clears the DataSet of any data by removing all rows in all tables.

Clone()

Copies the structure of the DataSet, including all DataTable schemas, relations, and constraints. Does not copy any data.

Copy()

Copies both the structure and data for this DataSet.

Create<wbr>Data<wbr>Reader(Data<wbr>Table[])

Returns a DataTableReader with one result set per DataTable.

Create<wbr>Data<wbr>Reader()

Returns a DataTableReader with one result set per DataTable, in the same sequence as the tables appear in the Tables collection.

Determine<wbr>Schema<wbr>Serialization<wbr>Mode(Xml<wbr>Reader)

Determines the SchemaSerializationMode for a DataSet.

Determine<wbr>Schema<wbr>Serialization<wbr>Mode(Serialization<wbr>Info, Streaming<wbr>Context)

Determines the SchemaSerializationMode for a DataSet.

End<wbr>Init()

Ends the initialization of a DataSet that is used on a form or used by another component. The initialization occurs at run time.

Get<wbr>Changes()

Gets a copy of the DataSet that contains all changes made to it since it was loaded or since AcceptChanges() was last called.

Get<wbr>Changes(Data<wbr>Row<wbr>State)

Gets a copy of the DataSet containing all changes made to it since it was last loaded, or since AcceptChanges() was called, filtered by DataRowState.

Get<wbr>Data<wbr>Set<wbr>Schema(Xml<wbr>Schema<wbr>Set)

Gets a copy of XmlSchemaSet for the DataSet.

Get<wbr>Object<wbr>Data(Serialization<wbr>Info, Streaming<wbr>Context)

Populates a serialization information object with the data needed to serialize the DataSet.

Get<wbr>Schema<wbr>Serializable()

Returns a serializable System.Xml.Schema.XMLSchema instance.

Get<wbr>Serialization<wbr>Data(Serialization<wbr>Info, Streaming<wbr>Context)

Deserializes the table data from the binary or XML stream.

Get<wbr>Xml()

Returns the XML representation of the data stored in the DataSet.

Get<wbr>Xml<wbr>Schema()

Returns the XML Schema for the XML representation of the data stored in the DataSet.

Has<wbr>Changes()

Gets a value indicating whether the DataSet has changes, including new, deleted, or modified rows.

Has<wbr>Changes(Data<wbr>Row<wbr>State)

Gets a value indicating whether the DataSet has changes, including new, deleted, or modified rows, filtered by DataRowState.

Infer<wbr>Xml<wbr>Schema(Stream, String[])

Applies the XML schema from the specified Stream to the DataSet.

Infer<wbr>Xml<wbr>Schema(Text<wbr>Reader, String[])

Applies the XML schema from the specified TextReader to the DataSet.

Infer<wbr>Xml<wbr>Schema(String, String[])

Applies the XML schema from the specified file to the DataSet.

Infer<wbr>Xml<wbr>Schema(Xml<wbr>Reader, String[])

Applies the XML schema from the specified XmlReader to the DataSet.

Initialize<wbr>Derived<wbr>Data<wbr>Set()

Deserialize all of the tables data of the DataSet from the binary or XML stream.

Is<wbr>Binary<wbr>Serialized(Serialization<wbr>Info, Streaming<wbr>Context)

Inspects the format of the serialized representation of the DataSet.

Load(IData<wbr>Reader, Load<wbr>Option, Data<wbr>Table[])

Fills a DataSet with values from a data source using the supplied IDataReader, using an array of DataTable instances to supply the schema and namespace information.

Load(IData<wbr>Reader, Load<wbr>Option, String[])

Fills a DataSet with values from a data source using the supplied IDataReader, using an array of strings to supply the names for the tables within the DataSet.

Load(IData<wbr>Reader, Load<wbr>Option, Fill<wbr>Error<wbr>Event<wbr>Handler, Data<wbr>Table[])

Fills a DataSet with values from a data source using the supplied IDataReader, using an array of DataTable instances to supply the schema and namespace information.

Merge(Data<wbr>Row[])

Merges an array of DataRow objects into the current DataSet.

Merge(Data<wbr>Set)

Merges a specified DataSet and its schema into the current DataSet.

Merge(Data<wbr>Table)

Merges a specified DataTable and its schema into the current DataSet.

Merge(Data<wbr>Set, Boolean)

Merges a specified DataSet and its schema into the current DataSet, preserving or discarding any changes in this DataSet according to the given argument.

Merge(Data<wbr>Row[], Boolean, Missing<wbr>Schema<wbr>Action)

Merges an array of DataRow objects into the current DataSet, preserving or discarding changes in the DataSet and handling an incompatible schema according to the given arguments.

Merge(Data<wbr>Set, Boolean, Missing<wbr>Schema<wbr>Action)

Merges a specified DataSet and its schema with the current DataSet, preserving or discarding changes in the current DataSet and handling an incompatible schema according to the given arguments.

Merge(Data<wbr>Table, Boolean, Missing<wbr>Schema<wbr>Action)

Merges a specified DataTable and its schema into the current DataSet, preserving or discarding changes in the DataSet and handling an incompatible schema according to the given arguments.

On<wbr>Property<wbr>Changing(Property<wbr>Changed<wbr>Event<wbr>Args)

Raises the OnPropertyChanging(PropertyChangedEventArgs) event.

On<wbr>Remove<wbr>Relation(Data<wbr>Relation)

Occurs when a DataRelation object is removed from a DataTable.

On<wbr>Remove<wbr>Table(Data<wbr>Table)

Occurs when a DataTable is removed from a DataSet.

Raise<wbr>Property<wbr>Changing(String)

Sends a notification that the specified DataSet property is about to change.

Read<wbr>Xml(Xml<wbr>Reader, Xml<wbr>Read<wbr>Mode)

Reads XML schema and data into the DataSet using the specified XmlReader and XmlReadMode.

Read<wbr>Xml(String, Xml<wbr>Read<wbr>Mode)

Reads XML schema and data into the DataSet using the specified file and XmlReadMode.

Read<wbr>Xml(Stream, Xml<wbr>Read<wbr>Mode)

Reads XML schema and data into the DataSet using the specified Stream and XmlReadMode.

Read<wbr>Xml(Text<wbr>Reader, Xml<wbr>Read<wbr>Mode)

Reads XML schema and data into the DataSet using the specified TextReader and XmlReadMode.

Read<wbr>Xml(String)

Reads XML schema and data into the DataSet using the specified file.

Read<wbr>Xml(Text<wbr>Reader)

Reads XML schema and data into the DataSet using the specified TextReader.

Read<wbr>Xml(Stream)

Reads XML schema and data into the DataSet using the specified Stream.

Read<wbr>Xml(Xml<wbr>Reader)

Reads XML schema and data into the DataSet using the specified XmlReader.

Read<wbr>Xml<wbr>Schema(Stream)

Reads the XML schema from the specified Stream into the DataSet.

Read<wbr>Xml<wbr>Schema(Text<wbr>Reader)

Reads the XML schema from the specified TextReader into the DataSet.

Read<wbr>Xml<wbr>Schema(String)

Reads the XML schema from the specified file into the DataSet.

Read<wbr>Xml<wbr>Schema(Xml<wbr>Reader)

Reads the XML schema from the specified XmlReader into the DataSet.

Read<wbr>Xml<wbr>Serializable(Xml<wbr>Reader)

Ignores attributes and returns an empty DataSet.

Reject<wbr>Changes()

Rolls back all the changes made to the DataSet since it was created, or since the last time AcceptChanges() was called.

Reset()

Clears all tables and removes all relations, foreign constraints, and tables from the DataSet. Subclasses should override Reset() to restore a DataSet to its original state.

Should<wbr>Serialize<wbr>Relations()

Gets a value indicating whether Relations property should be persisted.

Should<wbr>Serialize<wbr>Tables()

Gets a value indicating whether Tables property should be persisted.

Write<wbr>Xml(Xml<wbr>Writer, Xml<wbr>Write<wbr>Mode)

Writes the current data, and optionally the schema, for the DataSet using the specified XmlWriter and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.

Write<wbr>Xml(String, Xml<wbr>Write<wbr>Mode)

Writes the current data, and optionally the schema, for the DataSet to the specified file using the specified XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.

Write<wbr>Xml(Text<wbr>Writer, Xml<wbr>Write<wbr>Mode)

Writes the current data, and optionally the schema, for the DataSet using the specified TextWriter and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.

Write<wbr>Xml(Stream, Xml<wbr>Write<wbr>Mode)

Writes the current data, and optionally the schema, for the DataSet using the specified Stream and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.

Write<wbr>Xml(String)

Writes the current data for the DataSet to the specified file.

Write<wbr>Xml(Text<wbr>Writer)

Writes the current data for the DataSet using the specified TextWriter.

Write<wbr>Xml(Xml<wbr>Writer)

Writes the current data for the DataSet to the specified XmlWriter.

Write<wbr>Xml(Stream)

Writes the current data for the DataSet using the specified Stream.

Write<wbr>Xml<wbr>Schema(String, Converter<Type,String>)

Writes the DataSet structure as an XML schema to a file.

Write<wbr>Xml<wbr>Schema(Stream)

Writes the DataSet structure as an XML schema to the specified Stream object.

Write<wbr>Xml<wbr>Schema(Text<wbr>Writer)

Writes the DataSet structure as an XML schema to the specified TextWriter object.

Write<wbr>Xml<wbr>Schema(String)

Writes the DataSet structure as an XML schema to a file.

Write<wbr>Xml<wbr>Schema(Xml<wbr>Writer)

Writes the DataSet structure as an XML schema to an XmlWriter object.

Write<wbr>Xml<wbr>Schema(Stream, Converter<Type,String>)

Writes the DataSet structure as an XML schema to the specified Stream object.

Write<wbr>Xml<wbr>Schema(Text<wbr>Writer, Converter<Type,String>)

Writes the DataSet structure as an XML schema to the specified TextWriter.

Write<wbr>Xml<wbr>Schema(Xml<wbr>Writer, Converter<Type,String>)

Writes the DataSet structure as an XML schema to the specified XmlWriter.

Events

Initialized

Occurs after the DataSet is initialized.

Merge<wbr>Failed

Occurs when a target and source DataRow have the same primary key value, and EnforceConstraints is set to true.

Explicit Interface Implementations

IList<wbr>Source.<wbr>Contains<wbr>List<wbr>Collection

For a description of this member, see ContainsListCollection.

IList<wbr>Source.<wbr>Get<wbr>List()

For a description of this member, see GetList().

IXml<wbr>Serializable.<wbr>Get<wbr>Schema()

For a description of this member, see GetSchema().

IXml<wbr>Serializable.<wbr>Read<wbr>Xml(Xml<wbr>Reader)

For a description of this member, see ReadXml(XmlReader).

IXml<wbr>Serializable.<wbr>Write<wbr>Xml(Xml<wbr>Writer)

For a description of this member, see WriteXml(XmlWriter).

Thread Safety

This type is safe for multithreaded read operations. You must synchronize any write operations.