1 //===============================================================================
   2 // OracleHelper based on Microsoft Data Access Application Block (DAAB) for .NET
   3 // http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
   4 //
   5 // OracleHelper.cs
   6 //
   7 // This file contains the implementations of the OracleHelper and OracleHelperParameterCache
   8 // classes.
   9 //
  10 // The DAAB for MS .NET Provider for Oracle has been tested in the context of this Nile implementation,
  11 // but has not undergone the generic functional testing that the SQL version has gone through.
  12 // You can use it in other .NET applications using Oracle databases.  For complete docs explaining how to use
  13 // and how it's built go to the originl appblock link. 
  14 // For this sample, the code resides in the Nile namespaces not the Microsoft.ApplicationBlocks namespace
  15 //==============================================================================
  16 
  17 using System;
  18 using System.Data;
  19 using System.Xml;
  20 using System.Data.OracleClient;
  21 using System.Collections;
  22 
  23 
  24 namespace Microsoft.ApplicationBlocks.Data
  25 {
  26     /// <summary>
  27     /// The OracleHelper class is intended to encapsulate high performance, scalable best practices for 
  28     /// common uses of OracleClient.
  29     /// </summary>
  30     public sealed class OracleHelper
  31     {
  32         #region private utility methods & constructors
  33 
  34         //Since this class provides only static methods, make the default constructor private to prevent 
  35         //instances from being created with "new OracleHelper()".
  36         private OracleHelper() {}
  37 
  38         /// <summary>
  39         /// This method is used to attach array's of OracleParameters to an OracleCommand.
  40         /// 
  41         /// This method will assign a value of DbNull to any parameter with a direction of
  42         /// InputOutput and a value of null.  
  43         /// 
  44         /// This behavior will prevent default values from being used, but
  45         /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
  46         /// where the user provided no input value.
  47         /// </summary>
  48         /// <param name="command">The command to which the parameters will be added</param>
  49         /// <param name="commandParameters">an array of OracleParameters tho be added to command</param>
  50         private static void AttachParameters(OracleCommand command, OracleParameter[] commandParameters)
  51         {
  52             foreach (OracleParameter p in commandParameters)
  53             {
  54                 //check for derived output value with no value assigned
  55                 if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
  56                 {
  57                     p.Value = DBNull.Value;
  58                 }
  59                 
  60                 command.Parameters.Add(p);
  61             }
  62         }
  63 
  64         /// <summary>
  65         /// This method assigns an array of values to an array of OracleParameters.
  66         /// </summary>
  67         /// <param name="commandParameters">array of OracleParameters to be assigned values</param>
  68         /// <param name="parameterValues">array of objects holding the values to be assigned</param>
  69         private static void AssignParameterValues(OracleParameter[] commandParameters, object[] parameterValues)
  70         {
  71             if ((commandParameters == null) || (parameterValues == null)) 
  72             {
  73                 //do nothing if we get no data
  74                 return;
  75             }
  76 
  77             // we must have the same number of values as we pave parameters to put them in
  78             if (commandParameters.Length != parameterValues.Length)
  79             {
  80                 throw new ArgumentException("Parameter count does not match Parameter Value count.");
  81             }
  82 
  83             //iterate through the OracleParameters, assigning the values from the corresponding position in the 
  84             //value array
  85             for (int i = 0, j = commandParameters.Length; i < j; i++)
  86             {
  87                 commandParameters[i].Value = parameterValues[i];
  88             }
  89         }
  90 
  91         /// <summary>
  92         /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
  93         /// to the provided command.
  94         /// </summary>
  95         /// <param name="command">the OracleCommand to be prepared</param>
  96         /// <param name="connection">a valid OracleConnection, on which to execute this command</param>
  97         /// <param name="transaction">a valid OracleTransaction, or 'null'</param>
  98         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  99         /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
 100         /// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
 101         private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
 102         {
 103             //if the provided connection is not open, we will open it
 104             if (connection.State != ConnectionState.Open)
 105             {
 106                 connection.Open();
 107             }
 108 
 109             //associate the connection with the command
 110             command.Connection = connection;
 111 
 112             //set the command text (stored procedure name or Oracle statement)
 113             command.CommandText = commandText;
 114 
 115             //if we were provided a transaction, assign it.
 116             if (transaction != null)
 117             {
 118                 command.Transaction = transaction;
 119             }
 120 
 121             //set the command type
 122             command.CommandType = commandType;
 123 
 124             //attach the command parameters if they are provided
 125             if (commandParameters != null)
 126             {
 127                 AttachParameters(command, commandParameters);
 128             }
 129 
 130             return;
 131         }
 132 
 133 
 134         #endregion private utility methods & constructors
 135 
 136         #region ExecuteNonQuery
 137 
 138         /// <summary>
 139         /// Execute an OracleCommand (that returns no resultset and takes no parameters) against the database specified in 
 140         /// the connection string. 
 141         /// </summary>
 142         /// <remarks>
 143         /// e.g.:  
 144         ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
 145         /// </remarks>
 146         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 147         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 148         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 149         /// <returns>an int representing the number of rows affected by the command</returns>
 150         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
 151         {
 152             //pass through the call providing null for the set of OracleParameters
 153             return ExecuteNonQuery(connectionString, commandType, commandText, (OracleParameter[])null);
 154         }
 155 
 156         /// <summary>
 157         /// Execute an OracleCommand (that returns no resultset) against the database specified in the connection string 
 158         /// using the provided parameters.
 159         /// </summary>
 160         /// <remarks>
 161         /// e.g.:  
 162         ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter("@prodid", 24));
 163         /// </remarks>
 164         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 165         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 166         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 167         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 168         /// <returns>an int representing the number of rows affected by the command</returns>
 169         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 170         {
 171             //create & open an OracleConnection, and dispose of it after we are done.
 172             using (OracleConnection cn = new OracleConnection(connectionString))
 173             {
 174                 cn.Open();
 175 
 176                 //call the overload that takes a connection in place of the connection string
 177                 return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
 178             }
 179         }
 180 
 181         /// <summary>
 182         /// Execute a stored procedure via an OracleCommand (that returns no resultset) against the database specified in 
 183         /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
 184         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 185         /// </summary>
 186         /// <remarks>
 187         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 188         /// 
 189         /// e.g.:  
 190         ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
 191         /// </remarks>
 192         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 193         /// <param name="spName">the name of the stored procedure</param>
 194         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 195         /// <returns>an int representing the number of rows affected by the command</returns>
 196         public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
 197         {
 198             //if we got parameter values, we need to figure out where they go
 199             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 200             {
 201                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 202                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
 203 
 204                 //assign the provided values to these parameters based on parameter order
 205                 AssignParameterValues(commandParameters, parameterValues);
 206 
 207                 //call the overload that takes an array of OracleParameters
 208                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 209             }
 210             //otherwise we can just call the SP without params
 211             else 
 212             {
 213                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
 214             }
 215         }
 216 
 217         /// <summary>
 218         /// Execute an OracleCommand (that returns no resultset and takes no parameters) against the provided OracleConnection. 
 219         /// </summary>
 220         /// <remarks>
 221         /// e.g.:  
 222         ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
 223         /// </remarks>
 224         /// <param name="connection">a valid OracleConnection</param>
 225         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 226         /// <param name="commandText">the stored procedure name or PL/SQL command</param>
 227         /// <returns>an int representing the number of rows affected by the command</returns>
 228         public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText)
 229         {
 230             //pass through the call providing null for the set of OracleParameters
 231             return ExecuteNonQuery(connection, commandType, commandText, (OracleParameter[])null);
 232         }
 233 
 234         /// <summary>
 235         /// Execute an OracleCommand (that returns no resultset) against the specified OracleConnection 
 236         /// using the provided parameters.
 237         /// </summary>
 238         /// <remarks>
 239         /// e.g.:  
 240         ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter("@prodid", 24));
 241         /// </remarks>
 242         /// <param name="connection">a valid OracleConnection</param>
 243         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 244         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 245         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 246         /// <returns>an int representing the number of rows affected by the command</returns>
 247         public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 248         {    
 249             //create a command and prepare it for execution
 250             OracleCommand cmd = new OracleCommand();
 251             PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
 252             
 253             //finally, execute the command.
 254             return cmd.ExecuteNonQuery();
 255         }
 256 
 257         /// <summary>
 258         /// Execute a stored procedure via an OracleCommand (that returns no resultset) against the specified OracleConnection 
 259         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
 260         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 261         /// </summary>
 262         /// <remarks>
 263         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 264         /// 
 265         /// e.g.:  
 266         ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
 267         /// </remarks>
 268         /// <param name="connection">a valid OracleConnection</param>
 269         /// <param name="spName">the name of the stored procedure</param>
 270         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 271         /// <returns>an int representing the number of rows affected by the command</returns>
 272         public static int ExecuteNonQuery(OracleConnection connection, string spName, params object[] parameterValues)
 273         {
 274             //if we got parameter values, we need to figure out where they go
 275             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 276             {
 277                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 278                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
 279 
 280                 //assign the provided values to these parameters based on parameter order
 281                 AssignParameterValues(commandParameters, parameterValues);
 282 
 283                 //call the overload that takes an array of OracleParameters
 284                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
 285             }
 286             //otherwise we can just call the SP without params
 287             else 
 288             {
 289                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
 290             }
 291         }
 292 
 293         /// <summary>
 294         /// Execute an OracleCommand (that returns no resultset and takes no parameters) against the provided OracleTransaction. 
 295         /// </summary>
 296         /// <remarks>
 297         /// e.g.:  
 298         ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
 299         /// </remarks>
 300         /// <param name="transaction">a valid OracleTransaction</param>
 301         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 302         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 303         /// <returns>an int representing the number of rows affected by the command</returns>
 304         public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText)
 305         {
 306             //pass through the call providing null for the set of OracleParameters
 307             return ExecuteNonQuery(transaction, commandType, commandText, (OracleParameter[])null);
 308         }
 309 
 310         /// <summary>
 311         /// Execute an OracleCommand (that returns no resultset) against the specified OracleTransaction
 312         /// using the provided parameters.
 313         /// </summary>
 314         /// <remarks>
 315         /// e.g.:  
 316         ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 317         /// </remarks>
 318         /// <param name="transaction">a valid OracleTransaction</param>
 319         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 320         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 321         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 322         /// <returns>an int representing the number of rows affected by the command</returns>
 323         public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 324         {
 325             //create a command and prepare it for execution
 326             OracleCommand cmd = new OracleCommand();
 327             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
 328             
 329             //finally, execute the command.
 330             return cmd.ExecuteNonQuery();
 331         }
 332 
 333         /// <summary>
 334         /// Execute a stored procedure via an OracleCommand (that returns no resultset) against the specified 
 335         /// OracleTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
 336         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 337         /// </summary>
 338         /// <remarks>
 339         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 340         /// 
 341         /// e.g.:  
 342         ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
 343         /// </remarks>
 344         /// <param name="transaction">a valid OracleTransaction</param>
 345         /// <param name="spName">the name of the stored procedure</param>
 346         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 347         /// <returns>an int representing the number of rows affected by the command</returns>
 348         public static int ExecuteNonQuery(OracleTransaction transaction, string spName, params object[] parameterValues)
 349         {
 350             //if we got parameter values, we need to figure out where they go
 351             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 352             {
 353                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
 354                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
 355 
 356                 //assign the provided values to these parameters based on parameter order
 357                 AssignParameterValues(commandParameters, parameterValues);
 358 
 359                 //call the overload that takes an array of OracleParameters
 360                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
 361             }
 362                 //otherwise we can just call the SP without params
 363             else 
 364             {
 365                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
 366             }
 367         }
 368 
 369         #endregion ExecuteNonQuery
 370 
 371         #region ExecuteDataSet
 372 
 373         /// <summary>
 374         /// Execute an OracleCommand (that returns a resultset and takes no parameters) against the database specified in 
 375         /// the connection string. 
 376         /// </summary>
 377         /// <remarks>
 378         /// e.g.:  
 379         ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
 380         /// </remarks>
 381         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 382         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 383         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 384         /// <returns>a dataset containing the resultset generated by the command</returns>
 385         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
 386         {
 387             //pass through the call providing null for the set of OracleParameters
 388             return ExecuteDataset(connectionString, commandType, commandText, (OracleParameter[])null);
 389         }
 390 
 391         /// <summary>
 392         /// Execute an OracleCommand (that returns a resultset) against the database specified in the connection string 
 393         /// using the provided parameters.
 394         /// </summary>
 395         /// <remarks>
 396         /// e.g.:  
 397         ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 398         /// </remarks>
 399         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 400         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 401         /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
 402         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 403         /// <returns>a dataset containing the resultset generated by the command</returns>
 404         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 405         {
 406             //create & open an OracleConnection, and dispose of it after we are done.
 407             using (OracleConnection cn = new OracleConnection(connectionString))
 408             {
 409                 cn.Open();
 410 
 411                 //call the overload that takes a connection in place of the connection string
 412                 return ExecuteDataset(cn, commandType, commandText, commandParameters);
 413             }
 414         }
 415 
 416         /// <summary>
 417         /// Execute a stored procedure via an OracleCommand (that returns a resultset) against the database specified in 
 418         /// the conneciton string using the provided parameter values.  This method will query the database to discover the parameters for the 
 419         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 420         /// </summary>
 421         /// <remarks>
 422         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 423         /// 
 424         /// e.g.:  
 425         ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
 426         /// </remarks>
 427         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 428         /// <param name="spName">the name of the stored procedure</param>
 429         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 430         /// <returns>a dataset containing the resultset generated by the command</returns>
 431         public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
 432         {
 433             //if we got parameter values, we need to figure out where they go
 434             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 435             {
 436                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
 437                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
 438 
 439                 //assign the provided values to these parameters based on parameter order
 440                 AssignParameterValues(commandParameters, parameterValues);
 441 
 442                 //call the overload that takes an array of OracleParameters
 443                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 444             }
 445                 //otherwise we can just call the SP without params
 446             else 
 447             {
 448                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
 449             }
 450         }
 451 
 452         /// <summary>
 453         /// Execute an OracleCommand (that returns a resultset and takes no parameters) against the provided OracleConnection. 
 454         /// </summary>
 455         /// <remarks>
 456         /// e.g.:  
 457         ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
 458         /// </remarks>
 459         /// <param name="connection">a valid OracleConnection</param>
 460         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 461         /// <param name="commandText">the stored procedure name or PL/SQL command</param>
 462         /// <returns>a dataset containing the resultset generated by the command</returns>
 463         public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText)
 464         {
 465             //pass through the call providing null for the set of OracleParameters
 466             return ExecuteDataset(connection, commandType, commandText, (OracleParameter[])null);
 467         }
 468         
 469         /// <summary>
 470         /// Execute an OracleCommand (that returns a resultset) against the specified OracleConnection 
 471         /// using the provided parameters.
 472         /// </summary>
 473         /// <remarks>
 474         /// e.g.:  
 475         ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 476         /// </remarks>
 477         /// <param name="connection">a valid OracleConnection</param>
 478         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 479         /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
 480         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 481         /// <returns>a dataset containing the resultset generated by the command</returns>
 482         public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 483         {
 484             //create a command and prepare it for execution
 485             OracleCommand cmd = new OracleCommand();
 486             PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
 487             
 488             //create the DataAdapter & DataSet
 489             OracleDataAdapter da = new OracleDataAdapter(cmd);
 490             DataSet ds = new DataSet();
 491 
 492             //fill the DataSet using default values for DataTable names, etc.
 493             da.Fill(ds);
 494             
 495             //return the dataset
 496             return ds;                        
 497         }
 498         
 499         /// <summary>
 500         /// Execute a stored procedure via an OracleCommand (that returns a resultset) against the specified OracleConnection 
 501         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
 502         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 503         /// </summary>
 504         /// <remarks>
 505         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 506         /// 
 507         /// e.g.:  
 508         ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
 509         /// </remarks>
 510         /// <param name="connection">a valid OracleConnection</param>
 511         /// <param name="spName">the name of the stored procedure</param>
 512         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 513         /// <returns>a dataset containing the resultset generated by the command</returns>
 514         public static DataSet ExecuteDataset(OracleConnection connection, string spName, params object[] parameterValues)
 515         {
 516             //if we got parameter values, we need to figure out where they go
 517             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 518             {
 519                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 520                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
 521 
 522                 //assign the provided values to these parameters based on parameter order
 523                 AssignParameterValues(commandParameters, parameterValues);
 524 
 525                 //call the overload that takes an array of OracleParameters
 526                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
 527             }
 528                 //otherwise we can just call the SP without params
 529             else 
 530             {
 531                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
 532             }
 533         }
 534 
 535         /// <summary>
 536         /// Execute an OracleCommand (that returns a resultset and takes no parameters) against the provided OracleTransaction. 
 537         /// </summary>
 538         /// <remarks>
 539         /// e.g.:  
 540         ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
 541         /// </remarks>
 542         /// <param name="transaction">a valid OracleTransaction</param>
 543         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 544         /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
 545         /// <returns>a dataset containing the resultset generated by the command</returns>
 546         public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText)
 547         {
 548             //pass through the call providing null for the set of OracleParameters
 549             return ExecuteDataset(transaction, commandType, commandText, (OracleParameter[])null);
 550         }
 551         
 552         /// <summary>
 553         /// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
 554         /// using the provided parameters.
 555         /// </summary>
 556         /// <remarks>
 557         /// e.g.:  
 558         ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 559         /// </remarks>
 560         /// <param name="transaction">a valid OracleTransaction</param>
 561         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 562         /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
 563         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 564         /// <returns>a dataset containing the resultset generated by the command</returns>
 565         public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 566         {
 567             //create a command and prepare it for execution
 568             OracleCommand cmd = new OracleCommand();
 569             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
 570             
 571             //create the DataAdapter & DataSet
 572             OracleDataAdapter da = new OracleDataAdapter(cmd);
 573             DataSet ds = new DataSet();
 574 
 575             //fill the DataSet using default values for DataTable names, etc.
 576             da.Fill(ds);
 577             
 578             //return the dataset
 579             return ds;
 580         }
 581         
 582         /// <summary>
 583         /// Execute a stored procedure via an OracleCommand (that returns a resultset) against the specified 
 584         /// OracleTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
 585         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 586         /// </summary>
 587         /// <remarks>
 588         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 589         /// 
 590         /// e.g.:  
 591         ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
 592         /// </remarks>
 593         /// <param name="transaction">a valid OracleTransaction</param>
 594         /// <param name="spName">the name of the stored procedure</param>
 595         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 596         /// <returns>a dataset containing the resultset generated by the command</returns>
 597         public static DataSet ExecuteDataset(OracleTransaction transaction, string spName, params object[] parameterValues)
 598         {
 599             //if we got parameter values, we need to figure out where they go
 600             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 601             {
 602                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 603                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
 604 
 605                 //assign the provided values to these parameters based on parameter order
 606                 AssignParameterValues(commandParameters, parameterValues);
 607 
 608                 //call the overload that takes an array of OracleParameters
 609                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
 610             }
 611                 //otherwise we can just call the SP without params
 612             else 
 613             {
 614                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
 615             }
 616         }
 617 
 618         #endregion ExecuteDataSet
 619         
 620         #region ExecuteReader
 621 
 622         /// <summary>
 623         /// this enum is used to indicate weather the connection was provided by the caller, or created by OracleHelper, so that
 624         /// we can set the appropriate CommandBehavior when calling ExecuteReader()
 625         /// </summary>
 626         private enum OracleConnectionOwnership    
 627         {
 628             /// <summary>Connection is owned and managed by OracleHelper</summary>
 629             Internal, 
 630             /// <summary>Connection is owned and managed by the caller</summary>
 631             External
 632         }
 633 
 634 
 635         /// <summary>
 636         /// Create and prepare an OracleCommand, and call ExecuteReader with the appropriate CommandBehavior.
 637         /// </summary>
 638         /// <remarks>
 639         /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
 640         /// 
 641         /// If the caller provided the connection, we want to leave it to them to manage.
 642         /// </remarks>
 643         /// <param name="connection">a valid OracleConnection, on which to execute this command</param>
 644         /// <param name="transaction">a valid OracleTransaction, or 'null'</param>
 645         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 646         /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
 647         /// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
 648         /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleHelper</param>
 649         /// <returns>OracleDataReader containing the results of the command</returns>
 650         private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
 651         {    
 652             //create a command and prepare it for execution
 653             OracleCommand cmd = new OracleCommand();
 654             PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
 655             
 656             //create a reader
 657             OracleDataReader dr;
 658 
 659             // call ExecuteReader with the appropriate CommandBehavior
 660             if (connectionOwnership == OracleConnectionOwnership.External)
 661             {
 662                 dr = cmd.ExecuteReader();
 663             }
 664             else
 665             {
 666                 dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
 667             }
 668 
 669             return (OracleDataReader) dr;
 670         }
 671 
 672         /// <summary>
 673         /// Execute an OracleCommand (that returns a resultset and takes no parameters) against the database specified in 
 674         /// the connection string. 
 675         /// </summary>
 676         /// <remarks>
 677         /// e.g.:  
 678         ///  OracleDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
 679         /// </remarks>
 680         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 681         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 682         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 683         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 684         public static OracleDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
 685         {
 686             //pass through the call providing null for the set of OracleParameters
 687             return ExecuteReader(connectionString, commandType, commandText, (OracleParameter[])null);
 688         }
 689 
 690         /// <summary>
 691         /// Execute an OracleCommand (that returns a resultset) against the database specified in the connection string 
 692         /// using the provided parameters.
 693         /// </summary>
 694         /// <remarks>
 695         /// e.g.:  
 696         ///  OracleDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 697         /// </remarks>
 698         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 699         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 700         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 701         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 702         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 703         public static OracleDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 704         {
 705             //create & open an OraclebConnection
 706             OracleConnection cn = new OracleConnection(connectionString);
 707             cn.Open();
 708 
 709             try
 710             {
 711                 //call the private overload that takes an internally owned connection in place of the connection string
 712                 return ExecuteReader(cn, null, commandType, commandText, commandParameters, OracleConnectionOwnership.Internal);
 713             }
 714             catch
 715             {
 716                 //if we fail to return the OracleDataReader, we need to close the connection ourselves
 717                 cn.Close();
 718                 throw;
 719             }
 720         }
 721 
 722         /// <summary>
 723         /// Execute a stored procedure via an OracleCommand (that returns a resultset) against the database specified in 
 724         /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
 725         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 726         /// </summary>
 727         /// <remarks>
 728         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 729         /// 
 730         /// e.g.:  
 731         ///  OracleDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
 732         /// </remarks>
 733         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 734         /// <param name="spName">the name of the stored procedure</param>
 735         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 736         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 737         public static OracleDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
 738         {
 739             //if we got parameter values, we need to figure out where they go
 740             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 741             {
 742                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 743                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
 744 
 745                 //assign the provided values to these parameters based on parameter order
 746                 AssignParameterValues(commandParameters, parameterValues);
 747 
 748                 //call the overload that takes an array of OracleParameters
 749                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 750             }
 751             //otherwise we can just call the SP without params
 752             else 
 753             {
 754                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
 755             }
 756         }
 757 
 758         /// <summary>
 759         /// Execute an OracleCommand (that returns a resultset and takes no parameters) against the provided OracleConnection. 
 760         /// </summary>
 761         /// <remarks>
 762         /// e.g.:  
 763         ///  OracleDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
 764         /// </remarks>
 765         /// <param name="connection">a valid OracleConnection</param>
 766         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 767         /// <param name="commandText">the stored procedure name or PL/SQL command</param>
 768         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 769         public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText)
 770         {
 771             //pass through the call providing null for the set of OracleParameters
 772             return ExecuteReader(connection, commandType, commandText, (OracleParameter[])null);
 773         }
 774 
 775         /// <summary>
 776         /// Execute an OracleCommand (that returns a resultset) against the specified OracleConnection 
 777         /// using the provided parameters.
 778         /// </summary>
 779         /// <remarks>
 780         /// e.g.:  
 781         ///  OracleDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 782         /// </remarks>
 783         /// <param name="connection">a valid OracleConnection</param>
 784         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 785         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 786         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 787         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 788         public static OracleDataReader ExecuteReader(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 789         {
 790             //pass through the call to the private overload using a null transaction value and an externally owned connection
 791             return ExecuteReader(connection, (OracleTransaction)null, commandType, commandText, commandParameters, OracleConnectionOwnership.External);
 792         }
 793 
 794         /// <summary>
 795         /// Execute a stored procedure via an OracleCommand (that returns a resultset) against the specified OracleConnection 
 796         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
 797         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 798         /// </summary>
 799         /// <remarks>
 800         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 801         /// 
 802         /// e.g.:  
 803         ///  OracleDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
 804         /// </remarks>
 805         /// <param name="connection">a valid OracleConnection</param>
 806         /// <param name="spName">the name of the stored procedure</param>
 807         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 808         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 809         public static OracleDataReader ExecuteReader(OracleConnection connection, string spName, params object[] parameterValues)
 810         {
 811             //if we got parameter values, we need to figure out where they go
 812             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 813             {
 814                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
 815 
 816                 AssignParameterValues(commandParameters, parameterValues);
 817 
 818                 return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
 819             }
 820             //otherwise we can just call the SP without params
 821             else 
 822             {
 823                 return ExecuteReader(connection, CommandType.StoredProcedure, spName);
 824             }
 825         }
 826 
 827         /// <summary>
 828         /// Execute an OracleCommand (that returns a resultset and takes no parameters) against the provided OracleTransaction. 
 829         /// </summary>
 830         /// <remarks>
 831         /// e.g.:  
 832         ///  OracleDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
 833         /// </remarks>
 834         /// <param name="transaction">a valid OracleTransaction</param>
 835         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 836         /// <param name="commandText">the stored procedure name or PL/SQL command</param>  
 837         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 838         public static OracleDataReader ExecuteReader(OracleTransaction transaction, CommandType commandType, string commandText)
 839         {
 840             //pass through the call providing null for the set of OracleParameters
 841             return ExecuteReader(transaction, commandType, commandText, (OracleParameter[])null);
 842         }
 843 
 844         /// <summary>
 845         /// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
 846         /// using the provided parameters.
 847         /// </summary>
 848         /// <remarks>
 849         /// e.g.:  
 850         ///   OracleDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 851         /// </remarks>
 852         /// <param name="transaction">a valid OracleTransaction</param>
 853         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 854         /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
 855         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 856         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 857         public static OracleDataReader ExecuteReader(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 858         {
 859             //pass through to private overload, indicating that the connection is owned by the caller
 860             return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, OracleConnectionOwnership.External);
 861         }
 862 
 863         /// <summary>
 864         /// Execute a stored procedure via an OracleCommand (that returns a resultset) against the specified
 865         /// OracleTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
 866         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 867         /// </summary>
 868         /// <remarks>
 869         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 870         /// 
 871         /// e.g.:  
 872         ///  OracleDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
 873         /// </remarks>
 874         /// <param name="transaction">a valid OracleTransaction</param>
 875         /// <param name="spName">the name of the stored procedure</param>
 876         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 877         /// <returns>an OracleDataReader containing the resultset generated by the command</returns>
 878         public static OracleDataReader ExecuteReader(OracleTransaction transaction, string spName, params object[] parameterValues)
 879         {
 880             //if we got parameter values, we need to figure out where they go
 881             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 882             {
 883                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
 884 
 885                 AssignParameterValues(commandParameters, parameterValues);
 886 
 887                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
 888             }
 889                 //otherwise we can just call the SP without params
 890             else 
 891             {
 892                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
 893             }
 894         }
 895 
 896         #endregion ExecuteReader
 897 
 898         #region ExecuteScalar
 899         
 900         /// <summary>
 901         /// Execute an OracleCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in 
 902         /// the connection string. 
 903         /// </summary>
 904         /// <remarks>
 905         /// e.g.:  
 906         ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
 907         /// </remarks>
 908         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 909         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 910         /// <param name="commandText">the stored procedure name or T-Oracle command</param>
 911         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
 912         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
 913         {
 914             //pass through the call providing null for the set of OracleParameters
 915             return ExecuteScalar(connectionString, commandType, commandText, (OracleParameter[])null);
 916         }
 917 
 918         /// <summary>
 919         /// Execute an OracleCommand (that returns a 1x1 resultset) against the database specified in the connection string 
 920         /// using the provided parameters.
 921         /// </summary>
 922         /// <remarks>
 923         /// e.g.:  
 924         ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
 925         /// </remarks>
 926         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 927         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 928         /// <param name="commandText">the stored procedure name or T-Oracle command</param>
 929         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
 930         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
 931         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 932         {
 933             //create & open an OracleConnection, and dispose of it after we are done.
 934             using (OracleConnection cn = new OracleConnection(connectionString))
 935             {
 936                 cn.Open();
 937 
 938                 //call the overload that takes a connection in place of the connection string
 939                 return ExecuteScalar(cn, commandType, commandText, commandParameters);
 940             }
 941         }
 942 
 943         /// <summary>
 944         /// Execute a stored procedure via an OracleCommand (that returns a 1x1 resultset) against the database specified in 
 945         /// the conneciton string using the provided parameter values.  This method will query the database to discover the parameters for the 
 946         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 947         /// </summary>
 948         /// <remarks>
 949         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 950         /// 
 951         /// e.g.:  
 952         ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
 953         /// </remarks>
 954         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
 955         /// <param name="spName">the name of the stored procedure</param>
 956         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
 957         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
 958         public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
 959         {
 960             //if we got parameter values, we need to figure out where they go
 961             if ((parameterValues != null) && (parameterValues.Length > 0)) 
 962             {
 963                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
 964                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connectionString, spName);
 965 
 966                 //assign the provided values to these parameters based on parameter order
 967                 AssignParameterValues(commandParameters, parameterValues);
 968 
 969                 //call the overload that takes an array of OracleParameters
 970                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 971             }
 972                 //otherwise we can just call the SP without params
 973             else 
 974             {
 975                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
 976             }
 977         }
 978 
 979         /// <summary>
 980         /// Execute an OracleCommand (that returns a 1x1 resultset and takes no parameters) against the provided OracleConnection. 
 981         /// </summary>
 982         /// <remarks>
 983         /// e.g.:  
 984         ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
 985         /// </remarks>
 986         /// <param name="connection">a valid OracleConnection</param>
 987         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 988         /// <param name="commandText">the stored procedure name or T-Oracle command</param>
 989         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
 990         public static object ExecuteScalar(OracleConnection connection, CommandType commandType, string commandText)
 991         {
 992             //pass through the call providing null for the set of OracleParameters
 993             return ExecuteScalar(connection, commandType, commandText, (OracleParameter[])null);
 994         }
 995 
 996         /// <summary>
 997         /// Execute an OracleCommand (that returns a 1x1 resultset) against the specified OracleConnection 
 998         /// using the provided parameters.
 999         /// </summary>
1000         /// <remarks>
1001         /// e.g.:  
1002         ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
1003         /// </remarks>
1004         /// <param name="connection">a valid OracleConnection</param>
1005         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1006         /// <param name="commandText">the stored procedure name or T-OleDb command</param>
1007         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
1008         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1009         public static object ExecuteScalar(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
1010         {
1011             //create a command and prepare it for execution
1012             OracleCommand cmd = new OracleCommand();
1013             PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters);
1014             
1015             //execute the command & return the results
1016             return cmd.ExecuteScalar();
1017         }
1018 
1019         /// <summary>
1020         /// Execute a stored procedure via an OracleCommand (that returns a 1x1 resultset) against the specified OracleConnection 
1021         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
1022         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1023         /// </summary>
1024         /// <remarks>
1025         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1026         /// 
1027         /// e.g.:  
1028         ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
1029         /// </remarks>
1030         /// <param name="connection">a valid OracleConnection</param>
1031         /// <param name="spName">the name of the stored procedure</param>
1032         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
1033         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1034         public static object ExecuteScalar(OracleConnection connection, string spName, params object[] parameterValues)
1035         {
1036             //if we got parameter values, we need to figure out where they go
1037             if ((parameterValues != null) && (parameterValues.Length > 0)) 
1038             {
1039                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
1040                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
1041 
1042                 //assign the provided values to these parameters based on parameter order
1043                 AssignParameterValues(commandParameters, parameterValues);
1044 
1045                 //call the overload that takes an array of OracleParameters
1046                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
1047             }
1048                 //otherwise we can just call the SP without params
1049             else 
1050             {
1051                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
1052             }
1053         }
1054 
1055         /// <summary>
1056         /// Execute an OracleCommand (that returns a 1x1 resultset and takes no parameters) against the provided OracleTransaction. 
1057         /// </summary>
1058         /// <remarks>
1059         /// e.g.:  
1060         ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
1061         /// </remarks>
1062         /// <param name="transaction">a valid OracleTransaction</param>
1063         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1064         /// <param name="commandText">the stored procedure name or T-OleDb command</param>
1065         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1066         public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText)
1067         {
1068             //pass through the call providing null for the set of OracleParameters
1069             return ExecuteScalar(transaction, commandType, commandText, (OracleParameter[])null);
1070         }
1071 
1072         /// <summary>
1073         /// Execute an OracleCommand (that returns a 1x1 resultset) against the specified OracleTransaction
1074         /// using the provided parameters.
1075         /// </summary>
1076         /// <remarks>
1077         /// e.g.:  
1078         ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
1079         /// </remarks>
1080         /// <param name="transaction">a valid OracleTransaction</param>
1081         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1082         /// <param name="commandText">the stored procedure name or T-OleDb command</param>
1083         /// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
1084         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1085         public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
1086         {
1087             //create a command and prepare it for execution
1088             OracleCommand cmd = new OracleCommand();
1089             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
1090             
1091             //execute the command & return the results
1092             return cmd.ExecuteScalar();
1093 
1094         }
1095 
1096         /// <summary>
1097         /// Execute a stored procedure via an OracleCommand (that returns a 1x1 resultset) against the specified
1098         /// OracleTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
1099         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1100         /// </summary>
1101         /// <remarks>
1102         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1103         /// 
1104         /// e.g.:  
1105         ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
1106         /// </remarks>
1107         /// <param name="transaction">a valid OracleTransaction</param>
1108         /// <param name="spName">the name of the stored procedure</param>
1109         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
1110         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1111         public static object ExecuteScalar(OracleTransaction transaction, string spName, params object[] parameterValues)
1112         {
1113             //if we got parameter values, we need to figure out where they go
1114             if ((parameterValues != null) && (parameterValues.Length > 0)) 
1115             {
1116                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
1117                 OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
1118 
1119                 //assign the provided values to these parameters based on parameter order
1120                 AssignParameterValues(commandParameters, parameterValues);
1121 
1122                 //call the overload that takes an array of OracleParameters
1123                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
1124             }
1125                 //otherwise we can just call the SP without params
1126             else 
1127             {
1128                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
1129             }
1130         }
1131 
1132         #endregion ExecuteScalar
1133     }
1134 
1135     /// <summary>
1136     /// OracleHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
1137     /// ability to discover parameters for stored procedures at run-time.
1138     /// </summary>
1139     public sealed class OracleHelperParameterCache
1140     {
1141         #region private methods, variables, and constructors
1142 
1143         //Since this class provides only static methods, make the default constructor private to prevent 
1144         //instances from being created with "new OracleHelperParameterCache()".
1145         private OracleHelperParameterCache() {}
1146 
1147         private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
1148 
1149         /// <summary>
1150         /// resolve at run-time the appropriate set of OracleParameters for a stored procedure
1151         /// </summary>
1152         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
1153         /// <param name="spName">the name of the stored procedure</param>
1154         /// <param name="includeReturnValueParameter">whether or not to include ther return value parameter</param>
1155         /// <returns></returns>
1156         private static OracleParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
1157         {
1158             using (OracleConnection cn = new OracleConnection(connectionString)) 
1159             using (OracleCommand cmd = new OracleCommand(spName,cn))
1160             {
1161                 cn.Open();
1162                 cmd.CommandType = CommandType.StoredProcedure;
1163 
1164                 OracleCommandBuilder.DeriveParameters(cmd);
1165 
1166                 if (!includeReturnValueParameter) 
1167                 {
1168                     if (ParameterDirection.ReturnValue == cmd.Parameters[0].Direction)                
1169                         cmd.Parameters.RemoveAt(0);                                    
1170                 }
1171             
1172                 OracleParameter[] discoveredParameters = new OracleParameter[cmd.Parameters.Count];
1173 
1174                 cmd.Parameters.CopyTo(discoveredParameters, 0);
1175 
1176                 return discoveredParameters;
1177             }
1178         }
1179 
1180         //deep copy of cached OracleParameter array
1181         private static OracleParameter[] CloneParameters(OracleParameter[] originalParameters)
1182         {
1183             OracleParameter[] clonedParameters = new OracleParameter[originalParameters.Length];
1184 
1185             for (int i = 0, j = originalParameters.Length; i < j; i++)
1186             {
1187                 clonedParameters[i] = (OracleParameter)((ICloneable)originalParameters[i]).Clone();
1188             }
1189 
1190             return clonedParameters;
1191         }
1192 
1193         #endregion private methods, variables, and constructors
1194 
1195         #region caching functions
1196 
1197         /// <summary>
1198         /// add parameter array to the cache
1199         /// </summary>
1200         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
1201         /// <param name="commandText">the stored procedure name or T-OleDb command</param>
1202         /// <param name="commandParameters">an array of OracleParameters to be cached</param>
1203         public static void CacheParameterSet(string connectionString, string commandText, params OracleParameter[] commandParameters)
1204         {
1205             string hashKey = connectionString + ":" + commandText;
1206 
1207             paramCache[hashKey] = commandParameters;
1208         }
1209 
1210         /// <summary>
1211         /// retrieve a parameter array from the cache
1212         /// </summary>
1213         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
1214         /// <param name="commandText">the stored procedure name or T-OleDb command</param>
1215         /// <returns>an array of OracleParameters</returns>
1216         public static OracleParameter[] GetCachedParameterSet(string connectionString, string commandText)
1217         {
1218             string hashKey = connectionString + ":" + commandText;
1219 
1220             OracleParameter[] cachedParameters = (OracleParameter[])paramCache[hashKey];
1221             
1222             if (cachedParameters == null)
1223             {            
1224                 return null;
1225             }
1226             else
1227             {
1228                 return CloneParameters(cachedParameters);
1229             }
1230         }
1231 
1232         #endregion caching functions
1233 
1234         #region Parameter Discovery Functions
1235 
1236         /// <summary>
1237         /// Retrieves the set of OracleParameters appropriate for the stored procedure
1238         /// </summary>
1239         /// <remarks>
1240         /// This method will query the database for this information, and then store it in a cache for future requests.
1241         /// </remarks>
1242         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
1243         /// <param name="spName">the name of the stored procedure</param>
1244         /// <returns>an array of OracleParameters</returns>
1245         public static OracleParameter[] GetSpParameterSet(string connectionString, string spName)
1246         {
1247             return GetSpParameterSet(connectionString, spName, false);
1248         }
1249 
1250         /// <summary>
1251         /// Retrieves the set of OracleParameters appropriate for the stored procedure
1252         /// </summary>
1253         /// <remarks>
1254         /// This method will query the database for this information, and then store it in a cache for future requests.
1255         /// </remarks>
1256         /// <param name="connectionString">a valid connection string for an OracleConnection</param>
1257         /// <param name="spName">the name of the stored procedure</param>
1258         /// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param>
1259         /// <returns>an array of OracleParameters</returns>
1260         public static OracleParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
1261         {
1262             string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":"");
1263 
1264             OracleParameter[] cachedParameters;
1265             
1266             cachedParameters = (OracleParameter[])paramCache[hashKey];
1267 
1268             if (cachedParameters == null)
1269             {            
1270                 cachedParameters = (OracleParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
1271             }
1272             
1273             return CloneParameters(cachedParameters);
1274         }
1275 
1276         #endregion Parameter Discovery Functions
1277 
1278     }
1279 }