ADO.NET vs. ODBC vs. Parameters

Doing some coding this evening, I noticed there is a slight weirdness with how ODBC in ADO.NET handles parameters. Apparently, you can’t use named parameters when using generic ADO.NET code. So,

DbCommand cmd = _conn.CreateCommand();
cmd.CommandText = "SELECT id, name FROM names WHERE id=@id";
cmd.CommandType = CommandType.Text;DbParameter i = cmd.CreateParameter();
i.ParameterName = "id";
i.Value = (long)id;
cmd.Parameters.Add(i);

Does not return any rows when you execute the query. Instead, you have to do it this way, using anonymous parameters:

DbCommand cmd = _conn.CreateCommand();
cmd.CommandText = "SELECT id, name FROM names WHERE id=?";
cmd.CommandType = CommandType.Text;DbParameter i = cmd.CreateParameter();
i.Value = (long)id;
cmd.Parameters.Add(i);

When using multiple parameters, you need to call `Parameters.Add` in the correct order, and any repeated use of parameters have to now be repeated. Annoying. :(