1. Introduction
(ActiveX Data Objects) is a set of class libraries designed for data access in .NET environments. It supports a variety of data sources, such as SQL Server, Oracle, MySQL, etc., providing developers with a unified data access interface, greatly simplifying the process of data access.
(I) Core Components
Connection: Responsible for establishing a connection to a data source, such as SqlConnection used to connect to a SQL Server database. Different data sources correspond to different Connection implementation classes.
Command: Executable SQL commands or stored procedures, such as SqlCommand, is used to execute commands for SQL Server databases. Through it, developers can implement data query, insert, update and delete operations.
DataReader: Efficiently read data from a data source in a read-only and in-only manner. For example, SqlDataReader reads data from a SQL Server database. It is suitable for scenarios where large amounts of data need to be read quickly and no complex operations on the data are required.
DataAdapter: acts as a bridge between the data source and the DataSet for filling and updating data. For example, SqlDataAdapter can obtain data from the SQL Server database and populate it into the DataSet, and can also update the data in the DataSet back to the database.
DataSet: It is an in-memory data cache, similar to a small database that can store multiple DataTables and their relationships. DataSet allows developers to operate on data while disconnecting from data sources, improving application flexibility and performance.
2. Steps to access data using
(I) Connect to the database
First, you need to establish a connection to the database. The following is a sample code for connecting to the SQL Server database:
string connectionString = "server=your_server;database=your_database;user id=your_user;password=your_password;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
();
("The database connection is successful!");
// Conduct subsequent data operations here }
catch (SqlException ex)
{
("Database connection error: " + );
}
}
In the above code, the connectionString contains the server address, database name, user name, password and other information required to connect to the database. Using the using statement ensures that the SqlConnection object correctly releases resources after use.
(II) Execute SQL commands
Query data: Use the SqlCommand class to execute SQL query statements and read data through SqlDataReader.
string query = "select * from Employees";
using (SqlCommand command = new SqlCommand(query, connection))
{
SqlDataReader reader = ();
while (())
{
(reader["EmployeeName"].ToString());
}
();
}
In this code, query is a SQL query statement, and the SqlCommand object executes the query based on the statement and the established connection. The ExecuteReader method returns a SqlDataReader object, and the query results can be read line by line through a while loop.
Insert data: Inserting data also uses the SqlCommand object, but requires the ExecuteNonQuery method, which returns the number of affected rows.
string insertQuery = "insert into Employees (EmployeeName, Department) values ('John Doe', 'HR')";
using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection))
{
int rowsAffected = ();
(rowsAffected + "Line data has been inserted.");
}
Update data: The operation of updating data is similar to inserting data, you only need to modify the SQL statement.
string updateQuery = "update Employees set Department = 'IT' where EmployeeName = 'John Doe'";
using (SqlCommand updateCommand = new SqlCommand(updateQuery, connection))
{
int rowsAffected = ();
(rowsAffected + "Line data has been updated.");
}
Delete data: Deletion of data is also implemented through SqlCommand and ExecuteNonQuery methods.
string deleteQuery = "delete from Employees where EmployeeName = 'John Doe'";
using (SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection))
{
int rowsAffected = ();
(rowsAffected + "Line data has been deleted.");
}
(III) Use DataAdapter and DataSet
DataAdapter and DataSet provide the ability to access offline data, and the following is an example:
string selectQuery = "select * from Employees";
using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection))
{
DataSet dataset = new DataSet();
(dataset, "Employees");
DataTable table = ["Employees"];
foreach (DataRow row in )
{
(row["EmployeeName"].ToString());
}
In this example, SqlDataAdapter retrieves data from the database according to the query statement and populates it into the DataSet. The data in the DataSet can be operated offline. After the operation is completed, the changes will be updated back to the database through DataAdapter.
3. Exception handling
Exception handling is critical when performing database operations to ensure the stability of the application. The try-catch-finally structure is usually used to handle exceptions.
try
{
// Database operation code string connectionString = "server=your_server;database=your_database;user id=your_user;password=your_password;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
();
string query = "select * from Employees";
using (SqlCommand command = new SqlCommand(query, connection))
{
SqlDataReader reader = ();
while (())
{
(reader["EmployeeName"].ToString());
}
();
}
}
}
catch (SqlException ex)
{
("Database Error: " + );
}
finally
{
// Here you can add code to release resources, such as closing the connection, etc.}
Place the database operation code in the try block, the catch block catches and handles possible SqlException exceptions, and finally blocks are used to execute code that needs to be executed regardless of whether an exception occurs, such as closing a database connection.
Through the above, you have a comprehensive understanding of C# access data. In actual development, you can flexibly apply this knowledge according to specific needs to build efficient and stable data access functions.
This is the end of this article about C#’s detailed explanation of how to access data. For more related C# to access data, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!