接觸到一個老的項目，裡面大量使用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下不會出問題。
