接触到一个老的项目，里面大量使用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";
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();
}
}
}
}
看起来，在OleClient中使用块语句，还是有可能的。但愿9G下不会出问题。