ADO.NET Core DataSet

Back to: ADO.NET Core Tutorial For Beginners and Professionals

ADO.NET Core DataSet with Examples

In this article, I will discuss ADO.NET Core DataSet with Examples. Please read our previous article discussing ADO.NET Core DataTable with SQL Server. ADO.NET Core is a set of classes and interfaces in the .NET Framework (and .NET Core) that provide data access services. The DataSet is a key component in ADO.NET Core, providing an in-memory data store for managing relational data.

What is a DataSet in ADO.NET Core?

In ADO.NET Core, a DataSet is a collection of DataTable objects. Each DataTable holds a set of data rows and columns, much like a relational database table. The DataSet itself does not maintain direct connections to the data source. Instead, it acts as an in-memory repository, making it ideal for scenarios where data is fetched once, worked on independently, and then optionally persisted back to the source.

Creating a DataSet and Adding DataTables in ADO.NET Core

The following example demonstrates the syntax of creating a Data Set and adding multiple Data Tables to it without connecting to any database. It shows how to define in-memory data tables, add sample data, and display the data. The example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;

namespace DataSetBasics
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new DataSet instance
            DataSet companyDataSet = new DataSet("CompanyDataSet");

            // Create the first DataTable "Employees"
            DataTable dtEmployees = new DataTable("Employees");

            // Define columns for the Employees table
            DataColumn colEmployeeID = new DataColumn("EmployeeID", typeof(int))
            {
                Unique = true,
                AutoIncrement = true,
                AutoIncrementSeed = 1, 
                AutoIncrementStep = 1,
            };

            DataColumn colEmployeeName = new DataColumn("EmployeeName", typeof(string))
            {
                AllowDBNull = false,
                MaxLength = 50
            };

            // Add columns to the Employees DataTable
            dtEmployees.Columns.Add(colEmployeeID);
            dtEmployees.Columns.Add(colEmployeeName);

            // Set the primary key for Employees table
            dtEmployees.PrimaryKey = [colEmployeeID];

            // Create the second DataTable "Departments"
            DataTable dtDepartments = new DataTable("Departments");

            // Define columns for the Departments table
            DataColumn colDepartmentID = new DataColumn("DepartmentID", typeof(int))
            {
                Unique = true,
                AutoIncrement = true,
                AutoIncrementSeed = 1, 
                AutoIncrementStep = 1,
            };
            DataColumn colDepartmentName = new DataColumn("DepartmentName", typeof(string))
            {
                AllowDBNull = false,
                MaxLength = 50
            };

            // Add columns to the Departments DataTable
            dtDepartments.Columns.Add(colDepartmentID);
            dtDepartments.Columns.Add(colDepartmentName);

            // Set the primary key for Departments table
            dtDepartments.PrimaryKey = [colDepartmentID];

            // Add the DataTables to the DataSet
            companyDataSet.Tables.Add(dtEmployees);
            companyDataSet.Tables.Add(dtDepartments);

            // Add sample data to Employees table
            DataRow empRow1 = dtEmployees.NewRow();
            empRow1["EmployeeName"] = "John Doe";
            dtEmployees.Rows.Add(empRow1);

            DataRow empRow2 = dtEmployees.NewRow();
            empRow2["EmployeeName"] = "Jane Smith";
            dtEmployees.Rows.Add(empRow2);

            // Add sample data to Departments table
            DataRow deptRow1 = dtDepartments.NewRow();
            deptRow1["DepartmentName"] = "HR";
            dtDepartments.Rows.Add(deptRow1);

            DataRow deptRow2 = dtDepartments.NewRow();
            deptRow2["DepartmentName"] = "IT";
            dtDepartments.Rows.Add(deptRow2);

            // Display the data from Employees table
            Console.WriteLine("Employees Table Data:");
            foreach (DataRow row in companyDataSet.Tables[0].Rows)
            {
                Console.WriteLine($"EmployeeID: {row["EmployeeID"]}, EmployeeName: {row["EmployeeName"]}");
            }

            // Display the data from Departments table
            Console.WriteLine("\nDepartments Table Data:");
            foreach (DataRow row in companyDataSet.Tables["Departments"].Rows)
            {
                Console.WriteLine($"DepartmentID: {row["DepartmentID"]}, DepartmentName: {row["DepartmentName"]}");
            }

            Console.ReadKey();
        }
    }
}

Code Explanation:

• DataSet and DataTables Creation: A DataSet object named “CompanyDataSet” is created. Two DataTables named “Employees” and “Departments” are then created.
• Schema Definition: For each table, columns (with data types and primary keys) are defined. The AutoIncrement property is used for primary key columns to automatically generate the identifiers.
• Data Population: Sample rows are created and added to each DataTable.

Output:

Managing Data Integrity Using DataSet Relationships in ADO.NET Core

In ADO.NET Core, the DataSet ensures data integrity using DataRelations (i.e., defining parent-child relationships between tables). For a better understanding, please have a look at the following example. The example creates two data tables (“Customers” as the Parent table and “Orders” as the child table) with an enforced relationship using the CustomerID field, ensuring referential integrity. The following example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;

namespace DataSetDataRelations
{
    public class Program
    {
        static void Main(string[] args)
        {
            // Create a new DataSet
            DataSet ds = new DataSet("BusinessData");

            // Create Customers DataTable (Parent Table)
            DataTable dtCustomers = new DataTable("Customers");
            DataColumn colCustID = new DataColumn("CustomerID", typeof(int))
            {
                AutoIncrement = true,
                AutoIncrementSeed = 100,
                AutoIncrementStep = 1,
            };
            DataColumn colCustName = new DataColumn("CustomerName", typeof(string));

            //Add Columns to dtCustomers data table
            dtCustomers.Columns.Add(colCustID);
            dtCustomers.Columns.Add(colCustName);

            //Set the Primary of dtCustomers data table
            dtCustomers.PrimaryKey = [colCustID];

            // Adding sample data for Customers
            DataRow custRow1 = dtCustomers.NewRow();
            custRow1["CustomerName"] = "Alice Johnson";
            dtCustomers.Rows.Add(custRow1);

            DataRow custRow2 = dtCustomers.NewRow();
            custRow2["CustomerName"] = "Bob Williams";
            dtCustomers.Rows.Add(custRow2);

            // Create Orders DataTable (Child Table)
            DataTable dtOrders = new DataTable("Orders");
            DataColumn colOrderID = new DataColumn("OrderID", typeof(int))
            {
                AutoIncrement = true,
                AutoIncrementSeed = 1000,
                AutoIncrementStep = 1,
            };

            // Foreign key column to link with Customers table.
            DataColumn colOrderCustID = new DataColumn("CustomerID", typeof(int));
            DataColumn colOrderDate = new DataColumn("OrderDate", typeof(DateTime));

            //Add Columns to dtOrders data table
            dtOrders.Columns.Add(colOrderID);
            dtOrders.Columns.Add(colOrderCustID);
            dtOrders.Columns.Add(colOrderDate);

            //Set the Primary of dtOrders data table
            dtOrders.PrimaryKey = [colOrderID];

            // Adding sample data for Orders
            DataRow orderRow1 = dtOrders.NewRow();
            orderRow1["CustomerID"] = custRow1["CustomerID"]; // Link to Alice
            orderRow1["OrderDate"] = DateTime.Now;
            dtOrders.Rows.Add(orderRow1);

            DataRow orderRow2 = dtOrders.NewRow();
            orderRow2["CustomerID"] = custRow2["CustomerID"]; // Link to Bob
            orderRow2["OrderDate"] = DateTime.Now.AddDays(-1);
            dtOrders.Rows.Add(orderRow2);

            DataRow orderRow3 = dtOrders.NewRow();
            orderRow3["CustomerID"] = custRow1["CustomerID"]; // Alice’s second order
            orderRow3["OrderDate"] = DateTime.Now.AddDays(-3);
            dtOrders.Rows.Add(orderRow3);

            // Add both tables to the DataSet.
            ds.Tables.Add(dtCustomers);
            ds.Tables.Add(dtOrders);

            // Create a DataRelation to enforce referential integrity.
            // The relation links the CustomerID column in Customers (parent) table
            // with the CustomerID column in Orders (child) table.
            DataRelation custOrderRelation = new DataRelation(
                "CustomerOrders",
                colCustID, //dtCustomers table CustomerID column
                colOrderCustID //dtOrders table CustomerID column
            );

            // Add the relation to the DataSet.
            ds.Relations.Add(custOrderRelation);

            // Display Customers and their Orders by utilizing DataRelation
            Console.WriteLine("Customers and their Orders:");

            //foreach (DataRow customer in ds.Tables[0].Rows)
            foreach (DataRow customer in dtCustomers.Rows)
            {
                Console.WriteLine($"CustomerID: {customer["CustomerID"]}, Name: {customer["CustomerName"]}");
                
                // Get the related child rows from Orders table
                DataRow[] orders = customer.GetChildRows(custOrderRelation);
                foreach (DataRow order in orders)
                {
                    Console.WriteLine($"\tOrderID: {order["OrderID"]}, OrderDate: {order["OrderDate"]}");
                }
            }

            Console.ReadKey();
        }
    }
}

Code Explanation:

• Parent Table (Customers): A DataTable “Customers” is created with columns for CustomerID and CustomerName. CustomerID is set as the primary key.
• Child Table (Orders): The “Orders” table includes OrderID, OrderDate, and CustomerID (as a foreign key). This table references the Customers table.
• Data Population: The Customers and Orders tables are filled with sample in-memory data, ensuring each order is related to a valid customer.
• DataRelation Creation: After creating two DataTables, the DataRelation object named “CustomerOrders” is established between the CustomerID column in the Customers table and the same column in the Orders table. This enforces referential integrity—orders must refer to a valid customer.
• Displaying Data: The program iterates through each Customer, fetches its related Orders rows using GetChildRows(), and then displays the results in a structured format.

Output:

Copy, Clone, and Clear Methods of ADO.NET Core DataSet

Let us first understand the syntax and use of the Copy, Clone, and Clear methods in a Data Set, and then we will see one example to understand these methods.

Copy:

Creates a new DataSet with both structure and data. You need to use this method to duplicate an existing DataSet, including both schema and data. The Syntax is: DataSet copyDataSet = originalDataSet.Copy();

Clone:

Creates a new DataSet with the same structure (schema) but without data. You need to use this method when you only need the schema (tables, columns, constraints) but no data. Typically used when you want to insert fresh data into a schema-identical structure. The Syntax is: DataSet clonedDataSet = originalDataSet.Clone();

Clear:

Remove all data from the current DataSet while retaining its structure. You need to use this method when you want to remove all data from all tables in a DataSet but keep the schema intact (tables/columns remain, but all rows are removed). The Syntax is: originalDataSet.Clear();

Example to Understand Copy, Clone, and Clear Methods:

Please look at the following example to understand the use of a dataset’s Copy, Clone, and Clear Methods. The following example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;

namespace DataSetCopyCloneClear
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a DataSet and add a DataTable with sample data
            DataSet originalDS = new DataSet("OriginalDataSet");

            // Create a DataTable "Products"
            DataTable dtProducts = new DataTable("Products");
            DataColumn dcProductID = new DataColumn("ProductID", typeof(int))
            {
                AutoIncrement = true,
                AutoIncrementSeed = 1,
                AutoIncrementStep = 1,
            };

            dtProducts.Columns.Add(dcProductID);
            dtProducts.Columns.Add(new DataColumn("ProductName", typeof(string)));
            dtProducts.Columns.Add("Price", typeof(decimal));
            dtProducts.PrimaryKey = [dcProductID];

            // Add DataTable to DataSet
            originalDS.Tables.Add(dtProducts);

            // Add sample data
            DataRow prodRow1 = dtProducts.NewRow();
            prodRow1["ProductName"] = "Laptop";
            prodRow1["Price"] = 1200.50m;
            dtProducts.Rows.Add(prodRow1);

            DataRow prodRow2 = dtProducts.NewRow();
            prodRow2["ProductName"] = "Smartphone";
            prodRow2["Price"] = 699.99m;
            dtProducts.Rows.Add(prodRow2);

            // Display original DataSet content
            Console.WriteLine("Original DataSet Data:");
            DisplayProducts(dtProducts);

            // Demonstrate Clone: Creates a new DataSet with the same structure but no data
            DataSet clonedDS = originalDS.Clone();
            //Tables[0] represents the first data table in the clonedDS dataset, in this case Products table
            // clonedDS.Tables[0].Rows.Count = 0 rows
            Console.WriteLine($"\nCloned DataSet (Structure Only) Data Count: {clonedDS.Tables[0].Rows.Count}");

            // Demonstrate Copy: Creates a new DataSet with the same structure and data
            DataSet copyDS = originalDS.Copy();
            Console.WriteLine("\nCopied DataSet Data:");
            //Tables[0] represents the first data table in the copyDS dataset, in this case Products table
            DisplayProducts(copyDS.Tables[0]);

            // Demonstrate Clear: Remove all data from the original DataSet while keeping the structure
            originalDS.Clear();
            Console.WriteLine("\nOriginal DataSet Data After Clear:");
            //Tables[0] represents the first data table in the originalDS dataset, in this case Products table
            DisplayProducts(originalDS.Tables[0]);

            Console.ReadKey();
        }

        // Helper method to display product details from a DataTable
        private static void DisplayProducts(DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine($"ProductID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["Price"]}");
            }
        }
    }
}

Code Explanation:

• Copy: originalDS.Copy() creates a deep copy of the entire DataSet, including the structure and data. The copied DataSet (copyDS) is then displayed and contains the same data as the original.
• Clone: originalDS.Clone() creates a new DataSet with the same schema as the original but with no rows. This is useful if you need an empty container with the same structure.
• Clear: originalDS.Clear() removes all data rows from the original DataSet while preserving its structure, which is helpful for resetting data.
• Display Logic: The helper method DisplayProducts iterates through the DataTable rows and displays the product details, with clear output statements to verify each operation.

Output:

Removing a DataTable from a DataSet in ADO.NET Core

When the DataTable is no longer needed or you want to replace it with a different set of data altogether, you need to remove the DataTable from the Dataset. You can use the Remove or RemoveAt method to remove a Data table from a DaaSet. The syntax is: dataSet.Tables.Remove(“TableName”) or dataSet.Tables.RemoveAt(index).

Let us see how to remove a DataTable from a DataSet when it is no longer needed. The following example creates a DataSet with two tables (“Employees” and “Departments”), then removes one table and displays the contents afterward. The following example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;

namespace RemoveDataTableExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a DataSet with two DataTables
            DataSet ds = new DataSet("CompanyData");

            // Create Employees DataTable
            DataTable dtEmployees = new DataTable("Employees");
            DataColumn dcEmployeeID = new DataColumn("EmployeeID", typeof(int))
            {
                AutoIncrement = true,
                AutoIncrementSeed = 1,
                AutoIncrementStep = 1
            };
            dtEmployees.Columns.Add(dcEmployeeID);
            dtEmployees.Columns.Add("EmployeeName", typeof(string));
            dtEmployees.PrimaryKey = [dcEmployeeID];

            // Create Departments DataTable
            DataTable dtDepartments = new DataTable("Departments");
            DataColumn dcDepartmentID = new DataColumn("DepartmentID", typeof(int))
            {
                AutoIncrement = true,
                AutoIncrementSeed = 1
            };
            dtDepartments.Columns.Add(dcDepartmentID);
            dtDepartments.Columns.Add("DepartmentName", typeof(string));
            dtDepartments.PrimaryKey = [dcDepartmentID];

            // Add both DataTables to the DataSet
            ds.Tables.Add(dtEmployees);
            ds.Tables.Add(dtDepartments);

            // Add sample data into Employees
            DataRow empRow = dtEmployees.NewRow();
            empRow["EmployeeName"] = "Michael Scott";
            dtEmployees.Rows.Add(empRow);

            // Add sample data into Departments
            DataRow deptRow = dtDepartments.NewRow();
            deptRow["DepartmentName"] = "Sales";
            dtDepartments.Rows.Add(deptRow);

            // Display DataSet tables name before removal
            Console.WriteLine("Before removal, DataSet contains the following tables:");
            foreach (DataTable table in ds.Tables)
            {
                Console.WriteLine(table.TableName);
            }

            // Remove the Departments DataTable
            ds.Tables.Remove("Departments");
            // ds.Tables.RemoveAt(1);

            // Display DataSet tables count after removal
            Console.WriteLine("\nAfter removal, DataSet contains the following tables:");
            foreach (DataTable table in ds.Tables)
            {
                Console.WriteLine(table.TableName);
            }

            Console.ReadKey();
        }
    }
}

Code Explanation:

• Setup: Two Data Tables (“Employees” and “Departments”) are created and added to the Data Set.
• Data Addition: Sample data is inserted into both tables.
• Removal: The Remove method is called on ds.Tables with the table name “Departments” to remove it from the DataSet. You can also use the RemoveAt method, and to this method, you need to specify the Index Position of the Departments table, which is, in this case, 1. The Index is 0-based.
• Output: Before and after the removal, the table names are printed to the console to confirm that the removal was successful.

Output:

When Should We Use ADO.NET Core DataSet in Real-Time Applications?

ADO.NET Core DataSet is best suited for the following real-time scenarios:

• Disconnected Data Operations: When you need to fetch data from a database, process it offline (or in-memory), and update the data source later without maintaining a continuous open connection.
• Complex Data Manipulation: When working with multiple interrelated tables, you can manipulate data relationships and perform filtering and sorting without additional database queries. The DataSet’s ability to maintain relationships and enforce constraints ensures data integrity.
• Data Caching for Scalability: When reducing database load is critical (e.g., web applications serving many users), using an in-memory DataSet to cache data can improve performance.
• Rich Data Binding: For desktop or web applications that require complex data binding scenarios, DataSet provides a flexible structure to bind multiple tables and relations to user interface controls.

A Data Set represents a mini-database that can store multiple tables (DataTable), their relationships (DataRelation), and constraints (UniqueConstraint, ForeignKeyConstraint) without requiring an actual database connection. It is used to process data offline or perform complex operations without the overhead of database calls.

In the next article, I will discuss ADO.NET Core SqlDataAdapter with Examples. In this article, I explain ADO.NET Core DataSet with Examples. I want your feedback. Please post your feedback, questions, or comments about this article.