What is DataAdapter

In ADO.NET, the DataAdapter acts as a bridge between a database and your application's in-memory dataset. It retrieves data using SQL commands and fills a DataSet, allowing you to work with the data disconnected from the database. The DataAdapter also lets you push changes made to the DataSet back to the database, keeping your data synchronized.

Purpose:
  1. Retrieving Data: The DataAdapter can execute SQL statements (typically SELECT queries) against the database to fetch data and populate a DataSet or DataTable object within your application.
  2. Updating Data: It enables you to push changes made to the data within the DataSet back to the database, effectively synchronizing the in-memory representation with the actual database content.
dataadapter

Core Functionalities

Connection Management

The DataAdapter relies on a Connection object to establish a connection with the target database. This connection is used to communicate with the database server and execute SQL commands.

Command Objects

It utilizes Command objects to define the specific SQL statements for data retrieval and manipulation. These commands can be SELECT queries for fetching data, or INSERT, UPDATE, and DELETE statements for modifying database content.

Data Transfer

The DataAdapter acts as the intermediary, transferring data retrieved from the database using the SelectCommand property to the DataSet or DataTable using its Fill or Update methods. It can also handle sending updates made to the DataSet tables back to the database tables using its update commands.

DataAdapter.Fill Method

The Fill method is a workhorse in ADO.NET's DataAdapter class. It's responsible for fetching data from a database and populating a DataSet or DataTable object within your VB.NET application. Here's a breakdown of its functionalities:

Functionality:

Retrieving Data

The Fill method leverages the SelectCommand property associated with the DataAdapter. This property typically holds a SELECT SQL statement that defines the data to be retrieved from the database.

Connection Management

If the connection to the database is not already open, the Fill method automatically opens the connection using the Connection property of the DataAdapter. Once data retrieval is complete, it closes the connection to release resources.

Data Transfer and Schema Handling

The Fill method reads the data returned by the SelectCommand and populates the specified DataSet or DataTable. Here's how it handles the schema (structure) of the data:

  1. If the DataSet or DataTable doesn't already exist, the Fill method creates them based on the retrieved data's schema (column names and data types).
  2. If the DataSet or DataTable already exists, the Fill method attempts to match the existing schema with the retrieved data. Columns with matching names and compatible data types are used to populate the existing table. New columns from the data are added to the existing table if the MissingSchemaAction property of the DataAdapter is set to AddWithKey (default behavior).

Populating a DataSet using DataAdapter

Steps Involved:

Establish Database Connection

  1. Create a connection string containing details like server name, database name, and credentials to connect to your database.
  2. Instantiate a Connection object using the connection string.

Define the DataAdapter

  1. Create a DataAdapter object.
  2. Set the Connection property of the DataAdapter to the connection object created in step 1.

Create the Select Command

  1. Define a SELECT SQL statement that retrieves the desired data from your database table.
  2. Create a Command object and set its CommandText property to the SELECT statement.
  3. Assign this Command object to the SelectCommand property of the DataAdapter.

Create the DataSet

  1. Instantiate a DataSet object. This will hold the data retrieved from the database.

Populate the DataSet

  1. Call the Fill method of the DataAdapter, passing the DataSet object as an argument.
  2. This method executes the SelectCommand, retrieves data from the database, and populates the tables within the DataSet with the retrieved data.
Example:
' Connection String (replace with your actual details) Dim connectionString As String = "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True" ' Establish Database Connection Dim connection As New SqlConnection(connectionString) connection.Open() ' Open the connection ' Create the DataAdapter Dim dataAdapter As New SqlDataAdapter() dataAdapter.Connection = connection ' Define the Select Command Dim selectCommand As New SqlCommand("SELECT * FROM Customers", connection) dataAdapter.SelectCommand = selectCommand ' Create the DataSet Dim customersDataSet As New DataSet() ' Populate the DataSet Dim rowsAffected As Integer = dataAdapter.Fill(customersDataSet) ' Close the connection (always recommended) connection.Close() ' Accessing Data from the DataSet If customersDataSet.Tables.Count > 0 Then Dim customersTable As DataTable = customersDataSet.Tables(0) For Each row As DataRow In customersTable.Rows Console.WriteLine("Customer ID: {0}, Name: {1}", row("CustomerID"), row("CustomerName")) Next Else Console.WriteLine("No data retrieved from the database.") End If

DataAdapter Constructors

Constructor Type
Arguments
Description
With SqlCommand Object
SqlCommand
Creates a DataAdapter using a pre-defined SqlCommand object. This SqlCommand object typically defines the SelectCommand for data retrieval.
With Connection String and Command Text
- String (connection string)
- String (command text)
Creates a DataAdapter by internally creating a new SqlCommand object using the provided connection string and command text. This SqlCommand object is then assigned to the SelectCommand property of the DataAdapter.

DataAdapter Properties

Properties Type
Description
Connection
Gets or sets the Connection object used to connect to the database.
SelectCommand
Gets or sets the SqlCommand object that defines the SELECT statement for data retrieval.
UpdateCommand (Optional)
Gets or sets the SqlCommand object that defines the UPDATE statement for modifying data in the database.
InsertCommand (Optional)
Gets or sets the SqlCommand object that defines the INSERT statement for adding new data to the database.
DeleteCommand (Optional)
Gets or sets the SqlCommand object that defines the DELETE statement for removing data from the database.
FillSchema
Gets or sets a value indicating whether the DataAdapter automatically retrieves and sets the schema (column names and data types) for the retrieved data during the Fill operation.
MissingSchemaAction
Gets or sets how the DataAdapter handles missing schema information when the DataSet or DataTable already exists. Possible values include Add (add new columns), AddWithKey (default - add new columns and set primary key), Error (throw an exception), and Ignore (skip missing columns).
TableMappings (Collection)
Gets a collection of DataTableMapping objects that define mappings between database tables and DataTable objects within the DataSet. This allows for customization of column names during data transfer.

DataAdapter Methods

Method
Arguments
Description
Fill(DataSet)
DataSet
Fills the specified DataSet object with data retrieved from the database using the SelectCommand property of the DataAdapter.
Fill(DataTable)
DataTable
Fills the specified DataTable object directly with data retrieved from the database using the SelectCommand property of the DataAdapter.
Fill(DataTable[], IDataReader, Int32, Int32)
- DataTable[] (array of DataTables)
- IDataReader
- Int32 (start index)
- Int32 (end index)
Provides more granular control for filling specific DataTable objects within an array. The IDataReader contains the retrieved data, and start/end indexes specify which rows to populate within the tables.
Update(DataSet)
DataSet
Synchronizes changes made to the tables within the DataSet with the corresponding database tables. It utilizes configured InsertCommand, UpdateCommand, and DeleteCommand properties (if set) to perform updates.
UpdateCommand
SqlCommand
Sets the UpdateCommand property of the DataAdapter. This command defines the SQL statement used to update data in the database when changes are made to the DataSet.
InsertCommand
SqlCommand
Sets the InsertCommand property of the DataAdapter. This command defines the SQL statement used to insert new data into the database when rows are added to the DataSet.
DeleteCommand
SqlCommand
Sets the DeleteCommand property of the DataAdapter. This command defines the SQL statement used to delete data from the database when rows are removed from the DataSet.

Conclusion

The ADO.NET DataAdapter serves as a vital intermediary between a dataset and a data source, facilitating data retrieval, manipulation, and synchronization in .NET applications. It enables efficient interaction with databases by executing SQL commands, populating datasets, and managing changes between in-memory data representations and database tables.