ADO.NET DataSet

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?

The DataSet represents a subset of the database in memory. That means the ADO.NET DataSet is a collection of data tables which 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 more about disconnected architecture of 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.

ADO.NET DataSet in C#

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

Example to understand DataSet in C#:

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

Creating Customers Data Table:

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 and added these three columns into the Customer data table. Finally, we created two data rows and add these two data rows into the Customer data table.

ADO.NET DataSet with Examples

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, CustomerId, and Amount) and add these three columns into the Orders table. Finally, we created two data rows and add these data rows into the Orders table.

DataSet in C#

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 table (Customers and Orders) into the DataSet. Please have a look the following image. Here, first, we created an instance of the DataSet and then add the two data tables using the Tables property of DataSet object.

Creating DataSet with DataTable:

Fetch DataTable from DataSet:

Now, let us see how to fetch the datatable from the dataset. You can fetch the datatable 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, so the Customer table Index position is 0. If you want to iterate through the Customers table data, then you could use a foreach loop to iterate as shown in the below image.

Fetching DataTable from DataSet using index position:

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 datatable 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 name and looping through the data using a foreach loop.

Fetching DataTable From DataSet using Name:

The complete code is given below.
using System;
using System.Data;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Craeting Customer table
                DataTable Customer = new DataTable("Customer");

                //Creating column and schema
                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 table
                Customer.Rows.Add(101, "Anurag", "2233445566");
                Customer.Rows.Add(202, "Manoj", "1234567890");
                
                // Craeting Orders table
                DataTable Orders = new DataTable("Orders");

                //Creating column and schema
                DataColumn OrderId = new DataColumn("ID", typeof(Int32));
                Orders.Columns.Add(OrderId);
                DataColumn CustId = new DataColumn("CustomerId", typeof(Int32));
                Orders.Columns.Add(CustId);
                DataColumn OrderAmount = new DataColumn("Amount", typeof(Int32));
                Orders.Columns.Add(OrderAmount);
                
                //Adding Data Rows into Orders 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);

                //Customer Table
                Console.WriteLine("Customer Table Data: ");

                //Fetching DataTable from dataset using the Index position
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Console.WriteLine(row["ID"] + ",  " + row["Name"] + ",  " + row["Mobile"]);
                }

                Console.WriteLine();

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

                //Fetching DataTable from the DataSet using the table name
                foreach (DataRow row in dataSet.Tables["Orders"].Rows)
                {
                    Console.WriteLine(row["ID"] + ",  " + row["CustomerId"] + ",  " + row["Amount"]);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

What is ADO.NET DataSet?

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

Constructors of DataSet in C#:

The DataSet in C# provides the following four constructors.

Constructors of DataSet in C#

Let us discuss each of these 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 gets or sets 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 gets 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 gets or sets the name of the current System.Data.DataSet.
  4. EnforceConstraints: It is used to gets or sets a value indicating whether constraint rules are followed when attempting any update operation.
  5. HasErrors: It is used gets 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 gets 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 gets or sets an XML prefix that aliases the namespace of the System.Data.DataSet.
  8. Locale: It is used to gets or sets the locale information used to compare strings within the table.
  9. Namespace: It is used to Gets or sets the namespace of the System.Data.DataSet.
  10. Site: It is used to gets or sets an 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 gets 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. Does 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 appear 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 are so many methods available in DataSet class. As we progress in this course, we will learn each and every methods in detail.

The following question is being asked in the interviews.

Which one to use between DataReader or DataSet?

DataSet to use:

  1. When you want to caches the data locally in your application, so that you can manipulate the data.
  2. When you want to interact with the data dynamically i.e. binding the data to windows forms control.
  3. When you want to work with disconnected architecture.

DataReader to use:

  1. If you required some other functionality mentioned above, 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 on 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 helps you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *