Add parameterized queries in MySql (OleDb)

To connect to MySQL database you can use the System.Data.Odbc namespace in .net

using (OdbcConnection con = new OdbcConnection(connectionString))
{
  using (OdbcCommand cmd = new OdbcCommand())
  {
    cmd.Connection = con;
    cmd.CommandText = "SELECT * FROM Employees";
    using (OdbcDataReader drd = cmd.ExecuteReader())
    {
      while (drd.Read())
      {
        // Read from data reader
      }
    }
  }
}

Adding parameters the queries in System.Data.Odbc works different than System.Data.SqlClient. While using a SqlCommand we add parameters in form of @parameter name. Parameter names are directly mapped to their values.

using (SqlConnection con = new SqlConnection(connectionString))
{
  using (SqlCommand cmd = new SqlCommand())
  {
    cmd.Connection = con;
    cmd.CommandText 
      = "SELECT * FROM Employees WHERE EmployeeId = @EmployeeId ";
    cmd.Parameters.AddWithValue("@EmployeeId", employeeId);
    using (SqlDataReader drd = cmd.ExecuteReader())
    {
      while (drd.Read())
      {
        // Read from data reader
      }
    }
  }
}

But this is not the case for an OdbcCommand. The parameters in an OdbcCommand are specified by “?” and the sequence of adding parameters is really important unlike to SqlCommand.

using (OdbcConnection con = new OdbcConnection(connectionString))
{
  using (OdbcCommand cmd = new OdbcCommand())
  {
    cmd.Connection = con;
    cmd.CommandText = 
      "SELECT * FROM Employees WHERE FirstName = ? AND LastName = ?";
    // Keep in mind the sequence in which you are adding the 
    // parameters should be same as the sequence they appear 
    // in the query
    cmd.Parameters.AddWithValue("@FirstName", firstName);
    cmd.Parameters.AddWithValue("@LastName", lastName);
    using (OdbcDataReader drd = cmd.ExecuteReader())
    {
      while (drd.Read())
      {
        // Read from data reader
      }
    }
  }
}

Discussion