ADO.NET Tutorial

ADO.NET Introduction

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

ADO.NET uses Data Providers, Connection objects, Command objects, DataReader, and DataAdapter 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 involves real-time interactions with a database through active connections.

ADO.NET Data Providers

A Data Provider is a set of classes used to interact with a specific type of data source. A data provider contains components like:

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

The primary ADO.NET Data Providers are:

• SQL Server Data Provider: Used for interacting with Microsoft SQL Server databases.
• Oracle Data Provider: Used for Oracle database interactions.
• OLE DB Data Provider: Used for data sources that support the OLE DB interface.
• ODBC Data Provider: Used for accessing 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 = "your_connection_string";
        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, the SqlConnection establishes a connection to the SQL Server, and the SqlCommand object is used to execute a SQL query. The SqlDataReader retrieves data from the database in a forward-only manner.

ADO.NET Data Adapter

The DataAdapter is an object used in ADO.NET for filling a DataSet and updating the data source. It acts as a bridge between the data source and the application by allowing data to be retrieved and modified while working in a disconnected mode.

Example: Using DataAdapter

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

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";
        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 to interact with SQL Server databases using the SQL Server Data Provider. The SqlConnection class in ADO.NET is used to open a connection to a SQL Server database. Once the connection is established, you can execute SQL queries or stored procedures to interact with the database.

The primary classes involved in SQL Server connections are:

• SqlConnection: Establishes the connection to the SQL Server database.
• SqlCommand: Executes SQL queries or stored procedures.
• SqlDataReader: Retrieves data in a forward-only manner from the database.
• SqlDataAdapter: Bridges the database and application to retrieve and update data in a disconnected manner.

Example: SQL Server Connection

The following example demonstrates how to establish a connection to a SQL Server database using SqlConnection, execute a simple SQL query, and retrieve the results using 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 contains the Server, Database, User ID, and Password required to connect to the SQL Server instance. The connection is opened using the SqlConnection object, and the query is executed with SqlCommand. The results are read using SqlDataReader.

Error Handling in SQL Server Connection

It's essential to handle exceptions when working with ADO.NET and SQL Server connections. You can use try-catch blocks to catch exceptions such as connection issues, invalid 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 a connection error or SQL query issue occurs, the SqlException will be caught, and an error message will be displayed. It's always good practice to include error handling when working with databases to avoid crashes and provide meaningful 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 the `using` statement for opening connections to ensure that resources are released properly once the connection is no longer needed.
• Handle Exceptions: Implement error handling with try-catch blocks to gracefully handle connection issues or SQL execution errors.
• Optimize Connection Pooling: Connection pooling allows multiple requests to share a set of database connections, reducing overhead. Ensure pooling is enabled in your connection string.
• Close Connections: Ensure that connections are closed when no longer in use to free up resources. The `using` statement automatically takes care of closing the connection.

ADO.NET Command

The SqlCommand class in ADO.NET is used to execute SQL queries or stored procedures on a SQL Server database. It can be used for SELECT, INSERT, UPDATE, and DELETE operations. It requires an open connection to the database to execute SQL commands.

The SqlCommand can be initialized with a SQL query string and a connection object. After setting up the command, you can execute it using methods like ExecuteReader, ExecuteNonQuery, or ExecuteScalar.

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. The parameters are added to prevent SQL injection attacks. The ExecuteNonQuery method is used here because it doesn't return any result set, but rather the number of rows affected.

ADO.NET DataReader

The SqlDataReader class is used to retrieve a forward-only stream of data from a SQL Server database. It is highly efficient for reading large datasets, as it fetches data directly from the database without buffering all of it in memory.

The ExecuteReader method of SqlCommand returns a SqlDataReader object, which you can iterate through using the Read() method to access data row by row.

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 SqlDataReader object is used to read the data returned by the query in a forward-only manner.

The Read() method moves the reader to the next row, and you can access the data in that row using column names or column indices. The loop continues until all rows are read.

ADO.NET Command Types

SqlCommand allows you to execute different types of SQL commands:

• ExecuteNonQuery: Used for executing commands that do not return any data, such as INSERT, UPDATE, or DELETE.
• ExecuteScalar: Used for executing commands that return a single value, like COUNT, MAX, or SUM.
• ExecuteReader: Used to execute commands that return a result set (rows of data), such as SELECT queries. Returns a SqlDataReader object.

ADO.NET Command Parameters

SqlCommand supports parameters to securely pass data to SQL queries. This is important to prevent SQL injection attacks. You can add parameters using 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 to safely pass the value to the query. This prevents direct insertion of user input into the query, which could otherwise lead to SQL injection vulnerabilities.

ADO.NET DataSet

The DataSet class in ADO.NET is an in-memory cache of data that can be used to work with disconnected data. It allows you to store multiple tables of data and their relationships in memory.

A DataSet is not connected directly to a database. It holds copies of data from the database, which means it can be modified locally without affecting the database until changes are explicitly written back. This is useful when working with offline or disconnected data.

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. The data is then accessed and printed out from the DataTable inside the DataSet.

The DataSet allows for complex data structures with multiple related tables, making it easier to manage and work with data in an offline manner.

ADO.NET DataAdapter

The SqlDataAdapter serves as a bridge between the DataSet and the database. It is responsible for retrieving data from the database and filling the DataSet with that data. Additionally, it can be used to update the database with any changes made to the DataSet.

The DataAdapter provides methods such as Fill to load data into a DataSet, and Update to propagate changes from the DataSet back to the database.

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 use the SqlDataAdapter to load data from the Employees table into a DataSet. We then modify the DataSet by changing the JobTitle of the first employee.

After modifying the DataSet, we create an UpdateCommand and associate it with the SqlDataAdapter. The Update method is used to send the changes back to the database. This is an efficient way to manage changes in data and synchronize the local changes with 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 very powerful, allowing for complex data manipulation and offline data management. You can add, remove, and modify tables and rows, and then use the DataAdapter to push the changes back to the database.

ADO.NET DataTables

A DataTable is a fundamental object in ADO.NET that represents a single table of in-memory data. You can manipulate a DataTable by adding, deleting, or updating rows of data, and it supports constraints, relationships, and indexing.

DataTable is part of the DataSet class, but it can be used independently. It represents one table of data and is an excellent way to store and manipulate tabular data before committing changes back 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 to represent employee data. We define the columns and add rows with employee details. The foreach loop then iterates over the rows to display the data.

You can also manipulate rows in a DataTable, such as adding new rows, updating existing ones, or deleting rows, which are very useful when dealing with offline data.

ADO.NET Web Forms Example

Web Forms (WF) is an event-driven model for building web applications in ASP.NET. It provides a way to design pages using controls such as GridView, TextBox, and Button without worrying about the underlying HTML structure.

Below is an example of how to use ADO.NET to connect to a database, retrieve data, and bind it to a GridView in 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 use SqlDataAdapter to retrieve data from the Employees table in the database. The data is loaded into a DataSet, and then the DataSet is bound to a GridView control on the Web Form.

This approach allows you to work with data disconnected from the database and present it in an easy-to-use format on a web page. In this case, GridView is used to display employee details in a tabular form.

The code inside the Page_Load method will be executed when the page is loaded. The data retrieval, filling, and binding process happen on the server side, which sends the generated HTML to the client.

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 above markup, we define a GridView control that will display the EmployeeID, EmployeeName, and JobTitle columns. The AutoGenerateColumns property is set to true, allowing the grid to automatically generate columns based on the data.

ADO.NET MVC Example

ADO.NET is commonly used in an MVC architecture for handling database operations in web applications. In an ASP.NET MVC application, ADO.NET can be used to interact with the database by retrieving and manipulating data.

Below is an example of an ADO.NET implementation in an MVC controller. This controller retrieves data from the database and passes it to a view to display the data in a table.

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 to fill a DataTable with data from the database. The data is then passed to the view.

MVC View (Razor Markup)

  @model System.Data.DataTable

  
Employee List
  
          @foreach (DataRow row in Model.Rows)
          {
              
          }
      

      

          

              
Employee ID
              
Employee Name
              
Job Title
          
      
      

                  
@row["EmployeeID"]
                  
@row["EmployeeName"]
                  
@row["JobTitle"]
              
  

The Razor view binds the DataTable passed from the controller to an HTML table. The foreach loop iterates over each row in the DataTable and renders the data in the table cells.

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 key differences.

ADO (ActiveX Data Objects)

ADO was introduced with ActiveX controls and was primarily used with Classic ASP and early .NET Framework applications. ADO allows access to relational data and other data sources, providing methods to execute queries, retrieve records, and manipulate 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 evolution of ADO designed for the .NET Framework. It provides a more powerful and flexible way to interact with databases in a disconnected fashion, allowing developers to build more scalable and efficient applications. It is commonly used in ASP.NET and WinForms applications.

• 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