.Net程序员学用Oracle系列(16)：访问数据库(ODP.NET)
- 1、.Net for Oracle 常见数据库驱动
- 2、ODP.NET 常见问题分析
- 2.1、参数化问题
- 2.2、方法调用问题
- 2.3、取不到存储过程的输出参数值
- 3、总结
网上有大量诸如 C#/.Net 连接 Oracle 的几种方式之类的帖子，无非也就是介绍几种驱动，大部分内容还雷同。曾经我只是想上网找一个适合手头上项目访问 Oracle 的方法，结果却迷失在浩瀚如烟的相似帖子中望洋兴叹。随着时间的推移，我逐渐理清了这里面的关系，本文将按我的理解介绍几个常见的 .Net for Oracle 数据库驱动，并重点分析我本人在使用 ODP.NET 中遇到的 3 类问题。
1、.Net for Oracle 常见数据库驱动
数据库驱动是由数据库厂商或第三方数据库驱动开发商为了某种开发语言（如 C#、Java）能够方便的访问某种数据库（如 SQL Server、Oracle）而提供的接口程序。程序代码一般都是通过数据库驱动来访问数据库，其实 .Net 连接 Oracle 的方式也就一种——先选定一个数据库驱动，然后按这个驱动的调用方法来连接数据库。本节将分类逐一介绍 ODBC、OLE DB、ADO.NET、ODAC、ODP.NET、dotConnect for Oracle 共 6 个数据库驱动。
1.1、微软提供的驱动
微软的数据库访问技术由来已久，可选的驱动也比较多。如果数据库用的是 SQL Server，那么微软提供的数据库驱动将是不二选择，但如果用的是 Oracle，情况就相对复杂了，下文会详细说明。
ODBC：即开放数据库互连(Open Database Connectivity)，它定义了访问数据库 API 的一个规范，是 Microsoft 提出的数据库访问接口标准，这些 API 独立于不同厂商的 DBMS，也独立于具体的编程语言。但这是一种非常古老的访问技术，本人工作中从未见人用 ODBC，我自己也从没用过，同时也不建议读者朋友们用这种方式。本人从《Oracle 数据库开发指南》中找到《Using the Oracle ODBC Driver》，有兴趣的读者可通过此文稍微了解下。
OLE DB：即数据库链接和嵌入对象(Object Linking and Embedding Database)，是微软提出的基于 COM 思想且面向对象的一种技术标准，目的是提供一种统一的数据访问接口来访问各种数据源。OLE DB 不仅包括微软资助的标准数据接口开放数据库互连(ODBC)的结构化查询语言(SQL)能力，还具有访问其他非 SQL 数据类型的能力。OLE DB 比 ODBC 要先进的多，著名的 ADO 就是基于 OLE DB 开发的，有兴趣读者朋友可以看看《Provider for OLE DB Developer's Guide》。在本人工作这几年里倒是没见人用过它，我此前也没用过，下例我第一次尝试用 OLE DB 驱动连接 Oracle 数据库的代码，其中连接字符串里的Provider用于指定驱动提供商，可选项有MSDAORA和OraOLEDB.Oracle，而Data Source用于指定要连接的数据库，一般写主机字符串即可。
using System;
using System.Data.OleDb;
namespace OracleSeries
{
class ConnectOracleByOleDb
{
static void Main(string[] args)
{
string connectionString = @"Provider=OraOLEDB.Oracle;Data Source=ORCL_127.0.0.1;User ID=demo;PassWord=test;";
using (OleDbConnection conn = new OleDbConnection(connectionString))
using (OleDbCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT SYSDATE FROM DUAL";
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine("服务器时间: {0}", dr[0]);
}
}
}
Console.ReadKey();
}
}
}
写这段代码的过程中我遇到一个问题：刚开始我选择的驱动是 MSDAORA，一运行代码就报“未在本地计算机上注册“MSDAORA”提供程序”，后来发现只要把项目的目标平台改为 x86 就好了，于是乎我换成 OraOLEDB.Oracle 又试了一下，不改目标平台也可以运行，甚是奇怪，由于平常从来不用它，我也就没深入去研究这个问题了。
ADO.NET：可能每个 .Net 程序都用过 ADO.NET，但大部分应该是用它来连接 SQL Server。在早期的 .Net 版本中，微软提供了对 Oracle 的支持，但最终微软还是停止了更新。本人在微软官网上找到了一个说明——《Oracle 和 ADO.NET》。其实在 .Net 4.0 中我们可以通过手动添加System.Data.OracleClient引用的方式来继续使用 ADO.NET 连接 Oracle，且 VS 会提示相关类型已过时，但代码依然可以照常运行。示例：
using System;
using System.Data.OracleClient;
namespace OracleSeries
{
class ConnectOracleByAdoNet
{
static void Main(string[] args)
{
string connectionString = @"Data Source=ORCL_127.0.0.1;User ID=demo;PassWord=test;";
using (OracleConnection conn = new OracleConnection(connectionString))
using (OracleCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT SYSDATE FROM DUAL";
using (OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine("服务器时间: {0}", dr[0]);
}
}
}
Console.ReadKey();
}
}
}
1.2、甲骨文提供的驱动
甲骨文提供了一个针对 Windows 和 .NET 的数据库访问组件——ODAC(Oracle Data Access Components)，它包含了一系列的数据访问驱动程序和工具，如：Oracle Data Provider for .NET(ODP.NET)、Oracle Provider for OLE DB、Oracle ODBC Driver、Oracle Developer Tools for Visual Studio、Oracle SQL*Plus、Oracle Instant Client 等。本节将重点讨论 ODP.NET，我觉得它应该是当下 .Net 程序访问 Oracle 的最佳选择。
ODP.NET 先后提供了两个访问 Oracle 的 DLL 文件，分别是非托管的Oracle.DataAccess.dll和托管的Oracle.ManagedDataAccess.dll。ODP.NET 是可以单独使用的，无需安装 ODAC 或其它任何组件，只需要将 DLL 文件拷贝到项目中引用一下即可。其中非托管的 DLL 得装 Oracle 客户端才能访问 Oracle，而托管的 DLL 免装客户端。它们的命名空间分别是Oracle.DataAccess.Client和Oracle.ManagedDataAccess.Client，但 API 命名貌似完全一致，至少我目前还没发现有任何不同。如果你想进一步了解 ODP.NET，请看官方说明——《Oracle Data Provider for .NET》。
注意：本人强烈不建议使用非托管的 DLL，首先它没有客户端就不能访问 Oracle，像我这种喜欢用简易客户端的人就不能用它，否则项目都无法调试了；更加令人恶心的是它还有两个版本限制，一个是操作系统的版本限制，32位和64位得区别对待，另一个是 DLL 文件版本的限制，哪怕是小版本的区别都有可能导致程序集加载失败，尽管可以通过 DLL 重定向来解决，但比较繁琐。我不太喜欢装一大堆东西，所以有时候我感觉Oracle.DataAccess.dll简直就是个垃圾！如果项目比较老，在还没有 Oracle.ManagedDataAccess.dll的时候不慎用了Oracle.DataAccess.dll，想切换过来也是非常容易的，只需要替换 DLL 文件和命名空间即可。调用托管 DLL 的示例：
using System;
using Oracle.ManagedDataAccess.Client;
namespace OracleSeries
{
class ConnectOracleByOdpNet
{
static void Main(string[] args)
{
string connectionString = @"Data Source=ORCL_127.0.0.1;User ID=demo;PassWord=test;";
using (OracleConnection conn = new OracleConnection(connectionString))
using (OracleCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT SYSDATE FROM DUAL";
using (OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine("服务器时间: {0}", dr[0]);
}
}
}
Console.ReadKey();
}
}
}
细心的读者应该能够发现，除了命名空间不同之外，其它代码与 ADO.NET 的示例完全一样，事实上它俩的 API 区别也的确是微乎其微！可能一开始 Oracle 就考虑到让 ODP.NET 与 ADO.NET 兼容吧，这样也方便旧项目从 ADO.NET 切换到 ODP.NET。
1.3、其它厂商提供的驱动
数据库驱动这种程序，一般都是数据库厂商或语言厂商做的比较好，但依然有部分第三方厂商做的数据库驱动也还不错。譬如 Devart 公司推出的商业版数据库驱动 dotConnect for Oracle 等。
dotConnect for Oracle：原名 OraDirect，完全基于 ADO.NET 开发的，不用装 Oracle 客户端，而且性能非常好——《dotConnect for Oracle Performance》，应该是 .Net for Oracle 第三方驱动中做的最好的。本人待过的两个公司都不用这个驱动，估计有免费的 ODP.NET 公司就都不愿掏钱了吧！我自己目前也没用过，有兴趣的读者可自行研究。官网：http://www.devart.com/dotconnect/oracle/。
2、ODP.NET 常见问题分析
2.1、参数化问题
可能每个程序员都知道“参数化查询”是怎么回事儿，在 ODP.NET 中表示参数的类是 OracleParameter，用法与 ADO.NET 中提供的基本一致，但依然有几个小问题需要稍微注意下，具体请看下文。
参数前缀问题：一般参数化时所有的 SQL 参数都得加一个特殊的符号前缀。SQL Server 和 SQLite 的参数前缀是@，如@Name；MySQL 的参数前缀是?，如?Name；Oracle 的参数前缀是:，如:Name；而 Access 的所有参数都直接用?来表示（因此需要按照列的出现顺序来给参数赋值）。
用 PLSQL 定义并访问变量user_id的示例：
DECLARE
user_id NUMBER(10);
BEGIN
DELETE FROM t_user t WHERE t.user_id=:user_id;
END;
第一次用 ODP.NET 中的 OracleParameter 时，我习惯性的写成：new OracleParameter("@user_id", userId);，结果不行。想到 PLSQL 里访问变量得用:，就连赋值都得用:开头——:=，于是我就想当然的写成：new OracleParameter(":@user_id", userId);，试了一下报“ORA-01745: 无效的主机/绑定变量名”。实在试不出来了就查了下资料，正确写法示例：
new OracleParameter(":user_id", userId); // 标准写法
new OracleParameter("user_id", userId); // 简易写法
有一次我偶然写成这样：new OracleParameter(":_user_id", userId);，报了个“ORA-00911: 无效字符”，明明 userId 的值就是个数字，为啥会报无效数字呢？把字母 u 前面的_去掉就好了，当时真的是百思不得其解啊！后来我才发现，原来_是 Oracle 中的转义字符。
变量绑定问题：默认情况下 ODP.NET 要求程序中参数顺序必须与 PLSQL 语句中的参数顺序完全一致，假如要执行如下语句：
UPDATE t_user t SET t.birthday=:birthday WHERE t.user_name=:user_name AND t.gender=:gender;
那么参数数组中的参数就必须按 birthday->user_name->gender 的顺序定义和赋值，其它顺序都不行，示例：
OracleParameter[] ps =
{
new OracleParameter("birthday", OracleDbType.Date),
new OracleParameter("user_name", OracleDbType.Varchar2,20),
new OracleParameter("gender", OracleDbType.Int32)
};
ps[0].Value = birthday;
ps[1].Value = userName;
ps[2].Value = gender;
在 ODP.NET 中对参数个数的要求也极为严格，必须与 PLSQL 语句中的参数占位符个数相同。假如某个参数在 PLSQL 语句中出现多次，那么程序中也得多次定义和赋值，假如要执行如下语句：
UPDATE t_user t SET t.user_name=:user_name WHERE t.user_name=:user_name;
正确的参数数组定义如下：
OracleParameter[] ps =
{
new OracleParameter("user_name", OracleDbType.Varchar2,20),
new OracleParameter("user_name", OracleDbType.Varchar2,20)
};
ps[0].Value = userName;
ps[1].Value = userName;
其实有个绑定变量的小技巧，就是把 OracleCommand 实例的 BindByName 属性值设置为 true。这样参数顺序和参数个数就没必要和 SQL 语句中的完全一致了。示例：
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true; // 相信你看了这个属性的名字之后就知道是怎么回事儿了吧
2.2、方法调用问题
ExecuteNonQuery() 方法的返回值：一般 ExecuteNonQuery 方法的返回值就是执行 Command 命令中 SQL 语句后，数据库中受影响的数据行数。但如果执行的是存储过程，你会发现无论数据库中数据是否受影响，ExecuteNonQuery 方法的返回值都是 -1。其实这是数据库里的set nocount on导致的，ExecuteNonQuery 方法的返回值有几个规律如下：
- 1、对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。
- 2、对于 CREATE TABLE 和 DROP TABLE 语句，返回值为 0。
- 3、对于其他所有类型的语句，返回值为 -1。
DataTable.Load() 方法装载不了阅读器中的数据：本人曾遇到执行 OracleCommand 对象的 ExecuteReader() 方法之后，得到了一个带有数据的 OracleDataReader 对象 reader，能在 VS 中监视到 reader 里面有一行数据，reader.Read() 的返回值也是 true，但调用 Load() 方法之后 DataTable 对象中却依然是空的，包含表结构，但就是没有数据，当时那个郁闷啊！
后来仔细测试发现，把 reader.Read() 去掉就能拿到 reader 里面哪行数据了。应该是 Read() 之后游标移动到第二行，结果就拿不到第一行的数据了。其实这并不是 ODP.NET 的问题，而是我自己没搞清楚 Read() 是怎么回事儿，但程序员在学习使用新技术遇到问题时，往往会先怀疑这个技术可能有问题，而不会先怀疑自己过去的认知，但基础牢固、理解深入的人就很少犯这种错了，可见对程序员来说基础是非常重要的！
2.3、取不到存储过程的输出参数值(类型为变长字符串VARCHAR2)
- 可能原因1：没有指定输出参数的大小，将得到 object{Oracle.ManagedDataAccess.Types.OracleString} 类型的值 {null}。
我曾在 ODP.NET 中调用带 VARCHAR2 类型输出参数的存储过程，结果却取不到存储过程里输出参数的值，得到是一个实际类型为 Oracle.ManagedDataAccess.Types.OracleString 的 object 类型的 null，折腾了老半天也还是不行。于是我请教了在公司工作了十多年的技术专家，对话大致如下：
我：“我用 ODP.NET 调用了一个带输出参数的存储过程，存储过程和 C# 代码都没有语法错误，而且输出参数也有值，但代码里就是取不到值，可能是啥情况啊？”
专家：“你有在 C# 代码里指定输出参数的长度吗？”
我：“额，没有，有这个必要吗？”。我特地翻看了一下之前的代码，的确是没有指定长度。我写代码定义长度，向来都是以数据库中的长度为参照，因为在数据库里存储过程的参数不允许定义长度，否则会报语法错误，所以此处我代码也与数据库中保持一致了。
专家：“我们之前也遇到过这个问题，好像是必须加的，否则就取不到值，要不你加上试试看？”
我：“好吧”。我将信将疑的照做之后果然 OK 了！
- 可能原因2：输出参数的值是空字符串，也会得到和第 1 种情况一样的值，在代码中 ToString() 后会变成 "null"。
如果返回值是一个空字符串，程序里得到的值也是一个 null。我的理解是：因为 Oracle 把空字符串做了与 null 值等同的处理，所以存储过程返回参数实际返回的值应该是 null 值，所以程序里也会得到 null。
3、总结
本文对常见 .NET 访问 Oracle 数据库的驱动做了个归类和总结，同时也对我在使用 ODP.NET 过程中遇到的 3 类问题做了分析。如果你在做基于 Oracle 的 .NET 开发，那么我强烈的推荐你使用 ODP.NET，因为它是免费驱动中做的最好的，而且它的维护者是 Oracle 亲爹。
本文链接：http://www.cnblogs.com/hanzongze/p/oracle-odp_net.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！
