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 data tables that contains the 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 Data Table in our previous article. If you have not read that article then please our ADO.NET DataTable article before proceeding to this article. 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 add 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 add these three data columns to the Orders data table. Finally, we created two data rows and add these two data rows to the Orders data table.

Creating DataSet with DataTable:

As we already discussed the DataSet is a collection of DataTables. 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 add 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, then you could use a for-each loop to iterate as shown in the below image.

Fetching DataTable From DataSet using Name:

The second data table that we added to the dataset is Orders and it 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.

1. DataSet(): It initializes a new instance of the System.Data.DataSet class..
2. 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.
3. DataSet(SerializationInfo info, StreamingContext context): It initializes a new instance of a System.Data.DataSet class that has the given serialization information and context. Here, 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.
4. 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.

1. 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 false. The default is false.
2. 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.
3. DataSetName: It is used to get or set the name of the current System.Data.DataSet.
4. EnforceConstraints: It is used to get or set a value indicating whether constraint rules are followed when attempting any update operation.
5. 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.
6. 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 false.
7. Prefix: It is used to get or set an XML prefix that aliases the namespace of the System.Data.DataSet.
8. Locale: It is used to get or set the locale information used to compare strings within the table.
9. Namespace: It is used to get or set the namespace of the System.Data.DataSet.
10. Site: It is used to get or set up a System.ComponentModel.ISite for the System.Data.DataSet.
11. Relations: It is used to get the collection of relations that link tables and allow navigation from parent tables to child tables.
12. 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.

1. 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.
2. Clear(): It Clears the System.Data.DataSet of any data by removing all rows in all tables.
3. Clone(): It Copies the structure of the System.Data.DataSet, including all System.Data.DataTable schemas, relations, and constraints. Do not copy any data.
4. Copy(): It Copies both the structure and data for this System.Data.DataSet.
5. CreateDataReader(): It Returns a System.Data.DataTableReader with one result set per System.Data.DataTable, in the same sequence as the tables, appears in the System.Data.DataSet.Tables collection.
6. 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
7. 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.
8. GetXml(): It Returns the XML representation of the data stored in the System.Data.DataSet.
9. 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.

The following question is being asked in the interviews.

Which one to use DataReader or DataSet?

DataSet to use:

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

 DataReader to use:

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

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