c#开发连接oracle数据库查询条件中含汉字但是查不到数据

时间:2021-08-15 23:48:26

被这个问题纠结了一晚上,上网查了很多帖子,有说本地Oracle字符集问题的,有说连接字符串中加入“Unicode=true”的,感觉都不对,验证了也确实不能解决问题。


问题描述:

DBConn.cs

==============================

OleDbConnection conn = new OleDbConnection("Provider=OraOLEDB.Oracle;Server=" + Properties.Settings.Default.ServerAddress + 
";Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = " + Properties.Settings.Default.ServerAddress + ")(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = " + Properties.Settings.Default.DbServiceName + ")));
User ID=" + userID + ";Password=" + userPWD + ";");

Gateway.cs

==============================

public DataSet GetPatientBasicInfo(string startDate,string endDate)
{
DataSet mydataset = new DataSet();
using (OleDbConnection conn = Models.EHR.DBConnManager.GetConnServer())
{
conn.Open();
string sqlStr = @"SELECT *
FROM TABLE1
WHERE COLUMN1=?";
using (OleDbCommand cmd = new OleDbCommand(sqlStr, conn))
{
cmd.Parameters.Add("column1", OleDbType.VarChar).Value ="张三";
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(mydataset);
}
}
return mydataset;
}


问题分析解决:

1. 首先感觉是自己开发环境的问题,于是将编译好的可执行程序拷贝到其他计算机测试,都提取不到结果。

2. 感觉是连接字符串的问题,于是换成下面方式,改用Oracle客户端配置,问题依旧。

OleDbConnection conn = new OleDbConnection("Provider=OraOLEDB.Oracle;Data Source=TESTDB;
User Id=" + userID + ";Password=" + userPWD + ";Unicode=true;"

3. 最后觉得是不是Provider的问题,于是换成MSDAORA,问题得到了解决。

OleDbConnection conn = new OleDbConnection("Provider=MSDAORA;Data Source=TESTDB;
User Id=" + userID + ";Password=" + userPWD + ";Unicode=true;"

4. 回过头来把字符串方式的Provider也改过来,也成功了。

OleDbConnection conn = new OleDbConnection("Provider=MSDAORA;Server=" + Properties.Settings.Default.ServerAddress + 
";Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = " + Properties.Settings.Default.ServerAddress + ")(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = " + Properties.Settings.Default.DbServiceName + ")));
User ID=" + userID + ";Password=" + userPWD + ";");

5. 查阅资料

OleDB连接驱动有MSDAORA和OraOLEDB.Oracle,他们分别由微软和Oracle提供。

可能是OraOLEDB的问题。


参考:http://blog.csdn.net/whuarui2010/article/details/9456741