//--------------------------------------------------------------------- // 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 } }