[Oracle]ASP.NET中使用ODP.NET的DeriveParameters方法得到存儲過程的參數列表並傳值.

ODP.NET是Oracle針對asp.net平臺出品的一個組件,他是ODAC的一部分,功能比microsoft的oracleclient強大.

下面介紹DeriveParameters方法的使用.

 

1.寫DataOP的公共操作類

代码

public   partial   class  DataOP
    {
         public   string  conStr  =   @" Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleServer)));User Id=root;Password=root; " ;     

        private static volatile DataOP instance;
        private static object syncRoot = new object();

        public static DataOP Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        if (instance == null)
                            instance = new DataOP();
                    }
                }
                return instance;
            }
        }  
        

public  OracleParameter[] GetSpParameters( string  spName,  bool  hasReturn)
        {
            OracleParameter[] cachedParameters  =   GetSpParameterSet(spName, hasReturn);
             return  CloneParameters(cachedParameters);
        }

         public  OracleParameter[] CloneParameters(OracleParameter[] originalParameters)
        {
            OracleParameter[] clonedParameters  =   new  OracleParameter[originalParameters.Length];

             for  ( int  i  =   0 , j  =  originalParameters.Length; i  <  j; i ++ )
            {
                clonedParameters[i]  =  (OracleParameter)originalParameters[i].Clone();
                clonedParameters[i].CollectionType  =  originalParameters[i].CollectionType;
            }
             return  clonedParameters;
        }

         public   bool  AssignParameterValues(OracleParameter[] commandParameters,  object [] parameterValues)
        {
             if  ((commandParameters  ==   null )  ||  (parameterValues  ==   null ))
            {
                 return   true ;
            }

             if  (commandParameters.Length  !=  parameterValues.Length)
            {
                 // Trace.Write("AssignParameterValues", "Error", "參數個數和參數值不匹配。");
                 return   false ;
            }

             for  ( int  i  =   0 , j  =  commandParameters.Length; i  <  j; i ++ )
            {
                commandParameters[i].Value  =  parameterValues[i];
            }

             return   true ;
        }
     
         public  OracleParameter[] GetSpParameterSet ( string  spName,  bool  hasReturn) 
        {
             using  (OracleCommand cmd  =   new  OracleCommand())
            {
                cmd.Connection  =   new  OracleConnection(conStr);

                cmd.CommandText  =  spName;
                cmd.CommandType  =  CommandType.StoredProcedure;
                 try
                {
                     if  (cmd.Connection.State  !=  ConnectionState.Open)
                        cmd.Connection.Open();
                    OracleCommandBuilder.DeriveParameters(cmd);
                }
                 catch  (Exception e)
                {
                     string  msg  =  e.Message;
                    Console.WriteLine(e.Message);
                     // Trace.Write("DeriveParameters", "Error", spName + "|" + msg);
                }
                 finally
                {
                     if  (cmd.Connection.State  !=  ConnectionState.Closed)
                        cmd.Connection.Close();
                }
                 try
                {
                     if  ( ! hasReturn)
                    {
                        cmd.Parameters.RemoveAt( 0 );
                    }
                }
                 catch  (Exception e)
                {
                     string  msg  =  e.Message;
                    Console.WriteLine(e.Message);
                }

                OracleParameter[] para  =   new  OracleParameter[cmd.Parameters.Count];
                cmd.Parameters.CopyTo(para,  0 );

                 return  para;
            }
        }     

    }

 

2.調用方法如下:

 

代码

            PlantInfo[] records  =   new  PlantInfo[ 1 ];
            PlantInfo record  =   new  PlantInfo();
            record.PLANT  =   " W000 " ;
            record.SUBPLANT  =   " MP " ;
            records[ 0 ]  =  record;
　　　　　　 string  spName  =   " MYPACK_TEST.TEST2;
　　　　　　　object [] parameterValues  =   new   object [] {  new   object []{records },  null  };
            DataTable dt  =   new  DataTable();
            OracleParameter[] commandParameters  =  GetSpParameters(spName,  true );
            AssignParameterValues(commandParameters, parameterValues);

            OracleCommand cmd  =   new  OracleCommand();
            cmd.CommandType  =  CommandType.StoredProcedure;
             foreach  (OracleParameter pi  in  commandParameters)
                cmd.Parameters.Add(pi);
            cmd.CommandText  =  spName;
            cmd.Connection  =   new  OracleConnection(conStr);

             try
            {
                 if  (cmd.Connection.State  !=  ConnectionState.Open)
                    cmd.Connection.Open();

                OracleDataReader reader  =  cmd.ExecuteReader();
                 //   OracleDataReader reader = ((OracleRefCursor)cmd.Parameters[7].Value).GetDataReader();

                dt.Load(reader);
            }
             catch  (Exception e)
            {
                 throw  e;
            }
             finally
            {
                 if  (cmd.Connection.State  !=  ConnectionState.Closed)
                    cmd.Connection.Close();
                cmd.Dispose();
            }
             return  dt;

 

Note:Direction = OutPut的傳值為null.

 

转载于:https://www.cnblogs.com/zzyyll2/archive/2010/01/25/1655664.html