De: jhansen chavez mestanza - PERU
Hola gente de foros del web necesito un sqlhelper para oracle o en todo caso si pueden enviarme un pequeño demo de una aplicacion de c# con oracle, buscando por la red encontre un este codigo pero no pasa nada. a ver si me pueden ayudar con eso GRACIAS.
using System;
using System.Text;
using System.Data.OracleClient;
using System.Data.SqlTypes;
using System.Data;
using System.IO;
using System.Collections;
namespace AddGroupRateFromStaging
{
/// <summary>
/// OracleHelper.cs
/// This class will facilitate execution of Oracle methods
///
/// Usage:
/// OpenConnection
/// CloseConnection
/// ExecuteDataset
/// ExecuteNonDataset
///
///
/// Revision History:
/// </summary>
public class OracleHelper
{
private string _strconn = null;
private OracleConnection orconn = null;
private Hashtable _oraclecommands = new Hashtable();
/// <summary>
/// Constructor method, overloaded to create the Oracle connection string
/// </summary>
/// <param name="_puserid">Oracle user id</param>
/// <param name="_ppassword">Oracle password</param>
/// <param name="_pdatasrc">Oracle Datasource,matches the TNS Entry</param>
public OracleHelper(string _puserid, string _ppassword, string _pdatasrc)
{
lock (this)
{
string connstr = null;
connstr = "user id=" + _puserid + ";";
connstr = connstr + "Password=" + _ppassword + ";";
connstr = connstr + "Data Source=" + _pdatasrc + ";";
this._strconn = connstr;
}
}
/// <summary>
/// Opens the Oracle connection using System.Data.OracleClient
/// </summary>
private void OpenConnection()
{
lock (this)
{
bool connstate = true;
//Check if connection is open/null
if (orconn != null)
{
//Check if not null but closed
if (orconn.State == ConnectionState.Closed)
{
connstate = false;
this.orconn = null;
}
}
else//null
connstate = false;
//Connection needs to be opened
if (connstate == false)
{
orconn = new OracleConnection(this._strconn);
orconn.Open();
}
}
}
/// <summary>
/// Executes stored procedure using oracle command and returns resultset
/// Used when there is a return resultset
/// Will open connection if it is null
/// </summary>
/// <param name="_pstrprocname">name of oracle stored proc</param>
/// <param name="_pcursor">array list containing names of ref cursors</param>
/// <returns>returns dataset that has the executed results</returns>
public DataSet ExecuteDataset(string _pstrprocname, ArrayList _pcursor)
{
lock (this)
{
DataSet _odsCursorset = null;
OracleCommand _cmdResultset = null;
OracleDataAdapter _odaFetch = null;
this.OpenConnection();
_cmdResultset = new OracleCommand();
_cmdResultset.CommandText = _pstrprocname;
_cmdResultset.CommandType = CommandType.StoredProcedure;
_cmdResultset.Connection = this.orconn;
for (int counter = 0; counter <= _pcursor.Count - 1; counter++)
_cmdResultset.Parameters.Add(_pcursor[counter].ToString(), OracleType.Cursor).Direction = ParameterDirection.Output;
_odaFetch = new OracleDataAdapter(_cmdResultset);
_odsCursorset = new DataSet();
_odaFetch.Fill(_odsCursorset);
for (int counter = 0; counter <= _odsCursorset.Tables.Count - 1; counter++)
_odsCursorset.Tables[counter].TableName = _pcursor[counter].ToString();
return _odsCursorset;
}
}
/// <summary>
/// Executes stored procedure using oracle command and returns rows affected
/// Used when there is a no return resultset
/// Will open connection if it is null
/// </summary>
/// <param name="_pstrprocname">name of oracle stored proc</param>
/// <returns>returns rows affected</returns>
public int ExecuteNonDataset(string _pstrprocname)
{
OracleCommand _cmdResultset = null;
int retval;
lock (this)
{
this.OpenConnection();
_cmdResultset = new OracleCommand();
_cmdResultset.CommandText = _pstrprocname;
_cmdResultset.CommandType = CommandType.StoredProcedure;
_cmdResultset.Connection = this.orconn;
retval = _cmdResultset.ExecuteNonQuery();
return retval;
}
}
/// <summary>
/// Executes stored procedure using oracle command and returns resultset
/// Used when there is a return resultset
/// Will open connection if it is null
/// </summary>
/// <param name="_pstrprocname">name of oracle stored proc</param>
/// <param name="_pcursor">array list containing names of ref cursors</param>
/// <returns>returns dataset that has the executed results</returns>
public int ExecuteNonDataset(string _pstrprocname, Hashtable _pcursor)
{
OracleCommand _cmdResultset = null;
lock (this)
{
if (_oraclecommands.Contains(_pstrprocname) == false)
{
this.OpenConnection();
_cmdResultset = new OracleCommand();
_cmdResultset.CommandText = _pstrprocname;
_cmdResultset.CommandType = CommandType.StoredProcedure;
_cmdResultset.Connection = this.orconn;
OracleCommandBuilder.DeriveParameters((OracleComma nd)_cmdResultset);
_oraclecommands.Add(_pstrprocname, _cmdResultset);
}
else
_cmdResultset = (OracleCommand)_oraclecommands[_pstrprocname];
foreach (OracleParameter orp in _cmdResultset.Parameters)
{
orp.Value = System.DBNull.Value;
if ((orp.Direction == ParameterDirection.Input || orp.Direction == ParameterDirection.InputOutput) && _pcursor.ContainsKey(orp.ParameterName.ToUpper()))
orp.Value = _pcursor[orp.ParameterName];
}
_cmdResultset.ExecuteNonQuery();
return Convert.ToInt32(_cmdResultset.Parameters["RETURN_VALUE"].Value);
}
}
/// <summary>
///
/// </summary>
/// <param name="_pstrprocname"></param>
/// <param name="_pcursor"></param>
/// <returns></returns>
public int ExecuteDataset(string _pstrprocname, ref Hashtable _pcursor, StreamWriter sw)
{
OracleCommand _cmdResultset = null;
lock (this)
{
if (_oraclecommands.Contains(_pstrprocname) == false)
{
this.OpenConnection();
_cmdResultset = new OracleCommand();
_cmdResultset.CommandText = _pstrprocname;
_cmdResultset.CommandType = CommandType.StoredProcedure;
_cmdResultset.Connection = this.orconn;
OracleCommandBuilder.DeriveParameters((OracleComma nd)_cmdResultset);
_oraclecommands.Add(_pstrprocname, _cmdResultset);
}
else
_cmdResultset = (OracleCommand)_oraclecommands[_pstrprocname];
foreach (OracleParameter orp in _cmdResultset.Parameters)
{
orp.Value = System.DBNull.Value;
if (orp.OracleType.ToString() == "Cursor")
orp.Direction = ParameterDirection.Output;
if ((orp.Direction == ParameterDirection.Input || orp.Direction == ParameterDirection.InputOutput) && _pcursor.ContainsKey(orp.ParameterName.ToUpper()))
orp.Value = _pcursor[orp.ParameterName];
}
//Execute the resultset
//this is the part ART Added
try
{
_cmdResultset.ExecuteNonQuery();
// oracleConn1.Open();
}
catch (OracleException ex)
{
// Console.WriteLine("Error in trying to add record: " + ex);
string soutput = "Error trying to add record: " + ex;
Printit.PrintToFile(sw, soutput);
_cmdResultset.Parameters["RETURN_VALUE"].Value = 0;
}
//this is the part ART Added
// _cmdResultset.ExecuteNonQuery();
//Fill out parameters
_pcursor.Clear();
foreach (OracleParameter orp in _cmdResultset.Parameters)
{
if (orp.Direction == ParameterDirection.Output || orp.Direction == ParameterDirection.InputOutput)
_pcursor.Add(orp.ParameterName, orp.Value);
}
//send back the return value from the function
return Convert.ToInt32(_cmdResultset.Parameters["RETURN_VALUE"].Value);
}
}
/// <summary>
///
/// </summary>
/// <param name="_pstrprocname"></param>
/// <param name="_pcursor"></param>
/// <returns></returns>
public OracleDataReader ExecuteTextDataset(string _pstrprocname)
{
OracleCommand _cmdResultset = null;
lock (this)
{
this.OpenConnection();
_cmdResultset = new OracleCommand();
_cmdResultset.CommandText = _pstrprocname;
_cmdResultset.CommandType = CommandType.Text;
_cmdResultset.Connection = this.orconn;
//send back the return value from the function
return ((OracleDataReader)_cmdResultset.ExecuteReader());
}
}
/// <summary>
/// Close the Oracle Connection
/// </summary>
public void CloseConnection()
{
lock (this)
{
if (orconn != null)
{
this.orconn.Close();
this.orconn.Dispose();
this.orconn = null;
}
}
}
}
}
