• 顺序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下不会出问题。
  • 相关阅读:
    委托系列整理
    EF Lambda 多表查询
    枚举,Enum,常规使用demo记录
    自定义Window 服务
    xpath 操作XML
    MVC 自定义过滤器
    时间比对,常用细节记录
    Lock锁_线程_线程域
    break、continue和goto 三者作用介绍
    .net 学习路线感想
  • 原文地址:https://www.cnblogs.com/evlon/p/1427806.html
Copyright © 2020-2023  润新知