Best resource for Online free Education
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.
- What is ADO.NET SqlDataAdapter?
- Understanding Constructors, Methods, and Properties of SqlDataAdapter.
- How to create an instance of SqlDataAdapter class in ADO.NET?
- SqlDataAdapter using Stored Procedure.
- 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 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 image, 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.
Constructors of ADO.NET SqlDataAdapter class in C#:
The SqlDataAdapter class provides the following constructors.
- SqlDataAdapter(): Initializes a new instance of the SqlDataAdapter class.
- 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.
- 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.
- 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.
- CloneInternals(): It is used to create a copy of this instance of DataAdapter.
- Dispose(Boolean): It is used to release the unmanaged resources used by the DataAdapter.
- Fill(DataSet): It is used to add rows in the DataSet to match those in the data source.
- FillSchema(DataSet, SchemaType, String, IDataReader): It is used to add a DataTable to the specified DataSet.
- GetFillParameters(): It is used to get the parameters set by the user when executing an SQL SELECT statement.
- ResetFillLoadOption(): It is used to reset FillLoadOption to its default state.
- ShouldSerializeAcceptChangesDuringFill(): It determines whether the
- ShouldSerializeFillLoadOption(): It determines whether the FillLoadOption property should be persisted or not.
- ShouldSerializeTableMappings(): It determines whether one or more DataTableMapping objects exist or not.
- 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, 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
GO
Example: Using ADO.NET SqlDataAdapter in C#
We need to fetch all the data from the student table and need to display it in the console using SqlDataAdapter in C#. 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=.; 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.
" + 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 in our upcoming Video.
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 in detail in our upcoming Videos. Here, in this Video, 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 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.
" + e);
}
Console.ReadKey();
}
}
}
Output:
