Ado.net

Learning ADO.NET: Key Components for Building Applications

If you’re venturing into building applications based on MS Office, understanding ADO.NET is essential. While you might have explored resources like the MSDN Library, the information can be overwhelming due to its vastness and complexity. In this post, we’ll break down the foundational elements of ADO.NET to help streamline your learning journey.

What is ADO.NET?

ADO.NET is a set of classes that expose data access services for .NET programming. It allows you to interact with databases, perform operations such as querying, inserting, updating, and deleting data, and efficiently manage connections and commands.

Key Components of ADO.NET

To effectively learn ADO.NET, focus on these three fundamental components, particularly if you’re using SQL Server:

  1. SQLConnection: This class establishes a connection to a specific database using a connection string.
  2. SqlCommand: This class is used to execute SQL queries and commands against the database.
  3. SqlDataReader: This class provides a way to read a forward-only stream of rows from a database.

Adapting to Other Databases

If you’re working with a database other than SQL Server (like MySQL or Oracle), you can replace the Sql prefix with the corresponding class name, such as:

  • MySqlConnection
  • OracleCommand

The rest of the structure will remain largely the same, allowing you to adapt your knowledge to different database systems.

Examples of ADO.NET in action

Let’s take a look at a couple of practical examples to solidify your understanding:

Example 1: Reading Data from a Database

In this example, we’ll select online users from the database.

using (SqlConnection connection = new SqlConnection("CONNECTION STRING"))
using (SqlCommand command = new SqlCommand())
{
  command.CommandText = "SELECT Name FROM Users WHERE Status = @OnlineStatus";
  command.Connection = connection;
  command.Parameters.Add("@OnlineStatus", SqlDbType.Int).Value = 1; // replace with enum
  connection.Open();

  using (SqlDataReader dr = command.ExecuteReader())
  {
      List<string> onlineUsers = new List<string>();

      while (dr.Read())
      {
         onlineUsers.Add(dr.GetString(0));
      }
  }
}

Explanation of Example 1

  • We establish a connection using SqlConnection and pass in a connection string.
  • We define the SQL command and set the necessary parameter for the query.
  • After opening the connection, we execute the command using SqlDataReader to fetch the data.

Example 2: Deleting Data from a Database

Here’s how you can delete a user based on their email address:

using (SqlConnection connection = new SqlConnection("CONNECTION STRING"))
using (SqlCommand command = new SqlCommand())
{
  command.CommandText = "DELETE FROM Users where Email = @Email";
  command.Connection = connection;
  command.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = "user@host.com";
  connection.Open();
  command.ExecuteNonQuery();
}

Explanation of Example 2

  • Similar to the first example, we connect to the database and prepare a SQL delete command.
  • We set the email parameter to the specific user’s email and execute the command using ExecuteNonQuery, which is suited for commands that do not return data.

Conclusion

By focusing on these three components of ADO.NET—SQLConnection, SqlCommand, and SqlDataReader—you can form a solid foundation for working with databases in your applications. As you grow more comfortable with these concepts, you can explore more advanced functionality and build robust applications in MS Office and beyond.

Taking the time to practice these examples and experimenting with variations will enhance your skills and confidence in working with ADO.NET.