ADO.NET DataSet

Back to: ADO.NET Tutorial For Beginners and Professionals

ADO.NET DataSet in C# with Examples

In this article, I am going to discuss ADO.NET DataSet in C# with Examples. Please read our previous article, where we discussed ADO.NET DataTable with Examples. At the end of this article, you will understand what exactly ADO.NET DataSet is and when and how to create and use DataSet in .NET applications.

What is ADO.NET DataSet in C#?

The DataSet represents a subset of the database in memory. That means the ADO.NET DataSet is a collection of tables containing relational data in memory in tabular format.

It does not require a continuous open or active connection to the database. The DataSet is based on the disconnected architecture. This is the reason why it is used to fetch the data without interacting with any data source. We will discuss the disconnected architecture of the data set in our upcoming articles.

Note: The ADO.NET DataSet class is the core component for providing data access in a distributed and disconnected environment. The ADO.NET DataSet class belongs to the System.Data namespace.

Signature of DataSet in C#:

The signature of the DataSet class is shown in the below image.

Let us first see an example to create and use a DataSet object, and then we will discuss the different constructors, properties, and methods of the DataSet object.

Example to understand DataSet in C#:

Let us understand how to create and use ADO.NET DataSet in C# with an example. Here, we want to create two data tables (Customers and Orders), and then we want to add both these data tables to the DataSet, and then we want to log the data into the console.

Creating Customers Data Table:

We have already discussed how to create a Data Table in our previous article. If you have not read that article, please read our ADO.NET DataTable article before proceeding. Please have a look at the below image. As you can see here, we created one DataTable with the name Customer. Then we created three data columns (ID of type Int32, Name of type string, and Mobile of type string) and added these three columns to the Customer data table. Finally, we created two data rows and added these two data rows to the Customer data table.

Creating Orders Data Table:

Please have a look at the following image. Here, you can see we created the DataTable with the name Orders. Then we created three data columns (Id of type Int32, CustomerId of type Int32, and Amount of type Int32) and added these three data columns to the Orders data table. Finally, we created two data rows and added these two data rows to the Orders data table.

Creating DataSet with DataTable:

As we already discussed, the DataSet is a collection of data tables. So, let’s create a DataSet object and then add the two data tables (Customers and Orders) into the DataSet. Please have a look at the following image. Here, first, we created an instance of the DataSet and then added the two data tables using the Tables property of the DataSet object.

Fetch DataTable from DataSet:

Now, let us see how to fetch the data table from the dataset. You can fetch the data table from a dataset in two ways i.e. using the index position and using the table name (if provided).

Fetching DataTable from DataSet using index position:

As we first add the Customers table to DataSet, the Customer table Index position is 0. If you want to iterate through the Customer’s table data, you could use a for-each loop to iterate, as shown in the image below.

Fetching DataTable From DataSet using Name:

The second data table we added to the dataset is Orders, which will be added at index position 1. Further, if you notice, while creating the data table, we have provided a name for the data table, i.e., Orders. Now, if you want to fetch the data table from the dataset, then you can use the name instead of the index position. The following image shows how to fetch the data table using the name and looping through the data using a for each loop.

Complete Example Code:

The following example code is self-explained. So, please go through the comment lines.

using System;
using System.Data;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Creating Customer Data Table
                DataTable Customer = new DataTable("Customer");

                // Adding Data Columns to the Customer Data Table
                DataColumn CustomerId = new DataColumn("ID", typeof(Int32));
                Customer.Columns.Add(CustomerId);
                DataColumn CustomerName = new DataColumn("Name", typeof(string));
                Customer.Columns.Add(CustomerName);
                DataColumn CustomerMobile = new DataColumn("Mobile", typeof(string));
                Customer.Columns.Add(CustomerMobile);

                //Adding Data Rows into Customer Data Table
                Customer.Rows.Add(101, "Anurag", "2233445566");
                Customer.Rows.Add(202, "Manoj", "1234567890");

                // Creating Orders Data Table
                DataTable Orders = new DataTable("Orders");

                // Adding Data Columns to the Orders Data Table
                DataColumn OrderId = new DataColumn("ID", typeof(System.Int32));
                Orders.Columns.Add(OrderId);
                DataColumn CustId = new DataColumn("CustomerId", typeof(Int32));
                Orders.Columns.Add(CustId);
                DataColumn OrderAmount = new DataColumn("Amount", typeof(int));
                Orders.Columns.Add(OrderAmount);

                //Adding Data Rows into Orders Data Table
                Orders.Rows.Add(10001, 101, 20000);
                Orders.Rows.Add(10002, 102, 30000);

                //Creating DataSet Object
                DataSet dataSet = new DataSet();

                //Adding DataTables into DataSet
                dataSet.Tables.Add(Customer);
                dataSet.Tables.Add(Orders);

                //Fetching Customer Data Table Data
                Console.WriteLine("Customer Table Data: ");
                //Fetching DataTable from Dataset using the Index position
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    //Accessing the data using string column name
                    Console.WriteLine(row["ID"] + ",  " + row["Name"] + ",  " + row["Mobile"]);

                    //Accessing the data using integer index position
                    //Console.WriteLine(row[0] + ",  " + row[1] + ",  " + row[2]);
                }

                Console.WriteLine();

                //Fetching Orders Data Table Data
                Console.WriteLine("Orders Table Data: ");

                //Fetching DataTable from the DataSet using the table name
                foreach (DataRow row in dataSet.Tables["Orders"].Rows)
                {
                    //Accessing the data using string column name
                    Console.WriteLine(row["ID"] + ",  " + row["CustomerId"] + ",  " + row["Amount"]);

                    //Accessing the data using integer index position
                    //Console.WriteLine(row[0] + ",  " + row[1] + ",  " + row[2]);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception Occurred: {ex.Message}");
            }
            Console.ReadKey();
        }
    }
}

Output:

Now, let us proceed and understand the different Constructors, Methods, and Properties of the DataSet class.

Constructors of DataSet in C#:

The DataSet in C# provides the following four constructors.

DataSet(): It initializes a new instance of the System.Data.DataSet class..
DataSet(string dataSetName): It initializes a new instance of a System.Data.DataSet class with the given name. Here, the string parameter dataSetName specifies the name of the System.Data.DataSet.
DataSet(SerializationInfo info, StreamingContext context): It initializes a new instance of a System.Data.DataSet class that has the given serialization information and context. The parameter info is the data needed to serialize or deserialize an object. The context specifies the source and destination of a given serialized stream.
DataSet(SerializationInfo info, StreamingContext context, bool ConstructSchema): It initializes a new instance of the System.Data.DataSet class.

Properties of DataSet in C#:

The DataSet class provides the following properties.

CaseSensitive: It is used to get or set a value indicating whether string comparisons within System.Data.DataTable objects are case-sensitive. It returns true if string comparisons are case-sensitive; otherwise, it is false. The default is false.
DefaultViewManager: It is used to get a custom view of the data contained in the System.Data.DataSet to allow filtering, searching, and navigating using a custom System.Data.DataViewManager.
DataSetName: It is used to get or set the name of the current System.Data.DataSet.
EnforceConstraints: It is used to get or set a value indicating whether constraint rules are followed when attempting any update operation.
HasErrors: It is used to get a value indicating whether there are errors in any of the System.Data.DataTable objects within this System.Data.DataSet.
IsInitialized: It is used to get a value that indicates whether the System.Data.DataSet is initialized. It returns true to indicate the component has completed initialization; otherwise, it is false.
Prefix: It is used to get or set an XML prefix that aliases the namespace of the System.Data.DataSet.
Locale: It is used to get or set the locale information used to compare strings within the table.
Namespace: It is used to get or set the namespace of the System.Data.DataSet.
Site: It is used to get or set up a System.ComponentModel.ISite for the System.Data.DataSet.
Relations: It is used to get the collection of relations that link tables and allow navigation from parent tables to child tables.
Tables: It is used to get the collection of tables contained in the System.Data.DataSet.

Methods of ADO.NET DataSet Class:

Following are the methods provided by C# DataSet Class.

BeginInit(): It Begins the initialization of a System.Data.DataSet that is used on a form or used by another component. The initialization occurs at run time.
Clear(): It Clears the System.Data.DataSet of any data by removing all rows in all tables.
Clone(): It Copies the structure of the System.Data.DataSet, including all System.Data.DataTable schemas, relations, and constraints. Do not copy any data.
Copy(): It Copies both the structure and data for this System.Data.DataSet.
CreateDataReader(): It Returns a System.Data.DataTableReader with one result set per System.Data.DataTable appears in the System in the same sequence as the tables.Data.DataSet.Tables collection.
CreateDataReader(params DataTable[] dataTables): It returns a System.Data.DataTableReader with one result set per System.Data.DataTable. Here, the parameter dataTables specifies an array of DataTables providing the order of the result sets to be returned in the System.Data.DataTableReader
EndInit(): It Ends the initialization of a System.Data.DataSet that is used on a form or used by another component. The initialization occurs at run time.
GetXml(): It Returns the XML representation of the data stored in the System.Data.DataSet.
GetXmlSchema(): It Returns the XML Schema for the XML representation of the data stored in the System.Data.DataSet.

Like these, there are so many methods available in the DataSet class. As we progress in this course, we will learn each and every method in detail.

When to use ADO.NET DataSet?

DataSet from ADO.NET is a versatile tool that’s suitable for various scenarios where you need to work with complex data structures, manage relationships between multiple tables, and work with data in a disconnected environment. Here are some situations where you might consider using DataSet:

Complex Data Structures: When your application involves multiple tables with relationships, DataSet provides a unified way to represent and manage these structures. It’s particularly useful when working with data models that mirror a relational database.
Disconnected Data Manipulation: DataSet is designed for scenarios where you need to work with data offline. You can fill a DataSet with data, manipulate it locally, and then apply changes back to the database using DataAdapter.
Data Binding with Relationships: If you’re building data-driven user interfaces that involve displaying data from multiple related tables, DataSet allows you to set up relationships between tables and bind controls to show the related data.
Data Consolidation: If you’re pulling data from multiple data sources or services and need to consolidate it for processing, DataSet can hold data from different sources and unify it for further manipulation.
Hierarchical Data: If your data has a hierarchical structure, such as categories and subcategories, DataSet supports such relationships naturally, making it easier to work with parent-child data.
Data Caching and Offline Access: In scenarios where network connectivity is intermittent or where data retrieval from the database is resource-intensive, DataSet can store a local copy of the data for offline usage.
Batch Updates: When you need to perform batch updates that involve changes across multiple related tables, DataSet in conjunction with DataAdapter, enables you to apply changes to the database as a transaction.
Data Transformation: If you need to transform or manipulate data before persisting it to the database or passing it to another layer of your application, DataSet provides tools to achieve these tasks.
Reporting and Data Analysis: For tasks like generating reports or performing data analysis that require working with subsets of data, DataSet offers features like filtering, sorting, and grouping.
Data Serialization: If you need to serialize your data for storage, exchange, or caching, DataSet can be serialized to formats like XML, which makes it easy to save and retrieve complex data structures.

However, it’s important to consider the following aspects:

Memory Usage: DataSet can consume more memory compared to simpler alternatives like DataTable or SqlDataReader. It might not be suitable for very large datasets or resource-constrained environments.
Performance: While DataSet provides great flexibility, the overhead of managing relationships and constraints might impact performance, especially for read-heavy scenarios.
Data Access Strategy: If your application primarily involves read-only operations or requires real-time access to data without extensive local manipulation, alternatives like SqlDataReader might be more efficient.

So, use DataSet when working with complex data structures, managing relationships, enabling data binding with related data, or performing data manipulation in a disconnected environment. It’s especially beneficial for applications that mirror a relational database structure and require local data manipulation capabilities.

DataReader or DataSet in ADO.NET:

DataReader and DataSet are two distinct components in ADO.NET that serve different purposes for working with data. Each has its own strengths and weaknesses, and the choice between them depends on your specific application requirements. Let’s compare DataReader and DataSet in ADO.NET:

DataReader:

Forward-Only Cursor: DataReader provides a fast, forward-only, read-only cursor for fetching data from a database. It’s optimized for retrieving data row by row.
Memory Efficiency: DataReader is memory-efficient as it streams data from the database without loading the entire dataset into memory. This makes it suitable for large result sets.
Real-Time Access: It’s useful when you need to process data in real time, especially when memory consumption needs to be minimal.
Performance: DataReader generally has better performance for read-heavy scenarios due to its lightweight nature and optimized data retrieval.
Read-Only Operations: DataReader is designed primarily for reading data. You can’t perform updates, insertions, or deletions using DataReader.
Disconnected Usage: This is not suitable for disconnected scenarios where you want to work with data locally before updating the database.

DataSet:

In-Memory Representation: DataSet is an in-memory representation of a relational database, including multiple tables, relationships, and constraints.
Disconnected Usage: It’s designed for disconnected scenarios where you can fill it with data from a database, manipulate it locally, and then apply changes back to the database using DataAdapter.
Complex Data Structures: Suitable for handling complex data models involving multiple tables, relationships, and constraints.
Data Manipulation: DataSet supports various data manipulation operations like sorting, filtering, grouping, and updating. It’s versatile for both read and write operations.
Data Binding: DataSet supports data binding with controls, making it suitable for building data-driven user interfaces.
Memory Consumption: DataSet can consume more memory than DataReader because it loads data into memory. It might not be suitable for extremely large datasets.
Performance: DataSet might have slightly lower performance compared to DataReader due to the overhead of managing relationships and constraints.

So, use DataReader when you primarily need to read large amounts of data efficiently and in a forward-only manner, especially for read-heavy scenarios. Use DataSet when you require an in-memory representation of a complete relational database, need to work with complex data models, perform data manipulation in a disconnected environment, or build data-bound user interfaces. In many scenarios, the choice might also depend on balancing memory consumption, real-time access needs, and the extent of data manipulation required.

Which one to use, DataReader or DataSet?

DataSet to use:

When you want to cache the data locally in your application so that you can manipulate the data.
When you want to work with disconnected architecture.

DataReader to use:

If you do not want to cache the data locally, then you need to use DataReader, which will improve the performance of your application.
DataReader works on connected-oriented architecture, i.e., requiring an open connection to the database.

Summary of ADO.NET DataSet:

DataSet is a fundamental component of the ADO.NET framework provided by Microsoft. It’s an in-memory representation of a complete relational database, including tables, relationships, and constraints. The DataSet can hold multiple DataTable objects along with information about their structure and relationships, making it a powerful tool for managing disconnected data and complex data models.

Here are the key features and use cases of DataSet:

Container of Data: A DataSet can hold multiple DataTable objects representing individual tables in a relational database. This allows you to model complex data structures.
Data Relationships: DataSet supports defining relationships between multiple tables, which helps maintain data integrity and allows you to work with related data as if it were in a single database.
Data Constraints: You can apply constraints, such as uniqueness and foreign key constraints, to the DataSet. These constraints help ensure data consistency and integrity.
Data Binding: DataSet is suitable for data binding scenarios where you want to bind UI controls to a complete set of data, including multiple related tables.
Disconnected Data Operations: Like DataTable, DataSet allows you to work with data in a disconnected manner. You can fill it with data from a database, modify the data locally, and then synchronize changes back to the database.
XML Serialization: DataSet can be serialized to XML, which is useful for saving data in a platform-independent format or exchanging data between different layers of an application.
Batch Updates: You can use DataSet in combination with SqlDataAdapter to perform batch updates, where changes made to multiple tables can be applied to the database in a single transaction.
Data Transformation: DataSet provides tools for data transformation, including copying data between different DataSet objects, merging data from different sources, and more.
Complex Data Operations: When dealing with data that involves multiple tables and relationships, DataSet provides a cohesive framework for managing and manipulating the entire data structure.

DataSet is particularly useful when dealing with complex data models, disconnected scenarios, and applications that require managing multiple related tables. It provides a comprehensive framework for handling data with relationships and constraints while allowing you to work with data locally before updating the database.

In the next article, I am going to discuss DataSet using SQL Server in detail. In this article, I try to explain the ADO.NET DataSet with examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET DataSet with examples article.