Can ODBC parameter place holders be named?

user_767192 0
Viewed 17699

I did some searching and haven't found a definitive answer to my questions.

Is there a way to define which ? in a SQL query belongs to which parameter?
For example, I need to perform something like this:

SELECT * FROM myTable WHERE myField = @Param1 OR myField2 = @Param1 
       OR myField1 = @Param2 OR myField2 = @Param2

The same query in ODBC is:

SELECT * FROM myTable WHERE myField = ? or myField2 = ? or myField1 = ? 
       or myField2 = ?

Is there a way to tell the ODBC command which parameter is which besides loading parameters in twice for each value?

I suspect there isn't but could use perspective from more experienced ODBC programmers.

EDIT : The ODBC driver I'm using is a BBj ODBC Driver.

odbc sql c#
7 Answers

I altered answer provided by David Liebeherr to come up code below.

It allows for Select @@Identity as mentioned by mfeineis.

public static IDbCommand ReplaceCommndTextAndParameters(this IDbCommand command, string commandText, List<IDbDataParameter> parameters) {
  command.CommandText = commandText;
  command.Parameters.Clear();
  foreach (var p in parameters) {
      command.Parameters.Add(p);
  }
  return command;
}

public static IDbCommand ConvertNamedParametersToPositionalParameters(this IDbCommand command) {
  var newCommand = command.GetConvertNamedParametersToPositionalParameters();
  return command.ReplaceCommndTextAndParameters(newCommand.CommandText, newCommand.Parameters);
}

public static (string CommandText, List<IDbDataParameter> Parameters) GetConvertNamedParametersToPositionalParameters(this IDbCommand command) {
  //1. Find all occurrences parameters references in the SQL statement (such as @MyParameter).
  //2. Find the corresponding parameter in the command's parameters list.
  //3. Add the found parameter to the newParameters list and replace the parameter reference in the SQL with a question mark (?).
  //4. Replace the command's parameters list with the newParameters list.
  var oldParameters = command.Parameters;
  var oldCommandText = command.CommandText;
  var newParameters = new List<IDbDataParameter>();
  var newCommandText = oldCommandText;
  var paramNames = oldCommandText.Replace("@@", "??").Split('@').Select(x => x.Split(new[] { ' ', ')', ';', '\r', '\n' }).FirstOrDefault().Trim()).ToList().Skip(1);
  foreach (var p in paramNames) {
    newCommandText = newCommandText.Replace("@" + p, "?");
    var parameter = oldParameters.OfType<IDbDataParameter>().FirstOrDefault(a => a.ParameterName == p);
    if (parameter != null) {
      parameter.ParameterName = $"{parameter.ParameterName}_{newParameters.Count}";
      newParameters.Add(parameter);
    }
  }
  return (newCommandText, newParameters);
}
user_86240100
Related