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();
}