In this article I will explain with an example, how to use SqlDataAdapter in C# and VB.Net.
For illustration purposes, this article will use DataGridView to display the data populated from database using SqlDataAdapter in C# and VB.Net.
 
 

What is SqlDataAdapter?

The SqlDataAdapter works as a bridge between a DataSet or DataTable and SQL Server Database for retrieving and saving data.
The SqlDataAdapter uses disconnection-oriented architecture i.e. it does not require an open connection to the Data Source while retrieving the data.
It is used to fill the DataSet or DataTable and update the data source as well.
It has a Fill method, which adds or refreshes rows in the DataSet or DataTable to match those in the Data Source.
The SqlDataAdapter class has following constructors.
1. SqlDataAdapter(): Initializes a new instance of the SqlDataAdapter class.
2. SqlDataAdapter(SqlCommand): Initializes a new instance of the SqlDataAdapter class with the specified SqlCommand as the SelectCommand property.
3. SqlDataAdapter(String, SqlConnection): Initializes a new instance of the SqlDataAdapter class with a SelectCommand and a SqlConnection object.
4. SqlDataAdapter(String, String): Initializes a new instance of the SqlDataAdapter class with the SelectCommand and a connection string.
Note: For more details about SqlDataAdapter, please refer MSDN.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 

Form Design

You need to add a DataGridView control to the Windows Form.
SqlDataAdapter Tutorial with example in C# and VB.Net
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
 
 

Populate DataTable using SqlDataAdapter in C# and VB.Net

Inside the Form Load event handler, BindGrid method is called.
Inside BindGrid method, SqlDataAdapter object is initialized with the SqlCommand and using the Fill function, the DataTable is populated with the records from database.
Finally, the DataTable is used to populate the DataGridView.
C#
private void Form1_Load(object sender, EventArgs e)
{
    this.BindGrid();
}
 
private void BindGrid()
{
    string constring = @"Data Source=.\SQL2019;Initial Catalog=Northwind;User id= sa;password=pass@123";
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
            }
        }
    }
}
 
VB.Net
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.BindGrid()
End Sub
 
Private Sub BindGrid()
    Dim constring As String = "Data Source=.\SQL2019;Initial Catalog=Northwind;User id= sa;password=pass@123"
    Using con As New SqlConnection(constring)
        Using cmd As New SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers", con)
            Using sda As New SqlDataAdapter(cmd)
                Using dt As New DataTable()
                    sda.Fill(dt)
                    dataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Using
End Sub
 
 

Screenshot

SqlDataAdapter Tutorial with example in C# and VB.Net
 
 

Downloads

Download Code