ADO.NET SqlDataAdapter

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 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 SqlDataAdapter in C# works as a bridge between a DataSet 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 can be used to fill the DataSet and update the data source.

Signature of SqlDataAdapter in C#

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

What is ADO.NET SqlDataAdapter

Constructors of ADO.NET SqlDataAdapter class in C#:

The SqlDataAdapter class provides the following constructors.

Constructors of SqlDataAdapter class:

  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 a 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 a 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 instance of C# SqlDataAdapter class in ADO.NET?

In order to create an instance of the SqlDataAdapter class, 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. Following is the syntax to create an instance of the SqlDataAdapter class.

How to create instance of SqlDataAdapter class in ADO.NET?

Example to understand the SqlDataAdapter in C#:

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

Example to understand the SqlDataAdapter:

Please use below SQL script to create a database called StudentDB, a table called Student with the required test 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: Using ADO.NET SqlDataAdapter in C#

We need to fetch all the data from the student table and need to display in the console using using SqlDataAdapter. Let us first write the code and then we will understand the code. Following is 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=.; 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);
                    Console.WriteLine("Using Data Table");
                    foreach (DataRow row in dt.Rows)
                    {
                        Console.WriteLine(row["Name"] +",  " + row["Email"] + ",  " + row["Mobile"]);
                    }

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

                    //Using DataSet
                    DataSet ds = new DataSet();
                    da.Fill(ds, "student");                   
                    Console.WriteLine("Using Data Set");
                    foreach (DataRow row in ds.Tables["student"].Rows)
                    {
                        Console.WriteLine(row["Name"] + ",  " + row["Email"] + ",  " + row["Mobile"]);
                    }                     
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }

            Console.ReadKey();
        }
    }
}

Output:

Example Using ADO.NET SqlDataAdapter

Code Explanation:

Here we creates an instance of SqlDataAdapter class using the constructor which takes two parameters i.e. the SqlCommandText and the Connection object.

Then we creates an instance of DataSet and Datatable object. Both DataSet and DataTable are in-memory data store, that can store tables, just like a database. We will discuss DataTable and DataSet in our next article.

Then we call the Fill() method of the DataAdapter class. This method does most of the work behind for us. It opens the connection to the database, executes the SQL command, fills the dataset and datatable 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.

Finally, we are using DataRow to loop though each record and print the data on the console. We will also discuss DataRow in detail in our upcoming articles.

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 procedure using SqlDataAdapter.

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 SqlDataAdapter in C#?

In order to execute stored procedure using SqlDataAdapter, then you just need to specify the name of the procedure instead of the in-line SQL statement and then you have to specify the command type as StoredProcedure using the command type property of the SqlDataAdapter object as shown in the below image.

How to call a stored procedure using SqlDataAdapter

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:

SqlDataAdapter using Stored Procedure:

In our upcoming articles, we will discuss how to call stored procedure 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 helps you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

1 thought on “ADO.NET SqlDataAdapter”

Leave a Reply

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