interview

home / developersection / interviews / what is sqldataadapter?

Ask Question
ICSM Computer

IT-Hardware & Networking

Ravi Vishwakarma is a dedicated Software Developer with a passion for crafting efficient and innovative solutions. With a keen eye for detail and years of experience, he excels in developing robust software systems that meet client needs. His expertise spans across multiple programming languages and technologies, making him a valuable asset in any software development project.

Follow
Can you answer this question?
Answer

1 Answers

ICSM Computer

ICSM Computer

12-Feb-2025

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.

What is SqlDataAdapter?

Key Features:

  1. Works disconnected from the database.
  2. Fetches data into a DataSet or DataTable.
  3. Allows batch updates without manually writing INSERT, UPDATE, or DELETE queries.
  4. 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

  1. Use DataTable or DataSet when working disconnected from the database.
  2. Always use parameterized queries to prevent SQL injection.
  3. Use SqlCommandBuilder when you don’t want to manually write UPDATE, INSERT, and DELETE statements.
  4. Prefer batch updates instead of running multiple individual SQL commands.
  5. 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.

 

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy