顺序OleDbCommand命名参数，你了解不？

大家好，又见面了，我是你们的朋友全栈君。

接触到一个老的项目，里面大量使用OleDbConnection进行数据库操作，在执行SQL块语句时，对它的顺序参数、命名参数很不了解。据说不能使用命名参数，但我这里试验了一下，好像是可以的，只是对参数的顺序还是有要求。看看你能知道下面的输出结果吗？

测试环境：OleDbConnection+Oracle10G

using
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
“
;

}

///

<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();

            }
        }
    }
}