private void AddParameter(OracleCommand dbCmd, string paramName, object paramValue, ParameterDirection direction) { if (dbCmd == null) return; OracleParameter param = dbCmd.CreateParameter(); param.Direction = direction; param.ParameterName = paramName; if (paramValue == null) { param.Value = DBNull.Value; } else { Type valueType = paramValue.GetType(); if (valueType.IsEnum) param.Value = (int)paramValue; else if (valueType == typeof(Boolean)) param.Value = (bool)paramValue ? 1 : 0; else if (valueType == typeof(DateTime) && DateTime.MinValue.Equals(paramValue)) param.Value = DBNull.Value; else if (valueType == typeof(Guid)) param.Value = ((Guid)paramValue).ToByteArray(); else param.Value = paramValue; } dbCmd.Parameters.Add(param); }
private void AdicionaParametrosNoComando(OracleCommand comando) { OracleParameter _dbParametro; foreach (var param in parametros) { _dbParametro = comando.CreateParameter(); _dbParametro.ParameterName = param.Key; _dbParametro.Value = param.Value; comando.Parameters.Add(_dbParametro); } _dbParametro = null; parametros.Clear(); }
public bool RefreshColumns(TableManager.Table table) { // set up command and parameters OracleCommand cmd = new OracleCommand(ALL_TAB_COLUMNS_TABLE_SELECT, conn); OracleParameter ownerParam = cmd.CreateParameter(); ownerParam.ParameterName = "owner"; ownerParam.OracleDbType = OracleDbType.Char; ownerParam.Direction = System.Data.ParameterDirection.Input; ownerParam.Value = table.Name; cmd.Parameters.Add(ownerParam); OracleParameter tableParam = cmd.CreateParameter(); tableParam.ParameterName = "table_name"; tableParam.OracleDbType = OracleDbType.Char; tableParam.Direction = System.Data.ParameterDirection.Input; tableParam.Value = table.Owner; cmd.Parameters.Add(tableParam); // prepare data reader OracleDataReader odr = cmd.ExecuteReader(); if (!odr.HasRows) { return(false); } // purge table columns data purgeTableColumnsData(table); while (odr.Read()) { TableColumn column = LoadColumn(odr); columns.Add(column); } return(true); }
/// <summary> /// Adds the parameters to a Oracle command /// </summary> /// <param name="commandText">The Oracle query to execute</param> /// <param name="parameters">Parameters to pass to the Oracle query</param> private void AddParameters(OracleCommand command, Dictionary <string, object> parameters) { if (parameters == null) { return; } foreach (var param in parameters) { var parameter = command.CreateParameter(); parameter.ParameterName = param.Key; parameter.Value = param.Value ?? DBNull.Value; command.Parameters.Add(parameter); } }
private static void AdicionarParamatros(OracleCommand cmdComando, Dictionary <string, object> parametros) { if (parametros == null) { return; } foreach (var item in parametros) { var parametro = cmdComando.CreateParameter(); parametro.ParameterName = item.Key; parametro.Value = item.Value ?? DBNull.Value; cmdComando.Parameters.Add(parametro); } }
protected DbCommand BuildStoredProcedureCommand(string query, ParamSet paramSet) { DbCommand cmd = null; switch (DBEngine) { case DBEngines.MSSqlServer: cmd = new SqlCommand(); break; case DBEngines.Oracle: cmd = new OracleCommand(); break; } cmd.Connection = DbConnection; cmd.Transaction = DbTransaction; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ResolveStoredProcedurePlaceholders(query); if (paramSet != null) { foreach (ParamSet.ParamInfo pi in paramSet.GetInfos()) { IDbDataParameter param = cmd.CreateParameter(); param.ParameterName = pi.Name; if (pi.OracleDbType.HasValue && DBEngine == DBEngines.Oracle) { OracleParameter oracleParam = param as OracleParameter; if (oracleParam != null) { oracleParam.OracleDbType = pi.OracleDbType.Value; } } else { param.DbType = pi.DbType; } param.Value = pi.Value; param.Direction = pi.Direction; cmd.Parameters.Add(param); } } return(cmd); }
private OracleCommand CreateCommand(string procName, OracleParameterCollection opc) { #region if (null == _conn) { _conn = GetDBConnection(); } OracleCommand cmd = new OracleCommand(procName, _conn); cmd.CommandType = CommandType.StoredProcedure; try { cmd.Parameters.Clear(); if (null != opc) { foreach (OracleParameter op in opc) { OracleParameter mp = cmd.CreateParameter(); mp.OracleType = op.OracleType; mp.ParameterName = op.ParameterName; mp.Value = op.Value; mp.Size = op.Size; if (op.Value == null) { mp.Direction = ParameterDirection.Output; } else { mp.Direction = ParameterDirection.Input; } cmd.Parameters.Add(mp); } } } catch { throw; } return(cmd); #endregion }
private void AddParameter(OracleCommand dbCmd, string paramName, object paramValue, ParameterDirection direction) { if (dbCmd == null) { return; } OracleParameter param = dbCmd.CreateParameter(); param.Direction = direction; param.ParameterName = paramName; if (paramValue == null) { param.Value = DBNull.Value; } else { Type valueType = paramValue.GetType(); if (valueType.IsEnum) { param.Value = (int)paramValue; } else if (valueType == typeof(Boolean)) { param.Value = (bool)paramValue ? 1 : 0; } else if (valueType == typeof(DateTime) && DateTime.MinValue.Equals(paramValue)) { param.Value = DBNull.Value; } else if (valueType == typeof(Guid)) { param.Value = ((Guid)paramValue).ToByteArray(); } else { param.Value = paramValue; } } dbCmd.Parameters.Add(param); }
protected override IDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters) { OracleCommand mySqlCommand = (OracleCommand)Connection.CreateCommand(); mySqlCommand.Transaction = (OracleTransaction)CurrentTransaction; mySqlCommand.BindByName = true; if (sqlQuery.StartsWith("!")) { mySqlCommand.CommandType = CommandType.StoredProcedure; mySqlCommand.CommandText = sqlQuery.Substring(1); } else { mySqlCommand.CommandType = CommandType.Text; mySqlCommand.CommandText = sqlQuery; } mySqlCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", ":${name}"); if (parameters != null && !parameters.IsEmpty) { foreach (CSParameter parameter in parameters) { OracleParameter dataParameter = mySqlCommand.CreateParameter(); dataParameter.ParameterName = ":" + parameter.Name.Substring(1); dataParameter.Direction = ParameterDirection.Input; if (parameter.Value is Guid) { dataParameter.Value = ((Guid)parameter.Value).ToByteArray(); } else { dataParameter.Value = ConvertParameter(parameter.Value); } mySqlCommand.Parameters.Add(dataParameter); } } return(mySqlCommand); }
static EmployeeList Get() { OracleCommand cmd = con.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "HR.EMPLOYEE_UDT_EXAMPLE.GET"; //The return values must always be declared as a the first parameter var ret = cmd.CreateParameter(); ret.Direction = ParameterDirection.ReturnValue; ret.OracleDbType = OracleDbType.Object; ret.UdtTypeName = "HR.EMPLOYEE_LIST"; cmd.Parameters.Add(ret); cmd.ExecuteNonQuery(); return(ret.Value as EmployeeList); }
public void PrepareCommand(OracleCommand command, IDictionary <string, object> parameterDictionary) { foreach (var key in parameterDictionary.Keys) { var parameter = command.CreateParameter(); parameter.ParameterName = key; if (parameterDictionary[key] == null) { parameter.Value = DBNull.Value; command.Parameters.Add(parameter); } else { parameter.Value = parameterDictionary[key]; command.Parameters.Add(parameter); } } }
private void button1_Click(object sender, EventArgs e) { string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; string _sql; OracleDataReader _rdrObj; try { OracleConnection _connObj = new OracleConnection(_connstring); DataSet _ds = new DataSet(); _connObj.Open(); _sql = "SELECT * FROM Products WHERE ID=:IDValue"; OracleCommand _cmdObj = new OracleCommand(_sql, _connObj); OracleParameter _idParam = _cmdObj.CreateParameter(); _idParam.ParameterName = "IDValue"; _idParam.OracleDbType = OracleDbType.Varchar2; _idParam.Value = txtID.Text; _cmdObj.Parameters.Add(_idParam); _rdrObj = _cmdObj.ExecuteReader(); if (_rdrObj.HasRows) { if (_rdrObj.Read()) { txtName.Text = _rdrObj.GetString(_rdrObj.GetOrdinal("Name")); txtRemarks.Text = _rdrObj.GetString(_rdrObj.GetOrdinal("Remarks")); numPrice.Value = _rdrObj.GetDecimal(_rdrObj.GetOrdinal("Price")); } } else { MessageBox.Show("A record with a matching ID was not found"); } _connObj.Close(); _connObj.Dispose(); _connObj = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
static void Main(string[] args) { ProductInfoList productInfoList = new ProductInfoList(); ProductInfo[] productInfoArray = new ProductInfo[] { new ProductInfo() { Id = 1, Description = "Producto 1", Price = 1.3 }, new ProductInfo() { Id = 2, Description = "Producto 2", Price = 2.4 } }; productInfoList.Values = productInfoArray; string connectionString = ConfigurationManager.ConnectionStrings["connectionStrings"].ConnectionString; OracleConnection con = new OracleConnection(); con.ConnectionString = connectionString; con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "PKG_TEST_01.CREATE_PRODUCT"; cmd.CommandType = System.Data.CommandType.StoredProcedure; OracleParameter Oparameter1 = cmd.CreateParameter(); Oparameter1.Direction = ParameterDirection.Input; Oparameter1.ParameterName = "P_PRODUCTS"; Oparameter1.OracleDbType = OracleDbType.Array; Oparameter1.UdtTypeName = "PRODUCT_TABLE_UDT"; Oparameter1.Value = productInfoList; cmd.Parameters.Add(Oparameter1); cmd.ExecuteNonQuery(); Console.WriteLine("Presione una tecla para finalizar..."); Console.ReadLine(); }
private static void SetParameters(Procedure procedure, OracleCommand cmd, IDictionary <string, object> suppliedParameters) { int i = 0; foreach (var parameter in procedure.Parameters) { if (parameter.IsReturnOrOutput()) { var p = cmd.CreateParameter(); p.ConfigureOutputParameterFromArgument(parameter); cmd.Parameters.Add(p); } else { object value; suppliedParameters.TryGetValue("_" + i, out value); cmd.Parameters.Add(parameter.Name, value); i++; } } }
private OracleCommand GetCommand(OracleConnection connection, string query, SqlParameter[] parameters = null) { var command = new OracleCommand(query, connection); command.CommandTimeout = 15 * 60; command.Prepare(); if (parameters != null) { foreach (var parameter in parameters) { var p = command.CreateParameter(); p.ParameterName = parameter.Name; p.Value = parameter.Value; p.DbType = parameter.Type; command.Parameters.Add(p); } } return(command); }
//Metodo para listar los departamentos de cada comuna. public DataTable ListaDepartamentoPorComuna(int id_comuna) { try { OracleCommand _sql = Enlace.ComandoSP(); _sql.CommandText = "pkg_finanzas.sp_DepartamentoPorComuna"; _sql.Parameters.Add("o_data", OracleDbType.RefCursor).Direction = ParameterDirection.Output; OracleParameter comuna = _sql.CreateParameter(); comuna.ParameterName = "comuna"; comuna.Value = id_comuna; comuna.OracleDbType = OracleDbType.Int64; _sql.Parameters.Add(comuna); return(Enlace.RegresaDatos(_sql)); } catch (OracleException ex) { throw ex; } }
// DataDirect Oracle Data Provider Connection String Options support: [ParameterMode = ANSI | BindByOrdinal | BindByName] #if (ODP_NET || ODP_NET_MANAGED) // ODP.NET /* * partial void OnOracleCommandCreating(DbCommand dbCmd, ref bool processed) * { * if (processed) * return; * * OracleCommand oraCmd = dbCmd as OracleCommand; * * if (oraCmd != null) * { * oraCmd.BindByName = true; * processed = true; * } * } */ partial void OnOracleReaderExecuting(DbCommand dbCmd, int resultSetCnt /* = 1 */, ref bool processed) { if (processed) { return; } OracleCommand oraCmd = dbCmd as OracleCommand; if (oraCmd != null) { if (oraCmd.CommandType == CommandType.StoredProcedure) { int cntRefCursorParam = oraCmd.Parameters.OfType <OracleParameter>().Count(p => p.OracleDbType == OracleDbType.RefCursor && p.Direction != ParameterDirection.Input); if (cntRefCursorParam < resultSetCnt) { if (oraCmd.BindByName || _AutoDeriveRefCursorParameters) { DerivedParametersCache.DeriveParameters(dbCmd, null, false); } else { for (; cntRefCursorParam < resultSetCnt; cntRefCursorParam++) { OracleParameter paramRefCursor = oraCmd.CreateParameter(); paramRefCursor.OracleDbType = OracleDbType.RefCursor; paramRefCursor.Direction = ParameterDirection.Output; paramRefCursor.Value = DBNull.Value; oraCmd.Parameters.Add(paramRefCursor); } } } } processed = true; } }
public virtual void Prepare(OracleCommand command) { OracleParameter param = command.CreateParameter(); param.ParameterName = this.m_parameterName; if (!this.m_inferType) { param.DbType = this.m_dbType; } if (this.m_precision != 0) { param.Precision = this.m_precision; } if (this.m_scale != 0) { param.Scale = this.m_scale; } if (this.m_size != 0) { param.Size = this.m_size; } command.Parameters.Add(param); }
/// <summary> /// PrepareParameter /// </summary> /// <param name="command"></param> /// <param name="parameters"></param> private void PrepareParameter(OracleCommand command, IList <object> parameters) { if (parameters == null || parameters.Count == 0) { return; } string[] parts = command.CommandText.Split('?'); if (parts.Length - 1 != parameters.Count) { throw new ArgumentOutOfRangeException("参数数量与参数值数量不一致。"); } StringBuilder builder = new StringBuilder(); for (int i = 0; i < parts.Length; i++) { builder.Append(parts[i]); if (i < parts.Length - 1) { string parameterName = string.Format("{0}P{1}", Constants.ParameterPrefix, i); object value = parameters[i]; builder.Append(parameterName); OracleParameter parameter = command.CreateParameter(); parameter.ParameterName = parameterName; parameter.Value = value == null ? DBNull.Value : value; command.Parameters.Add(parameter); } } command.CommandText = builder.ToString(); }
public virtual void Prepare(OracleCommand command) { OracleParameter param = command.CreateParameter(); param.ParameterName = this.m_parameterName; if (!this.m_inferType) { param.DbType = this.m_dbType; } if (this.m_precision != 0) { param.Precision = this.m_precision; } if (this.m_scale != 0) { param.Scale = this.m_scale; } if (this.m_size != 0) { param.Size = this.m_size; } command.Parameters.Add(param); }
private DbCommandWrapper CreateDbCommand(MultiPartMessage msg) { bool isNonQuery = true; IDbCommand command = new OracleCommand(); command.CommandType = CommandType.StoredProcedure; #if ODP_NET ((OracleCommand)command).BindByName = true; #endif Uri uri = msg.Metadata.Read("SendUri") as Uri; string instanceId = uri.Host; command.CommandText = uri.Segments[1] + uri.Segments[2]; command.CommandText = command.CommandText.Replace("/", "."); foreach (string propertyName in msg.Properties) { IDbDataParameter param = command.CreateParameter(); param.ParameterName = propertyName; Type paramType = msg.Properties.Read(propertyName).GetType(); if (paramType == typeof(string)) { param.DbType = DbType.String; } else if (paramType == typeof(short)) { param.DbType = DbType.Decimal; } else if (paramType == typeof(int)) { param.DbType = DbType.Decimal; } else if (paramType == typeof(double)) { param.DbType = DbType.Decimal; } else if (paramType == typeof(DateTime)) { param.DbType = DbType.DateTime; } if (propertyName.EndsWith("_I")) { param.Direction = ParameterDirection.Input; param.Value = msg.Properties.Read(propertyName); } else if (propertyName.EndsWith("Cur_O")) { isNonQuery = false; param.Direction = ParameterDirection.Output; #if ODP_NET ((OracleParameter)param).OracleDbType = OracleDbType.RefCursor; #else ((OracleParameter)param).OracleType = OracleType.Cursor; #endif } else if (propertyName.EndsWith("_O")) { param.Direction = ParameterDirection.Output; if (param.DbType == DbType.String) { if (msg.Metadata.Contains(propertyName)) { param.Size = msg.Metadata.ReadAsInt(propertyName); } else { param.Size = 255; } } } else if (propertyName.EndsWith("_IO")) { param.Direction = ParameterDirection.InputOutput; if (param.DbType == DbType.String) { param.Size = 255; } param.Value = msg.Properties.Read(propertyName); } command.Parameters.Add(param); } return(new DbCommandWrapper(command, instanceId, isNonQuery)); }
private async Task <DataTable> GetDataTableUsingReaderAsync(string Sql, List <ReportFilter> Filter = null) { DataTable retval = new DataTable(); OracleDataReader reader; OracleCommand cmd = (OracleCommand)GetDataCommandForOracle(); try { // Get Filterd Query with parameters if (Filter != null) { if (Filter.Count > 0) { if (Filter.Where(p => !string.IsNullOrEmpty(p.FilterValue) && !string.IsNullOrWhiteSpace(p.FilterValue) && !string.IsNullOrEmpty(p.Operator) && !string.IsNullOrWhiteSpace(p.Operator)).Any()) { foreach (ReportFilter item in Filter.Where(p => !string.IsNullOrEmpty(p.FilterValue) && !string.IsNullOrWhiteSpace(p.FilterValue))) { OracleParameter parameter = cmd.CreateParameter(); string dr = Filter.Where(i => i.FieldName == item.FieldName).FirstOrDefault().FilterValue; parameter.ParameterName = "p_" + item.FieldName; if (item.valueType == "System.DateTime") { parameter.DbType = DbType.DateTime; parameter.Value = DateTime.Parse(dr).ToShortDateString(); } else { parameter.Value = dr; } if (item.Operator.ToLower() == "between") { OracleParameter parameter1 = cmd.CreateParameter(); parameter1.ParameterName = "p_" + item.FieldName + "1"; parameter1.DbType = DbType.DateTime; string dr1 = Filter.Where(i => i.FieldName == item.FieldName).FirstOrDefault().FilterValue1; parameter1.Value = DateTime.Parse(dr1).ToShortDateString(); cmd.Parameters.Add(parameter1); } // parameter.DbType = TypeToDbType(tb.Columns[item.fieldname].DataType); cmd.Parameters.Add(parameter); } } } } // Get Table from Reader CancellationToken cancellationToken = new CancellationToken(); cmd.CommandText = Sql; reader = (OracleDataReader)await cmd.ExecuteReaderAsync(CommandBehavior.Default, cancellationToken); reader.SuppressGetDecimalInvalidCastException = true; retval = new DataTable(); retval.Load(reader); reader.Close(); cmd.Dispose(); return(retval); } catch (Exception ex) { return(null); } }
private void OracleUdtTest(BcrData bcrData) { try { using (OracleConnection connection = new OracleConnection(ConnectionString)) { connection.Open(); // // oracle package OracleCommand command = new OracleCommand("TEST.PKG_DEV_TEST.UDT_TEST", connection); command.CommandType = CommandType.StoredProcedure; command.BindByName = true; // // only input param OracleParameter i_bcr_data = command.CreateParameter(); i_bcr_data.ParameterName = "i_bcr_data"; i_bcr_data.Direction = ParameterDirection.Input; i_bcr_data.OracleDbType = OracleDbType.Object; i_bcr_data.UdtTypeName = BcrData.Name; i_bcr_data.Value = bcrData; // // input and output param OracleParameter io_bcr_data = command.CreateParameter(); io_bcr_data.ParameterName = "io_bcr_data"; io_bcr_data.Direction = ParameterDirection.InputOutput; io_bcr_data.OracleDbType = OracleDbType.Object; io_bcr_data.UdtTypeName = BcrData.Name; io_bcr_data.Value = bcrData; // // only output param OracleParameter o_result = command.CreateParameter(); o_result.ParameterName = "o_result"; o_result.Size = 1000; o_result.DbType = DbType.String; o_result.Direction = ParameterDirection.Output; o_result.Value = "APPEND"; // // append params command.Parameters.Add(i_bcr_data); command.Parameters.Add(io_bcr_data); command.Parameters.Add(o_result); // // execute command.ExecuteNonQuery(); // // input and output param result BcrData outBcrData = (BcrData)command.Parameters["io_bcr_data"].Value; // // only output param result string result = command.Parameters["o_result"].Value.ToString(); Console.WriteLine($"outBcrData = {outBcrData.Id}"); Console.WriteLine($"result = {result}"); } } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); } }
public IDbDataParameter CreateParameter(IDbCommand command) { OracleCommand SQLcommand = (OracleCommand)command; return(SQLcommand.CreateParameter()); }
/// <summary> /// Method to carry out processing of Writeback /// </summary> /// <param name="p_sJobIdentifier"> G3E_JOB.G3E_IDENTIFIER</param> /// <param name="p_passWord">Password string in the form Password='******';</param> /// <param name="p_sStatus">G3E_JOB.JOB_STATUS to be set for the job</param> /// <param name="p_errorInfo">Out parameter for the error info</param> public void ProcessWriteBackStatusUpdate(string p_sJobIdentifier, string p_passWord, bool p_AlternateJob, out WebServiceResponse p_sStatus, out string p_errorInfo) { string sRequestURL = string.Empty; string sRequestXML = string.Empty; string sResponse = string.Empty; string sWRNumber = string.Empty; int iPollingInterval = 0; string sDatabaseName = string.Empty; try { p_sStatus = WebServiceResponse.Success; p_errorInfo = string.Empty; DataAccess oDataAccess = new DataAccess(m_oApp); sRequestURL = oDataAccess.GetEFUrl("WMIS_WriteBack", "EdgeFrontier"); iPollingInterval = Convert.ToInt32(oDataAccess.GetEFUrl("WMIS_WritebackPollingInterval", "WMIS")); sDatabaseName = Convert.ToString(oDataAccess.GetFirstFieldValueFromRecordset("select sys_context('userenv','instance_name') from dual")); sWRNumber = oDataAccess.GetFirstFieldValueFromRecordset(string.Format("select WR_NBR from g3e_job where g3e_identifier ='{0}'", p_sJobIdentifier)); string sTransactionID = string.Empty; sTransactionID = oDataAccess.GetFirstFieldValueFromRecordset("select CORRELATION_ID_SEQ.nextval from dual"); sRequestXML = GetRequestXML(sWRNumber, sTransactionID, p_sJobIdentifier, p_AlternateJob); sResponse = ProcessRequest(sRequestURL, sRequestXML, p_errorInfo); if (sResponse.ToLower().Equals("success")) //Move forward { OracleConnection oConnection = new Oracle.DataAccess.Client.OracleConnection(); string constr = string.Empty; // Need to add a proper call that uses parameters to get a SYS_GENERALPARAMETER value to the DataAccess object... string sql = string.Format("select param_value from sys_generalparameter where subsystem_name='{0}' and subsystem_component='{1}' and param_name='{2}'", "WMIS", "WMIS_WRITEBACK", "ConnectionString"); ADODB.Recordset rs = oDataAccess.GetRecordSet(sql); if (null != rs && 0 < rs.RecordCount) { if (DBNull.Value != rs.Fields[0].Value) { constr = rs.Fields[0].Value.ToString(); } rs.Close(); rs = null; } if (string.IsNullOrEmpty(constr)) { // Issue an exception here? return; } //oConnection.ConnectionString = "User Id=" + m_oApp.DataContext.DatabaseUserName + ";" + p_passWord + "Data Source=" + sDatabaseName; oConnection.ConnectionString = constr; oConnection.Open(); OracleCommand oraCommand = new OracleCommand(); oraCommand.CommandText = "WMIS_PollJobTable"; oraCommand.CommandType = System.Data.CommandType.StoredProcedure; oraCommand.Connection = oConnection; OracleParameter jobIdentifier = oraCommand.CreateParameter(); jobIdentifier.Direction = System.Data.ParameterDirection.Input; jobIdentifier.DbType = System.Data.DbType.String; jobIdentifier.OracleDbType = OracleDbType.Varchar2; jobIdentifier.Size = 30; jobIdentifier.Value = p_sJobIdentifier; jobIdentifier.ParameterName = "p_jobIdentifier"; oraCommand.Parameters.Add(jobIdentifier); OracleParameter pollingInterval = oraCommand.CreateParameter(); pollingInterval.Direction = System.Data.ParameterDirection.Input; pollingInterval.DbType = System.Data.DbType.String; pollingInterval.OracleDbType = OracleDbType.NVarchar2; pollingInterval.Size = 30; pollingInterval.Value = iPollingInterval; pollingInterval.ParameterName = "p_pollingInterval"; oraCommand.Parameters.Add(pollingInterval); OracleParameter outStatus = oraCommand.CreateParameter(); outStatus.Direction = System.Data.ParameterDirection.Output; outStatus.DbType = System.Data.DbType.String; outStatus.OracleDbType = OracleDbType.NVarchar2; outStatus.Size = 30; outStatus.ParameterName = "p_status"; oraCommand.Parameters.Add(outStatus); OracleParameter outWritebackSet = oraCommand.CreateParameter(); outWritebackSet.Direction = System.Data.ParameterDirection.Output; outWritebackSet.DbType = System.Data.DbType.String; outWritebackSet.OracleDbType = OracleDbType.NVarchar2; outWritebackSet.Size = 30; outWritebackSet.ParameterName = "p_writeBackSet"; oraCommand.Parameters.Add(outWritebackSet); oraCommand.ExecuteNonQuery(); string statusMessage = Convert.ToString(oraCommand.Parameters["p_status"].Value); string writeBackSet = Convert.ToString(oraCommand.Parameters["p_writeBackSet"].Value); if ("1" == writeBackSet) { // WMIS_STATUS_C was changed to WRITEBACK during the loop. // Check the status and report accordingly switch (statusMessage) { case "SUCCESS": p_errorInfo = ""; p_sStatus = WebServiceResponse.Success; break; case "WRITEBACK": p_errorInfo = String.Format("Write back operation for WR {0} timed out after {1}. Please check back later to see if it completed successfully.", p_sJobIdentifier, timeString(iPollingInterval)); p_sStatus = WebServiceResponse.Timeout; break; case "FAILURE": p_errorInfo = String.Format("A Failure has occurred while setting the WMIS Status. Examine the job properties for more information."); p_sStatus = WebServiceResponse.Error; break; default: p_errorInfo = String.Format("An unexpected status value of '{0}' was returned while waiting on the WMIS Status to be set. Examine the job properties for more information.", statusMessage); p_sStatus = WebServiceResponse.Error; break; } } else { // WMIS_STATUS_C was never set to WRITEBACK during the loop. // Report this a a failure p_errorInfo = String.Format("WMIS Status was never set to WRITEBACK for this WR: {0}. Examine the job properties for more information.", p_sJobIdentifier); p_sStatus = WebServiceResponse.Error; } } else //Error has occured while submitting the request, return to caller { // If the request returns additional information in p_errorInfo, then use that; else, something else happened... if (string.IsNullOrEmpty(p_errorInfo)) { p_errorInfo = "Writeback did not return a SUCCESS status for this job. Examine the job properties for more information."; } p_sStatus = WebServiceResponse.Error; } } catch (Exception EX) { p_errorInfo = EX.Message; p_sStatus = WebServiceResponse.Error; } }
private static void SetParameters(Procedure procedure, OracleCommand cmd, IDictionary<string, object> suppliedParameters) { int i = 0; foreach (var parameter in procedure.Parameters) { if (parameter.IsReturnOrOutput()) { var p = cmd.CreateParameter(); p.ConfigureOutputParameterFromArgument(parameter); cmd.Parameters.Add(p); } else { object value; suppliedParameters.TryGetValue("_" + i, out value); cmd.Parameters.Add(parameter.Name, value); i++; } } }
/// <summary> /// call StoredProcedure 但是參數可有output /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <param name="hasReturnParameter"></param> /// <returns></returns> public DataTable ExecuteStoredProcedureEx(string sql, List <DbParameterEx> parameters, bool hasReturnParameter) { ResultData resultData = new ResultData(); resultData.Status = ResultStatus.Fail; try { var connection = CreateConnection(); OracleCommand command = new OracleCommand(sql, (OracleConnection)connection); //ken command.CommandType = CommandType.StoredProcedure; //ken if (parameters != null) { foreach (DbParameterEx everyPara in parameters) { DbParameter dbParam = command.CreateParameter(); dbParam = TransformToDbParameter(dbParam, everyPara); command.Parameters.Add(dbParam); } } DbParameter dbParamReturn = null; if (hasReturnParameter) { if (command is OracleCommand) { dbParamReturn = command.CreateParameter(); dbParamReturn.ParameterName = "RETURNPARAMETER"; dbParamReturn.Direction = ParameterDirection.Output; ((OracleParameter)dbParamReturn).OracleDbType = OracleDbType.RefCursor;//ken command.Parameters.Add(dbParamReturn); } } command.ExecuteNonQuery(); //ken OracleDataAdapter da = new OracleDataAdapter(command); //ken DataTable dtResult = new DataTable(); da.Fill(dtResult); //ken,如果有多個output cursor,則需要用以下寫法 //DataTable dtResult = (DataTable)command.Parameters[2]; return(dtResult); } catch (Exception ex) { string errorStr = ""; if (ex is AseException) { AseException aseEx = ((AseException)ex); foreach (AseError error in aseEx.Errors) { errorStr += Environment.NewLine + error.ProcName + Environment.NewLine + error.MessageNumber + Environment.NewLine + "LineNum:" + error.LineNum; } } Exception exNew = new Exception(ExceptionHelper.TranformException(ex).Message + errorStr); throw exNew; } }
public bool RefreshColumns(TableManager.Table table) { // set up command and parameters OracleCommand cmd = new OracleCommand(ALL_TAB_COLUMNS_TABLE_SELECT, conn); OracleParameter ownerParam = cmd.CreateParameter(); ownerParam.ParameterName = "owner"; ownerParam.OracleDbType = OracleDbType.Char; ownerParam.Direction = System.Data.ParameterDirection.Input; ownerParam.Value = table.Name; cmd.Parameters.Add(ownerParam); OracleParameter tableParam = cmd.CreateParameter(); tableParam.ParameterName = "table_name"; tableParam.OracleDbType = OracleDbType.Char; tableParam.Direction = System.Data.ParameterDirection.Input; tableParam.Value = table.Owner; cmd.Parameters.Add(tableParam); // prepare data reader OracleDataReader odr = cmd.ExecuteReader(); if (!odr.HasRows) return false; // purge table columns data purgeTableColumnsData(table); while (odr.Read()) { TableColumn column = LoadColumn(odr); columns.Add(column); } return true; }
/* * Using array binding (batch) to insert number of new employees */ private int AddEmployees(int numOfEmps, int empPK, int deptPK, int mgrID, double empBasicSalary) { // Creating arrays to contain data relevant to each field in the table int[] empnoArr = new int[numOfEmps]; string[] enameArr = new string[numOfEmps]; string[] jobArr = new string[numOfEmps]; int[] mgrArr = new int[numOfEmps]; DateTime[] hiredateArr = new DateTime[numOfEmps]; double[] salArr = new double[numOfEmps]; object[] commArr = new object[numOfEmps]; int[] deptnoArr = new int[numOfEmps]; DateTime hireDate = DateTime.Today; string insertStmt = "insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) " + "values (:1, :2, :3, :4, :5, :6, :7, :8)"; // Populating arrays with data for (int emp = 0; emp < numOfEmps; emp++) { empnoArr[emp] = ++empPK; enameArr[emp] = "DVLPR" + (emp + 1); jobArr[emp] = "DEVELOPER"; mgrArr[emp] = mgrID; hiredateArr[emp] = hireDate; salArr[emp] = empBasicSalary; commArr[emp] = DBNull.Value; deptnoArr[emp] = deptPK; if (dbg) { PrintLine(empnoArr[emp] + ", " + enameArr[emp] + ", " + jobArr[emp] + ", " + mgrArr[emp] + ", " + hiredateArr[emp] + ", " + salArr[emp] + ", " + commArr[emp] + ", " + deptnoArr[emp]); } } int param = 0; OracleCommand insert = null; try { insert = new OracleCommand(insertStmt, conn); insert.CommandType = CommandType.Text; insert.ArrayBindCount = numOfEmps; OracleParameter[] inParArr = new OracleParameter[8]; // 8 is number of columns in EMP table // Setting parameters for every column in EMP table // // EMPNO inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.Int32; inParArr[param].Value = empnoArr; inParArr[param++].Direction = ParameterDirection.Input; // ENAME inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.Varchar2; inParArr[param].Value = enameArr; inParArr[param++].Direction = ParameterDirection.Input; // JOB inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.Varchar2; inParArr[param].Value = jobArr; inParArr[param++].Direction = ParameterDirection.Input; // MGR inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.Int32; inParArr[param].Value = mgrArr; inParArr[param++].Direction = ParameterDirection.Input; // HIREDATE inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.TimeStamp; inParArr[param].Value = hiredateArr; inParArr[param++].Direction = ParameterDirection.Input; // SAL inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.Double; inParArr[param].Value = salArr; inParArr[param++].Direction = ParameterDirection.Input; // COMM inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.Varchar2; inParArr[param].Value = commArr; inParArr[param++].Direction = ParameterDirection.Input; // DEPTNO inParArr[param] = insert.CreateParameter(); inParArr[param].OracleDbType = OracleDbType.Int32; inParArr[param].Value = deptnoArr; inParArr[param++].Direction = ParameterDirection.Input; insert.Parameters.AddRange(inParArr); insert.ExecuteNonQuery(); } finally { insert.Dispose(); } // returning the highest employee number return(empnoArr[numOfEmps - 1]); }
public bool RefreshColumns(string schema) { OracleCommand cmd = new OracleCommand(ALL_TAB_COLUMNS_SCHEMA_SELECT, conn); // set up parameters OracleParameter ownerParam = cmd.CreateParameter(); ownerParam.ParameterName = "owner"; ownerParam.OracleDbType = OracleDbType.Char; ownerParam.Direction = System.Data.ParameterDirection.Input; ownerParam.Value = schema; cmd.Parameters.Add(ownerParam); // execute command OracleDataReader odr = cmd.ExecuteReader(); if (!odr.HasRows) return false; // purge old data purgeTableColumnsData(schema); while (odr.Read()) { TableColumn column = LoadColumn(odr); columns.Add(column); } return true; }
public IEnumerable <GLInitialRepo> GLReversalList() { try { var app = new AppSettings(); con = app.GetConnection(); DataSet ds = new DataSet(); OracleDataAdapter da = new OracleDataAdapter(); OracleCommand cmd = new OracleCommand(); OracleParameter param = cmd.CreateParameter(); cmd.CommandText = "SEL_INITIAL_ACTIVE"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = (OracleConnection)con; //OracleParameter ParamfromDate = new OracleParameter("p_fromDate", OracleDbType.Date, From_Date, ParameterDirection.Input); //cmd.Parameters.Add(ParamfromDate); //OracleParameter ParamtoDate = new OracleParameter("p_toDate", OracleDbType.Date, To_Date, ParameterDirection.Input); //cmd.Parameters.Add(ParamtoDate); param = cmd.Parameters.Add("p_result", OracleDbType.RefCursor); param.Direction = ParameterDirection.Output; da = new OracleDataAdapter(cmd); da.Fill(ds, "journal"); var eList = ds.Tables[0].AsEnumerable().Select(row => new GLInitialRepo { TID = row.Field <decimal>("TID"), Gl_Account_No = row.Field <string>("GL_ACCOUNT_NO"), Gl_Account_Name = row.Field <string>("GL_Account_Name"), //Debit_Gl_Balance = row.Field<decimal>("GL_Balance1") * -1, //Credit_Gl_No = row.Field<string>("CREDIT_GL_NO"), //Credit_Gl_Name = row.Field<string>("GL_Account_Name"), Gl_Balance = row.Field <decimal>("GL_Balance") * -1, //Narration = row.Field<string>("NARRATION"), Amount = row.Field <decimal>("AMOUNT"), Trans_Date = row.Field <DateTime>("TRANS_DATE"), Trans_Type = (row["TRANS_TYPE"]).ToString(), Scheme_Fund_Id = row.Field <string>("SCHEME_FUND_ID"), Fund = row.Field <string>("FUND_NAME"), Scheme = row.Field <string>("SCHEME_NAME"), }).ToList(); return(eList); } catch (Exception ex) { throw ex; } finally { if (con.State == ConnectionState.Open) { con.Close(); if (con != null) { con = null; } } } }
public bool Refresh(TableManager.Table table) { OracleCommand cmd = new OracleCommand(ALL_CONSTRAINTS_TABLE_SELECT, conn); cmd.BindByName = true; // set up parameters // schemaParam OracleParameter schemaParam = cmd.CreateParameter(); schemaParam.ParameterName = "owner"; schemaParam.OracleDbType = OracleDbType.Char; schemaParam.Direction = System.Data.ParameterDirection.Input; schemaParam.Value = table.Owner; cmd.Parameters.Add(schemaParam); // tableParam OracleParameter tableParam = cmd.CreateParameter(); tableParam.ParameterName = "table_name"; tableParam.OracleDbType = OracleDbType.Char; tableParam.Direction = System.Data.ParameterDirection.Input; tableParam.Value = table.Name; cmd.Parameters.Add(tableParam); // execute OracleDataReader odr = cmd.ExecuteReader(); if (!odr.HasRows) return false; // purge old data purgeConstraintData(table); while (odr.Read()) { ConstraintBase constraint = LoadConstraint(odr); constraints.Add(constraint); } return true; }
protected void LinkButton1_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(tb_qr_viv.Text) && string.IsNullOrEmpty(tb_contra_viv.Text)) { if (string.IsNullOrEmpty(tb_etiqueta.Text)) { if (string.IsNullOrEmpty(tb_entidad.Text) || string.IsNullOrEmpty(tb_municipio.Text) || string.IsNullOrEmpty(tb_localidad.Text) || string.IsNullOrEmpty(tb_calle.Text) || string.IsNullOrEmpty(tb_numext.Text) || string.IsNullOrEmpty(tb_numint.Text) || string.IsNullOrEmpty(tb_colonia.Text)) { Label17.Text = "Aviso"; Label10.Text = "No debe dejar los campos vacios para continuar"; programmaticModalPopup.Show(); } else { string oradb = ConfigurationManager.AppSettings["cai2020"]; OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = oradb.ToString(); try { conn.Open(); OracleCommand cmd1 = new OracleCommand(); cmd1.Connection = conn; cmd1.CommandText = "SELECT USUARIO,ACTIVO FROM CAP_INT_TH_CAT WHERE ACTIVO IS NULL "; cmd1.CommandType = CommandType.Text; OracleDataReader dr1 = cmd1.ExecuteReader(); if (dr1.Read()) { Session["qr_viv"] = dr1["USUARIO"].ToString(); string oradb4 = ConfigurationManager.AppSettings["cai2020"]; OracleConnection conn4 = new OracleConnection(); // C# conn4.ConnectionString = oradb4.ToString(); conn4.Open(); try { OracleCommand miComando = new OracleCommand(); miComando.Connection = conn4; miComando.CommandText = "update CAP_INT_TH_CAT set ACTIVO = '1' where USUARIO = :usr"; miComando.CommandType = CommandType.Text; miComando.Parameters.Add("usr", OracleDbType.NVarchar2); miComando.Parameters["usr"].Value = dr1["USUARIO"].ToString(); int k = miComando.ExecuteNonQuery(); miComando.Dispose(); } catch (Exception e1) { HttpContext.Current.Session["MensajeDeError"] = e1.ToString(); } finally { conn4.Dispose(); } string oradb41 = ConfigurationManager.AppSettings["cai2020"]; OracleConnection conn41 = new OracleConnection(); // C# conn41.ConnectionString = oradb41.ToString(); conn41.Open(); try { OracleCommand miComando1 = new OracleCommand(); miComando1.Connection = conn41; miComando1.CommandText = "insert into CAP_INT_TH_INGRESO_CAT (USUARIO,ENTIDAD,MUNICIPIO,LOCALIDAD,COLONIA,CALLE,NOINT,NOEXT,QR_CAT) values ('" + HttpContext.Current.Session["UserName"].ToString() + "',:entidad,:municipio,:localidad,:colonia,:calle,:noint,:noext,:qrcat)"; miComando1.CommandType = CommandType.Text; miComando1.Parameters.Add("entidad", OracleDbType.NVarchar2); miComando1.Parameters["entidad"].Value = tb_entidad.Text; miComando1.Parameters.Add("municipio", OracleDbType.NVarchar2); miComando1.Parameters["municipio"].Value = tb_municipio.Text; miComando1.Parameters.Add("localidad", OracleDbType.NVarchar2); miComando1.Parameters["localidad"].Value = tb_localidad.Text; miComando1.Parameters.Add("colonia", OracleDbType.NVarchar2); miComando1.Parameters["colonia"].Value = tb_colonia.Text; miComando1.Parameters.Add("calle", OracleDbType.NVarchar2); miComando1.Parameters["calle"].Value = tb_calle.Text; miComando1.Parameters.Add("noint", OracleDbType.NVarchar2); miComando1.Parameters["noint"].Value = tb_numext.Text; miComando1.Parameters.Add("noext", OracleDbType.NVarchar2); miComando1.Parameters["noext"].Value = tb_numint.Text; miComando1.Parameters.Add("qrcat", OracleDbType.NVarchar2); miComando1.Parameters["qrcat"].Value = dr1["USUARIO"].ToString(); int k1 = miComando1.ExecuteNonQuery(); miComando1.Dispose(); } catch (Exception e1) { HttpContext.Current.Session["MensajeDeError"] = e1.ToString(); } finally { conn41.Dispose(); } } dr1.Dispose(); cmd1.Dispose(); conn.Dispose(); Response.Redirect("CuestionarioVivienda.aspx"); } catch (Exception e1) { HttpContext.Current.Session["MensajeDeError"] = e1.ToString(); conn.Dispose(); } finally { } } } else { string oradb = ConfigurationManager.AppSettings["cai2020"]; OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = oradb.ToString(); int op1 = 0; try { conn.Open(); //EVALUAR SI YA SE TIENEN REGISTROS GUARDADOS LA LISTA OracleCommand cmd1 = new OracleCommand(); cmd1.Connection = conn; OracleParameter myParam1 = cmd1.CreateParameter(); myParam1.OracleDbType = OracleDbType.NVarchar2; myParam1.Value = tb_etiqueta.Text; myParam1.ParameterName = "usr"; cmd1.Parameters.Add(myParam1); cmd1.CommandText = "SELECT QR_VIV FROM CAP_INT_TH_LLENADO where QR_VIV = :usr"; cmd1.CommandType = CommandType.Text; OracleDataReader dr1 = cmd1.ExecuteReader(); if (dr1.Read()) { Label17.Text = "Aviso"; Label10.Text = "Esa etiqueta ya contiene cuestionario registrado"; programmaticModalPopup.Show(); } else { OracleCommand cmd2 = new OracleCommand(); cmd2.Connection = conn; OracleParameter myParam3 = cmd2.CreateParameter(); myParam3.OracleDbType = OracleDbType.NVarchar2; myParam3.Value = tb_etiqueta.Text; myParam3.ParameterName = "usr"; cmd2.Parameters.Add(myParam3); cmd2.CommandText = "SELECT USUARIO FROM CAP_INT_TH_ETIQUETA where USUARIO = :usr"; cmd2.CommandType = CommandType.Text; OracleDataReader dr2 = cmd2.ExecuteReader(); if (dr2.Read()) { Session["qr_viv"] = dr2["USUARIO"].ToString(); string oradb41 = ConfigurationManager.AppSettings["cai2020"]; OracleConnection conn41 = new OracleConnection(); // C# conn41.ConnectionString = oradb41.ToString(); conn41.Open(); try { OracleCommand miComando1 = new OracleCommand(); miComando1.Connection = conn41; miComando1.CommandText = "insert into CAP_INT_TH_INGRESO_CAT (USUARIO,ETIQUETA) values ('" + HttpContext.Current.Session["UserName"].ToString() + "',:etiqueta)"; miComando1.CommandType = CommandType.Text; miComando1.Parameters.Add("etiqueta", OracleDbType.NVarchar2); miComando1.Parameters["etiqueta"].Value = dr2["USUARIO"].ToString(); int k1 = miComando1.ExecuteNonQuery(); miComando1.Dispose(); } catch (Exception e1) { HttpContext.Current.Session["MensajeDeError"] = e1.ToString(); } finally { conn41.Dispose(); } op1 = 1; } else { Label17.Text = "Aviso"; Label10.Text = "Código de etiqueta no valido"; programmaticModalPopup.Show(); } dr2.Dispose(); cmd2.Dispose(); } dr1.Dispose(); cmd1.Dispose(); } catch (Exception e1) { HttpContext.Current.Session["MensajeDeError"] = e1.ToString(); } finally { conn.Dispose(); } if (op1 == 1) { Response.Redirect("CuestionarioVivienda.aspx"); } } } else { string oradb = ConfigurationManager.AppSettings["cai2020"]; OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = oradb.ToString(); int op = 0; try { conn.Open(); //EVALUAR SI YA SE TIENEN REGISTROS GUARDADOS LA LISTA OracleCommand cmd1 = new OracleCommand(); cmd1.Connection = conn; OracleParameter myParam1 = cmd1.CreateParameter(); myParam1.OracleDbType = OracleDbType.NVarchar2; myParam1.Value = tb_qr_viv.Text; myParam1.ParameterName = "usr"; cmd1.Parameters.Add(myParam1); cmd1.CommandText = "SELECT QR_VIV FROM CAP_INT_TH_LLENADO where QR_VIV = :usr"; cmd1.CommandType = CommandType.Text; OracleDataReader dr1 = cmd1.ExecuteReader(); if (dr1.Read()) { Label17.Text = "Aviso"; Label10.Text = "Esa clave de invitación ya contiene cuestionario registrado"; programmaticModalPopup.Show(); } else { OracleCommand cmd2 = new OracleCommand(); cmd2.Connection = conn; OracleParameter myParam3 = cmd2.CreateParameter(); myParam3.OracleDbType = OracleDbType.NVarchar2; myParam3.Value = tb_qr_viv.Text; myParam3.ParameterName = "usr"; cmd2.Parameters.Add(myParam3); OracleParameter myParam4 = cmd2.CreateParameter(); myParam4.OracleDbType = OracleDbType.NVarchar2; myParam4.Value = tb_contra_viv.Text; myParam4.ParameterName = "contra"; cmd2.Parameters.Add(myParam4); cmd2.CommandText = "SELECT USUARIO,CONTRASENA FROM CAP_INT_TC_USUARIO where USUARIO = :usr and CONTRASENA = :contra"; cmd2.CommandType = CommandType.Text; OracleDataReader dr2 = cmd2.ExecuteReader(); if (dr2.Read()) { Session["qr_viv"] = dr2["USUARIO"].ToString(); string oradb41 = ConfigurationManager.AppSettings["cai2020"]; OracleConnection conn41 = new OracleConnection(); // C# conn41.ConnectionString = oradb41.ToString(); conn41.Open(); try { OracleCommand miComando1 = new OracleCommand(); miComando1.Connection = conn41; miComando1.CommandText = "insert into CAP_INT_TH_INGRESO_CAT (USUARIO,QR_VIV) values ('" + HttpContext.Current.Session["UserName"].ToString() + "',:qrviv)"; miComando1.CommandType = CommandType.Text; miComando1.Parameters.Add("qrviv", OracleDbType.NVarchar2); miComando1.Parameters["qrviv"].Value = dr2["USUARIO"].ToString(); int k1 = miComando1.ExecuteNonQuery(); miComando1.Dispose(); } catch (Exception e1) { HttpContext.Current.Session["MensajeDeError"] = e1.ToString(); } finally { conn41.Dispose(); } op = 1; } else { Label17.Text = "Aviso"; Label10.Text = "Usuario o contraseña de invitación incorrectos"; programmaticModalPopup.Show(); } dr2.Dispose(); cmd2.Dispose(); } dr1.Dispose(); cmd1.Dispose(); } catch (Exception e1) { HttpContext.Current.Session["MensajeDeError"] = e1.ToString(); } finally { conn.Dispose(); } if (op == 1) { Response.Redirect("CuestionarioVivienda.aspx"); } } }
/* * Using PLSQL blocks with IN/OUT parameters * * adding new employee that reports to the PRESIDENT * has salary that is half of PRESIDENT's salary * and has title 'MANAGER' */ private int AddNewManager(int empPK, string empName, int deptNo) { int mgrNo = empPK + 10; int presidentId = 0; double presidentSal = 0; // Getting PRESIDENT's employee number and salary // PRESIDENT does not report to anyone, thus MGR fieled is NULL string selectStmt = "begin " + " select empno, sal " + " into :1, :2 " + " from emp " + " where mgr is null " + " and rownum < 2; " + // just in case that for some reason there are more than one PRESIDENT "end;"; OracleCommand select = null; try { select = new OracleCommand(selectStmt, conn); select.CommandType = CommandType.Text; OracleParameter[] outParArr = new OracleParameter[2]; // EMPNO outParArr[0] = select.CreateParameter(); outParArr[0].OracleDbType = OracleDbType.Int32; outParArr[0].Direction = ParameterDirection.Output; // SALARY outParArr[1] = select.CreateParameter(); outParArr[1].OracleDbType = OracleDbType.Double; outParArr[1].Direction = ParameterDirection.Output; select.Parameters.AddRange(outParArr); select.ExecuteNonQuery(); presidentId = Convert.ToInt32(outParArr[0].Value.ToString()); presidentSal = Convert.ToDouble(outParArr[1].Value.ToString()); } finally { select.Dispose(); } // Adding new MANAGER to new depatment string insertStmt = "insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) " + "values (:1, :2, 'MANAGER', :3, sysdate, :4, null, :5)"; double salary = Math.Round(presidentSal / 2); OracleCommand insert = null; try { insert = new OracleCommand(insertStmt, conn); insert.CommandType = CommandType.Text; OracleParameter[] inParArr = new OracleParameter[5]; // EMPNO inParArr[0] = insert.CreateParameter(); inParArr[0].OracleDbType = OracleDbType.Int32; inParArr[0].Value = mgrNo; inParArr[0].Direction = ParameterDirection.Input; // ENAME inParArr[1] = insert.CreateParameter(); inParArr[1].OracleDbType = OracleDbType.Varchar2; inParArr[1].Value = empName; inParArr[1].Direction = ParameterDirection.Input; // MGR inParArr[2] = insert.CreateParameter(); inParArr[2].OracleDbType = OracleDbType.Int32; inParArr[2].Value = presidentId; inParArr[2].Direction = ParameterDirection.Input; // SAL inParArr[3] = insert.CreateParameter(); inParArr[3].OracleDbType = OracleDbType.Double; inParArr[3].Value = salary; inParArr[3].Direction = ParameterDirection.Input; // DEPTNO inParArr[4] = insert.CreateParameter(); inParArr[4].OracleDbType = OracleDbType.Int32; inParArr[4].Value = deptNo; inParArr[4].Direction = ParameterDirection.Input; if (dbg) { PrintLine("AddNewManager --> inserting values = " + mgrNo + ", " + empName + ", " + presidentId + ", " + salary + ", " + deptNo); } insert.Parameters.AddRange(inParArr); insert.ExecuteNonQuery(); } finally { insert.Dispose(); } return(mgrNo); }
internal static OracleParameter AddSimpleParameter(this OracleCommand command, string parameterName, object value, string databaseType = null, int?size = null) { var parameter = command.CreateParameter(); parameter.ParameterName = parameterName; parameter.Direction = ParameterDirection.InputOutput; parameter.Value = Equals(value, String.Empty) ? null : value; switch (databaseType) { case TerminalValues.Char: parameter.OracleDbType = OracleDbType.Char; parameter.Size = MaximumCharacterValueSize; break; case TerminalValues.Clob: parameter.OracleDbType = OracleDbType.Clob; break; case TerminalValues.Timestamp: parameter.OracleDbType = OracleDbType.TimeStamp; break; case TerminalValues.Date: parameter.OracleDbType = OracleDbType.Date; break; case TerminalValues.Number: parameter.OracleDbType = OracleDbType.Decimal; break; case TerminalValues.NChar: parameter.OracleDbType = OracleDbType.NChar; parameter.Size = MaximumCharacterValueSize; break; case OracleBindVariable.DataTypeUnicodeClob: parameter.OracleDbType = OracleDbType.NClob; break; case TerminalValues.NVarchar2: parameter.OracleDbType = OracleDbType.NVarchar2; parameter.Size = MaximumValueSize / 2; break; case TerminalValues.Varchar2: parameter.OracleDbType = OracleDbType.Varchar2; parameter.Size = MaximumValueSize; break; case OracleBindVariable.DataTypeRefCursor: parameter.OracleDbType = OracleDbType.RefCursor; break; case TerminalValues.Raw: switch (value) { case byte[] byteArray: parameter.Value = byteArray; break; case OracleBinary oracleBinary: parameter.Value = oracleBinary.Value; break; default: parameter.Value = HexStringToByteArray((string)value); break; } parameter.OracleDbType = OracleDbType.Raw; parameter.Size = MaximumValueSize; break; case TerminalValues.Blob: parameter.Value = value as byte[] ?? HexStringToByteArray((string)value); parameter.OracleDbType = OracleDbType.Blob; break; } if (size.HasValue) { parameter.Size = size.Value; } command.Parameters.Add(parameter); return(parameter); }
public bool Refresh(string schema) { OracleCommand cmd = new OracleCommand(ALL_CONSTRAINTS_SCHEMA_SELECT, conn); cmd.BindByName = true; // set up parameters OracleParameter schemaParam = cmd.CreateParameter(); schemaParam.ParameterName = "owner"; schemaParam.OracleDbType = OracleDbType.Char; schemaParam.Direction = System.Data.ParameterDirection.Input; schemaParam.Value = schema; // add it cmd.Parameters.Add(schemaParam); // execute OracleDataReader odr = cmd.ExecuteReader(); if (!odr.HasRows) return false; // purge old data purgeConstraintData(schema); while (odr.Read()) { ConstraintBase constraint = LoadConstraint(odr); constraints.Add(constraint); } return true; }
static void Main(string[] args) { string xml = "<Employees><Employee number='1' firstname='john' lastname='smith'></Employee><Employee number='2' firstname='john1' lastname='smith1'></Employee><Employee number='3' firstname='john2' lastname='smith2'></Employee></Employees>"; OracleConnection oc = new OracleConnection("Data Source=dev1;User ID=system;Password=oracle;"); oc.Open(); OracleCommand cmd = oc.CreateCommand(); string sql = @" SELECT xt.* FROM XMLTABLE ( '/Employees/Employee' PASSING :l_xml COLUMNS empno VARCHAR2(4) PATH '@number', firstname VARCHAR2(10) PATH '@firstname', lastname VARCHAR2(9) PATH '@lastname' ) xt join employees e on xt.empno = e.empno"; cmd.CommandText = sql; OracleParameter p = cmd.CreateParameter(); p.ParameterName = "l_xml"; OracleXmlType t = new OracleXmlType(oc, xml); p.Value = t; /*p.OracleDbType = OracleDbType.Clob; * OracleClob c = new OracleClob(oc); * c.Read(xml.ToCharArray(), 0, xml.Length); * p.Value = c;*/ cmd.Parameters.Add(p); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader.HasRows) { object[] values = new object[reader.FieldCount]; reader.GetValues(values); Console.WriteLine ( string.Format ( "empno='{0}' lastname={1} firstname='{2}'", reader.GetDecimal(reader.GetOrdinal("empno")), reader.GetString(reader.GetOrdinal("lastname")), reader.GetString(reader.GetOrdinal("firstname")) ) ); } } //c.Close(); reader.Close(); oc.Close(); }