大家好，又见面了，我是你们的朋友全栈君。
接触到一个老的项目，里面大量使用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下不会出问题。
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/144489.html原文链接：https://javaforall.cn
