In this Video, I am going to discuss ADO.NET DataSet in C# with Examples. Please watch our previous Video where we discussed ADO.NET DataTable with Examples. At the end of this Video, 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 Videos.
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 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.
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.
Creating DataSet with DataTable:
As we alwatchy discussed the DataSet is a collection of DataTables. So, lets 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, so the Customer table Index position is 0. If you want to iterate through the Customers 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 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 the name and looping through the data using a for each loop.
The complete code is given below.
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.
Let us discuss each of these 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. 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.
- 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 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.
- 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 sets the name of the current System.Data.DataSet.
- EnforceConstraints: It is used to gets or sets 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 gets a value that indicates whether the System.Data.DataSet is initialized. It returns true to indicate the component has completed initialization; otherwise false.
- Prefix: It is used to gets or sets an XML prefix that aliases the namespace of the System.Data.DataSet.
- Locale: It is used to gets or sets the locale information used to compare strings within the table.
- Namespace: It is used to Gets or sets the namespace of the System.Data.DataSet.
- Site: It is used to gets or sets 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 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.
- 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, in the same sequence as the tables appear in the System.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.
The following question is being asked in the interviews.
Which one to use between DataReader or DataSet?
DataSet to use:
- When you want to caches the data locally in your application so that you can manipulate the data.
- When you want to interact with the data dynamically i.e. binding the data to windows form control.
- When you want to work with disconnected architecture.
DataReader to use:
- If you require some other functionality mentioned above, then you need to use DataReader which will improve the performance of your application.
- DataReader works on connected-oriented architecture i.e. it requires an open connection to the database.