/// <summary>
/// This fuction takes command, query and parameters and executes the query
/// </summary>
/// <param name="pCommand"></param>
/// <param name="pQuery"></param>
/// <param name="pParamCollection"></param>
/// <returns></returns>
public int FExecuteNonQuery(OracleCommand pCommand, string pQuery, OracleParameterCollection pParamCollection)
{
if (pCommand.Connection != null)
{
if (pParamCollection == null)
{
pCommand.CommandText = pQuery;
return(pCommand.ExecuteNonQuery());
}
else
{
pCommand.Parameters.Clear();
foreach (OracleParameter lop in pParamCollection)
{
pCommand.Parameters.Add(lop.Clone());
}
pCommand.CommandText = pQuery;
return(pCommand.ExecuteNonQuery());
}
}
else
{
return(-2);
}
}
public DataSet GetDitteFornitoriRuoli(int idditta)
{
OracleParameterCollection Coll = new OracleParameterCollection();
OracleParameter s_id = new OracleParameter();
s_id.ParameterName = "p_Ditta_id";
s_id.OracleType = OracleType.Int32;
s_id.Direction = ParameterDirection.Input;
s_id.Value = idditta;
Coll.Add(s_id);
OracleParameter s_CurrentUser = new OracleParameter();
s_CurrentUser.ParameterName = "p_CurrentUser";
s_CurrentUser.OracleType = OracleType.VarChar;
s_CurrentUser.Direction = ParameterDirection.Input;
s_CurrentUser.Value = this.userName;
Coll.Add(s_CurrentUser);
OracleParameter PaCursor = new OracleParameter();
PaCursor.ParameterName = "IO_CURSOR";
PaCursor.Direction = ParameterDirection.Output;
PaCursor.OracleType = OracleType.Cursor;
Coll.Add(PaCursor);
DataSet _MyDs = base.GetData(Coll, "PACK_DITTE.SP_GETGESTORI_FORNITORI_RUOLO");
return(_MyDs);
}
public static void Extract(OracleConnection connection, int resourceId, out Int64?globalId, out int?sprOborId)
{
var parameters = new OracleParameterCollection();
parameters.Add("p_resource_id", resourceId);
parameters.Add("p_id_global", OracleDbType.VarChar).Direction = System.Data.ParameterDirection.Output;
parameters.Add("p_obor_id", OracleDbType.Integer).Direction = System.Data.ParameterDirection.Output;
GetPackage(connection).ExecuteProcedure("extract", parameters);
var o = parameters["p_id_global"].Value;
if (o == DBNull.Value)
{
globalId = null;
}
else
{
globalId = Convert.ToInt64(o);
}
o = parameters["p_obor_id"].Value;
if (o == DBNull.Value)
{
sprOborId = null;
}
else
{
sprOborId = (int)o;
}
}
public override void Add(ScopeRule r)
{
string sql = String.Format("insert into {0}.SCOPE_RULE "
+ "("
+ " SCOPE_RULE"
+ ", DESCR "
+ " ) "
+ "values "
+ " ( "
+ " :SCOPE_RULE"
+ ", :DESCR"
+ " ) "
, SchemaName);
try
{
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":SCOPE_RULE", CheckNull(r.SCOPE_RULE), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DESCR", CheckNull(r.DESCR), OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
private DataSet AggiornaWo(int itemId)
{
OracleParameterCollection Coll = new OracleParameterCollection();
Class.ClassCompletaOrdine _Completa = new Class.ClassCompletaOrdine();
CompletamentoUserControl user = (CompletamentoUserControl)pnlRisultati.Controls[0].Controls[0].FindControl("Completamento1");
DropDownList pcmbsAddetti0 = user.pcmbsAddetti0;
int wo_id = itemId;
OracleParameter p_wo_id = new OracleParameter();
p_wo_id.ParameterName = "p_wo_id";
p_wo_id.OracleType = OracleType.Int32;
p_wo_id.Direction = ParameterDirection.Input;
p_wo_id.Value = wo_id;
Coll.Add(p_wo_id);
OracleParameter p_addetto_id = new OracleParameter();
p_addetto_id.ParameterName = "p_addetto_id";
p_addetto_id.OracleType = OracleType.Int32;
p_addetto_id.Direction = ParameterDirection.Input;
p_addetto_id.Value = pcmbsAddetti0.SelectedValue;
Coll.Add(p_addetto_id);
DataSet Ds = _Completa.AggiornaWO(Coll);
return(Ds);
}
/// <summary>
/// If no rows are affected by the DML, we do not want to invoke the setters
/// </summary>
/// <param name="nRowsAffected"></param>
/// <param name="parameters"></param>
internal override void OnQueryExecuted(int nRowsAffected, OracleParameterCollection parameters)
{
if (nRowsAffected > 0)
{
base.OnQueryExecuted(nRowsAffected, parameters);
}
}
/// <summary>
/// Special note on this. The keys are
/// CONTRACT_ID
/// TAT_SCHED_ID
/// BEGIN_ON
///
/// The fields TAT_SCHED_ID and BEGIN_ON are editable and represent a condition
/// where they keys may collide - Current software checks this condition and
/// displays a warning to avoid it.
/// </summary>
/// <param name="r"></param>
public override void Update(VolumeEvtType r)
{
try
{
string sql = String.Format("update {0}.VOLUME_EVT_TYPE set "
+ " SCOPE_RULE = :SCOPE_RULE "
+ " ,DESCR = :DESCR "
+ " ,ADJ_TYPE = :ADJ_TYPE "
+ " ,ADD_ON_CHG_TYPE_ID = :ADD_ON_CHG_TYPE_ID "
+ " where "
+ " VOLUME_EVT_TYPE_ID = :VOLUME_EVT_TYPE_ID "
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":VOLUME_EVT_TYPE_ID", CheckNull(r.VOLUME_EVT_TYPE_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":SCOPE_RULE", CheckNull(r.SCOPE_RULE), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DESCR", CheckNull(r.DESCR), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADJ_TYPE", CheckNull(r.ADJ_TYPE), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADD_ON_CHG_TYPE_ID", CheckNull(r.ADD_ON_CHG_TYPE_ID), OracleType.Number, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
/// <summary>
/// Returns the specified error from the database, or null
/// if it does not exist.
/// </summary>
public override ErrorLogEntry GetError(string id)
{
if (id == null)
{
throw new ArgumentNullException("id");
}
if (id.Length == 0)
{
throw new ArgumentException(null, "id");
}
Guid errorGuid;
try
{
errorGuid = new Guid(id);
}
catch (FormatException e)
{
throw new ArgumentException(e.Message, "id", e);
}
string errorXml;
using (OracleConnection connection = new OracleConnection(this.ConnectionString))
using (OracleCommand command = connection.CreateCommand())
{
command.CommandText = SchemaOwner + "pkg_elmah$get_error.GetErrorXml";
command.CommandType = CommandType.StoredProcedure;
OracleParameterCollection parameters = command.Parameters;
parameters.Add("v_Application", OracleType.NVarChar, _maxAppNameLength).Value = ApplicationName;
parameters.Add("v_ErrorId", OracleType.NVarChar, 32).Value = errorGuid.ToString("N");
parameters.Add("v_AllXml", OracleType.NClob).Direction = ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
OracleLob xmlLob = (OracleLob)command.Parameters["v_AllXml"].Value;
StreamReader streamreader = new StreamReader(xmlLob, Encoding.Unicode);
char[] cbuffer = new char[1000];
int actual;
StringBuilder sb = new StringBuilder();
while ((actual = streamreader.Read(cbuffer, 0, cbuffer.Length)) > 0)
{
sb.Append(cbuffer, 0, actual);
}
errorXml = sb.ToString();
}
if (errorXml == null)
{
return(null);
}
Error error = ErrorXml.DecodeString(errorXml);
return(new ErrorLogEntry(this, id, error));
}
/// <summary>
/// Returns a page of errors from the databse in descending order
/// of logged time.
/// </summary>
public override int GetErrors(int pageIndex, int pageSize, IList <ErrorLogEntry> errorEntryList)
{
if (pageIndex < 0)
{
throw new ArgumentOutOfRangeException("pageIndex", pageIndex, null);
}
if (pageSize < 0)
{
throw new ArgumentOutOfRangeException("pageSize", pageSize, null);
}
using (OracleConnection connection = new OracleConnection(this.ConnectionString))
using (OracleCommand command = connection.CreateCommand())
{
command.CommandText = _schemaOwner + "pkg_elmah$get_error.GetErrorsXml";
command.CommandType = CommandType.StoredProcedure;
OracleParameterCollection parameters = command.Parameters;
parameters.Add("v_Application", OracleType.NVarChar, _maxAppNameLength).Value = ApplicationName;
parameters.Add("v_PageIndex", OracleType.Int32).Value = pageIndex;
parameters.Add("v_PageSize", OracleType.Int32).Value = pageSize;
parameters.Add("v_TotalCount", OracleType.Int32).Direction = ParameterDirection.Output;
parameters.Add("v_Results", OracleType.Cursor).Direction = ParameterDirection.Output;
connection.Open();
using (OracleDataReader reader = command.ExecuteReader())
{
Debug.Assert(reader != null);
if (errorEntryList != null)
{
while (reader.Read())
{
var id = reader["ErrorId"].ToString();
var guid = new Guid(id);
var error = new Error
{
ApplicationName = reader["Application"].ToString(),
HostName = reader["Host"].ToString(),
Type = reader["Type"].ToString(),
Source = reader["Source"].ToString(),
Message = reader["Message"].ToString(),
User = reader["UserName"].ToString(),
StatusCode = Convert.ToInt32(reader["StatusCode"]),
Time = Convert.ToDateTime(reader["TimeUtc"]).ToLocalTime()
};
errorEntryList.Add(new ErrorLogEntry(this, guid.ToString(), error));
}
}
reader.Close();
}
return((int)command.Parameters["v_TotalCount"].Value);
}
}
public DataSet GetRichiedenti(string NomeCompleto)
{
OracleParameterCollection Coll = new OracleParameterCollection();
OracleParameter s_p_NomeCompleto = new OracleParameter();
s_p_NomeCompleto.ParameterName = "p_NomeCompleto";
s_p_NomeCompleto.OracleType = OracleType.VarChar;
s_p_NomeCompleto.Direction = ParameterDirection.Input;
s_p_NomeCompleto.Size = 50;
s_p_NomeCompleto.Value = NomeCompleto;
Coll.Add(s_p_NomeCompleto);
OracleParameter s_Cursor = new OracleParameter();
s_Cursor.ParameterName = "IO_CURSOR";
s_Cursor.OracleType = OracleType.Cursor;
s_Cursor.Direction = ParameterDirection.Output;
Coll.Add(s_Cursor);
DataSet _MyDs = base.GetData(Coll, "PACK_MAN_ORD.SP_GetRichiedenti");
return(_MyDs);
}
/// <summary>
/// Builds an OracleParametersCollection for an INSERT command, according to the DbTypeParameters in this collection.
/// </summary>
/// <param name="a_oParams">The OracleParameterCollection to be filled.</param>
private void AddInsertCommandParameters(OracleParameterCollection a_oParams)
{
foreach (DbTypeParameter l_oCurrent in this)
{
a_oParams.Add(l_oCurrent.ParameterName, l_oCurrent.Value);
}
}
internal void ObtenerParametrosSalida(OracleParameterCollection coleccionparametros, OracleType tiporetorno, ref object retorno, object[] paramout)
{
int index = 0;
foreach (OracleParameter coleccionparametro in (DbParameterCollection)coleccionparametros)
{
switch (coleccionparametro.Direction)
{
case ParameterDirection.Output:
paramout[index] = this.EstablecerParametro(paramout[index], coleccionparametro.Value);
++index;
continue;
case ParameterDirection.InputOutput:
paramout[index] = this.EstablecerParametro(paramout[index], coleccionparametro.Value);
++index;
continue;
case ParameterDirection.ReturnValue:
if (!tiporetorno.Equals((object)OracleType.Cursor))
{
retorno = this.EstablecerParametro(retorno, coleccionparametro.Value);
continue;
}
continue;
default:
continue;
}
}
}
// <summary>
/// Exec a procedure in oracle
/// </summary>
/// <param name="StoreName">Name (with namespace) of Store procedure</param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteProcedure(string StoreName, OracleParameterCollection parameters)
{
int result = -1;
using (var conn = new OracleConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = StoreName;
if (parameters != null)
{
foreach (OracleParameter param in parameters)
{
if (param.Value == null)
{
param.Value = System.DBNull.Value;
}
command.Parameters.AddWithValue(param.ParameterName, param.Value);
}
}
result = command.ExecuteNonQuery();
}
conn.Close();
}
return(result);
}
private void SetRowUniqueIdentifierValue(ResultArgs result, OracleCommand OracleCommand)
{
if (OracleCommand.CommandType == CommandType.StoredProcedure)
{
OracleParameterCollection OracleParameterCollection = OracleCommand.Parameters;
string paramName = "";
foreach (OracleParameter OracleParameter in OracleParameterCollection)
{
if (OracleParameter.ParameterName == rowUniqueParmName)
{
paramName = RemoveParameterDelimiter(OracleParameter.ParameterName);
result.RowUniqueIdCollection[paramName] = OracleParameter.Value;
break;
}
}
}
else
{
if (getRowUniqueId)
{
string sQuery = "SELECT LAST_INSERT_ID()";
OracleCommand.CommandText = sQuery;
OracleCommand.CommandType = CommandType.Text;
result.RowUniqueId = OracleCommand.ExecuteScalar().ToString();
}
}
}
/// <summary>
/// Add range with value
/// </summary>
/// <param name="coll"></param>
/// <param name="values"></param>
public static void AddRangeWithValue(this OracleParameterCollection coll, Dictionary <string, object> values)
{
foreach (var keyValuePair in values)
{
coll.AddWithValue(keyValuePair.Key, keyValuePair.Value);
}
}
public void ExecuteNonQuery(string sql, OracleParameterCollection opc)
{
if (null == _conn)
{
_conn = GetDBConnection();
}
try
{
OracleCommand cmd = new OracleCommand(sql, _conn);
foreach (OracleParameter op in opc)
{
cmd.Parameters.Add(op.ParameterName, op.OracleType, op.Size).Value = op.Value;
}
if (null != _st)
{
cmd.Transaction = _st;
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (null == _st)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
return;
}
public string GetNumeroApprovate(string _bl_id)
{
OracleParameterCollection _SColl = new OracleParameterCollection();
OracleParameter s_p_sql = new OracleParameter();
s_p_sql.ParameterName = "p_sql";
s_p_sql.OracleType = OracleType.VarChar;
s_p_sql.Direction = ParameterDirection.Input;
s_p_sql.Size = 2000;
s_p_sql.Value = " Select count(wr.wr_id) from wr where wr.bl_id = '" + _bl_id + "' and wr.id_wr_status not in (1,15) and wr.tipomanutenzione_id = 1";
_SColl.Add(s_p_sql);
OracleParameter s_Cursor = new OracleParameter();
s_Cursor.ParameterName = "IO_CURSOR";
s_Cursor.OracleType = OracleType.Cursor;
s_Cursor.Direction = ParameterDirection.Output;
_SColl.Add(s_Cursor);
DataSet _MyDs = base.GetData(_SColl, "PACK_COMMON.SP_DYNAMIC_SELECT");
return(_MyDs.Tables[0].Rows[0][0].ToString());
}
public DataSet GetRDLApprovate(string codEdificio)
{
OracleParameterCollection _SColl = new OracleParameterCollection();
OracleParameter s_p_BL_ID = new OracleParameter();
s_p_BL_ID.ParameterName = "p_bl_id";
s_p_BL_ID.OracleType = OracleType.VarChar;
s_p_BL_ID.Direction = ParameterDirection.Input;
s_p_BL_ID.Size = 50;
s_p_BL_ID.Value = codEdificio;
_SColl.Add(s_p_BL_ID);
OracleParameter s_Cursor = new OracleParameter();
s_Cursor.ParameterName = "IO_CURSOR";
s_Cursor.OracleType = OracleType.Cursor;
s_Cursor.Direction = ParameterDirection.Output;
_SColl.Add(s_Cursor);
DataSet _MyDs = base.GetData(_SColl, "PACK_MAN_ORD.SP_GetRDLApprovate");
return(_MyDs);
}
// <Snippet1>
public void CreateOracleParamColl(OracleConnection connection)
{
OracleCommand command = new OracleCommand(
"SELECT Ename, DeptNo FROM Emp WHERE EmpNo = :pEmpNo", connection);
OracleParameterCollection paramCollection = command.Parameters;
OracleParameter parameter = paramCollection.Add(
"pEmpNo", OracleType.Number, 5, "EmpNo");
}
// <Snippet1>
public void CreateOracleParamColl()
{
OracleCommand command = new OracleCommand(
"SELECT Ename, DeptNo FROM Emp WHERE EmpNo = :pEmpNo", connection);
OracleParameterCollection paramCollection = command.Parameters;
object parameter = new OracleParameter("pEmpNo", OracleType.Number);
int pIndex = paramCollection.Add(parameter);
}
/// <summary>
/// Logs an error to the database.
/// </summary>
/// <remarks>
/// Use the stored procedure called by this implementation to set a
/// policy on how long errors are kept in the log. The default
/// implementation stores all errors for an indefinite time.
/// </remarks>
public override string Log(Error error)
{
if (error == null)
{
throw new ArgumentNullException("error");
}
string errorXml = ErrorXml.EncodeString(error);
Guid id = Guid.NewGuid();
using (OracleConnection connection = new OracleConnection(this.ConnectionString))
using (OracleCommand command = connection.CreateCommand())
{
connection.Open();
using (OracleTransaction transaction = connection.BeginTransaction())
{
// because we are storing the XML data in a NClob, we need to jump through a few hoops!!
// so first we've got to operate within a transaction
command.Transaction = transaction;
// then we need to create a temporary lob on the database server
command.CommandText = "declare xx nclob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
command.CommandType = CommandType.Text;
OracleParameterCollection parameters = command.Parameters;
parameters.Add("tempblob", OracleType.NClob).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
// now we can get a handle to the NClob
OracleLob xmlLob = (OracleLob)parameters[0].Value;
// create a temporary buffer in which to store the XML
byte[] tempbuff = Encoding.Unicode.GetBytes(errorXml);
// and finally we can write to it!
xmlLob.BeginBatch(OracleLobOpenMode.ReadWrite);
xmlLob.Write(tempbuff, 0, tempbuff.Length);
xmlLob.EndBatch();
command.CommandText = SchemaOwner + "pkg_elmah$log_error.LogError";
command.CommandType = CommandType.StoredProcedure;
parameters.Clear();
parameters.Add("v_ErrorId", OracleType.NVarChar, 32).Value = id.ToString("N");
parameters.Add("v_Application", OracleType.NVarChar, _maxAppNameLength).Value = ApplicationName;
parameters.Add("v_Host", OracleType.NVarChar, 30).Value = error.HostName;
parameters.Add("v_Type", OracleType.NVarChar, 100).Value = error.Type;
parameters.Add("v_Source", OracleType.NVarChar, 60).Value = error.Source;
parameters.Add("v_Message", OracleType.NVarChar, 500).Value = error.Message;
parameters.Add("v_User", OracleType.NVarChar, 50).Value = error.User;
parameters.Add("v_AllXml", OracleType.NClob).Value = xmlLob;
parameters.Add("v_StatusCode", OracleType.Int32).Value = error.StatusCode;
parameters.Add("v_TimeUtc", OracleType.DateTime).Value = error.Time.ToUniversalTime();
command.ExecuteNonQuery();
transaction.Commit();
}
return(id.ToString());
}
}
public override void Remove(ClientLocation client)
{
string sql = String.Format("DELETE FROM {0}.EXT_CLIENT WHERE EXT_SYS = :EXT_SYS and EXT_CLIENT_KEY = :EXT_CLIENT_KEY", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":EXT_SYS", client.ExtSys, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":EXT_CLIENT_KEY", client.ExtClientKey, OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
internal OracleType ObtenerParametros(object[] parametrosentrada, OracleParameterCollection coleccionparametros)
{
OracleType oracleType = OracleType.Cursor;
int index = 0;
try
{
foreach (OracleParameter coleccionparametro in (DbParameterCollection)coleccionparametros)
{
switch (coleccionparametro.Direction)
{
case ParameterDirection.Input:
if (parametrosentrada.GetValue(index) == null)
{
coleccionparametro.Value = (object)DBNull.Value;
}
else
{
coleccionparametro.Value = parametrosentrada.GetValue(index);
}
++index;
continue;
case ParameterDirection.Output:
coleccionparametro.Value = (object)DBNull.Value;
continue;
case ParameterDirection.InputOutput:
if (parametrosentrada.GetValue(index) == null)
{
coleccionparametro.Value = (object)DBNull.Value;
}
else
{
coleccionparametro.Value = parametrosentrada.GetValue(index);
}
++index;
continue;
case ParameterDirection.ReturnValue:
oracleType = coleccionparametro.OracleType;
coleccionparametro.Value = (object)DBNull.Value;
continue;
default:
coleccionparametro.Value = (object)DBNull.Value;
continue;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
return(oracleType);
}
public override void Remove(ExtWorkType entity)
{
string sql = String.Format("DELETE FROM {0}.ext_work_type WHERE EXT_SYS = :EXT_SYS and EXT_CLIENT_KEY = :EXT_CLIENT_KEY and EXT_WORK_TYPE = :EXT_WORK_TYPE", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":EXT_SYS", entity.EXT_SYS, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":EXT_CLIENT_KEY", entity.EXT_CLIENT_KEY, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":EXT_WORK_TYPE", entity.EXT_WORK_TYPE, OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
public static void Exec(
string sign,
string name,
decimal owner,
decimal state,
decimal user,
decimal fixtype,
ref decimal code)
{
if (command == null)
{
OracleParameter p_sign = new OracleParameter("p_sign", OracleDbType.Varchar2);
OracleParameter p_name = new OracleParameter("p_name", OracleDbType.Varchar2);
OracleParameter p_owner = new OracleParameter("p_owner", OracleDbType.Decimal);
OracleParameter p_state = new OracleParameter("p_state", OracleDbType.Decimal);
OracleParameter p_user = new OracleParameter("p_user", OracleDbType.Decimal);
OracleParameter p_fixtype = new OracleParameter("p_fixtype", OracleDbType.Decimal);
OracleParameter p_code = new OracleParameter(
"p_code",
OracleDbType.Decimal
);
p_code.Direction = System.Data.ParameterDirection.Output;
command = new OracleCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "omp_adm.pkg_sepo_tflex_synch_omp.create_spec_fix";
command.Parameters.AddRange(
new OracleParameter[]
{
p_sign,
p_name,
p_owner,
p_state,
p_user,
p_fixtype,
p_code
});
}
OracleParameterCollection pars = command.Parameters;
pars["p_sign"].Value = sign;
pars["p_name"].Value = name;
pars["p_owner"].Value = owner;
pars["p_state"].Value = state;
pars["p_user"].Value = user;
pars["p_fixtype"].Value = fixtype;
command.Connection = Connection.GetInstance();
command.ExecuteNonQuery();
decimal.TryParse(pars["p_code"].Value.ToString(), out code);
}
public static byte[] GetBlobOrDefault(this OracleParameterCollection parameters, string name)
{
var value = parameters[name].Value;
if (DBNull.Value == null || value == null)
{
return(null);
}
return((byte[])value);
}
public static string GetLogSql(string sql, OracleParameterCollection parameters)
{
foreach (OracleParameter kv in parameters)
{
string regexKey = string.Format(@"{0}{1}(?=[\)\, ]?)", ":", kv.ParameterName);
string part = ToDbString(kv.Value);
sql = Regex.Replace(sql, regexKey, part);
}
return(sql);
}
private static void RetrieveOutputParameters(OracleParameterCollection parameters, IDictionary <string, object> suppliedParameters)
{
var output = from p in parameters.OfType <OracleParameter>()
where p.Direction == ParameterDirection.Output
select new { p.ParameterName, p.Value };
foreach (var o in output)
{
suppliedParameters[o.ParameterName] = o.Value;
}
}
public static string KeyToWhere(IDataStoreKey key, OracleParameterCollection parameters)
{
string where = null;
if (key is CounterDataStoreKey)
{
where = "T1.COUNTER = :CTR";
var par = new OracleParameter();
par.ParameterName = ":CTR";
par.Value = ((CounterDataStoreKey)key).Counter;
parameters.Add(par);
}
else
if (key is GDID)
{
where = "T1.GDID = :CTR";
var par = new OracleParameter();
par.ParameterName = ":CTR";
par.Value = key;
parameters.Add(par);
}
else
if (key is NameValueDataStoreKey)
{
var dict = key as NameValueDataStoreKey;
var s = new StringBuilder();
var idx = 0;
foreach (var e in dict)
{
s.AppendFormat(" (T1.\"{0}\" = :P{1}) AND", e.Key, idx);
var par = new OracleParameter();
par.ParameterName = "?P" + idx.ToString();
par.Value = e.Value;
parameters.Add(par);
idx++;
}
if (s.Length > 0)
{
s.Remove(s.Length - 3, 3); //cut "AND"
}
where = s.ToString();
}
else
{
throw new OracleDataAccessException(StringConsts.INVALID_KEY_TYPE_ERROR);
}
return(where);
}
public int Update(OracleParameterCollection CollezioneControlli, int itemId)
{
OracleParameter PaCursor = new OracleParameter();
PaCursor.ParameterName = "p_IdOut";
PaCursor.Direction = ParameterDirection.Output;
PaCursor.OracleType = OracleType.Int32;
CollezioneControlli.Add(PaCursor);
return(base.Update(CollezioneControlli, "p_wr_id", itemId, "PACK_MOBILE.SP_UPDATECOMPLETAMENTO"));
}
/// <summary>
///
/// </summary>
/// <param name="strConn"></param>
/// <param name="pName"></param>
/// <param name="prs"></param>
/// <returns></returns>
public static DataSet GetDataSet(string strConn, string pName,OracleParameterCollection prs)
{
using (OracleConnection ocn = new OracleConnection(strConn))
{
ocn.Open();
OracleCommand ocmd = new OracleCommand(pName, ocn);
ocmd.CommandType = CommandType.StoredProcedure;
OracleParameter par = new OracleParameter();
ocmd.Parameters.Add(par);
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(ocmd);
adapter.Fill(ds);
return ds;
}
}
/// <summary>
/// Ищет параметр OracleParameter в коллекции prms по имени paramName
/// </summary>
/// <param name="prms"></param>
/// <param name="paramName"></param>
/// <returns></returns>
public static OracleParameter FindOraParam(OracleParameterCollection prms, String paramName) {
if ((prms != null) && !String.IsNullOrEmpty(paramName)) {
return prms.Cast<OracleParameter>().FirstOrDefault(p => {
var pn1 = p.ParameterName;
var pn2 = paramName;
Utl.RegexReplace(ref pn1, @"^\bp_", String.Empty, true);
Utl.RegexReplace(ref pn2, @"^\bp_", String.Empty, true);
return String.Equals(pn1, pn2, StringComparison.CurrentCultureIgnoreCase);
});
}
return null;
}
/// <summary>
/// Call the setters for each out parameter. Setter is not called if the out value is null.
/// </summary>
/// <param name="nRowsAffected">Return value of ExecuteDML</param>
/// <param name="parameters">List of oracle parameters</param>
internal virtual void OnQueryExecuted(int nRowsAffected, OracleParameterCollection parameters)
{
// For array parameters, _bindParameters contains "BUCKETLIST" whereas parameters contains "BUCKETLIST0"
// For this reason we cannot expect all names in parameters to be available in _bindParameters
// Thus we are including the _bindParameters.Contains() condition
var query = from OracleParameter p in parameters
where _bindParameters.Contains(p.ParameterName)
let setter = _bindParameters[p.ParameterName].OutputValueUpdater
where setter != null
select new
{
Value = p.Value,
Setter = setter
};
foreach (var item in query)
{
item.Setter(item.Value);
}
}
private static void RetrieveOutputParameters(OracleParameterCollection parameters, IDictionary<string, object> suppliedParameters)
{
var output = from p in parameters.OfType<OracleParameter>()
where p.Direction == ParameterDirection.Output
select new {p.ParameterName, p.Value};
foreach (var o in output)
suppliedParameters[o.ParameterName] = o.Value;
}
public void ExecuteSP(string procedureName)
{
OracleCommand cmd = new OracleCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
//if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
cmd.ExecuteNonQuery();
_parameterCollection = cmd.Parameters;
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
public void ExecuteSPDataSet(ref DataSet dataSet, string procedureName, string tableName)
{
OracleCommand cmd = new OracleCommand();
this.Connect();
OracleDataAdapter da = new OracleDataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
//if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
_parameterCollection = cmd.Parameters;
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
public void Reset()
{
if (_parameters != null)
{
_parameters.Clear();
}
if (_parameterCollection != null)
{
_parameterCollection = null;
}
}
public XmlReader ExecuteSPXmlReader(string procedureName)
{
XmlReader reader;
OracleCommand cmd = new OracleCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
//if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
reader = cmd.ExecuteXmlReader();
_parameterCollection = cmd.Parameters;
cmd.Dispose();
return reader;
}
public OracleDataReader ExecuteSPReader(string procedureName)
{
OracleDataReader reader;
OracleCommand cmd = new OracleCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
//if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
CommandBehavior behavior = CommandBehavior.Default;
if (this.AutoCloseConnection) behavior = behavior | CommandBehavior.CloseConnection;
if (_isSingleRow) behavior = behavior | CommandBehavior.SingleRow;
reader = cmd.ExecuteReader(behavior);
_parameterCollection = cmd.Parameters;
cmd.Dispose();
return reader;
}
