Microsoft Enterprise Library Data Access Block [DAAB] on Oracle Provider [ODP.NET]

using System.Collections.Generic;
using System;
using System.Data;
using System.Globalization;
using System.Text;
using System.Xml;
using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle.Configuration;

namespace DataAccessQuickStart
{
    class OracleData
    {        
        /// Demonstrates retrieving multiple rows of data using a DataReader
        public string GetCustomerList()
        {
            // DataReader that will hold the returned results		
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.

            Database db = DatabaseFactory.CreateDatabase("OracleSource");

            string sqlCommand = @"Select    DAABCUSTOMERID CustomerID, 
                                            DAABCONTACTNAME Name, 
                                            DAABADDRESS Address, 
                                            DAABCITY City, 
                                            DAABCOUNTRY Country, 
                                            DAABPOSTCODE PostalCode 
                                From DAABCUSTOMER";
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

            StringBuilder readerData = new StringBuilder();

            // The ExecuteReader call will request the connection to be closed upon
            // the closing of the DataReader. The DataReader will be closed 
            // automatically when it is disposed.
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                // Iterate through DataReader and put results to the text box.
                // DataReaders cannot be bound to Windows Form controls (e.g. the
                // resultsDataGrid), but may be bound to Web Form controls.
                while (dataReader.Read())
                {
                    // Get the value of the 'Name' column in the DataReader
                    readerData.Append(dataReader["Name"]);
                    readerData.Append(Environment.NewLine);
                }
            }

            return readerData.ToString();
        }

        //Demonstrates retrieving multiple rows using a DataSet.
        public DataSet GetCustomerInPostcode(string PostCode)
        {
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.            
            Database db = DatabaseFactory.CreateDatabase("OracleSource");

            string sqlCommand = "PKG_DAABCUSTOMER.P_DAABCustomerByPostcode";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            
            // Retrieve products from the specified category.
            db.AddInParameter(dbCommand, "DAABPOSTCODE", DbType.String, PostCode);                        
            db.AddCursorOutParameter(dbCommand, "O_Rts");

            // DataSet that will hold the returned results		
            DataSet productsDataSet = null;

            productsDataSet = db.ExecuteDataSet(dbCommand);
            // Note: connection was closed by ExecuteDataSet method call 
            return productsDataSet;
        }
        
        //Demonstrates retrieving a single row of data using output parameters.</remarks>
        public string GetCustomerDetails(int customerID)
        {
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase("OracleSource");
            
            string sqlCommand = "PKG_DAABCUSTOMER.P_DAABCustomerDetails";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

            // Add paramters
            // Input parameters can specify the input value
            db.AddInParameter(dbCommand, "I_Reference", DbType.Int32, customerID);
            
            // Output parameters specify the size of the return data
            db.AddOutParameter(dbCommand, "O_CustomerID", DbType.Int32, 8);
            db.AddOutParameter(dbCommand, "O_CustomerName", DbType.String, 100);
            
            db.ExecuteNonQuery(dbCommand);

            // Row of data is captured via output parameters
            string results = string.Format(CultureInfo.CurrentCulture, "{0}, {1}, {2:C} ",
                                           db.GetParameterValue(dbCommand, "I_Reference"),
                                           db.GetParameterValue(dbCommand, "O_CustomerName"),
                                           db.GetParameterValue(dbCommand, "O_CustomerID"));

            return results;
        }


        //Demonstrates retrieving a single item. Parameter discovery
        /// is used for determining the properties of the productID parameter.</remarks>
        public string GetCustomerName(int customerID)
        {
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase("OracleSource");
            //OracleDatabase db = new OracleDatabase("USER ID=SSM_CS;PASSWORD=SSM_CS;DATA SOURCE=NTDEV1_APPS.WORLD");

            // Passing the productID value to the GetStoredProcCommand
            // results in parameter discovery being used to correctly establish the parameter
            // information for the productID. Subsequent calls to this method will
            // cause the block to retrieve the parameter information from the 
            // cache, and not require rediscovery.
            //string sqlCommand = "GetProductName";
            string sqlCommand = "PKG_DAABCUSTOMER.P_DAABCustomerName";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "I_Reference", DbType.Int32, customerID);
            db.AddOutParameter(dbCommand, "O_CustomerName", DbType.String, 100);

            db.ExecuteNonQuery(dbCommand);
            // Retrieve ProdcutName. ExecuteScalar returns an object, so
            // we cast to the correct type (string).
            //string productName = (string)db.ExecuteScalar(dbCommand);

            //string customerName = System.Convert.ToString( db.GetParameterValue(dbCommand, "O_CustomerName") );

            string results = string.Format(CultureInfo.CurrentCulture, "{0} ",                                           
                                           db.GetParameterValue(dbCommand, "O_CustomerName")
                                           );

            return results;
        }


        //Populate DataSet with Multiple REF Cursors Sample
        public DataSet GetMultipleTables(string PostCode)
        {
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.            
            Database db = DatabaseFactory.CreateDatabase("OracleSource");

            string sqlCommand = "PKG_DAABCUSTOMER.P_DAABMultipleCursor";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            
            // Retrieve products from the specified category.
            db.AddInParameter(dbCommand, "DAABPOSTCODE", DbType.String, PostCode);
            db.AddCursorOutParameter(dbCommand, "O_PostcodePresent");
            db.AddCursorOutParameter(dbCommand, "O_PostcodeNotPresent");

            // DataSet that will hold the returned results		
            DataSet custDataSet = null;

            custDataSet = db.ExecuteDataSet(dbCommand);

            //DataTable with Postcode
            DataTable dtWithPC = custDataSet.Tables[0];
            DataTable dtWithoutPC = custDataSet.Tables[1];


            // ** Use the datatables above

            


            //custDataSet = null;

            //DataSet oDataSet = new DataSet();
            //oDataSet.Tables.Add(dtWithPC);
            //oDataSet.Merge(dtWithoutPC); 


            // Note: connection was closed by ExecuteDataSet method call 
            return oDataSet;
        }


        

        
               

    }
}