ODP.NET的用法与微软的组件差别不大,但是还是有些不同的,主要集中在OracleCommand对象上。
1,参数前缀
ODP.NET使用":",微软的组件使用"@"。
2,BindByName
参数数组与SQL语句参数匹配规则:微软的组件中默认是用参数数组的名称,而ODP.NET中默认是用参数数组的索引。可能这么说太不明白,看下面的代码:
string
text
=
"
select count(*) from tbl_user where (name = :name and password = :password)
or (email = :email and password = :password)
"
; OracleCommand comm
=
new
OracleCommand(text); comm.Parameters.Add(
new
OracleParameter(
"
:name
"
, OracleDbType.Varchar2,
20
)); comm.Parameters.Add(
new
OracleParameter(
"
:password
"
, OracleDbType.Varchar2,
64
)); comm.Parameters.Add(
new
OracleParameter(
"
:email
"
, OracleDbType.Varchar2,
50
));
按照上面写法应该是没有问题的,但事实并非如此,运行的时候会报错。应该这样写:
string
text
=
"
select count(*) from tbl_user where (name = :name and password = :password)
or (email = :email and password = :password)
"
;
OracleCommand comm
=
new
OracleCommand(text);
comm.BindByName
=
true
; comm.Parameters.Add(
new
OracleParameter(
"
:name
"
, OracleDbType.Varchar2,
20
)); comm.Parameters.Add(
new
OracleParameter(
"
:password
"
, OracleDbType.Varchar2,
64
)); comm.Parameters.Add(
new
OracleParameter(
"
:email
"
, OracleDbType.Varchar2,
50
));
加了一句comm.BindByName = true。或者:
string
text
=
"
select count(*) from tbl_user where (name = :name and password = :password)
or (email = :email and password = :password)
"
;
OracleCommand comm
=
new
OracleCommand(text);
comm.Parameters.Add(
new
OracleParameter(
"
:name
"
, OracleDbType.Varchar2,
20
));
comm.Parameters.Add(
new
OracleParameter(
"
:password
"
, OracleDbType.Varchar2,
64
));
comm.Parameters.Add(
new
OracleParameter(
"
:email
"
, OracleDbType.Varchar2,
50
));
comm.Parameters.Add(new OracleParameter(":password", OracleDbType.Varchar2, 64));