Introduction to ADO.NET
ADO.NET (ActiveX Data Objects) is a powerful data access technology in the .NET Framework, designed for connecting to databases, retrieving data, and performing operations like data manipulation and updates. It provides a rich set of objects that allow developers to interact with data in a disconnected manner, offering more flexibility and better performance for web and desktop applications.
Unlike traditional data access technologies, ADO.NET operates on a disconnected data architecture. This means data is retrieved from the database, stored in memory, and manipulated there before changes are optionally written back to the database. This architecture helps reduce resource consumption by minimizing the need for constant database connections.
In this blog post, we will cover the basics of ADO.NET, key components like Connection Object, Command Object, DataSet, DataReader, and DataAdapter, as well as their practical use cases in a .NET application.
5.1 Basics of ADO.NET
Core Objects of ADO.NET
ADO.NET provides several objects that facilitate data access and manipulation. These include:
- Connection Object: Establishes a connection to the database.
- Command Object: Executes SQL queries and stored procedures.
- Dataset: An in-memory collection of data tables.
- Data Table: Represents a single table of data in memory.
- DataReader Object: Provides forward-only, read-only access to data from the database.
- Data Adapter Object: Fills the DataSet with data and can update the data source.
5.1.1 Connection Object
The Connection Object in ADO.NET is responsible for establishing a connection between your application and the database.
Syntax:
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
Key Properties:
- ConnectionString: A string that contains information about the data source (server, database, credentials, etc.).
- Open(): Opens a connection to the database.
- Close(): Closes the connection once the operations are completed.
Example:
SqlConnection conn = new SqlConnection("Data Source=ServerName;Initial Catalog=DatabaseName;User ID=User;Password=Password");
conn.Open();
// Perform database operations here
conn.Close();
5.1.2 Command Object
The Command Object is used to execute SQL queries or stored procedures. It is an essential object in ADO.NET for interacting with the database.
Types of Commands:
- ExecuteNonQuery: Executes SQL commands like
INSERT,UPDATE,DELETE(does not return data). - ExecuteReader: Executes SQL queries that return rows of data (e.g.,
SELECT). - ExecuteScalar: Returns a single value from the database.
Example:
SqlCommand cmd = new SqlCommand("SELECT * FROM Students", conn);
SqlDataReader reader = cmd.ExecuteReader();
5.1.3 Dataset
The Dataset is an in-memory cache of data retrieved from the database. It is a collection of DataTable objects and can be used to manipulate and update data before pushing changes back to the database.
Example:
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Students", conn);
DataSet ds = new DataSet();
da.Fill(ds); // Fills the DataSet with data
5.1.4 Data Table
A DataTable is a collection of rows and columns that represents a single table in a Dataset.
Operations:
- Add rows: Insert new records.
- Update rows: Modify existing records.
- Delete rows: Remove records.
Example:
DataTable dt = ds.Tables[0];
DataRow newRow = dt.NewRow();
newRow["Name"] = "John Doe";
dt.Rows.Add(newRow); // Adds a new row to the DataTable
5.1.5 Data Reader Object
The DataReader Object provides efficient, forward-only, read-only access to data from the database. It is highly performant when working with large datasets because it streams data from the database one record at a time.
Example:
SqlCommand cmd = new SqlCommand("SELECT Name FROM Students", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["Name"]);
}
reader.Close(); // Always close the reader after use
5.1.6 Data Adapter Object
The Data Adapter Object serves as a bridge between the database and the Dataset. It is used to fill the Dataset with data and update changes back to the database.
Example:
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Students", conn);
DataSet ds = new DataSet();
da.Fill(ds); // Fills the DataSet with data
5.2 DataGridView & Data Binding: Insert, Update, Delete Records
In ADO.NET, Data Binding allows data to be dynamically displayed in UI controls like DataGridView. These controls reflect real-time changes to the underlying data, allowing users to interact with the data easily.
Insert, Update, Delete Operations in DataGridView
- Insert: Add new records to the
DataGridView. - Update: Modify existing records in the
DataGridView. - Delete: Remove records from the
DataGridView.
Example for Binding a DataGridView to DataTable:
dataGridView1.DataSource = ds.Tables[0]; // Binding DataGridView to DataTable
Insert Operation Example:
DataRow newRow = dt.NewRow();
newRow["Name"] = "Jane Doe";
dt.Rows.Add(newRow); // Adds a new row to the DataTable
Update Operation Example:
dt.Rows[0]["Name"] = "Updated Name"; // Modifies the first row
Delete Operation Example:
dt.Rows[0].Delete(); // Deletes the first row
5.3 Navigation Using Data Source
Navigation in ADO.NET refers to the ability to move through records of the data source. There are several ways to navigate through data:
Methods of Navigation:
- Binding Navigator: A UI control that helps navigate through records.
- DataReader: Allows row-by-row navigation to retrieve data efficiently.
Example using DataReader:
SqlCommand cmd = new SqlCommand("SELECT * FROM Students", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// Access data from each row
}
reader.Close(); // Close the reader after use
Summary
In this blog post, we explored ADO.NET, which provides a powerful, efficient way to work with data in .NET applications. We discussed essential objects like Connection, Command, DataSet, DataReader, and DataAdapter, and provided examples of how to use these objects to interact with databases.
Key Takeaways:
- ADO.NET is a robust data access framework in .NET for working with databases.
- Core objects like Connection, Command, Dataset, DataReader, and DataAdapter make data operations easy and efficient.
- DataGridView and Data Binding allow developers to display and manipulate data dynamically in user interfaces.
- Operations like Insert, Update, and Delete can be handled easily in a DataTable, and DataBinding makes reflecting those changes in the UI seamless.
Whether you are building a web or desktop application, understanding these concepts of ADO.NET will help you manage data effectively and build powerful data-driven applications.