OleDbCommand Guide: Execute SQL Easily

OleDbCommand is a class used to execute SQL statements in an OleDb connection. It can perform operations such as querying, inserting, updating, and deleting data.

Here are some commonly used methods and properties of the OleDbCommand.

1. Constructor:
2. OleDbCommand(): Constructor with no parameters.
3. OleDbCommand(string cmdText): Create an OleDbCommand object using the specified SQL statement.
4. Create an OleDbCommand object using the specified SQL statement and OleDbConnection object.
5. Characteristics:
6. Command Text: Get or set the SQL statement to be executed.
7. Command Type: Gets or sets the type of command to be executed (Text, TableDirect, StoredProcedure, etc.).
8. Connection: retrieve or set the OleDbConnection object associated with the OleDbCommand.
9. Parameters: Retrieve the collection of parameters associated with the OleDbCommand.
10. Method:
11. ExecuteNonQuery() method: Executes the SQL statement and returns the number of affected rows, suitable for insert, update, and delete operations.
12. ExecuteReader(): Executes a SQL statement and returns an OleDbDataReader object that can be used to read the results of the query.
13. ExecuteScalar(): Execute a SQL statement and return the value of the first column of the first row in the result set.
14. Prepare(): Get ready to execute the SQL statement or stored procedure.

Here is an example code using OleDbCommand to execute a query:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\mydb.accdb";
string query = "SELECT * FROM Customers WHERE Country = ?";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();
    using (OleDbCommand command = new OleDbCommand(query, connection))
    {
        command.Parameters.AddWithValue("Country", "USA");
        using (OleDbDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine("CustomerID: {0}, CompanyName: {1}, ContactName: {2}",
                    reader["CustomerID"], reader["CompanyName"], reader["ContactName"]);
            }
        }
    }
}

The above example demonstrates the use of the OleDbCommand constructor and ExecuteReader() method to execute a query, as well as utilizing parameterized queries to prevent SQL injection attacks.