Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I execute a case-insensitive search?

457378Jan 9 2007 — edited Jan 10 2007
I have created a table
CREATE TABLE parsPatient
   (	
	patPARSID VARCHAR2(40),			
	patFirstName VARCHAR2(30), 		
	patMiddleName VARCHAR2(30),		
	patLastName VARCHAR2(30),			
	patDOB DATE, 							
	CONSTRAINT parsPatient_PK PRIMARY KEY (patPARSID) ENABLE
   )
and I would like to search the patLastName field for all patients that match a wildcard (i.e. I want to see Abcd, ABcd, AB, and abcd when I search for "AB%".

Using Oracle Database 10g Express Edition (10.2), I have done the following successfully:
SQL> alter session set nls_comp='LINGUISTIC';
SQL> select count(patparsid) from parspatient where patlastname like 'ab%';

COUNT(PATPARSID)
----------------
               7
However, when I try to do this using .NET, I get 0 rows. I'm doing the following (simplified, hopefully, for posting):
DataSet dsResults = new DataSet("patients");
try
{
	// Establish connection
	OracleConnection cn = new OracleConnection();
	cn.ConnectionString = settings.ConnectionString;
	cn.Open();
	cn.ClientId = settings.CurrentUser;

	// Set the linguistic mode
	OracleCommand command = new OracleCommand();
	command.Connection = cn;
	command.BindByName = true;
	command.CommandText = "ALTER SESSION SET NLS_COMP='LINGUISTIC'";
	command.CommandType = CommandType.Text;
	command.Parameters.Clear();		// Remove all parameters, if any.

	int iValue = command.ExecuteNonQuery(); // This returns a -1 -- is that normal?

	command.CommandText = "ALTER SESSION SET NLS_COMP='ANSI'";
	iValue = command.ExecuteNonQuery();

	command.CommandText = "SELECT patPARSID FROM parsPatient WHERE patLastName LIKE 'ab%'";
	OracleDataAdapter adapter = new OracleDataAdapter(); 
	adapter.SelectCommand = command;
	adapter.Fill(dsResults, "SearchResults"); 

	// Clean up
	command.Dispose();
	command = null;

	cn.Close();
	cn.Dispose();
	cn = null;

}
catch (OracleException oex)
{
	LogDBError("TestCaseInsensitiveSearch", oex);
}
Any help/pointers would be greatly appreciated!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 7 2007
Added on Jan 9 2007
2 comments
1,285 views