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!