Can I indicate where my MySQL parameter should go more meaningfully than just having a ? to mark the position - using ODBC connectors and .NET

Question

I've got a chunk of code where I can pass info into a MySQL command using parameters through an ODBC connection. Example code showing surname passed in using string surnameToLookFor:

using (OdbcConnection DbConn = new OdbcConnection( connectToDB ))
{

   OdbcDataAdapter cmd = new OdbcDataAdapter(
   "SELECT Firstname, Address, Postcode FROM customers WHERE Surname = ?", DbConn);

    OdbcParameter odbcParam = new OdbcParameter("surname", surnameToLookFor);
    cmd.SelectCommand.Parameters.Add(odbcParam);

    cmd.Fill(dsCustomers, "customers");
}

What I'd like to know is whether I can indicate where my parameter should go more meaningfully than just having a ? to mark the position - as I could see this getting quite hard to debug if there are multiple parameters being replaced.

I'd like to provide a name to the parameter in a manner something like this:

SELECT Firstname, Address, Postcode FROM customers WHERE Surname = ?surname ", 

When I try this it just chokes.

The following code

    public System.Data.DataSet Customer_Open(string sConnString, long ld) 
    { 
        using (MySqlConnection oConn = new MySqlConnection(sConnString)) 
        { 
            oConn.Open(); 

            MySqlCommand oCommand = oConn.CreateCommand(); 
            oCommand.CommandText = "select * from cust_customer where id=?id"; 

            MySqlParameter oParam = oCommand.Parameters.Add("?id", MySqlDbType.Int32); 
            oParam.Value = ld; 

            oCommand.Connection = oConn; 
            DataSet oDataSet = new DataSet(); 
            MySqlDataAdapter oAdapter = new MySqlDataAdapter(); 
            oAdapter.SelectCommand = oCommand; 
            oAdapter.Fill(oDataSet); 
            oConn.Close(); 
            return oDataSet; 
        } 
    }

is from http://www.programmingado.net/a-389/MySQL-NET-parameters-in-query.aspx and includes the fragment

where id=?id

Which would be ideal.

Is this only available through the .Net connector rather than the ODBC? If it is possible to do using ODBC how would I need to change my code fragment to enable this?

Answer 1

It would be nice if ODBC had this feature but it doesn't, I'm afraid. I'm speaking of the C API itself here - I don't know if there is a C# layer that adds it. It's not too difficult to write your own wrapper function to implement a simple version of it though.