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
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
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
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
Here are few lines of code with required namespace, we fill a dataset and a datatable using SqlDataAdapter in below example
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];
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); }
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: