ADO.NET SqlDataAdapter

Back to: ADO.NET Tutorial For Beginners and Professionals

ADO.NET SqlDataAdapter Class in C# with Examples

In this article, I am going to discuss the ADO.NET SqlDataAdapter Class in C# with Examples. Please read our previous article discussing ADO.NET SqlDataReader Class in C# with Examples. At the end of this article, we are going to discuss the following pointers in detail, which are related to C# SqlDataAdapter.

1. What is ADO.NET SqlDataAdapter?
2. Understanding Constructors, Methods, and Properties of  SqlDataAdapter.
3. How do you create an instance of the SqlDataAdapter class in ADO.NET?
4. SqlDataAdapter using Stored Procedure.
5. How do you call a stored procedure using SqlDataAdapter?

What is ADO.NET SqlDataAdapter in C#?

The ADO.NET SqlDataAdapter in C# bridges a DataSet or DataTable and a Data Source (SQL Server Database) to retrieve data. The SqlDataAdapter is a class that represents a set of SQL commands and a database connection. It is used to fill the DataSet or DataTable and update the data source as well.

Signature of SqlDataAdapter in C#

As you can see in the below image, the SqlDataAdapter class is a sealed class, so it cannot be inherited. Again, it is inherited from the DbDataAdapter class and implements the IDbDataAdapter, IDataAdapter, and ICloneable interface.

Constructors of ADO.NET SqlDataAdapter class in C#:

The SqlDataAdapter class provides the following constructors.

1. SqlDataAdapter(): Initializes a new instance of the SqlDataAdapter class.
2. SqlDataAdapter(SqlCommand selectCommand): Initializes a new instance of the SqlDataAdapter class with the specified SqlCommand. The selectCommand can be a Transact-SQL SELECT statement or a stored procedure.
3. SqlDataAdapter(string selectCommandText, string selectConnectionString): Initializes a new instance of the SqlDataAdapter class with the command and a connection string. The selectCommandText can be a Transact-SQL SELECT statement or a stored procedure.
4. SqlDataAdapter(string selectCommandText, SqlConnection selectConnection): Initializes a new instance of the SqlDataAdapter class with the command and a connection string. The selectCommandText can be a Transact-SQL SELECT statement or a stored procedure. If your connection string does not use Integrated Security = true, you can use System.Data.SqlClient.SqlCredential to pass the user ID and password more securely than by specifying the user ID and password as text in the connection string.

Methods of ADO.NET SqlDataAdapter class in C#:

The C# SqlDataAdapter class provides the following methods.

1. CloneInternals(): It is used to create a copy of this instance of DataAdapter.
2. Dispose(Boolean): It is used to release the unmanaged resources used by the DataAdapter.
3. Fill(DataSet): It is used to add rows in the DataSet to match those in the data source.
4. FillSchema(DataSet, SchemaType, String, IDataReader): Add a DataTable to the specified DataSet.
5. GetFillParameters(): It is used to get the parameters set by the user when executing an SQL SELECT statement.
6. ResetFillLoadOption(): It is used to reset FillLoadOption to its default state.
7. ShouldSerializeAcceptChangesDuringFill(): It determines whether the
8. ShouldSerializeFillLoadOption(): It determines whether the FillLoadOption property should be persisted or not.
9. ShouldSerializeTableMappings(): It determines whether one or more DataTableMapping objects exist or not.
10. Update(DataSet): It is used to call the respective INSERT, UPDATE, or DELETE statements.

How to create an instance of the C# SqlDataAdapter class in ADO.NET?

In order to create an instance of the SqlDataAdapter class in C#, we need to specify two things. The SQL command we want to execute and the connection we want to execute is like how we create the SqlCoomand object. Following is the syntax to create an instance of the SqlDataAdapter class.

Example to understand the SqlDataAdapter in C#:

We are going to use the following student table to understand the SqlDataAdapter object.

Please use the below SQL script to create a database called StudentDB and a table called Student with the required sample data.

CREATE DATABASE StudentDB;
GO

USE StudentDB;
GO

CREATE TABLE Student(
 Id INT PRIMARY KEY,
 Name VARCHAR(100),
 Email VARCHAR(50),
 Mobile VARCHAR(50)
)
GO

INSERT INTO Student VALUES (101, 'Anurag', 'Anurag@dotnettutorial.net', '1234567890')
INSERT INTO Student VALUES (102, 'Priyanka', 'Priyanka@dotnettutorial.net', '2233445566')
INSERT INTO Student VALUES (103, 'Preety', 'Preety@dotnettutorial.net', '6655443322')
INSERT INTO Student VALUES (104, 'Sambit', 'Sambit@dotnettutorial.net', '9876543210')
GO

Example to Understand ADO.NET SqlDataAdapter in C#

Now, we need to develop an application where we will fetch all the data from the student table, and then we need to display the student data in the console using SqlDataAdapter in C# using ADO.NET. Let us first write the code, and then we will understand the code.

using System;
using System.Data;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    SqlDataAdapter da = new SqlDataAdapter("select * from student", connection);

                    //Using Data Table
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    //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

                    Console.WriteLine("Using Data Table");
                    //Active and Open connection is not required
                    //dt.Rows: Gets the collection of rows that belong to this table
                    //DataRow: Represents a row of data in a DataTable.
                    foreach (DataRow row in dt.Rows)
                    {
                        //Accessing using string Key Name
                        Console.WriteLine(row["Name"] + ",  " + row["Email"] + ",  " + row["Mobile"]);
                        //Accessing using integer index position
                        //Console.WriteLine(row[0] + ",  " + row[1] + ",  " + row[2]);
                    }

                    Console.WriteLine("---------------");

                    //Using DataSet
                    DataSet ds = new DataSet();
                    da.Fill(ds, "student"); //Here, the datatable student will be stored in Index position 0
                    Console.WriteLine("Using Data Set");

                    //Tables: Gets the collection of tables contained in the System.Data.DataSet.
                    //Accessing the datatable from the dataset using the datatable name
                    foreach (DataRow row in ds.Tables["student"].Rows)
                    {
                        //Accessing the data using string Key Name
                        Console.WriteLine(row["Name"] + ",  " + row["Email"] + ",  " + row["Mobile"]);
                        //Accessing the data using integer index position
                        //Console.WriteLine(row[0] + ",  " + row[1] + ",  " + row[2]);
                    }

                    //Accessing the datatable from the dataset using the datatable index position
                    //foreach (DataRow row in ds.Tables[0].Rows)
                    //{
                    //    Console.WriteLine(row["Name"] + ",  " + row["Email"] + ",  " + row["Mobile"]);
                    //}
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }

            Console.ReadKey();
        }
    }
}

Output:

Code Explanation:

Here, we create an instance of the SqlDataAdapter class using the constructor, which takes two parameters, i.e., the SqlCommandText and the Connection object. Then, we create an instance of DataSet and Datatable object. DataSet and DataTable are in-memory data stores that can store tables like a database. We will discuss DataTable and DataSet in our upcoming article.

Then, we call the Fill() method of the DataAdapter class. This method does most of the work behind us. It opens the connection to the database, executes the SQL command, fills the dataset and data tables with the data, and closes the connection. This method automatically handles the Opening and Closing of the database connections. The connection is kept open only as long as it is needed. That means once the Fill method completes its execution, then the connection closes automatically. Finally, we are using DataRow to loop through each record and print the data on the console. 

Once the dataset or data table is filled, then no active connection is required to read the data.

C# SqlDataAdapter using SQL Server Stored Procedure:

In our upcoming articles, we will discuss how to call Stored Procedure in ADO.NET. In this article, I will show you a simple example to make you understand how to call stored procedures using SqlDataAdapter in C#.

Creating Stored Procedure:

First, create the following stored procedure in the StudentDB database.

CREATE PROCEDURE spGetStudents
AS
BEGIN
 SELECT Id, Name, Email, Mobile 
 FROM Student
END

How to call a stored procedure using ADO.NET SqlDataAdapter in C#?

In order to execute a stored procedure using SqlDataAdapter in C#, we need to specify the name of the stored procedure instead of the in-line SQL statement. Then we have to specify the command type as StoredProcedure using the command type property of the SqlDataAdapter object as shown in the below image.

The complete code is given below:

using System;
using System.Data;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = "data source=.; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    
                    SqlDataAdapter da = new SqlDataAdapter("spGetStudents", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    foreach (DataRow row in dt.Rows)
                    {
                        Console.WriteLine(row["Name"] +",  " + row["Email"] + ",  " + row["Mobile"]);
                    }     
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }

            Console.ReadKey();
        }
    }
}

Output:

When to use ADO.NET SqlDataAdapter Class in C#?

SqlDataAdapter from ADO.NET is particularly useful in scenarios where you need to work with disconnected data models, perform batch updates, and synchronize changes between your application and a database. Here are some situations where you might consider using SqlDataAdapter:

• Disconnected Data Operations: If your application needs to work with data offline (i.e., without a continuous connection to the database), SqlDataAdapter is a good choice. It allows you to fill a DataSet or DataTable with data, manipulate it locally, and then apply changes back to the database.
• Batch Updates: SqlDataAdapter supports batch updates, where you can accumulate changes made to multiple rows in memory and then commit them to the database in a single batch. This can improve efficiency and reduce the number of round-trips to the database.
• Data Binding: If you’re building data-bound user interfaces, SqlDataAdapter simplifies the process. You can bind controls directly to the DataTable within a DataSet, making it easier to display and manipulate data.
• Caching and Offline Access: You can use SqlDataAdapter to populate a DataSet with data, store it in memory, and allow users to work with the data even when offline or disconnected from the database.
• Complex Data Manipulation: When your application requires more than just simple data retrieval, and you need to perform inserts, updates and deletes on the data, SqlDataAdapter provides an efficient way to manage these changes.
• Concurrency Control: SqlDataAdapter can handle concurrency issues by using optimistic concurrency. It checks if the data being updated in the database matches the data in the DataSet before applying changes, preventing unintended overwrites.
• Data Transformation: You can use SqlDataAdapter along with other ADO.NET components to perform data transformation tasks, like changing data types, merging data from different sources, and more.
• Performance: In scenarios where you want to minimize the number of database round-trips while working with data, SqlDataAdapter allows you to fetch a bulk of data at once and work with it locally before applying changes back to the database.

Keep in mind that while SqlDataAdapter offers these advantages, it also has some limitations:

1. It might not be as memory-efficient as using a SqlDataReader for large data sets, as it loads all the data into memory.
2. It’s more suitable for scenarios where you have a relatively small number of rows compared to cases where you’re dealing with very large datasets.

Overall, suppose your application requires working with data in a disconnected manner, performing batch updates, supporting data binding, and managing complex data operations. In that case, SqlDataAdapter can provide a robust and efficient solution.

Summary of ADO.NET SqlDataAdapter Class in C#:

SqlDataAdapter is another key component of the ADO.NET framework provided by Microsoft. Unlike SqlDataReader, which is primarily used for reading data from a database, SqlDataAdapter retrieves and updates data between a dataset and a SQL Server database. It acts as a bridge between your application’s dataset and the actual database.

Here’s how SqlDataAdapter works:

• Connection: You establish a connection to your SQL Server database using SqlConnection.
• Command: You create one or more instances of SqlCommand that represent different SQL queries (e.g., SELECT, INSERT, UPDATE, DELETE) to be executed on the database.
• DataAdapter Configuration: You create and associate a SqlDataAdapter instance with the SqlCommand objects. This essentially tells the adapter which queries to use for different database operations.
• Dataset: You create an instance of DataSet or DataTable (which can hold multiple tables) in your application to store the retrieved data.
• Data Retrieval: You use the Fill() method of the SqlDataAdapter to execute the SELECT query and populate the DataSet or DataTable with the retrieved data.
• Data Manipulation: You can use the same SqlDataAdapter to update, insert, or delete data in the database. You modify the data in your DataSet or DataTable and then call the Update() method of the SqlDataAdapter to apply these changes to the database.

SqlDataAdapter is especially useful when working with disconnected data models, modifying the data locally, and then synchronizing those changes with the database. It simplifies the data retrieval and manipulation process, making it a preferred choice for scenarios involving more than just reading data.

In the next article, I am going to discuss ADO.NET DataTable in detail. In this article, I try to explain the ADO.NET SqlDataAdapter Class in C# with Examples. I hope this ADO.NET SqlDataAdapter Class in C# article will help you with your needs. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET SqlDataAdapter Class with Examples article.