接触到一个老的项目，里面大量使用OleDbConnection进行数据库操作，在执行SQL块语句时，对它的顺序参数、命名参数很不了解。据说不能使用命名参数，但我这里试验了一下，好像是可以的，只是对参数的顺序还是有要求。看看你能知道下面的输出结果吗？
测试环境：OleDbConnection+Oracle10G
System;
using
System.Data;
using
System.Data.OleDb;
using
System.Data.OracleClient;
using
System.Text.RegularExpressions;
using
System.Text;
using
System.Collections;
using
System.Diagnostics;
namespace
ConsoleApplication1
{
///
<summary>
///
Class1 的摘要说明。
///
</summary>
class
Program
{
private
void
Test1()
{
using
(OleDbConnection conn
=
new
OleDbConnection(
“
Provider=OraOLEDB.Oracle;User ID=oauser;Data Source=oa163;Password=1234;Persist Security Info=True;
“
))
{
string
sql
=
“
begin delete from B; insert into B(A,B) values(:a,:b); end;
“
;
//
很正常的
OleDbCommand cmd
=
new
OleDbCommand(sql,conn);
cmd.Parameters.Add(
“
a
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
a
“
].Value
=
“
a
“
;
cmd.Parameters.Add(
“
b
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
b
“
].Value
=
“
b
“
;
cmd.CommandType
=
CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText
=
“
select a,b from B
“
;
using
(OleDbDataReader dr
=
cmd.ExecuteReader(CommandBehavior.SingleRow))
{
Debug.Assert(dr.Read());
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
a
“
))
==
“
a
“
);
//
正常结果
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
b
“
))
==
“
b
“
);
}
}
}
private
void
Test2()
{
using
(OleDbConnection conn
=
new
OleDbConnection(
“
Provider=OraOLEDB.Oracle;User ID=oauser;Data Source=oa163;Password=1234;Persist Security Info=True;
“
))
{
string
sql
=
“
begin delete from B; insert into B(B,A) values(:b,:a); end;
“
;
//
这里换一下顺序
OleDbCommand cmd
=
new
OleDbCommand(sql,conn);
cmd.Parameters.Add(
“
a
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
a
“
].Value
=
“
a
“
;
cmd.Parameters.Add(
“
b
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
b
“
].Value
=
“
b
“
;
cmd.CommandType
=
CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText
=
“
select a,b from B
“
;
using
(OleDbDataReader dr
=
cmd.ExecuteReader(CommandBehavior.SingleRow))
{
Debug.Assert(dr.Read());
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
a
“
))
==
“
b
“
);
//
结果不一样了吧
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
b
“
))
==
“
a
“
);
}
}
}
private
void
Test3()
{
using
(OleDbConnection conn
=
new
OleDbConnection(
“
Provider=OraOLEDB.Oracle;User ID=oauser;Data Source=oa163;Password=1234;Persist Security Info=True;
“
))
{
string
sql
=
“
declare v_exists int := 1;
“
+
“
begin
“
+
“
delete from B;
“
+
“
select count(*) into v_exists from B where a=:a and b=:b and rownum < 2;
“
+
//
很正常的
“
if (v_exists = 0) then
“
+
“
insert into B(A,B) values(:a,:b);
“
+
“
end if;
“
+
“
end;
“
;
OleDbCommand cmd
=
new
OleDbCommand(sql,conn);
cmd.Parameters.Add(
“
a
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
a
“
].Value
=
“
a
“
;
cmd.Parameters.Add(
“
b
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
b
“
].Value
=
“
b
“
;
cmd.CommandType
=
CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText
=
“
select a,b from B
“
;
using
(OleDbDataReader dr
=
cmd.ExecuteReader(CommandBehavior.SingleRow))
{
Debug.Assert(dr.Read());
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
a
“
))
==
“
a
“
);
//
正常结果
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
b
“
))
==
“
b
“
);
}
}
}
private
void
Test4()
{
using
(OleDbConnection conn
=
new
OleDbConnection(
“
Provider=OraOLEDB.Oracle;User ID=oauser;Data Source=oa163;Password=1234;Persist Security Info=True;
“
))
{
string
sql
=
“
declare v_exists int := 1;
“
+
“
begin
“
+
“
delete from B;
“
+
“
select count(*) into v_exists from B where b=:b and a=:a and rownum < 2;
“
+
//
b=:b and a=:a 换一下顺序
“
if (v_exists = 0) then
“
+
“
insert into B(A,B) values(:a,:b);
“
+
“
end if;
“
+
“
end;
“
;
OleDbCommand cmd
=
new
OleDbCommand(sql,conn);
cmd.Parameters.Add(
“
a
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
a
“
].Value
=
“
a
“
;
cmd.Parameters.Add(
“
b
“
,OleDbType.VarChar,
100
);
cmd.Parameters[
“
b
“
].Value
=
“
b
“
;
cmd.CommandType
=
CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText
=
“
select a,b from B
“
;
using
(OleDbDataReader dr
=
cmd.ExecuteReader(CommandBehavior.SingleRow))
{
Debug.Assert(dr.Read());
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
a
“
))
==
“
b
“
);
//
结果不一样了吧
Debug.Assert(dr.GetString(dr.GetOrdinal(
“
b
“
))
==
“
a
“
);
}
}
}
///
<summary>
///
应用程序的主入口点。
///
</summary>
[STAThread]
static
void
Main(
string
[] args)
{
//
//
TODO: 在此处添加代码以启动应用程序
//
try
{
Program prog
=
new
Program();
prog.Test1();
prog.Test2();
prog.Test3();
prog.Test4();
}
catch
(Exception exp)
{
Console.WriteLine(exp.ToString());
}
finally
{
Console.ReadLine();
}
}
}
}
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/36218.html
