ADO - SqlDataSet Class

The ADO.NET DataSet class in C# is a part of the ADO.NET library and is used to represent an in-memory cache of data that can hold multiple DataTable objects. It provides a disconnected and independent representation of data retrieved from a data source.

What is ADO.NET DataSet in C#?

The ADO.NET DataSet class represents an in-memory cache of data that can hold multiple DataTable objects, relationships between tables, and constraints. It is a disconnected and independent representation of data that can be used to store, manipulate, and transfer data between different tiers of an application.

Constructors of DataSet in C#:

The DataSet class has several constructors to create an instance of a DataSet object. Some commonly used constructors include:

  • DataSet(): Initializes a new instance of the DataSet class with no arguments.
  • DataSet(string dataSetName): Initializes a new instance of the DataSet class with the specified name.

Properties of DataSet in C#:

The DataSet class has various properties that allow you to access and manipulate its structure and data. Some important properties include:

  • Tables: Gets the collection of DataTable objects contained within the DataSet.
  • Relations: Gets the collection of DataRelation objects that define the relationships between tables.
  • DataSetName: Gets or sets the name of the DataSet.
  • IsInitialized: Gets a value indicating whether the DataSet is initialized.

Methods of ADO.NET DataSet Class:

The DataSet class provides methods to perform various operations on the data stored within it. Some important methods include:

  • ReadXml(): Reads XML schema and data into the DataSet.
  • WriteXml(): Writes the DataSet structure and data as XML.
  • AcceptChanges(): Commits all the changes made to the DataSet since the last call to AcceptChanges or RejectChanges.
  • RejectChanges(): Rolls back all the changes made to the DataSet since the last call to AcceptChanges or RejectChanges.

Creating Data Table:

To create a DataTable within a DataSet, you can use the DataSet.Tables.Add() method and provide the necessary information for the table.

DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable("YourTableName");
// Add columns to the DataTable
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
// Add the DataTable to the DataSet
dataSet.Tables.Add(dataTable);

Creating DataSet with DataTable:

To create a DataSet with a DataTable, you can instantiate a new DataSet object and add the DataTable object to the DataSet's Tables collection.

DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable("YourTableName");
// Add columns to the DataTable
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
// Add the DataTable to the DataSet
dataSet.Tables.Add(dataTable);

Fetching DataTable from DataSet using index position:

You can fetch a DataTable from a DataSet by accessing it using its index position in the Tables collection.

DataTable dataTable = dataSet.Tables[0]; // Accessing the first DataTable in the DataSet

Fetching DataTable From DataSet using Name:

You can fetch a DataTable from a DataSet by accessing it using its name.

DataTable dataTable = dataSet.Tables["YourTableName"]; // Accessing the DataTable with the specified name

In the above code examples, make sure to replace "YourTableName" with the actual name of the DataTable you want to fetch.

The DataSet class in ADO.NET provides a versatile and powerful way to work with multiple DataTable objects and relationships between them. It allows you to store, manipulate, and transfer data between different tiers of an application while maintaining a disconnected and independent representation of the data.

// Creating a DataSet with a DataTable
DataSet dataSet = new DataSet();
// Creating a DataTable
DataTable dataTable = new DataTable("Employee");
// Adding columns to the DataTable
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
// Adding rows to the DataTable
DataRow row1 = dataTable.NewRow();
row1["ID"] = 1;
row1["Name"] = "John Doe";
row1["Age"] = 30;
dataTable.Rows.Add(row1);
DataRow row2 = dataTable.NewRow();
row2["ID"] = 2;
row2["Name"] = "Jane Smith";
row2["Age"] = 28;
dataTable.Rows.Add(row2);
// Adding the DataTable to the DataSet
dataSet.Tables.Add(dataTable);

Explanation:

First, we create a new instance of the DataSet class using the constructor DataSet(). This creates an empty DataSet object.

Next, we create a new instance of the DataTable class using the constructor DataTable("Employee"). We provide the name "Employee" to the constructor, which sets the name of the DataTable.

Then, we define the columns of the DataTable using the Columns.Add() method. We add three columns: "ID" of type int, "Name" of type string, and "Age" of type int.

After defining the columns, we create two rows using the NewRow() method of the DataTable. We assign values to each row's columns using the indexer row[columnName].

We add the rows to the DataTable using the Rows.Add() method.

Finally, we add the DataTable to the DataSet using the Tables.Add() method.

Now, let's see how to fetch a DataTable from the DataSet:

// Fetching DataTable from DataSet using index position
DataTable fetchedTable1 = dataSet.Tables[0]; // Accessing the first DataTable in the DataSet
// Fetching DataTable from DataSet using name
DataTable fetchedTable2 = dataSet.Tables["Employee"]; // Accessing the DataTable with the specified name

Explanation:

To fetch a DataTable from the DataSet, we use the indexer dataSet.Tables[index] or dataSet.Tables["tableName"].

In the first example, we fetch the first DataTable from the DataSet by providing the index position [0].

In the second example, we fetch the DataTable with the name "Employee" from the DataSet by providing the name as a string.

By fetching the DataTable from the DataSet, you can then perform various operations on the DataTable, such as iterating through rows, accessing columns, modifying data, and so on.

The DataSet and DataTable classes in ADO.NET provide a powerful and flexible way to manage and manipulate data in memory. They allow you to represent and work with tabular data, define relationships between tables, and perform operations on the data independently of a connected data source.