[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.