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 ));
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.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));
3,ArrayBindCount
使用这个属性可以使用Oracle批量的功能。假如我们要批量锁定一些用户帐号,一般我们都是这样写SQL:
string
text
=
"
update tbl_user set is_lock = 0 where user_id in (123,456,789)
"
;
OracleCommand comm = new OracleCommand(text);
OracleCommand comm = new OracleCommand(text);
使用ODP.NET可以这样写:
string
text
=
"
update tbl_user set is_lock = 0 where user_id = :user_id
"
;
int [] userIds = new int [] { 123 , 456 , 789 };
OracleCommand comm = new OracleCommand(text);
comm.ArrayBindCount = 3 ;
comm.Parameters.Add( new OracleParameter( " :user_id " , OracleDbType.Int32, 8 ));
comm.Parameters[ 0 ].Value = userIds;
int [] userIds = new int [] { 123 , 456 , 789 };
OracleCommand comm = new OracleCommand(text);
comm.ArrayBindCount = 3 ;
comm.Parameters.Add( new OracleParameter( " :user_id " , OracleDbType.Int32, 8 ));
comm.Parameters[ 0 ].Value = userIds;