顺序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 “ ;

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