Mega Code Archive
Static Helper for executing SQL statements against Oracle Requires the ODP NET provider
<![CDATA[
//---------------------------------------------------------------------
// 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
}
}
]]>