SqlDataAdapter Class in ADO.Net

DataSet Class in ADO.Net

• It Stores Tables & Relationships
• It is Data Provider independent
• It is not in the System.Data.SqlClient
• It is present in the System.Data
• It is Common To All Data Providers

SqlDataAdapter Class in ADO.Net

• The SqlDataAdapter class is found in the System.Data.SqlClient namespace.
• It is a very important class in the .NET framework.
• It works as a bridge between DataSet and Database.
• It opens the database connection, executes the SQL statements and closes the connection at last. There is no need to open and close the connection.
• 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.
• Both DataSet and DataTable are in-memory data stores, that can store tables, just like a database.

Fill Method Of SqlDataAdapter

• 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.

SqlDataAdapter Signature

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.

•We can call stored procedure with input parameters.

SOURCE CODE

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace SqlDataAdapterDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter Id Of Any Employee: ");
            int id = Convert.ToInt32(Console.ReadLine());

            string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            SqlConnection con = new SqlConnection(cs);
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand("spGetEmployees", con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.Parameters.AddWithValue("@id", id);
            DataSet ds = new DataSet();
            sda.Fill(ds);

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine("{0} {1} {2} {3} {4} {5}", row[0], row[1], row[2], row[3], row[4], row[5]);
            }

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

            //DataTable dt = new DataTable();
            //sda.Fill(dt);

            //foreach (DataRow row in dt.Rows)
            //{
            //    Console.WriteLine("{0} {1} {2} {3} {4} {5}", row[0], row[1], row[2], row[3], row[4], row[5]);
            //}

            Console.ReadLine();

        }
    }
}