ADO.NET SqlDataAdapter

Back to: ADO.NET Tutorial For Beginners and Professionals

ADO.NET SqlDataAdapter in C# with Examples

In this article, I am going to discuss the ADO.NET SqlDataAdapter in C# with Examples. Please read our previous article where we discussed ADO.NET SqlDataReader 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 to create an instance of SqlDataAdapter class in ADO.NET?
4. SqlDataAdapter using Stored Procedure.
5. How to call a stored procedure using SqlDataAdapter?

What is ADO.NET SqlDataAdapter in C#?

The ADO.NET SqlDataAdapter in C# works as a bridge between 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 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. Here, 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. Here, 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. Here, 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): It is used to 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 that we want to execute and the connection on which we want to execute the command are like the way 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 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. Both DataSet and DataTable are in-memory data stores, that can store tables, just 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 handles the Opening and Closing of the database connections automatically for us. 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:

We will discuss how to call Stored Procedure in ADO.NET in detail in our upcoming articles. Here, in this article, I am going to 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 just need to specify the name of the stored procedure instead of the in-line SQL statement and 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:

In our upcoming articles, we will discuss how to call stored procedures with input and output parameters.

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