VB.NET DataAdapter.Fill

The DataAdapter in ADO.NET plays a crucial role as a connector between a DataSet, which represents an in-memory cache of data, and a data source, such as a database. Its primary purpose is to facilitate the retrieval and saving of data between the two.

Fill and Update

To achieve this, the DataAdapter employs two key methods: Fill and Update. The Fill method populates the DataSet with rows retrieved from the data source. It uses a SELECT statement specified by the SelectCommand property associated with the DataAdapter. This allows the DataAdapter to retrieve data from the data source and synchronize it with the DataSet, ensuring that the in-memory representation matches the actual data in the source.


However, the Update method performs the opposite operation. It takes the changes made within the DataSet and propagates them back to the data source, modifying the corresponding data. This way, any updates, insertions, or deletions made within the DataSet are reflected in the underlying data source.

DataTable within the DataSet

When populating a DataTable within the DataSet, the DataAdapter ensures that each column name is unique. In cases where duplicate column names are encountered, the DataAdapter generates names for subsequent columns using a pattern such as "columnname1," "columnname2," "columnname3," and so on. This prevents conflicts and ensures that all columns within the DataTable have distinct names. From the following program you can understand how to use DataAdapter.Fill method in VB.NET applications.

Full Source VB.NET
Imports System.IO Imports System.Data.SqlClient Public Class Form1 Dim cnn As SqlConnection Dim connectionString As String Dim sqlAdp As SqlDataAdapter Dim ds As New DataSet Dim dt As New DataSet Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim i As Integer connectionString = "Data Source=servername; Initial Catalog=databasename; User ID=userid; Password=password" cnn = New SqlConnection(connectionString) cnn.Open() sqlAdp = New SqlDataAdapter("select * from users", cnn) cnn.Close() 'connection close here , that is disconnected from data source sqlAdp.Fill(ds) sqlAdp.Fill(dt) 'fetching data from dataset in disconnected mode For i = 0 To ds.Tables(0).Rows.Count - 1 MsgBox(ds.Tables(0).Rows(i).Item(0)) Next 'fetching data from datatable in disconnected mode For i = 0 To dt.Tables(0).Rows.Count - 1 MsgBox(dt.Tables(0).Rows(i).Item(0)) Next End Sub End Class