An Introduction to ADO.NET

 ADO.NET is a data access framework used in .NET applications to interact with databases and data sources. It provides a rich set of classes that handle tasks like connecting to a database, executing commands, retrieving data, and handling errors. In this post, we'll take a quick introductory look at some of the key components of ADO.NET.

Connecting to a Database

The first step when working with ADO.NET is establishing a connection to the database you want to interact with. The main class for this is SqlConnection. You instantiate a connection object, specify the database connection string, and open the connection:

csharp
Copy code
string connString = "Data Source=(localdb)\\MSSQLLocalDB;" + 
                    "Initial Catalog=TestDB;" +
                    "Integrated Security=True";

using (SqlConnection conn = new SqlConnection(connString)) 
{
  conn.Open();

  // Use the open connection here...

  conn.Close();
}

The connection string contains information like the database server location, database name, and security credentials.

Executing SQL Statements

Once connected, you can execute SQL statements against the database. The SqlCommand class represents a SQL statement. You specify the SQL command text and connection when constructing a SqlCommand, then call ExecuteNonQuery() or ExecuteReader() to run it:

csharp
Copy code
string sql = "INSERT INTO Customers (Name, Address) VALUES (@name, @address)";

using (SqlCommand cmd = new SqlCommand(sql, conn))
{
  cmd.Parameters.AddWithValue("@name", "John Smith");
  cmd.Parameters.AddWithValue("@address", "1 Main St"); 
  
  cmd.ExecuteNonQuery();
}

Parameters help protect against SQL injection attacks by avoiding concatenating raw user input into SQL strings.

Reading Data

To retrieve data from a database, you use the SqlDataReader class. It lets you stream rows from a SQL query result set:

csharp
Copy code
string sql = "SELECT * FROM Customers";

using (SqlCommand cmd = new SqlCommand(sql, conn))
{
  SqlDataReader reader = cmd.ExecuteReader();
    
  while (reader.Read())
  {
    Console.WriteLine(reader["Name"] + " - " + reader["Address"]); 
  }
}

You can access columns in the current data row either by index or by the column name.

The SqlDataReader provides forward-only, read-only access to query results. For updating data, use the SqlDataAdapter and DataSet classes instead.

Handling Errors

When working with databases, errors can occur like connectivity issues or SQL syntax errors. ADO.NET provides several ways to handle them:

• Check the ConnectionState property to verify the connection status
• Use TRY/CATCH blocks to catch SqlException errors
• Check the return value of methods like ExecuteNonQuery() for success/failure
• Handle events like SqlConnection.Error to get error details

Proper error handling ensures your application responds gracefully in the event of any database issues.

Summary

ADO.NET gives .NET developers a powerful framework for accessing relational data stores. Some key points:

• Use SqlConnection to connect to database
• Execute SQL statements with SqlCommand
• Read query results using SqlDataReader
• Parameterize SQL statements to prevent injection
• Handle errors using TRY/CATCH and checking return values