web analytics
Using Named Parameters with Oracle ODP.NET Options
codeling
Posted: Tuesday, July 10, 2018 3:25:54 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 984
Points: 4146

The Oracle data provider always binds parameters by position unless told otherwise. So, even though we have named parameters in our query, and we have added named parameters to the OracleCommand object, the data provider still binds the parameters by position; and as we have added the parameters in the 'wrong' order (albeit deliberately in this example) the query doesn't throw an exception, it merely returns an empty result set as the query received by the database is the equivalent of:

SELECT SOME_COLUMN, ANOTHER_COLUMN, THIRD_COLUMN FROM SOME_TABLE WHERE ANOTHER_COLUMN = 'Ping' AND THIRD_COLUMN = 'Foo'

 

The solution

This isn't made entirely clear on the Oracle documentation, but there is an additional property, BindByName, on the OracleCommand object, which must be set to true in order to bind parameters by name:

OracleCommand command = new OracleCommand(query, connection)

{

  CommandType = CommandType.Text,

  BindByName = true

};

Sponsor
Posted: Tuesday, July 10, 2018 3:25:54 PM
 
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2018 Digcode.com. All rights reserved.