ADO.NET Tutorial

ADO.NET Introduction

ADO.NET (ActiveX Data Objects) is a data access technology used in the .NET Framework to interact with databases. It provides a set of classes for working with data from different sources such as SQL Server, Oracle, and other relational databases.

ADO.NET uses Data Providers, Connection objects, Command objects, DataReader, and DataAdapters to manage the flow of data between applications and databases.

ADO.NET supports both disconnected and connected data models. The disconnected model uses datasets that can be manipulated offline, while the connected model interacts with the database in real-time through an active connection.

ADO.NET Data Providers

A Data Provider is a set of classes for interacting with a specific type of data source. A data provider has some key components such as:

Connection: Establishes a connection with the data source.
Command: Executes SQL commands or stored procedures.
DataReader: Retrieves data from the database in a forward-only, read-only manner.
DataAdapter: Acts as a data bridge between the application and the database, especially for disconnected operations.

The primary ADO.NET Data Providers are:

SQL Server Data Provider: Used to interact with Microsoft SQL Server databases.
Oracle Data Provider: Used to interact with Oracle databases.
OLE DB Data Provider: Used for data sources that support the OLE DB interface.
ODBC Data Provider: Used to access databases via ODBC drivers.

Example: SQL Server Data Provider

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM Employees";
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataReader reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                Console.WriteLine(reader["EmployeeName"]);
            }
        }
    }
}

In this example, SqlConnection establishes a connection with the SQL Server, and the SqlCommand object is used to execute an SQL query. SqlDataReader retrieves data from the database in a forward-only manner.

ADO.NET Data Adapter

DataAdapter is an object that is used in ADO.NET to fill DataSet and update the data source. It acts as a bridge between the data source and the application, through which data can be retrieved and modified when the application is working in disconnected mode.

Example: Using DataAdapter

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM Employees";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Employees");

            foreach (DataRow row in dataSet.Tables["Employees"].Rows)
            {
                Console.WriteLine(row["EmployeeName"]);
            }
        }
    }
}

In this example, the SqlDataAdapter fills a DataSet with data from the Employees table. The data can be modified in the DataSet, and changes can later be pushed back to the database using the Update method of the DataAdapter.

ADO.NET SQL Server Connection

ADO.NET provides classes for interacting with SQL Server databases through the SQL Server Data Provider. The SqlConnection class is used to open a connection to a SQL Server database. Once the connection is established, you can interact with the database by executing SQL queries or stored procedures.

The major classes in aggregate server connections are:

SqlConnection: Establishes a connection with the aggregate server database.
SqlCommand: SQL queries ya stored procedures ko execute karta hai.
SqlDataReader: Retrieves data from the database in a forward-only manner.
SqlDataAdapter: It acts as a bridge between the database and the application allowing data to be retrieved and updated in a disconnected mode.

Example: SQL Server Connection

The following example shows how to establish a connection to a SQL Server database using SqlConnection, execute a simple SQL query, and retrieve the results using a SqlDataReader.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Define the connection string
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword";
        
        // Establish connection to SQL Server
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the connection
            connection.Open();
            
            // Define the SQL query
            string query = "SELECT EmployeeName, JobTitle FROM Employees";
            
            // Create a command object with the query and connection
            SqlCommand command = new SqlCommand(query, connection);
            
            // Execute the query and retrieve data using SqlDataReader
            SqlDataReader reader = command.ExecuteReader();
            
            // Loop through the results and display
            while (reader.Read())
            {
                Console.WriteLine($"{reader["EmployeeName"]}, {reader["JobTitle"]}");
            }
        }
    }
}

In this example, the connection string includes the Server, Database, User ID, and Password required to connect to the SQL Server instance. The connection is opened using a SqlConnection object, the query is executed using a SqlCommand, and the results are read using a SqlDataReader.

Error Handling in SQL Server Connection

Handling exceptions is very important when working with ADO.NET and SQL Server connections. You can use try-catch blocks to catch exceptions such as connection issues, incorrect SQL queries, or database unavailability.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        try
        {
            string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Employees";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                
                while (reader.Read())
                {
                    Console.WriteLine(reader["EmployeeName"]);
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine("An error occurred: " + ex.Message);
        }
    }
}

In this example, if there is a connection error or a problem with the SQL query, a SqlException will be caught and an error message will be displayed. Using error handling when working with databases is a good practice as it can prevent application crashes and provide appropriate feedback to the user.

Best Practices for ADO.NET SQL Server Connection

When working with ADO.NET SQL Server connections, here are a few best practices to follow:

Use using Statements: Always use using to open connections so that resources are automatically released when the connection is no longer needed.
Handle Exceptions: Use try-catch blocks to gracefully handle connection issues or gross errors.
Optimize Connection Pooling: Connection pooling allows separate requests to use a shared set of database connections, which improves performance. Ensure that pooling is enabled in your connection string.
Close Connections: When the connection is not needed, it should be closed. The using block does this automatically.

ADO.NET Command

The SqlCommand class is used to execute SQL queries or stored procedures on a SQL Server database. This class can be used for SELECT, INSERT, UPDATE, and DELETE operations. Executing a SQL command requires an open connection.

The SqlCommand requires a SQL query string and a connection object to initialize it. Once the command is set up, you can use these methods to execute it:
ExecuteReader – For retrieving data (forward-only, read-only).
ExecuteNonQuery – For operations like Insert, Update, Delete.
ExecuteScalar – For retrieving single value (like COUNT, MAX, etc.).

SqlCommand Example

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Define the connection string
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        // Create a new connection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the connection
            connection.Open();
            
            // Create a SQL query to execute
            string query = "INSERT INTO Employees (EmployeeName, JobTitle) VALUES (@name, @job)";
            
            // Create a new SqlCommand object with the query and connection
            SqlCommand command = new SqlCommand(query, connection);
            
            // Add parameters to avoid SQL injection
            command.Parameters.AddWithValue("@name", "John Doe");
            command.Parameters.AddWithValue("@job", "Software Developer");
            
            // Execute the query
            int result = command.ExecuteNonQuery(); // Executes INSERT, UPDATE, DELETE queries
            
            if (result > 0)
            {
                Console.WriteLine("Data inserted successfully!");
            }
        }
    }
}

In this example, we create a SqlCommand to insert data into the Employees table. Parameters are used to protect against SQL injection attacks. Here the ExecuteNonQuery method is used because it does not return a result set but returns the number of affected rows.

ADO.NET DataReader

The SqlDataReader class is used to retrieve a forward-only data stream from a SQL Server database. It is very efficient for reading large datasets as it fetches the data directly from the database without loading the entire data into memory.

The ExecuteReader method of the SqlCommand returns a SqlDataReader object, which you can access by iterating row by row via the Read() method.

SqlDataReader Example

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Define the connection string
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        // Create a new connection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the connection
            connection.Open();
            
            // Create a SQL query to execute
            string query = "SELECT EmployeeName, JobTitle FROM Employees";
            
            // Create a new SqlCommand object with the query and connection
            SqlCommand command = new SqlCommand(query, connection);
            
            // Execute the query and get the SqlDataReader
            SqlDataReader reader = command.ExecuteReader();
            
            // Loop through the results
            while (reader.Read())
            {
                Console.WriteLine($"{reader["EmployeeName"]}, {reader["JobTitle"]}");
            }
        }
    }
}

In this example, SqlCommand is used to execute a SELECT query that retrieves employee names and job titles from the Employees table. The data returned by this query is read in a forward-only manner using a SqlDataReader object.

The Read() method takes the reader to the next row each time, and you can access the data in that row by column name or column index. This loop continues until all the rows are read.

ADO.NET Command Types

SqlCommand allows you to execute different types of SQL commands:

ExecuteNonQuery: For commands that do not return data, such as INSERT, UPDATE, or DELETE.
ExecuteScalar: For commands that return a single value, such as COUNT, MAX, or SUM.
ExecuteReader: For commands that return a result set, such as SELECT queries. This returns a SqlDataReader object.

ADO.NET Command Parameters

SqlCommand supports parameters that allow you to securely pass data in SQL queries. This protects against security threats such as SQL injection. You can add parameters via the AddWithValue method or other parameter methods provided by SqlCommand.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            string query = "SELECT EmployeeName FROM Employees WHERE JobTitle = @jobTitle";
            SqlCommand command = new SqlCommand(query, connection);
            
            // Add parameter to the command to prevent SQL injection
            command.Parameters.AddWithValue("@jobTitle", "Software Developer");
            
            SqlDataReader reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                Console.WriteLine(reader["EmployeeName"]);
            }
        }
    }
}

In this example, the @jobTitle parameter is added to the SQL query so that user input can be safely sent to the query. This approach is important because it prevents user input from being directly inserted into the query, which avoids security issues such as SQL injection.

ADO.NET DataSet

The DataSet class in ADO.NET is an in-memory data cache that is used to work with disconnected data. Through this, you can store one or more data tables and the relationships between them in memory.

A DataSet is not directly connected to the database. It just keeps a copy of the data from the database in memory. This means you can modify the data locally without immediately affecting the database. There is no effect on the original data until the changes are explicitly written back to the database.
This approach is very useful for scenarios where data processing is required in an offline or disconnected environment.

DataSet Example

  using System;
  using System.Data;
  using System.Data.SqlClient;

  class Program
  {
      static void Main()
      {
          // Define the connection string
          string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

          // Create a new SqlConnection
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
              // Create a DataSet object
              DataSet dataSet = new DataSet();

              // Open the connection
              connection.Open();
              
              // Create a SQL query
              string query = "SELECT EmployeeName, JobTitle FROM Employees";

              // Create a SqlDataAdapter to fill the DataSet
              SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

              // Fill the DataSet with data from the Employees table
              adapter.Fill(dataSet, "Employees");

              // Access the table in the DataSet
              DataTable employeeTable = dataSet.Tables["Employees"];
              
              // Loop through the rows in the table and print data
              foreach (DataRow row in employeeTable.Rows)
              {
                  Console.WriteLine($"{row["EmployeeName"]}, {row["JobTitle"]}");
              }
          }
      }
  }

In this example, a DataSet is created to store data from the Employees table. A SqlDataAdapter is used to retrieve data from the database and fill the DataSet. Then, the data is accessed from the DataTable inside the DataSet and printed.

DataSets allow multiple related tables to be stored in memory, making it easier to manage and process data in offline mode.

ADO.NET DataAdapter

SqlDataAdapter acts as a bridge that exchanges data between DataSet and the database. Its main job is to retrieve data from the database and fill the DataSet with that data. Also, it can be used to update the changes made in the DataSet in the database.

The DataAdapter provides some important methods:
Fill: Retrieves data from the database and loads it into the DataSet.
Update: Used to write changes made to the DataSet back into the database.
This is a powerful and flexible way to work with data in a disconnected architecture.

DataAdapter Example

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Define the connection string
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

        // Create a SqlConnection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create a DataSet object
            DataSet dataSet = new DataSet();

            // Create a SqlDataAdapter to retrieve data
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT EmployeeName, JobTitle FROM Employees", connection);

            // Use DataAdapter to fill the DataSet with data from the Employees table
            adapter.Fill(dataSet, "Employees");

            // Display original data
            Console.WriteLine("Original Data:");
            foreach (DataRow row in dataSet.Tables["Employees"].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}, {row["JobTitle"]}");
            }

            // Modify data in the DataSet (e.g., update job title)
            dataSet.Tables["Employees"].Rows[0]["JobTitle"] = "Senior Software Developer";

            // Create an update command for the SqlDataAdapter
            SqlCommand updateCommand = new SqlCommand("UPDATE Employees SET JobTitle = @jobTitle WHERE EmployeeName = @employeeName", connection);
            updateCommand.Parameters.Add("@jobTitle", SqlDbType.VarChar, 50, "JobTitle");
            updateCommand.Parameters.Add("@employeeName", SqlDbType.VarChar, 50, "EmployeeName");

            // Assign the update command to the DataAdapter
            adapter.UpdateCommand = updateCommand;

            // Open the connection and update the database with changes
            connection.Open();
            adapter.Update(dataSet, "Employees");

            Console.WriteLine("\nData after update:");
            foreach (DataRow row in dataSet.Tables["Employees"].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}, {row["JobTitle"]}");
            }
        }
    }
}

In this example, we first load the data from the Employees table into a DataSet using the SqlDataAdapter. Then, we modify the DataSet — for example, change the JobTitle of the first employee.

After making changes to the DataSet, an UpdateCommand is created which is associated with the SqlDataAdapter. Then these local changes are sent back to the database using the Update method. This is an efficient way to make changes to your local data, and update it when needed by synchronizing it with the database – without having to be constantly connected to the database.

ADO.NET DataSet Methods

The DataSet class provides several useful methods for manipulating and working with data:

Fill(DataSet, string): Fills a DataSet with data from a data source (e.g., database table).
Tables.Add(): Adds a DataTable to the DataSet.
Tables.Remove(): Removes a DataTable from the DataSet.
AcceptChanges(): Commits all changes made to the DataSet.
RejectChanges(): Rolls back all changes made to the DataSet since the last call to AcceptChanges.

The DataSet class is a very powerful tool in ADO.NET that is used for complex data manipulation and offline data management. You can add, remove, or modify tables and rows in it. When you need to write changes to the database, you can send these modifications back to the database using a DataAdapter.

ADO.NET DataTables

A DataTable is a fundamental object in ADO.NET that represents a single table of in-memory data. You can add, delete, or update rows in a DataTable. It also supports constraints (such as primary key), relationships, and indexing.

A DataTable, although part of a DataSet, can also be used independently. It represents data from a single table and is a good way to temporarily store and manipulate tabular data before committing changes to the database.

DataTable Example

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Create a DataTable object
        DataTable dataTable = new DataTable("Employees");

        // Define columns
        dataTable.Columns.Add("EmployeeID", typeof(int));
        dataTable.Columns.Add("EmployeeName", typeof(string));
        dataTable.Columns.Add("JobTitle", typeof(string));

        // Add rows to the DataTable
        dataTable.Rows.Add(1, "John Doe", "Software Developer");
        dataTable.Rows.Add(2, "Jane Smith", "Project Manager");

        // Loop through the rows and display data
        foreach (DataRow row in dataTable.Rows)
        {
            Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["EmployeeName"]}, Title: {row["JobTitle"]}");
        }
    }
}

In this example, we create a DataTable that represents employee data. First the columns are defined, then rows are added with employee details. After that, the data of all the rows is displayed through a foreach loop.

You can also easily manipulate the rows in a DataTable — adding new rows, updating existing rows, or deleting unwanted rows. This is very useful when working with offline data.

ADO.NET Web Forms Example

Web Forms (WF) is an event-driven model of ASP.NET used to build web applications. In it, you can design pages using controls such as GridView, TextBox, and Button without manually writing HTML.

Below is an example in which a connection is made to a database using ADO.NET, data is retrieved, and that data is bound to a GridView control — all in the context of a Web Forms application.

Web Forms Example with ADO.NET

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;

public partial class EmployeeList : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Define the connection string to the database
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        // Create a new SqlConnection object
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create a DataAdapter to retrieve data
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT EmployeeID, EmployeeName, JobTitle FROM Employees", connection);
            
            // Create a DataSet to store the data
            DataSet dataSet = new DataSet();
            
            // Fill the DataSet using the DataAdapter
            adapter.Fill(dataSet, "Employees");

            // Bind the DataSet to a GridView
            EmployeeGridView.DataSource = dataSet.Tables["Employees"];
            EmployeeGridView.DataBind();
        }
    }
}

In this Web Forms example, we retrieve data from the Employees table of the database using the SqlDataAdapter. This data is loaded into a DataSet, and then that DataSet is bound to the GridView control of the Web Form.

This approach allows you to work with data in a disconnected mode from the database, and presents the data on a web page in an easy-to-view format. In this example, GridView has been used to display employee details in tabular form.

The code written inside the Page_Load method is executed on the server side when the page is loaded. The entire process of retrieving data, filling it, and binding it to the GridView happens on the server, and after that the generated HTML is sent to the client (browser).

Web Form Markup (ASPX)


<asp:GridView ID="EmployeeGridView" runat="server" AutoGenerateColumns="True" Width="500px">
    <Columns>
        <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" SortExpression="EmployeeID" />
        <asp:BoundField DataField="EmployeeName" HeaderText="Employee Name" SortExpression="EmployeeName" />
        <asp:BoundField DataField="JobTitle" HeaderText="Job Title" SortExpression="JobTitle" />
    </Columns>
</asp:GridView>

In the markup above, we define a GridView control that will display EmployeeID, EmployeeName, and JobTitle columns. The AutoGenerateColumns property is set to true, so the GridView generates columns based on the columns in your data.

ADO.NET MVC Example

ADO.NET is often used in MVC architecture, especially to handle database operations in web applications. In ASP.NET MVC applications, data can be retrieved and manipulated from the database using ADO.NET.

An example is given below in which an MVC controller fetches data from a database using ADO.NET and passes it to a view, where the data is displayed in a tabular format.

MVC Controller with ADO.NET Example

  using System;
  using System.Data;
  using System.Data.SqlClient;
  using System.Web.Mvc;

  public class EmployeeController : Controller
  {
      private string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

      public ActionResult Index()
      {
          DataTable employees = GetEmployees();
          return View(employees);
      }

      private DataTable GetEmployees()
      {
          DataTable dataTable = new DataTable();
          
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
              string query = "SELECT EmployeeID, EmployeeName, JobTitle FROM Employees";
              SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
              adapter.Fill(dataTable);
          }

          return dataTable;
      }
  }

In this example, the EmployeeController retrieves employee data from a SQL Server database using ADO.NET. The GetEmployees() method uses a SqlDataAdapter that pulls the data from the database and fills it into a DataTable.
The data from this DataTable is then passed to a view, where it can be displayed to the user as an HTML table or some other format.
This approach maintains a clear separation between data access and presentation in the MVC architecture — where the Model handles the data, the Controller manages the logic, and the View presents the data.

MVC View (Razor Markup)


@model System.Data.DataTable

<h2>Employee List</h2>

<table border="1" cellpadding="5" cellspacing="0">
    <thead>
        <tr>
            <th>Employee ID</th>
            <th>Employee Name</th>
            <th>Job Title</th>
        </tr>
    </thead>
    <tbody>
        @foreach (System.Data.DataRow row in Model.Rows)
        {
            <tr>
                <td>@row["EmployeeID"]</td>
                <td>@row["EmployeeName"]</td>
                <td>@row["JobTitle"]</td>
            </tr>
        }
    </tbody>
</table>

In this example, Razor binds the DataTable returned from the view controller as an HTML table. Each row is iterated over using a foreach loop, and the data for each row is rendered into the cells of the table.

ADO vs ADO.NET

ADO (ActiveX Data Objects) and ADO.NET are both used to interact with databases, but they are part of different frameworks and have some important differences.

ADO (ActiveX Data Objects)

ADO was introduced along with ActiveX controls, and was primarily used in Classic ASP and early .NET applications. Through ADO, you can access relational data and other data sources. It provides methods for executing queries, retrieving records, and manipulating data.
ADO runs on a connected architecture, which requires you to maintain a constant connection to the database as long as you are working with the data.

Works primarily with COM objects.
Not designed for disconnected data operations.
Supports active record and cursor-based data manipulation.
Primarily used in older web applications like ASP Classic.

ADO.NET (ActiveX Data Objects .NET)

ADO.NET is an advanced version of ADO (ActiveX Data Objects), specifically designed for the .NET Framework.

It provides developers with a more powerful and flexible way to interact with databases, particularly through disconnected architecture — where you can work with data without a continuous database connection.

It is often used in ASP.NET and WinForms applications where scalability, performance, and offline data manipulation are important.

Key Benefits of ADO.NET:

Disconnected data access (DataSet, DataTable)
Strongly typed classes for data
Better performance through connection pooling
Support for XML integration
Improved error handling and transaction support

ADO.NET is more suitable for modern .NET applications than traditional ADO.

Works with managed code in the .NET Framework.
Supports disconnected data operations (via DataSet and DataTable).
Designed for use with XML-based data in addition to relational databases.
Allows for efficient database operations, such as batch processing and complex queries.
Supports DataSet, DataTable, DataAdapter, and other ADO.NET objects for better database handling and manipulation.

Key Differences

Feature	ADO	ADO.NET
Data Access	Active (direct connection to database)	Disconnected (uses DataSet, DataTable)
XML Support	No	Yes, native support for XML data
Data Handling	Rowset-based	DataSet-based (supports multiple tables)
Performance	Slower for large datasets	Optimized for better performance in web and enterprise apps
Usage	Used in older web technologies like Classic ASP	Used in modern .NET applications like ASP.NET, WinForms, etc.