none
named parameters in datacontext.ExecuteQuery RRS feed

  • Question

  • I have to execute a query on my datacontext. Because the SQL statement is dynamically generated, it's complicated to use integer-indexed parameters. So instead of:

    "Select Id from TestTable where ColA = {0} AND ColB = {1}"

    I would like to use:

    "Select Id from TestTable where ColA = @colA AND ColB = @colB"

    As far as I could see, DataContext.ExecuteQuery only works with the first statement. So is using the connection directly, creating a command and naming the parameters the only way to achieve this goal?

    string sqlQuery = "Select Id from TestTable where ColA = @colA AND ColB = @colB";
    command.CommandText = sqlQuery;
    command.Connection.Open();
    var colA = command.CreateParameter();
    colA.ParameterName = @colA;
    colA.Value = 1;
    command.Parameters.Add(colA);
    var reader = command.ExecuteReader();

    Thank you in advance

    Wednesday, December 24, 2008 8:32 AM

Answers

  •  Yes, if you want to use some other scheme for parameterization, you'll have to create & execute the command object yourself.   You can still use the Translate method to convert a DataReader into an IEnumerable<T> sequence.
    Wayward LINQ Lacky
    • Marked as answer by Dunken Monday, January 5, 2009 8:48 AM
    Wednesday, December 31, 2008 8:36 PM
    Moderator

All replies

  • Hello,

    Since this is a LINQ to SQL question, I will move your post to the LINQ to SQL Forum.

    Thanks,
    Diego
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, December 30, 2008 4:47 AM
  •  Yes, if you want to use some other scheme for parameterization, you'll have to create & execute the command object yourself.   You can still use the Translate method to convert a DataReader into an IEnumerable<T> sequence.
    Wayward LINQ Lacky
    • Marked as answer by Dunken Monday, January 5, 2009 8:48 AM
    Wednesday, December 31, 2008 8:36 PM
    Moderator