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