Ado.net DataAdapter C# Example

This Ado.net SqlDataAdapter Tutorial in C# for Beginners will guide you to learn Ado.net DataAdapter with some real-time examples. Please feel free to ask question, I will keep updating this tutorial with answer of your query

Ado.net Data Adapter works as a bridge between a DataSet and data source, using DataAdapter we can fill dataset, datatable etc. DataAdapter is basically a class that represents a set of SQL commands with a database connection property, the fill method is used for updating data source

How to use Ado.Net DataAdapter

There are different types of DataAdapter for different datasource in Ado.net, SqlDataAdapter, OleDbDataAdapter, OracleDataAdapter etc. but all DataAdapter class are inherited from same base class DbDataAdapter, IDbDataAdapter So the implementation remain same for all DataAdapter class.

let’s look at the following DataAdapter classes

SqlDataAdapter in Ado.Net

When we want to work with SQL Client we use SqlDataAdapter class, below is the definition of SqlDataAdapter class inherited from DbDataAdapter

using System.Data.SqlClient;
public sealed class SqlDataAdapter : DbDataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
OleDbDataAdapter in Ado.Net

Similarly we have OleDbDataAdapter, which is also inherited from same base class like SqlDataAdapter, so from implementation point of view there won’t be much difference between both classes, if you want to work with Oledb client then use OleDbDataAdapter, below is the OleDbDataAdapter class definition

using System.Data.OleDb;
public sealed class  OleDbDataAdapter : DbDataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
DataAdapter Methods

  • Fill(DataSet / DataTable) : It fill data from data source to dataset or datatable object
  • Update(DataSet / DataTable / DataRow) : this method will update data in dataset or datatable object, also can update data in datasource

DataAdapter Fill Method, Fill DataSet Example

Here are few lines of code with required namespace, we fill a dataset and a datatable using SqlDataAdapter in below example

Fill DataSet using DataAdapter
using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
    SqlDataAdapter sda = new SqlDataAdapter("Select * from tbOrder", con);
    DataSet ds = new DataSet();
    sda.Fill(ds);
}

Here is the complete example of how to fill dataset using SQL Data Adapter, and read data from dataset object.

using System.Data;
using System.Data.SqlClient;

string _conString = "user id=msa;password=mypass;Data Source='myserver\\SQLEXPRESS';Initial Catalog=MLData;Integrated Security=true;";

SqlConnection objCon = new SqlConnection(_conString);

SqlDataAdapter objDa = new SqlDataAdapter("Select StuId, Firstname, Lastname, Email, ContactNumber from [dbo].[tbStudent]", objCon);
            
DataSet objDs = new DataSet();
objDa.Fill(objDs);
DataTable objDt = objDs.Tables[0];
foreach (DataRow dr in objDt.Rows)
{
    Console.WriteLine($"{dr["Firstname"]} : {dr["Email"]}");
}
Console.WriteLine("reading complete");
objDs = null;

We also can use stored procedure with SqlDataAdapter, in below example you can see how to pass parameter in StoredProcedure using SqlCommand object, finally set SqlDataAdapter Select Command.

I have a simple stored procedure, which has one mandatory parameter StuId, we want to fetch the data where student id match.

Create PROCEDURE [dbo].[GetStudent]	
	@StuId int	
AS
BEGIN
	SET NOCOUNT ON;  
	SELECT * from tbStudent where StuId= @StuId
END

This is actually considered as good practice, instead of writing SQL query in code you can call the StoredProcedure, so next time you want to fetch another column, you don’t need to compile the code again, just make the changes in StoredProcedure, and code will remain clean.

using System.Data;
using System.Data.SqlClient;

string _conString = "user id=msa;password=mypass;Data Source='myserver\\SQLEXPRESS';Initial Catalog=MLData;Integrated Security=true;";

SqlConnection objCon = new SqlConnection(_conString);

SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand("GetStudent", objCon);
cmd.Parameters.Add(new SqlParameter("@StuId", 3));
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter objDa = new SqlDataAdapter();
objDa.SelectCommand = cmd;
          
DataSet objDs = new DataSet();
objDa.Fill(objDs);
DataTable objDt = objDs.Tables[0];
                       
Console.WriteLine("dataload complete.");

foreach (DataRow dr in objDt.Rows)
{
    Console.WriteLine($"{dr["Firstname"]} : {dr["Email"]}");
}
Console.WriteLine("reading complete");

Another advantage of using StoredProcedure instead of select statement is, that we can get multiple result set with just one call.

Suppose, we have two select statements in stored procedure, we can have even more.

Create PROCEDURE [dbo].[GetStudent]	
	@StuId int	
AS
BEGIN
	SET NOCOUNT ON;  
	SELECT * from tbStudent where StuId= @StuId
	SELECT * from tbStudent
END

The same above dataset loaded using SqlDataAdapter can get you multiple result sets like example below.

DataSet objDs = new DataSet();
objDa.Fill(objDs);
DataTable objDt1 = objDs.Tables[0];
DataTable objDt2 = objDs.Tables[1];
DataAdapter Fill Method, Fill DataTable Example in C#

Instead of dataset we also can fill DataTable directly from DataAdapter

using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
    SqlDataAdapter sda = new SqlDataAdapter("Select * from tbOrder", con);
    DataTable dt = new DataTable();
    sda.Fill(dt);
}
SqlDataAdapter UpdateCommand Example

Here is an example of how to use update command in SqlDataAdapter.

using System.Data;
using System.Data.SqlClient;

string _conString = "user id=msa;password=mypass;Data Source='myserver\\SQLEXPRESS';Initial Catalog=MLData;Integrated Security=true;";
SqlConnection objCon = new SqlConnection(_conString);

SqlDataAdapter objDa = new SqlDataAdapter("Select StuId, Firstname, Lastname, Email, ContactNumber from [dbo].[tbStudent]", objCon);
objDa.UpdateCommand= new SqlCommand("Update tbStudent set Email=@Email where @StuId= StuId", objCon);
objDa.UpdateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50, "Email");
SqlParameter parameter = objDa.UpdateCommand.Parameters.Add("@StuId", SqlDbType.Int);
parameter.SourceColumn = "StuId";
parameter.SourceVersion = DataRowVersion.Original;

DataTable objDt = new DataTable();
objDa.Fill(objDt);
            
DataRow _row = objDt.Rows[0];
_row["Email"] = "aviC@wtr.com";
          
objDa.Update(objDt);
Console.WriteLine("update complete.");

foreach (DataRow dr in objDt.Rows)
{
    Console.WriteLine($"{dr["Firstname"]} : {dr["Email"]}");
}
Console.WriteLine("reading complete");

You may be interested to read following articles:

 
Sql Data Adapter and OleDb Data Adapter DataAdapter C# Example
Learn MS-SQL Development
Ado.net Interview Questions Answers
Connect SQL database using Ado.Net component from Asp.net application, learn how to use Ado.net as data access layer.
Ado.Net C# Examples | Join Asp.Net MVC Course