ADO.NET Core SqlDataAdapter Class

ADO.NET Core SqlDataAdapter Class with Examples

In this article, I will discuss the ADO.NET Core SqlDataAdapter Class with Examples. Please read our previous article discussing the ADO.NET Core SqlDataReader Class. As part of this article, we will discuss the following pointers in detail.

  1. What is ADO.NET Core SqlDataAdapter Class?
  2. ADO.NET Core SqlDataAdapter Important Methods and Properties
  3. Example to Understand ADO.NET Core SqlDataAdapter Class
  4. Understanding SqlDataAdapter Fill Method
  5. How Do We Update Data using ADO.NET Core SqlDataAdapter?
  6. Example using ADO.NET Core SqlDataAdapter Properties
  7. ADO.NET Core SqlDataAdapter to Perform Async Operation
  8. ADO.NET Core SqlDataAdapter using Stored Procedure
  9. When Should We Use ADO.NET Core SqlDataAdapter?

What is ADO.NET Core SqlDataAdapter Class?

The SqlDataAdapter Object in ADO.NET Core serves as a bridge between a DataSet or DataTable and SQL Server for retrieving and saving data. It is used to execute SQL commands and fill the DataSet or DataTable with results, and it can also update the database to reflect changes made in the DataSet. The SqlDataAdapter is a class that represents a set of SQL commands and a database connection.

ADO.NET Core SqlDataAdapter Important Methods and Properties

ADO.NET Core, as part of the broader .NET Core framework, is designed to facilitate database operations, such as executing commands and managing disconnected data. The following are some of the important methods and properties of the ADO.NET Core SqlDataAdapter object.

Important Methods of SqlDataAdapter
  • Fill: This method adds or refreshes rows in the DataSet to match those in the data source using the DataSet and DataTable names or DataSet and DataTable objects.
  • Update: This method applies changes made in the DataSet or DataTable back to the data source. It requires the adapter to be set with proper command objects (InsertCommand, UpdateCommand, DeleteCommand).
Important Properties of SqlDataAdapter
  • SelectCommand: Gets or sets an SQL statement or stored procedure used to select records in the data source.
  • InsertCommand: Gets or sets an SQL statement or stored procedure used to insert new records into the data source.
  • UpdateCommand: Gets or sets an SQL statement or stored procedure used to update records in the data source.
  • DeleteCommand: Gets or sets an SQL statement or stored procedure used to delete records from the data source.
  • AcceptChangesDuringUpdate: Indicates whether AcceptChanges is called on a DataRow after it is updated in the data source.
  • ContinueUpdateOnError: Specifies whether to produce an exception when an error is encountered during an update operation. If set to false, an exception is thrown. Otherwise, the update operation continues despite the error.

Example to Understand ADO.NET Core SqlDataAdapter Class

Let us see an example to understand the ADO.NET Core SqlDataAdapter Class. We are going to use the following Employee table to understand this concept.

Example to Understand ADO.NET Core SqlDataAdapter Class

Please execute the following SQL script in your SQL Server to create the EmployeeDB database and the Employees table and insert some dummy data into it.

CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
GO

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Department NVARCHAR(50)
);
GO

INSERT INTO Employees (FirstName, LastName, Email, Department)
VALUES 
('John', 'Doe', 'john.doe@example.com', 'IT'),
('Jane', 'Doe', 'jane.doe@example.com', 'HR'),
('Jim', 'Beam', 'jim.beam@example.com', 'Finance');
GO
Access Employee Data Using ADO.NET Core SqlDataAdapter

To query the database and fill a DataSet or DataTable, we need to follow the below steps:

  • Create a Connection: Instantiate a SqlConnection object with your connection string.
  • Create a Command: Instantiate a SqlCommand object, specifying your SQL query and the connection object.
  • Create a SqlDataAdapter: Create an instance of the SqlDataAdapter object.
  • Fill a DataSet/DataTable: Use the adapter’s Fill method to execute the command and fill a DataSet or DataTable with the results.

For a better understanding, please modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //I am using Windows Authentication and hence no need to pass the User Id and Password
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

                //Prepare the Query
                string query = "SELECT * FROM Employees";

                //Create an Instance of SqlConnection
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    try
                    {
                        //Create an Instance of SqlDataAdapter with the Query and Connection
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(query, connection);

                        //Create a Data table
                        DataTable dataTable = new DataTable();

                        //Fill the Datatable using the Fill Method of the dataAdapter 
                        //The following things are done by the Fill method
                        //1. Open the connection
                        //2. Execute Command
                        //3. Retrieve the Result
                        //4. Fill/Store the Retrieve Result in the Data table
                        //5. Close the connection
                        dataAdapter.Fill(dataTable);

                        //Display the Data from the Data table
                        foreach (DataRow row in dataTable.Rows)
                        {
                            Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}, Department: {row["Department"]}");
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"An error occurred: {ex.Message}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Output:

What is ADO.NET Core SqlDataAdapter Class?

Understanding SqlDataAdapter Fill Method

The Fill method of the SqlDataAdapter class plays an important role in ADO.NET by serving as the bridge between a data source and a data set or a data table. The method is designed to fill a DataSet/DataTable with the results of the SelectCommand associated with the SqlDataAdapter. Here’s a detailed explanation of its functionality, usage, and key aspects:

  • Data Retrieval: The Fill method executes the SelectCommand (a SqlCommand object) associated with the SqlDataAdapter. This command is typically a SQL SELECT statement or a stored procedure that retrieves data from a database.
  • DataSet/DataTable Population: The data retrieved by the SelectCommand is used to populate a DataSet or a DataTable. If filling a DataSet, the method can fill multiple tables within the DataSet by using multiple database commands if configured to do so.
  • Schema Inference: If the DataSet or DataTable is initially empty, the Fill method can infer the schema from the retrieved data, creating the necessary columns in the DataTable objects within the DataSet.
  • Type Mapping: The method maps the data types from the data source to compatible .NET data types, ensuring the data is usable within the .NET environment.
  • Connection Management: The Fill method manages the connection to the data source automatically. If the connection is not open when Fill is called, the method opens the connection to execute the SelectCommand and then closes the connection when the operation is complete. If the connection is already open, it remains open after the Fill operation.
  • Concurrency and Transactions: If the SelectCommand is executed within a transaction, the Fill method respects the transaction context, ensuring that the data retrieval is consistent with the transaction’s state.
How to Update Data using ADO.NET Core SqlDataAdapter:

To update data using SqlDataAdapter, you need to follow a pattern that involves retrieving data into a DataTable, making changes to the rows of the DataTable, and then using the SqlDataAdapter to update the original database with the changes made in the DataTable. This process usually involves configuring the SqlDataAdapter with the appropriate SQL commands to update the database. For a better understanding, please modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //I am using Windows Authentication and hence no need to pass the User Id and Password
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

                //Prepare the Query
                string selectQuery = "SELECT * FROM Employees";

                //Create an Instance of SqlConnection
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //Create the SqlDataAdapter Object with the Select Query and Connection Object
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);

                    // Creating the commands that will be used by the SqlDataAdapter to update the database
                    SqlCommand updateCommand = new SqlCommand("UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Department = @Department WHERE EmployeeID = @EmployeeID", connection);

                    // Adding parameters for the update command
                    updateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                    updateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                    updateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 100, "Email");
                    updateCommand.Parameters.Add("@Department", SqlDbType.NVarChar, 50, "Department");
                    updateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");

                    dataAdapter.UpdateCommand = updateCommand;

                    DataTable dataTable = new DataTable();

                    // Fill the DataTable with data from the database
                    dataAdapter.Fill(dataTable);

                    // Assuming you want to update the first row's Email for demonstration
                    if (dataTable.Rows.Count > 0)
                    {
                        DataRow rowToUpdate = dataTable.Rows[0];
                        rowToUpdate["Email"] = "updated.email@example.com";
                    }

                    // Open the connection for the update
                    connection.Open();

                    // Perform the update on the database
                    int rowsAffected = dataAdapter.Update(dataTable);

                    // Close the connection
                    connection.Close();

                    Console.WriteLine($"{rowsAffected} row(s) were updated.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Explanation
  • SqlDataAdapter Setup: An instance of SqlDataAdapter is created to manage the retrieval and updating of data. It is initialized with a SELECT query to fetch data from the Employees table.
  • SqlCommand for Update: A SqlCommand object is prepared to update records in the database. This command includes parameters that correspond to the columns in the Employees table that might be updated.
  • Parameter Mapping: Parameters in the update command are mapped to the column names in the Data Table. This allows the SqlDataAdapter to apply changes from the DataTable to the database.
  • Fill the DataTable: The DataTable is filled with data from the Employees table using dataAdapter.Fill(dataTable).
  • Update DataTable: A row in the DataTable is updated. In this example, the email of the first row is changed. In a real application, you might search for a specific row based on some criteria.
  • Update Database: The connection to the database is opened, and dataAdapter.Update(dataTable) is called to apply the changes made in the DataTable to the database. The number of rows affected is output to the console.
  • Close Connection: Finally, the database connection is closed.

Example using ADO.NET Core SqlDataAdapter Properties

The following example will demonstrate the process of selecting, inserting, updating, and deleting records from the Employees table using SqlDataAdapter’s SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand, as well as managing schema actions and handling errors during updates.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //I am using Windows Authentication and hence no need to pass the User Id and Password
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

                //Create an Instance of SqlConnection
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter dataAdapter = new SqlDataAdapter();

                    // Setting up the SelectCommand
                    dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Employees", connection);

                    // Setting up the InsertCommand
                    SqlCommand insertCommand = new SqlCommand("INSERT INTO Employees (FirstName, LastName, Email, Department) VALUES (@FirstName, @LastName, @Email, @Department)", connection);
                    insertCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                    insertCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                    insertCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 100, "Email");
                    insertCommand.Parameters.Add("@Department", SqlDbType.NVarChar, 50, "Department");
                    dataAdapter.InsertCommand = insertCommand;

                    // Setting up the UpdateCommand
                    SqlCommand updateCommand = new SqlCommand("UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Department = @Department WHERE EmployeeID = @EmployeeID", connection);
                    updateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                    updateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                    updateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 100, "Email");
                    updateCommand.Parameters.Add("@Department", SqlDbType.NVarChar, 50, "Department");
                    updateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
                    dataAdapter.UpdateCommand = updateCommand;

                    // Setting up the DeleteCommand
                    SqlCommand deleteCommand = new SqlCommand("DELETE FROM Employees WHERE EmployeeID = @EmployeeID", connection);
                    deleteCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
                    dataAdapter.DeleteCommand = deleteCommand;

                    DataTable dataTable = new DataTable();
                    dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // Manage schema actions
                    dataAdapter.FillSchema(dataTable, SchemaType.Source); // Ensure schema is correct

                    // Fill the DataTable with current data
                    dataAdapter.Fill(dataTable);

                    // Demonstrating AcceptChangesDuringFill
                    dataAdapter.AcceptChangesDuringFill = false;

                    // Insert a new row (example)
                    DataRow newRow = dataTable.NewRow();
                    newRow["FirstName"] = "New";
                    newRow["LastName"] = "Employee";
                    newRow["Email"] = "new.employee@example.com";
                    newRow["Department"] = "IT";
                    dataTable.Rows.Add(newRow);

                    // Update an existing row (example)
                    if (dataTable.Rows.Count > 0)
                    {
                        DataRow rowToUpdate = dataTable.Rows[0];
                        rowToUpdate["Email"] = "updated.email@example.com";
                    }

                    // Delete a row (example)
                    if (dataTable.Rows.Count > 1)
                    {
                        dataTable.Rows[1].Delete();
                    }

                    // Handling errors during update
                    dataAdapter.ContinueUpdateOnError = true;

                    // Update the database
                    connection.Open();
                    dataAdapter.Update(dataTable);
                    connection.Close();

                    // Demonstrating AcceptChangesDuringUpdate
                    dataAdapter.AcceptChangesDuringUpdate = true;

                    // Check for errors
                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (row.HasErrors)
                        {
                            Console.WriteLine($"Row error: {row.RowError}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Explanation
  • Commands Setup: The SqlDataAdapter is configured with SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand, each defined to perform the respective operations on the Employees table.
  • Parameters: Parameters for each command are defined to map values between the DataTable and the database table during operations.
  • DataTable and Schema: A DataTable is created, and its schema is set up according to the source table in the database, ensuring column definitions match.
  • MissingSchemaAction: Determines the action to take when the incoming data does not have a corresponding table or column in the dataset. Here, it’s set to AddWithKey to add necessary schema elements, including primary keys.
  • AcceptChangesDuringFill and AcceptChangesDuringUpdate: Control whether changes to the DataTable are accepted during the Fill and Update operations. AcceptChangesDuringFill is set to false to keep rows in an added state after filling, and AcceptChangesDuringUpdate is set to true to accept changes after updates.
  • Data Manipulation: The example inserts a new row, updates an existing row’s email, and deletes a row as a demonstration of manipulating the DataTable.
  • ContinueUpdateOnError: When set to true, the update operation continues even if errors occur, allowing all changes to be attempted. Errors can be reviewed by checking the RowError property of each DataRow.
  • Error Handling: After the update, the code checks for rows with errors to handle any issues that occurred during the update process.

ADO.NET Core SqlDataAdapter to Perform Async Operation:

The SqlDataAdapter class itself does not provide asynchronous methods directly like FillAsync or UpdateAsync. However, you can use the asynchronous counterparts of the SqlCommand execution methods (like ExecuteNonQueryAsync, ExecuteReaderAsync, etc.) to perform database operations asynchronously in the context of preparing data for SqlDataAdapter or executing commands that are part of the SqlDataAdapter.

To perform operations using SqlDataAdapter in an asynchronous manner, you’ll need to manually implement the asynchronous execution of database commands for operations like inserting, updating, and deleting rows, as well as opening and closing the connection.

Below is an adapted version of the previous example that incorporates asynchronous programming for operations that support it, noting that the core Fill and Update operations of SqlDataAdapter are handled synchronously due to the limitations of SqlDataAdapter.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                //I am using Windows Authentication and hence no need to pass the User Id and Password
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

                // Asynchronously open a connection to the database
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    SqlDataAdapter dataAdapter = new SqlDataAdapter();
                    dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Employees", connection);

                    DataTable dataTable = new DataTable();

                    // Execute the Fill method on a background thread
                    await Task.Run(() => dataAdapter.Fill(dataTable));

                    // Simulate an update operation on the DataTable (for demonstration)
                    if (dataTable.Rows.Count > 0)
                    {
                        DataRow row = dataTable.Rows[0]; // Example: modify the first row
                        row["Email"] = "updated.email@example.com"; // Modify the email column
                    }

                    // Prepare an SqlCommand for async execution (example for insert operation)
                    SqlCommand insertCommand = new SqlCommand("INSERT INTO Employees (FirstName, LastName, Email, Department) VALUES (@FirstName, @LastName, @Email, @Department)", connection);
                    insertCommand.Parameters.AddWithValue("@FirstName", "Jane");
                    insertCommand.Parameters.AddWithValue("@LastName", "Doe");
                    insertCommand.Parameters.AddWithValue("@Email", "jane.doe@example.com");
                    insertCommand.Parameters.AddWithValue("@Department", "HR");

                    // Asynchronously execute the insert command
                    await insertCommand.ExecuteNonQueryAsync();

                    // Prepare the UpdateCommand for SqlDataAdapter to update the database based on changes in the DataTable
                    SqlCommand updateCommand = new SqlCommand("UPDATE Employees SET Email = @Email WHERE EmployeeID = @EmployeeID", connection);
                    updateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 100, "Email");
                    updateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID");
                    dataAdapter.UpdateCommand = updateCommand;

                    // Synchronously update the database with changes in the DataTable
                    // Note: There's no direct async equivalent for SqlDataAdapter.Update()
                    dataAdapter.Update(dataTable);

                    await connection.CloseAsync();
                    Console.WriteLine("Database operations have been completed successfully.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Explanation and Key Points
  • Asynchronous Connection Management: The example begins by opening and closing the database connection asynchronously using OpenAsync and CloseAsync.
  • DataAdapter for Selection: SqlDataAdapter is used with its SelectCommand to populate a DataTable. Since Fill does not support async, Task.Run is used to execute it on a background thread, allowing other operations to continue.
  • Simulating Data Modification: The example demonstrates modifying data within the loaded Data Table. This is to simulate an update scenario.
  • Asynchronous Command Execution: An insert operation is performed using SqlCommand with ExecuteNonQueryAsync for asynchronous execution.
  • Updating Database: The UpdateCommand of SqlDataAdapter is configured to update the database to reflect changes made to the DataTable. The actual update to the database is performed synchronously with dataAdapter.Update(dataTable) since no async version exists.
Limitations and Considerations
  • Hybrid Approach: This example uses a mix of asynchronous and synchronous operations due to the limitations of SqlDataAdapter. For operations that can’t be performed asynchronously with SqlDataAdapter, running them on a background thread is a workaround but does not provide true async I/O benefits.
  • Entity Framework Core: For fully asynchronous database operations, consider using Entity Framework Core, which offers comprehensive async support.
  • Exception Handling: Proper exception handling with try-catch blocks is essential, especially with asynchronous operations to manage exceptions effectively.

ADO.NET Core SqlDataAdapter using Stored Procedure

When working with stored procedures, the SqlDataAdapter can be utilized to execute these procedures and either fill a DataSet or update the SQL Server database based on the DataSet’s changes. To use a stored procedure with SqlDataAdapter for retrieving data from the Employees table, you will need to follow a two-step process:

  • Create the stored procedure in your SQL server database that selects and returns the data from the employees’ table.
  • Modify the C# Application to use SqlDataAdapter with this stored procedure to populate a DataTable or DataSet.
Create the Stored Procedure

Open SQL Server Management Studio (SSMS) and connect to your database. Then, execute the following SQL command to create a stored procedure named GetAllEmployees. The following stored procedure doesn’t require any parameters and selects all records from the Employees table.

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees;
END
Modify the C# Application

Now, modify your C# console application to use this stored procedure with a SqlDataAdapter. Here’s how you can do it:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                //I am using Windows Authentication and hence no need to pass the User Id and Password
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create the SqlCommand for the stored procedure
                    SqlCommand command = new SqlCommand("GetAllEmployees", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    // Create the SqlDataAdapter with the SqlCommand
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

                    // Create a DataTable to hold the retrieved data
                    DataTable dataTable = new DataTable();

                    // Open the connection and fill the DataTable
                    connection.Open();
                    dataAdapter.Fill(dataTable);
                    connection.Close();

                    // Display the data (for demonstration purposes)
                    foreach (DataRow row in dataTable.Rows)
                    {
                        Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}, Department: {row["Department"]}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}
Explanation
  • SqlCommand Configuration: The SqlCommand is configured with the name of the stored procedure and the connection. The CommandType is set to CommandType.StoredProcedure to indicate that the command text is the name of a stored procedure.
  • SqlDataAdapter and DataTable: A SqlDataAdapter is created with the configured SqlCommand. It is used to fill a Data Table with the data retrieved by executing the stored procedure.
  • Displaying Data: For demonstration purposes, the code iterates through the rows in the DataTable and prints out each employee’s details to the console.

When you run the above code, you will get the following output:

ADO.NET Core SqlDataAdapter Class with Examples

When Should We Use ADO.NET Core SqlDataAdapter?
  • Direct Database Interactions: If you prefer working directly with the database without an ORM, and your application requires operations such as loading data into DataTables or performing batch operations, you would use Microsoft.Data.SqlClient. It provides similar capabilities for executing SQL commands, but you’d manage data adaptability and updates more manually or through other utilities you create.
  • Batch Operations and Bulk Data Handling: For scenarios where you need to perform bulk inserts, updates, or deletes, you would directly use Microsoft.Data.SqlClient to execute SQL commands. For bulk operations, especially, you might leverage additional libraries designed for bulk data handling in .NET Core and .NET 5+ applications.
  • Using DataSet and DataTable: While less common in modern .NET Core applications due to the emphasis on ORM and code-first approaches, there are still situations where DataSet and DataTable are useful, particularly for legacy systems integration or when working with dynamically structured data. You can still use these classes in .NET Core and .NET 5+; however, the way you populate and manipulate them might slightly differ, emphasizing the use of command objects and manual handling over a SqlDataAdapter.
  • Performance Considerations: Direct database access using Microsoft.Data.SqlClient can offer more control over the execution of SQL commands and potentially better performance for certain scenarios, as it allows for fine-tuned optimizations that ORMs might abstract away.

Note: While SqlDataAdapter and DataSet are still supported and useful for certain scenarios, modern .NET applications often use Entity Framework Core (EF Core) for data access. EF Core provides a more intuitive, higher-level API for data manipulation and querying with strong typing, LINQ integration, and support for asynchronous operations. So, in this course, we are not going to keep focus on Data set and Data table concepts.

In the next article, I will discuss ADO.NET Core Using Stored Procedures with Examples. In this article, I explain the ADO.NET Core SqlDataAdapter Class with Examples. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET Core SqlDataAdapter Class article.

Leave a Reply

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