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

ADO.NET Core DataTable with SQL Server

In this article, I will discuss ADO.NET Core DataTable with SQL Server with Examples. Please read our previous article discussing the basic concepts of ADO.NET Core DataTable. Now, we will see a .NET Core Console Application to demonstrate how to connect to a SQL Server database using ADO.NET Core, execute T‑SQL statements to fetch a single result set or multiple result sets, and store the data in a DataTable (or a DataSet when working with more than one result set).

Creating the SQL Server Database with Dummy Data

First, open SQL Server Management Studio (SSMS) and execute the following T‑SQL script to create the CompanyDB database with Employees and Departments tables and add dummy data for testing purposes.

-- Create a new database called CompanyDB
CREATE DATABASE CompanyDB;
GO

-- Use the new database
USE CompanyDB;
GO

-- Create the Departments table with sample data
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName NVARCHAR(50) NOT NULL
);
GO

-- Insert dummy data into the Departments table
INSERT INTO Departments (DepartmentName)
VALUES 
('IT'),            -- DepartmentID 1 
('HR'),            -- DepartmentID 2
('Finance');       -- DepartmentID 3
GO

-- Create a table for storing employee details
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1), -- Primary key auto-increment field
    FirstName NVARCHAR(50) NOT NULL,          -- Employee first name
    LastName NVARCHAR(50) NOT NULL,           -- Employee last name
    Email NVARCHAR(100) NOT NULL,             -- Employee email address
    HireDate DATE NOT NULL,                   -- Date when the employee was hired
    DepartmentID INT,                         -- Employee Department
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
GO

-- Insert dummy data into the Employees table
INSERT INTO Employees (FirstName, LastName, Email, HireDate, DepartmentID)
VALUES 
('John', 'Doe', 'john.doe@example.com', '2020-01-15', 1),
('Jane', 'Smith', 'jane.smith@example.com', '2019-03-22', 2),
('Michael', 'Johnson', 'michael.johnson@example.com', '2021-07-01', 3);
GO

SQL Script Explanation:

• We create a database named CompanyDB and switch the context to it.
• The Departments master table is created with a simple schema including an identity column and Department Name field. Dummy data is inserted into the Departments table.
• The Employees table has a simple schema, including an identity column and required fields. Here, DepartmentID is created as a foreign key pointing to the DepartmentID column of the Departments table. Dummy data is inserted into the Employees table.

Fetching a Single Result Set into a DataTable

The following example fetches employee data using a single T‑SQL statement and loads the data into a data table. The example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;
using Microsoft.Data.SqlClient;

namespace DataTableExample
{
    class Program
    {
        // Connection string to connect to the SQL Server database.
        private static string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=CompanyDB;Trusted_Connection=True;TrustServerCertificate=True;";
        
        static void Main(string[] args)
        {
            // Display application objective
            Console.WriteLine("Fetch all records from the Employees table into a DataTable");

            // Define the T-SQL query to fetch data from the Employees table
            string query = "SELECT EmployeeID, FirstName, LastName, Email, HireDate FROM Employees;";

            // Create a new DataTable to store the query results
            DataTable employeeTable = new DataTable();

            // Using block ensures that the SqlConnection is properly disposed after usage
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    // Open the connection to the database
                    conn.Open();

                    // Create the SqlCommand object with the T-SQL query and the connection object
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        // Create a SqlDataAdapter to execute the query and fill the DataTable
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            // Fill the DataTable with data from the query result
                            adapter.Fill(employeeTable);
                        }
                    }

                    // Output the number of rows fetched
                    Console.WriteLine($"Number of Employees Fetched: {employeeTable.Rows.Count}");

                    // Loop through each row in the DataTable and display the record
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}, Hired On: {((DateTime)row["HireDate"]).ToShortDateString()}");
                    }
                }
                catch (SqlException sqlEx)
                {
                    // Handle SQL Server exceptions
                    Console.WriteLine("A SQL Exception occurred while fetching data.");
                    Console.WriteLine(sqlEx.Message);
                }
                catch (Exception ex)
                {
                    // Handle any general exception that might occur
                    Console.WriteLine("An error occurred while fetching data.");
                    Console.WriteLine(ex.Message);
                }
            }
            Console.ReadKey();
        }
    }
}

Code Explanation:

• Connection String: Configured to use integrated security. Change it if you’re using SQL Server Authentication.
• SqlConnection: Wrapped in a using block to ensure proper resource disposal.
• SqlCommand & SqlDataAdapter: Used to execute the T‑SQL query and fill the DataTable.

Output:

Fetching Two Result Sets

Sometimes, you may want to fetch two result sets (for example, data from the Employees and Departments tables) with one database call. This can be achieved using the SqlDataAdapter and a DataSet. The following example demonstrates how to fetch data from the Employees and Departments tables using one T‑SQL query that returns two result sets.

using System.Data;
using Microsoft.Data.SqlClient;

namespace DataTableExample
{
    class MultiResultSetWithDataAdapterExample
    {
        // Connection string to connect to the SQL Server database.
        private const string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=CompanyDB;Trusted_Connection=True;TrustServerCertificate=True;";
        
        static void Main(string[] args)
        {
            // Fetch records from the Employees and Departments tables using a DataAdapter for multiple result sets.");
            
            // Define a combined T-SQL query to fetch two result sets
            string multiQuery = @"
                SELECT EmployeeID, FirstName, LastName, Email, HireDate FROM Employees;
                SELECT DepartmentID, DepartmentName FROM Departments;
            ";

            // Create a DataSet to hold multiple DataTables (one for each result set)
            DataSet dataSet = new DataSet();

            // Using block ensures the SqlConnection is properly disposed after usage
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    // Open the SQL Server connection
                    conn.Open();

                    // Create a SqlCommand with the query and connection
                    using (SqlCommand cmd = new SqlCommand(multiQuery, conn))
                    {
                        // Create a SqlDataAdapter using the SqlCommand
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            // Fill the DataSet with both result sets.
                            // The first result set goes into Tables[0] and the second into Tables[1]
                            adapter.Fill(dataSet);
                        }
                    }

                    // Optionally, rename the DataTables for clarity
                    if (dataSet.Tables.Count >= 1)
                        dataSet.Tables[0].TableName = "Employees";
                    if (dataSet.Tables.Count >= 2)
                        dataSet.Tables[1].TableName = "Departments";

                    // Display fetched Employee records if the table exists in the DataSet
                    if (dataSet.Tables.Contains("Employees"))
                    {
                        DataTable? employeesTable = dataSet.Tables["Employees"];
                        Console.WriteLine($"Number of Employees Fetched: {employeesTable?.Rows.Count}");

                        if (employeesTable?.Rows != null)
                        {
                            foreach (DataRow row in employeesTable.Rows)
                            {
                                // Output each employee record with detailed information
                                Console.WriteLine($"Employee ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}, Hire Date: {((DateTime)row["HireDate"]).ToShortDateString()}");
                            }
                        }
                    }

                    // Display fetched Department records if the table exists in the DataSet
                    if (dataSet.Tables.Contains("Departments"))
                    {
                        DataTable? departmentsTable = dataSet.Tables["Departments"];
                        Console.WriteLine($"\nNumber of Departments Fetched: {departmentsTable?.Rows.Count}");
                        if (departmentsTable?.Rows != null)
                        {
                            foreach (DataRow row in departmentsTable.Rows)
                            {
                                // Output each department record with its corresponding details
                                Console.WriteLine($"Department ID: {row["DepartmentID"]}, Department Name: {row["DepartmentName"]}");
                            }
                        }  
                    }
                }
                catch (SqlException sqlEx)
                {
                    // Handle SQL Server-specific exceptions
                    Console.WriteLine("A SQL Exception occurred while fetching data.");
                    Console.WriteLine(sqlEx.Message);
                }
                catch (Exception ex)
                {
                    // Handle any general exceptions that might occur
                    Console.WriteLine("An error occurred while fetching data.");
                    Console.WriteLine(ex.Message);
                }
            }
            Console.ReadKey();
        }
    }
}

Code Explanation:

• SqlConnection: The SqlConnection is initialized with a connection string that specifies the database server and the target database. It’s wrapped in a using block to ensure that the connection is properly closed and disposed of.
• T‑SQL Query: The multi-line string (multiQuery) contains two SELECT statements separated by a semicolon. This query fetches all employees from the Employees table and all departments from the Departments table in a single database call.
• SqlDataAdapter & DataSet: The SqlDataAdapter executes the command and fills the DataSet with the results. By default, the adapter fills the first result set into DataSet.Tables[0] and the second into DataSet.Tables[1]. Optionally, the tables are renamed for better clarity.

Output:

SqlCommandBuilder Class:

The SqlCommandBuilder automatically generates the INSERT, UPDATE, and DELETE commands based on the SELECT command used to fill the DataTable. When we call the adapter.Update(dataTable), the DataAdapter inspects each row’s RowState. For rows in the Modified state, it will execute the UPDATE command; for rows in the Added state, it will execute the INSERT command; and for rows in the Deleted state, it will execute the DELETE command.

Data Manipulation and Change Tracking in ADO.NET Core Data Table

Each DataRow in a DataTable tracks its changes using the RowState property. The key states are:

• Added: When you create a new DataRow and add it to the DataTable, its RowState is set to Added. This indicates that the row is new and must be inserted into the database.
• Modified: Once a DataRow that was originally loaded (or accepted previously) is changed, its state becomes Modified. This signals that an update (UPDATE command) is needed in the database.
• Deleted: When you call the Delete() method on a DataRow, its state is marked as Deleted. Even though the row still exists in the table (until AcceptChanges is called), it is scheduled to be removed from the database with a DELETE command.
• Unchanged: After you load data or call AcceptChanges(), the DataRow’s state becomes Unchanged. This means the row is in sync with the database.

The SqlDataAdapter uses these states when you call its Update() method:

• It will insert rows marked as Added using the InsertCommand.
• It will update rows marked as Modified using the UpdateCommand.
• It will delete rows marked as Deleted using the DeleteCommand.

Example: DataTable Change Tracking and Updating the Database

The following example demonstrates fetching employee data into a DataTable, modifying an existing row, adding a new row, and deleting a row. It then uses a SqlDataAdapter to update the actual database accordingly. The example code is self-explained, so please read the comment lines for a better understanding:

using System.Data;
using Microsoft.Data.SqlClient;

namespace DataTableChangeTrackingExample
{
    class Program
    {
        // Connection string to connect to the SQL Server database.
        private const string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=CompanyDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static void Main(string[] args)
        {
            // Create a DataTable to hold employee data.
            DataTable employeeTable = new DataTable("Employees");

            // Query to fetch employee details.
            string query = "SELECT EmployeeID, FirstName, LastName, Email, HireDate, DepartmentID FROM Employees;";

            // Using a SqlConnection and SqlDataAdapter to fill the DataTable.
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
                {
                    // Fill the DataTable with data from the database.
                    adapter.Fill(employeeTable);

                    // Create a SqlCommandBuilder to auto-generate INSERT, UPDATE and DELETE commands.
                    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

                    // ----------------------------------------------------------------
                    // Step 1: Display initial state of each row (should be 'Unchanged')
                    // ----------------------------------------------------------------
                    Console.WriteLine("Initial DataRow states (After Fill):");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"EmployeeID: {row["EmployeeID"]} - RowState: {row.RowState}");
                    }

                    // ----------------------------------------------------------------
                    // Step 2: Simulate modifications on the DataTable
                    // ----------------------------------------------------------------

                    // a) Modify an existing row to simulate a Modified state.
                    if (employeeTable.Rows.Count > 0)
                    {
                        // Change the email address of the first employee.
                        employeeTable.Rows[0]["Email"] = "updated.email@example.com";
                    }

                    // b) Add a new row to simulate an Added state.
                    DataRow newRow = employeeTable.NewRow();
                    // Since EmployeeID is an identity field in the database, leave it for the database to assign.
                    newRow["FirstName"] = "Emma";
                    newRow["LastName"] = "Williams";
                    newRow["Email"] = "emma.williams@example.com";
                    newRow["HireDate"] = DateTime.Today;      // Use today's date as HireDate.
                    newRow["DepartmentID"] = 1;               // Assuming DepartmentID 1 exists.
                    employeeTable.Rows.Add(newRow);           // New row state becomes 'Added'.

                    // c) Delete an existing row to simulate a Deleted state.
                    if (employeeTable.Rows.Count > 1)
                    {
                        // Delete the second row in the DataTable.
                        employeeTable.Rows[1].Delete();   // This marks the row as 'Deleted'.
                    }

                    // ----------------------------------------------------------------
                    // Step 3: Display DataRow states after the changes.
                    // ----------------------------------------------------------------
                    Console.WriteLine("\nDataRow states after modifications:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        // For rows in the 'Added' state, there's no EmployeeID assigned yet. So, we are displaying the Id as New
                        // For rows marked as 'Deleted', use the Original version to retrieve EmployeeID.
                        string? employeeId = row.RowState == DataRowState.Deleted
                            ? row["EmployeeID", DataRowVersion.Original].ToString()
                            : (row["EmployeeID"] == DBNull.Value ? "New" : row["EmployeeID"].ToString());
                        
                        Console.WriteLine($"EmployeeID: {employeeId} - RowState: {row.RowState}");
                    }

                    // ----------------------------------------------------------------
                    // Step 4: Update the database with the changes.
                    // ----------------------------------------------------------------
                    // The adapter.Update() method uses the row states to determine which command 
                    // (INSERT, UPDATE, or DELETE) should be executed on the database.
                    int rowsAffected = adapter.Update(employeeTable);
                    Console.WriteLine($"\nDatabase updated. Total rows affected: {rowsAffected}");

                    // After a successful update, we call AcceptChanges to mark all rows as 'Unchanged'.
                    employeeTable.AcceptChanges();

                    // ----------------------------------------------------------------
                    // Step 5: Retrieve and display the final data from the database.
                    // ----------------------------------------------------------------
                    employeeTable.Clear(); // Clear local data.
                    adapter.Fill(employeeTable); // Refill from the database.
                    Console.WriteLine("\nFinal data in the database after Update:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"EmployeeID: {row["EmployeeID"]}, " +
                                          $"Name: {row["FirstName"]} {row["LastName"]}, " +
                                          $"Email: {row["Email"]}, " +
                                          $"DepartmentID: {row["DepartmentID"]}");
                    }
                }
            }

            Console.ReadKey();
        }
    }
}

Code Explanation

Database Connection and DataTable Setup:

• A connection string is specified for the SQL Server database (CompanyDB).
• A DataTable named Employees is created to mirror the Employees table in the database.
• A SELECT query retrieves the EmployeeID, FirstName, LastName, Email, HireDate, and DepartmentID columns.
• A SqlDataAdapter is used to fill the DataTable with data from the database.

Generating Command Objects:

• A SqlCommandBuilder is created to automatically generate SQL commands for INSERT, UPDATE, and DELETE based on the SELECT query provided.
• This enables the adapter to update changes made in the DataTable back to the database without manually writing each command.

Displaying the Initial Row States:

• After filling the DataTable, the row states for all data are printed.
• At this point, every row is Unchanged, meaning no local modifications have yet been made.

Simulating Data Changes Locally:

• Modified Row: The email of the first employee is changed, updating the row’s state to Modified.
• Added Row: A new row is created and filled with data. When added to the DataTable, its state is Added.
• Deleted Row: The second row (if available) is marked for deletion using the Delete() method, which sets its state to Deleted.

Displaying the Row States After Modifications:

• The application iterates over all rows to output each row’s state.
• For rows in the Deleted state, the original EmployeeID is used for display.
• For the newly created Row, we are displaying the EmployeeId as New.

Updating the Database:

The adapter.Update() method is called to push all changes back to the database.

• The Added row triggers an INSERT command.
• The Modified row triggers an UPDATE command.
• The Deleted row triggers a DELETE command.

The number of affected rows is printed. After a successful update, calling AcceptChanges() resets all row states to Unchanged.

Displaying Final Data from the Database:

The DataTable is cleared and refilled from the database using the adapter to verify that the changes have been successfully committed. The final data is printed to the console, showing the updated state of the table.

Output:

What are the Differences Between the Remove and Delete Methods of a DataTable?

In ADO.NET, there are two common approaches to managing data removal in a DataTable. They are as follows:

Remove and RemoveAt Methods:

The Remove() and RemoveAt() methods immediately remove the DataRow(s) from the DataTable’s Rows collection. As the row is entirely removed from the DataTable, there is no change tracking for that row. In other words, the DataRow disappears from the DataTable and will not be available during a later call to update the database.

When to Use Them:

• Use these methods when you have invalid or obsolete data that you do not need to propagate back to the database.
• As no change is tracked, these rows will not generate any DELETE commands when you update the database via a DataAdapter.
• Use these methods when you are sure you no longer need a row and do not plan to synchronize that deletion with an underlying data store.

Delete Method:

When you call the Delete() method on a DataRow, the row remains in the DataTable, but its RowState is set to Deleted. This state indicates that the row should be removed from the database when the Update() method is called on the DataAdapter. Please note that until you call AcceptChanges(), the row is still available (marked as Deleted), and you can revert the deletion by calling RejectChanges() if necessary.

When to Use It:

• Use Delete() when you want to record that a row has been removed and later update the database accordingly.
• The change is tracked so that the DataAdapter issues a DELETE command for the row marked as deleted.
• This method is useful when tracking deletions and later synchronizing changes with the external data store.

Example: Removing and Deleting Rows from a Datatable

Let us see an example to understand the use of RemoveAt, Remove, and Delete methods of a Data Table. The example demonstrates:

• Creating and populating a Products DataTable (assumed to be mapped to an existing Products table in your SQL Server database).
• Removing rows using RemoveAt() and Remove() methods (immediate deletion from DataTable without tracking).
• Deleting a row using the Delete() method (marks the row for deletion, thus tracked for database update).
• Using a SqlDataAdapter combined with SqlCommandBuilder to automatically generate database commands and synchronize changes back to the SQL Server database.

Please modify the Program class as follows: The example code is self-explained, so please read the comment lines for a better understanding:

using System.Data;
using Microsoft.Data.SqlClient;

namespace DataTableDeleteExample
{
    public class Program
    {
        static void Main(string[] args)
        {
            // Connection string to the SQL Server CompanyDB database 
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=CompanyDB;Trusted_Connection=True;TrustServerCertificate=True;";
            
            // Define a SELECT query to fetch employees from the Employees table.
            string selectQuery = "SELECT EmployeeID, FirstName, LastName, Email, HireDate, DepartmentID FROM Employees;";

            // Create a DataTable to hold employee data.
            DataTable employeeTable = new DataTable("Employees");

            // Open a connection and fill the DataTable using a SqlDataAdapter.
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, conn))
                {
                    // Use SqlCommandBuilder to auto-generate INSERT, UPDATE, DELETE commands.
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                    // Fill the DataTable with data from the Employees table.
                    adapter.Fill(employeeTable);

                    // ----------------------------------------------------------------
                    // Display initial data from the Employees table.
                    // ----------------------------------------------------------------
                    Console.WriteLine("Initial Employee Data from Database:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"EmployeeID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, RowState: {row.RowState}");
                    }

                    // ----------------------------------------------------------------
                    // Demonstrate Removing rows using RemoveAt and Remove methods.
                    // ----------------------------------------------------------------

                    // --- RemoveAt example ---
                    // Remove the row at index 1 (if exists) using RemoveAt.
                    if (employeeTable.Rows.Count > 1)
                    {
                        Console.WriteLine("\nUsing RemoveAt to immediately remove the row at index 1.");
                        employeeTable.Rows.RemoveAt(1); // Row is completely removed with no change tracking.
                    }

                    // Display data after RemoveAt.
                    Console.WriteLine("\nEmployee Data after RemoveAt:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"EmployeeID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, RowState: {row.RowState}");
                    }

                    // --- Remove example ---
                    // For demonstration, add a temporary employee row and then remove it using Remove().
                    DataRow tempEmployee = employeeTable.NewRow();
                    tempEmployee["FirstName"] = "Temp";
                    tempEmployee["LastName"] = "Employee";
                    tempEmployee["Email"] = "temp.employee@example.com";
                    tempEmployee["HireDate"] = DateTime.Now.Date;
                    tempEmployee["DepartmentID"] = 1; // Assuming DepartmentID 1 exists.
                    employeeTable.Rows.Add(tempEmployee);

                    Console.WriteLine("\nAfter adding a temporary employee row:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        // Use "New" for added rows without a valid EmployeeID.
                        string? empId = row.RowState == DataRowState.Added ? "New" : row["EmployeeID"].ToString();
                        Console.WriteLine($"EmployeeID: {empId}, Name: {row["FirstName"]} {row["LastName"]}, RowState: {row.RowState}");
                    }

                    // Remove the temporary row using Remove().
                    DataRow[] tempRows = employeeTable.Select("FirstName = 'Temp' AND LastName = 'Employee'");
                    foreach (DataRow row in tempRows)
                    {
                        employeeTable.Rows.Remove(row);
                        Console.WriteLine("Removed temporary employee using Remove()");
                    }

                    // Display data after Remove.
                    Console.WriteLine("\nEmployee Data after Remove:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"EmployeeID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, RowState: {row.RowState}");
                    }

                    // ----------------------------------------------------------------
                    // Demonstrate Deleting a row using the Delete() method.
                    // ----------------------------------------------------------------
                    // For demonstration, mark the first row for deletion (if available).
                    if (employeeTable.Rows.Count > 0)
                    {
                        Console.WriteLine("\nMarking the first row for deletion using Delete().");
                        employeeTable.Rows[0].Delete(); // Marks row with RowState = Deleted.
                    }

                    // Display employee data after Delete.
                    Console.WriteLine("\nEmployee Data after calling Delete():");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        // For Deleted rows, fetch the original EmployeeID.
                        string? empId = row.RowState == DataRowState.Deleted
                            ? row["EmployeeID", DataRowVersion.Original].ToString()
                            : row["EmployeeID"].ToString();
                        Console.WriteLine($"EmployeeID: {empId}, RowState: {row.RowState}");
                    }

                    // ----------------------------------------------------------------
                    // Update the Database with the changes in the DataTable.
                    // ----------------------------------------------------------------
                    // Calling adapter.Update() will process only the rows with a tracked state
                    // (i.e., Added, Modified, or Deleted).
                    // Rows removed using RemoveAt/Remove do not get updated.
                    int rowsAffected = adapter.Update(employeeTable);
                    Console.WriteLine($"\nRows affected in database update: {rowsAffected}");

                    // The SqlDataAdapter automatically calls AcceptChanges on updated rows.
                    // You may optionally call AcceptChanges() explicitly if needed.
                    employeeTable.AcceptChanges();

                    // ----------------------------------------------------------------
                    // All remaining rows in the DataTable should have RowState = Unchanged.
                    // ----------------------------------------------------------------
                    Console.WriteLine("\nEmployee Data in DataTable (should all be Unchanged):");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"EmployeeID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, RowState: {row.RowState}");
                    }

                    // ----------------------------------------------------------------
                    // Final Data in the Database: Retrieve and display the final data from the database.
                    // ----------------------------------------------------------------
                    employeeTable.Clear(); // Clear local data.
                    adapter.Fill(employeeTable); // Refill from the database.
                    Console.WriteLine("\nFinal data in the database after Update:");
                    foreach (DataRow row in employeeTable.Rows)
                    {
                        Console.WriteLine($"EmployeeID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, RowState: {row.RowState}");
                    }
                }
            }

            Console.ReadKey();
        }
    }
}

Code Explanation

• DataTable Creation and Population: The application opens an SQL Server connection and uses a SqlDataAdapter to execute a SELECT query to retrieve data from the Employees table. Based on the adapter, a SqlCommandBuilder is created so that INSERT, UPDATE, and DELETE commands are automatically generated. The fetched data is loaded into an in-memory Data Table called employeeTable. All rows initially have a RowState of Unchanged.
• Using RemoveAt: The row at index 1 (if available) is immediately removed from the DataTable using RemoveAt(). The DataTable does not track this removal, so no DELETE command is later executed for this row.
• Using Remove: A temporary employee row is added to the DataTable (its RowState becomes Added). Then, the row is located via a SELECT filter and is removed using the Remove() method. Like RemoveAt(), the row is immediately eliminated from the DataTable with no change tracking.
• Using Delete Method: The first row in the DataTable is marked for deletion by calling Delete(). This sets its RowState to Deleted, allowing the adapter to generate a DELETE command when Update() is called. When listing the rows, for any rows marked as Deleted, the code retrieves the original EmployeeID (using DataRowVersion.Original) to display proper identification.
• Synchronizing Changes with the Database: The call to adapter.Update(employeeTable) processes only tracked changes (rows in states Added, Modified, or Deleted). Rows removed via RemoveAt() and Remove() are not tracked for database synchronization. After a successful update, the adapter (or an explicit call) sets all remaining rows to Unchanged.

Output:

The SqlCommandBuilder, along with the SqlDataAdapter, makes it easy to automatically generate and execute the proper SQL commands (INSERT, UPDATE, DELETE) corresponding to the state of your DataTable rows.

In the next article, I will discuss ADO.NET Core DataSet with Examples. In this article, I explain ADO.NET Core DataTable with SQL Server with Examples. I would like your feedback. Please post your feedback, questions, or comments about this article.