ADO.
NET Basics
[Link] (Active Data Objects for .NET) is a set of classes in the .NET Framework that
provide access to relational databases and other data sources. It is used to interact with
databases (e.g., SQL Server, Oracle, MySQL) and retrieve, update, and manipulate data
within an application. [Link] allows for connecting to databases, executing commands,
and retrieving results in a disconnected and connected manner.
1. [Link] Architecture
[Link] works with a Connected and Disconnected model:
Connected Model: The application maintains a connection to the database during
data retrieval and manipulation.
Disconnected Model: The data is retrieved from the database and then the connection
is closed. The application can work with the data offline, and the changes can be
pushed back to the database later.
2. Core [Link] Objects
Here are the primary objects in [Link] that allow interaction with data sources:
Connection: This object is used to establish a connection to the database. It provides
the interface to interact with the data source.
o Example: SqlConnection (for SQL Server), OleDbConnection (for OLE DB
providers).
Command: The command object is used to execute SQL queries or stored procedures
against the database.
o Example: SqlCommand, OleDbCommand.
DataReader: This is a forward-only, read-only cursor for retrieving data from the
database. It is used in the connected model.
o Example: SqlDataReader.
DataAdapter: A data adapter serves as a bridge between the DataSet (disconnected
data) and the data source. It is used to fill a DataSet or DataTable with data and
update the data source with any changes made.
o Example: SqlDataAdapter.
DataSet: A DataSet is an in-memory representation of data, allowing for working
with multiple tables and relationships. It is used in the disconnected model.
o Example: DataSet and DataTable objects.
DataTable: A DataTable represents a single table of data in memory. It's used in
combination with a DataSet to hold data from a database.
o Example: DataTable.
3. Establishing a Database Connection
To interact with a database, you must first establish a connection. Here's an example using
SQL Server:
using [Link];
SqlConnection conn = new SqlConnection("YourConnectionStringHere");
[Link]();
The connection string typically includes the server name, database name, and authentication
details (username and password).
4. Executing Queries with [Link]
a. Using Command and DataReader (Connected Model)
To execute queries and retrieve data using a SqlDataReader, follow these steps:
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);
SqlDataReader reader = [Link]();
while ([Link]())
{
[Link](reader["CustomerName"].ToString());
}
[Link]();
ExecuteReader() executes the SQL query and returns a SqlDataReader object that
can be used to read rows of data.
b. Using Command and DataAdapter (Disconnected Model)
In the disconnected model, you use SqlDataAdapter to fill a DataSet or DataTable.
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers",
conn);
DataTable dt = new DataTable();
[Link](dt);
Once the data is in the DataTable, you can manipulate it offline. To update the database, you
would call Update() on the DataAdapter.
5. Using DataSet and DataTable
A DataSet can contain multiple DataTable objects, and you can manipulate data in a
disconnected manner.
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers",
conn);
[Link](ds, "Customers");
// Access data from the DataTable
foreach (DataRow row in [Link]["Customers"].Rows)
{
[Link](row["CustomerName"].ToString());
}
You can modify the data in the DataTable and use SqlDataAdapter to update the
database.
6. Handling Transactions
[Link] allows for database transactions to ensure data consistency. You can use the
SqlTransaction object to begin, commit, or roll back a transaction.
SqlTransaction transaction = [Link]();
SqlCommand cmd = [Link]();
[Link] = transaction;
try
{
[Link] = "INSERT INTO Customers (CustomerName) VALUES ('John
Doe')";
[Link]();
[Link]();
}
catch (Exception)
{
[Link]();
}
7. Error Handling
It is essential to handle exceptions when working with [Link] to prevent the application
from crashing and to manage any errors effectively.
try
{
[Link]();
}
catch (SqlException ex)
{
[Link]("Error: " + [Link]);
}
finally
{
[Link]();
}
8. Closing Connections
Always close the database connection when you're done:
[Link]();
Alternatively, you can use a using statement to automatically close the connection when it is
no longer needed:
csharp
Copy code
using (SqlConnection conn = new SqlConnection("YourConnectionStringHere"))
{
[Link]();
// Execute commands
}
Summary of Key [Link] Classes
SqlConnection: Represents a connection to a SQL Server database.
SqlCommand: Used to execute SQL commands.
SqlDataReader: Retrieves data in a forward-only, read-only manner.
SqlDataAdapter: Bridges the DataSet/DataTable and the database.
DataSet: In-memory data container.
DataTable: Represents a single table of data.
Conclusion
[Link] provides the necessary tools to interact with databases using both connected and
disconnected models. By understanding the core objects such as Connection, Command,
DataReader, DataAdapter, and DataSet, you can effectively manage database operations in
your .NET applications.