ASP.NET ADO(ActiveX Data Objects for .NET) Introduction:

ADO.NET (ActiveX Data Objects for .NET) is a set of classes in the .NET Framework that provides access to data sources such as databases and XML files. It allows you to perform various data operations, such as querying, inserting, updating, and deleting data, in your ASP.NET applications.

Here's a basic introduction to ADO.NET with an example:

1. Connecting to a Database: Before you can interact with a database using ADO.NET, you need to establish a connection to the database. You typically use a connection string to specify the database server, database name, authentication credentials, etc.

Example

using System.Data.SqlClient; // Namespace for SQL Server

string connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;";
SqlConnection connection = new SqlConnection(connectionString);

2. Executing Queries:

Once you have a connection, you can create and execute SQL commands to interact with the database. ADO.NET provides various classes such as SqlCommand and SqlDataReader for executing SQL queries and reading the results.

Example

// Example: Selecting data from a SQL Server database
string query = "SELECT * FROM Employees";
SqlCommand command = new SqlCommand(query, connection);

connection.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
// Access data using reader
int employeeId = reader.GetInt32(0);
string name = reader.GetString(1);
// Retrieve other columns similarly
}

reader.Close();
connection.Close();

3. Working with DataSets and DataAdapters:

ADO.NET also provides higher-level abstractions such as DataSet and DataAdapter for working with disconnected data. You can fill a DataSet with data from the database using a DataAdapter, and then manipulate the data locally.

Example

// Example: Filling a DataSet with data from a SQL Server database
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Employees");

// Access data in the DataSet
DataTable dataTable = dataSet.Tables["Employees"];
foreach (DataRow row in dataTable.Rows)
{
int employeeId = (int)row["EmployeeID"];
string name = (string)row["Name"];
// Retrieve other columns similarly
}

4. Executing Commands (Insert, Update, Delete):

You can execute non-query commands such as insert, update, and delete using the ExecuteNonQuery method of SqlCommand.

Example

// Example: Inserting data into a SQL Server database
string insertQuery = "INSERT INTO Employees (Name, Age) VALUES (@Name, @Age)";
SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.Parameters.AddWithValue("@Name", "John Doe");
insertCommand.Parameters.AddWithValue("@Age", 30);

connection.Open();
int rowsAffected = insertCommand.ExecuteNonQuery();
connection.Close();

5. Handling Exceptions:

It's important to handle exceptions when working with databases to gracefully manage errors. You can use try-catch blocks to catch and handle exceptions that may occur during database operations.

Example

try
{
// Database operation code
}
catch (Exception ex)
{
// Handle exception
}

This is a basic overview of how you can use ADO.NET in ASP.NET applications to interact with databases. Keep in mind that ADO.NET supports various data providers (e.g., SQL Server, Oracle, MySQL) and you may need to use different classes and namespaces depending on the database you're working with.