大家好，又见面了，我是你们的朋友全栈君。
接触到一个老的项目，里面大量使用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();
}
}
}
}
发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/144489.html原文链接：https://javaforall.cn
【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛
【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...
