What is SqlDataAdapter?

What is `SqlDataAdapter`?

The SqlDataAdapter class in ADO.NET is a bridge between a DataSet and a SQL Server database. It allows us to fetch data from a database, store it in a DataTable or DataSet, and even update the database without requiring an active connection.

Key Features:

Works disconnected from the database.
Fetches data into a DataSet or DataTable.
Allows batch updates without manually writing INSERT, UPDATE, or DELETE queries.
Supports automatic data synchronization using Update().

Namespace:

using System.Data.SqlClient;

1. Basic Usage of `SqlDataAdapter`

Fetching Data into a DataTable

string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;";
string query = "SELECT * FROM Users";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
    DataTable dataTable = new DataTable();

    adapter.Fill(dataTable); // Fetches data into the DataTable

    foreach (DataRow row in dataTable.Rows)
    {
        Console.WriteLine($"ID: {row["Id"]}, Username: {row["Username"]}");
    }
}

Fill(): Executes the query and fills the DataTable with the results.
Disconnected mode: The connection is only open during the Fill() operation.

2. Fetching Data into a `DataSet`

A DataSet can hold multiple DataTables, making it useful for handling multiple related tables.

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Users; SELECT * FROM Orders;", conn);
    DataSet dataSet = new DataSet();

    adapter.Fill(dataSet); // Fetches multiple tables

    DataTable usersTable = dataSet.Tables[0]; // First result set
    DataTable ordersTable = dataSet.Tables[1]; // Second result set

    Console.WriteLine("Users Table:");
    foreach (DataRow row in usersTable.Rows)
    {
        Console.WriteLine(row["Username"]);
    }

    Console.WriteLine("Orders Table:");
    foreach (DataRow row in ordersTable.Rows)
    {
        Console.WriteLine(row["OrderID"]);
    }
}

Multiple tables can be loaded into a DataSet at once.
dataSet.Tables[0] accesses the first result set (Users), and dataSet.Tables[1] accesses the second (Orders).

3. Updating the Database Using `SqlDataAdapter`

The SqlDataAdapter can automatically generate INSERT, UPDATE, and DELETE statements for a DataTable and apply changes to the database.

Example: Updating a DataTable and Saving Changes

using (SqlConnection conn = new SqlConnection(connectionString))
{
    string selectQuery = "SELECT Id, Username FROM Users";

    SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, conn);
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter); // Generates UPDATE/DELETE/INSERT commands

    DataTable usersTable = new DataTable();
    adapter.Fill(usersTable);

    // Modify a row
    usersTable.Rows[0]["Username"] = "NewUsername";

    // Apply the update back to the database
    adapter.Update(usersTable);
    Console.WriteLine("Database updated successfully.");
}

SqlCommandBuilder automatically generates INSERT, UPDATE, and DELETE commands.
Changes in DataTable are reflected in the database when calling adapter.Update(usersTable).

4. Using `SqlDataAdapter` with Parameterized Queries

To prevent SQL injection, use parameterized queries.

Example: Fetching Data Securely

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Users WHERE Id = @UserId", conn);
    adapter.SelectCommand.Parameters.AddWithValue("@UserId", 1);

    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);

    foreach (DataRow row in dataTable.Rows)
    {
        Console.WriteLine($"Username: {row["Username"]}");
    }
}

Prevents SQL injection
More efficient with cached execution plans

5. Insert, Update, and Delete Using `SqlDataAdapter`

We can manually define INSERT, UPDATE, and DELETE commands for better control.

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT Id, Username FROM Users", conn);

    // Define INSERT command
    adapter.InsertCommand = new SqlCommand("INSERT INTO Users (Username) VALUES (@Username)", conn);
    adapter.InsertCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50, "Username");

    // Define UPDATE command
    adapter.UpdateCommand = new SqlCommand("UPDATE Users SET Username = @Username WHERE Id = @Id", conn);
    adapter.UpdateCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50, "Username");
    adapter.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");

    // Define DELETE command
    adapter.DeleteCommand = new SqlCommand("DELETE FROM Users WHERE Id = @Id", conn);
    adapter.DeleteCommand.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");

    DataTable usersTable = new DataTable();
    adapter.Fill(usersTable);

    // Adding a new row
    DataRow newRow = usersTable.NewRow();
    newRow["Username"] = "JohnDoe";
    usersTable.Rows.Add(newRow);

    // Updating an existing row
    usersTable.Rows[0]["Username"] = "UpdatedUser";

    // Deleting a row
    usersTable.Rows[1].Delete();

    // Apply changes to the database
    adapter.Update(usersTable);
    Console.WriteLine("Database updated successfully.");
}

Manually defining commands allows more control over updates.
Ideal for bulk inserts, updates, and deletes.

6. Best Practices for Using `SqlDataAdapter`

Use DataTable or DataSet when working disconnected from the database.
Always use parameterized queries to prevent SQL injection.
Use SqlCommandBuilder when you don’t want to manually write UPDATE, INSERT, and DELETE statements.
Prefer batch updates instead of running multiple individual SQL commands.
Close connections properly to avoid memory leaks.

7. `SqlDataAdapter` vs `SqlDataReader`

Feature	`SqlDataAdapter`	`SqlDataReader`
Connection	Disconnected (fills `DataSet`/`DataTable`)	Requires an open connection
Usage	Works with `DataSet`/`DataTable` (multiple tables)	Reads data row by row (forward-only)
Best for	Bulk data retrieval & batch updates	Fast, lightweight, real-time data retrieval
Memory Usage	More memory (stores data in memory)	Less memory (reads row by row)
Performance	Slower than `SqlDataReader`	Faster since it doesn’t store data in memory

Conclusion

The SqlDataAdapter class is useful for working with data in a disconnected mode. It is ideal for fetching, modifying, and updating large datasets without maintaining a constant database connection.

interview

What is SqlDataAdapter?

Can you answer this question?

1 Answers

What is SqlDataAdapter?

1. Basic Usage of SqlDataAdapter

2. Fetching Data into a DataSet

3. Updating the Database Using SqlDataAdapter

4. Using SqlDataAdapter with Parameterized Queries

5. Insert, Update, and Delete Using SqlDataAdapter

6. Best Practices for Using SqlDataAdapter

7. SqlDataAdapter vs SqlDataReader

Conclusion

What is `SqlDataAdapter`?

1. Basic Usage of `SqlDataAdapter`

2. Fetching Data into a `DataSet`

3. Updating the Database Using `SqlDataAdapter`

4. Using `SqlDataAdapter` with Parameterized Queries

5. Insert, Update, and Delete Using `SqlDataAdapter`

6. Best Practices for Using `SqlDataAdapter`

7. `SqlDataAdapter` vs `SqlDataReader`