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
      }
    }
  }
}

6 comment(S)


as on Nov 17, 2009 10:38 PM

assas

modit on Apr 27, 2010 10:34 AM

thanks man, u saved hrs of coding for me!!!

Kholid on May 25, 2011 05:48 AM

Thanks, it works, that's why i always unable to save blob file, i always got null value,

aford on Jul 31, 2011 04:33 AM

Life-saver/time-saver... my first .net windows app...

thanks a loads

heloo on Dec 10, 2011 03:36 AM

excellent thanks

Ramesh Soni on Nov 28, 2013 07:26 AM

testing comments [Moved to ec2]

Leave a comment