Static Helper for executing SQL statements against Oracle Requires the ODP.NET provider : Oracle « Database ADO.net « C# / C Sharp






Static Helper for executing SQL statements against Oracle Requires the ODP.NET provider

 
  
//---------------------------------------------------------------------
// File: OracleDatabaseHelperEx.cs
// 
// Summary: 
//
// Copyright (c) Hammersmith & Fulham Bridge Partnership. All rights reserved.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
// KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
// PURPOSE.
//---------------------------------------------------------------------

using System;
using System.Data;
using System.Data.OracleClient;

namespace BizUnit.Extensions.Utilities
{
  /// <summary>
  /// Static Helper for executing SQL statements against Oracle
  /// Requires the ODP.NET provider
  /// </summary>
  public class OracleDatabaseHelperEx
  {
        #region constructor(s)
        /// <summary>
        /// Constructor for class, default constructor is private to prevent instances being
        /// created as the class only has static methods
        /// </summary>
        public OracleDatabaseHelperEx()
    {
    }
        #endregion

        #region Static Methods
        /// <summary>
        /// Excecutes the SQL statement against the database and returns a DataSet with the results
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>DataSet with the results of the executed command</returns>
        public DataSet ExecuteSqlCommand( string connectionString, string sqlCommand )
        {
            DataSet ds = new DataSet() ;
      try
      {
        using ( OracleConnection connection = new OracleConnection( connectionString ) )
        {
          OracleDataAdapter adapter = new OracleDataAdapter( sqlCommand, connection ) ;
          adapter.Fill( ds ) ;
        }   // connection
      }
      catch (Exception)
      {
        throw ;
      }
            return ds ;
        }

        /// <summary>
        /// Executes the SQL statement and returns the first column of the first row in the resultset returned by the query.
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>The contents of the first column of the first row in the resultset</returns>
        public int ExecuteScalar( string connectionString, string sqlCommand )
        {
            OracleConnection connection = null ;
            object col = 0 ;

            try 
            {
                connection = new OracleConnection( connectionString ) ;
                OracleCommand command = new OracleCommand( sqlCommand, connection ) ;
                command.Connection.Open() ;
                col = command.ExecuteScalar() ;
            }
            catch ( Exception )
            {
        throw;
            }
            finally 
            {
                connection.Close() ;
            }

            return Convert.ToInt32( col ) ;
        }

        /// <summary>
        /// Executes the SQL statement
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        public void ExecuteNonQuery( string connectionString, string sqlCommand )
        {
            OracleConnection connection = null ;

            try 
            {
                connection = new OracleConnection( connectionString ) ;
                OracleCommand command = new OracleCommand( sqlCommand, connection ) ;
                command.Connection.Open() ;
                command.ExecuteNonQuery() ;
            }
            catch ( Exception )
            {
        throw;
            }
            finally 
            {
                connection.Close() ;
            }
        }
        #endregion
  }
}

   
  








Related examples in the same category

1.How to use an OleDbConnection object to connect to an Oracle database
2.Oracle connection string for C#
3.Connect to an Oracle server